While every article about application development impresses upon us the importance of separating the data, logic and presentation layers, MS Excel gleefully mixes the three. While this gets sneers from most developers, it is precisely the reason that so many people use it and love it and get themselves into all kinds of trouble with it... and then ask me to help. So I was very excited to get my copy of Excel Hacks today. Among other things, it has a lot of tips for separating the data from the formulas and the data from the presentation, making a complex Excel spreadsheet more maintainable. Other things I was glad to see:
- Chapter devoted to the SpreadsheetML, or the xml/xslt format used to describe spreadsheets. This facilitates generating spreadsheets using PHP and Java and other technologies that have nothing to do with Excel
- Lots of hints for overcoming limits imposed by Excel: For example, override the number of undos allowed and override the limit to criteria for conditional formatting of cells.
- Lots of easy to understand VBA code
- Ways to recover data from corrupt workbooks
- Long chapter on charts with some neato ideas for custom charts. I get asked a lot of questions about Excel charts and I never really learned more than basic charting skills.
- And a question I have tried to solve several times, never getting a graceful solution: How to fill in empty cells with values
- Solutions to pivot tables
I am working my way through each of the 100 hacks and in almost every one, I am saying to myself, "Gee, I didn't know excel could do that" In summary, the O'Reilly hacks series has produced some excellent books and this one is one of the best. It is clear, concise, error free, and doesn't assume you have the most recent version of Excel. It is full of nuggets such as the fact that Excel purposely doesn't recognize that the year 1900 was a leap year (link blames it all on Lotus!). Who knows, you might encounter that some day.
10:56:46 AM 
|
|
