by Alan German

Another of those O'Reilly books “that should have been in the box”, this one is written by Matthew MacDonald for Pogue Press, and offers insight into the mysteries of the latest version of Microsoft's Excel spreadsheet program.

The introduction suggests that users of prior versions of the software (notably Excel 2002 or 2003) should seek help from “Excel 2003: The Missing Manual”. However, I would suggest that the basics, and many of the advanced features of Excel, are adequately covered in the current work. The main thing in Excel 2007 that's different is a set of features of the user interface (e.g. the Ribbon, Office Menu, Quick Access Toolbar, and Save-as-PDF), many of which are just icing on the cake rather than must-have functions. So, if you use Excel (or, like me, a clone such as Open Office Calc), you will find lots of extremely useful how-to information in this book.

The text consists of a substantial 831 pages plus a colophon (a new word for me - “a tail-piece in old books... giving information now placed on the title-page” - The Concise Oxford Dictionary). Excel 2007 is organized in eight parts – Worksheet Basics, Formulas and Functions, Organizing Worksheets, Charts and Graphics, Advanced Data Analysis, Sharing Data with the Rest of the World, Programming Excel, and an Appendix.

Each part is subsequently divided into chapters so that Worksheet Basics consists of the first seven chapters of the book and includes basic information on “Creating and Navigating Worksheets”, to more advanced features such as “Smart Formatting Tricks” that will, for example, illustrate the magic behind conditional formatting of specific cells. The chapter on “Viewing and Printing Worksheets” provides information on the very useful Window-Freeze command that is often used to keep the column titles at the top of the screen while the data below can be scrolled up and down, and there are several pages of must-know information on print settings in order to produce sophisticated printouts of your spreadsheets.

The section on formulas and functions includes details of how to specify an individual cell or a range of cells in a calculation, including absolute and partially fixed cell references that are frequently useful when copying formulae within a worksheet or even between spreadsheets. This part of the book is worth the price of admission by itself since it provides a valuable reference to the capabilities of the myriad of functions contained in Excel. This is information that is quite difficult to obtain from the program's help system, especially if you don't know that a specific function exists!

So, here you will find details of the COUNTA function that will, for example, help you total the number of non-blank cells in a column, or the COUNTIF function that you could use to count the number of cells containing a particular value. And, who could survive without the ATANH trigonometrical function; being able to use IRR to calculate the internal rate of return based on the cash flow of your business; or combining text strings with CONCATENATE? If your formula produces an outrageous result, did you know that you can use the Evaluate Formula tool to do some troubleshooting by processing the formula one step at a time, or perhaps using Excel's tracing feature to graphically show how certain cells are linked?

Part three of the book looks at tables and list management, with such topics as searching, sorting, filtering and grouping data, while part four shows how to create and modify various types of charts to pictorially represent the information in your spreadsheet, how to add clip art and even photographs to provide additional customization. Advanced Data Analysis, in part five of the book, shows how to create summary reports of complex spreadsheet data, how to perform goal seeking calculations, or how to use Solver for iterative computations in situations where a simple formula cannot provide the solution being sought. A particularly powerful analytical technique, where summary information is required on large datasets, is the use of pivot tables which the book's author describes as “a hidden gem in Excel”. Technically known as cross-tabulation, this technique provides a means summarizing complex data where multiple relationships exist.

As an example, after reading this section of the book, it was evident to me that a pivot table would readily produce a breakdown of the number of members of a national organization by geographic location. While this could be (and was previously!) obtained by using a set of COUNTIF functions, one for each jurisdiction, this is a cumbersome process when applied to thirteen provinces and territories across Canada, plus a number of foreign countries. Using a pivot table, it's simply a matter of creating a new column where, for each membership record, the cell in the new column contains the number “1”. The pivot table is then created using a wizard, detailed instructions for which, including a series of screen shots, are provided in the book. It's merely necessary to specify cross-tabulation of the column of data containing the geographical location of the members with the new column of 1's. The resulting pivot table instantly shows the total number of members in each geographical location by automatically summing the number of members in Ontario, Quebec, etc. Easy!

Part six of the book, Sharing Data with the Rest of the World, has information on data protection, working collaboratively with other users, issuing queries from Excel to database files, using XML, and exchanging data over the Internet. The discussion includes how to protect individual cells against invalid data entry, such as locking a specific cell that contains a fixed numerical value, and even how to create pop-up warning messages if things go awry. There are also details of the means to move data across various applications, such as embedding objects in Windows' programs, and creating comma-delimited text files for data transfer.

Macro programming forms the topic for part seven, everything from automatically recording a set of keystrokes as a macro, to the essentials of Visual Basic for Applications (VBA) programming. The text explains how to play back a stored macro, and even how to assign the macro to a graphical button on the spreadsheet to provide easy access any time the same set of commands is required to be applied to the spreadsheet. Twenty-five pages are dedicated to VBA, including use of the editor, coding, program objects, and debugging. It's probably enough to get you started in Excel, but if you are serious about VBA, I suspect that you will need a whole manual on just this topic (and, of course, O'Reilly provides a choice between several such texts!)

The final part of the book is an appendix discussing customization of the Quick Access Toolbar (QAT). This is a topic that is specific to Excel 2007. The author notes that, in contrast to earlier versions of the program, Excel 2007 restricts the end user's options to modify the menu structures to just making changes to the QAT. So, if you use Excel 2007, and are desperate to customize the system, this chapter is for you.

If you are new to Excel, and especially to Excel 2007, this book will provide a lot of basic information to help you get to know the wonders of spreadsheet use. For intermediate users, there is a host of tips and tricks to be found between the covers. And, even advanced users may find some gems of interest. There must be something that you don't know about Excel in over 800 pages of text!

Originally published: October, 2007

top of page