Charts, advanced formulas
For the intermediate spreadsheet, I added two more products to my fictional company’s line, and then created a summary spreadsheet, which consolidated sales for all three products for all representatives by month. From that data, I created a stacked bar chart. Finally, I added some formulas to grab the names of the highest-producing month and salesperson, as well as a formula to display the name of the top-selling product.
Creating extra worksheets was trivial in both programs, as was creating the consolidated worksheet with links to the others. The hardest part was adjusting to Numbers’ use of multiple tables on one page—it’s a very different approach than Excel’s.
Creating charts was simple, too: in both programs, I highlighted the range I wanted to graph and clicked on a couple of buttons, and I had a basic chart. Of the two, I found Excel’s raw chart to be more usable. First, the legend is attached to the chart, so they move as one object; in Numbers, the legend is a separate object, and I often forgot to move it when I moved the chart. Second, Excel scaled the y-axis more intelligently; Numbers tried to use noninteger values; Excel didn’t. Numbers, on the other hand, includes a default chart title, and makes it easier to see the data associated with the chart when you select it—the relevant range is highlighted in colors that match the colors used for the data points in the chart.
To pull the month name and salesperson’s name for the highest-production month, I had to build formulas that used the OFFSET(), MATCH(), and MAX() functions. Excel is superior at helping you enter complex formulas—a tooltip shows up below the cell, listing each function’s variables. In Numbers, no tooltips appear; I could have used the Insert Function feature, but this puts the descriptions of variables directly in the cell, and I would have had to manually overwrite them as I entered a formula.
More frustrating, however, is the inability to use the keyboard when entering formulas in Numbers. In Excel, I could use an arrow key and the shift key to rapidly select ranges while entering a formula. In Numbers, I needed to use the mouse, which really slowed down the process.
Finally, Numbers handles the Undo command in a confusing manner. If I changed a cell’s value and then pressed Command-Z, the cell would actually go blank. I needed to press Command-Z again to return to the original value. Before I figured this out, I wound up with blank cells in my worksheet on a few occasions.
The winner: Excel was much better for adding charts and formulas—the ability to use the keyboard, in particular, saved me much time in entering complex formulas.
Forms, conditional formatting
Lastly, using a simple design and each program’s tools for accepting user input, I created a sheet that would allow for easy entry of product and sales representatives’ names, along with the starting month for the sales report. The values in this input page are transferred to the tracking worksheet via formulas. To define the starting month, I created a pop-up menu containing Jan, Feb, and so forth, so that users wouldn’t make data-entry errors.
Translating this input into a date form that could then be used to calculate the column headers required a bit of thinking in both Numbers and Pages. That’s because I wanted the pop-up menu to display the month names, but I needed to enter an actual date value into my sheet, based on the user’s response to that pop-up.
So in Excel, I used a simple DATE() function with the returned value as the month number. In Numbers, though, I had to add a lookup table and use the VLOOKUP() function to convert the month name into a number usable in the DATE() formula.
I also thought about making it possible for an inexperienced user to input individual sales results in this sheet: a user would pick a sales representative’s name, the month, and the product line from a series of pop-up menus, and then enter the sales result for that month; the input sheet would copy that figure to the proper spot on the supporting worksheet.
In Excel 2004, I could have done all of this relatively easily with a macro. Neither Excel 2008 nor Numbers has macro support, however. Excel 2008 does have AppleScript support, but AppleScripts cannot be assigned to buttons on a worksheet, as macros could in Excel 2004; instead, they must be run from the Scripts menu. Additionally, building this functionality in AppleScript requires much more programming than do Excel 2004’s macro tools—because you can’t record an AppleScript as you could a macro. Numbers, lacking both macros and AppleScript support, doesn’t seem to have any way of creating such a function.
Given these problems in both programs, I didn’t pursue this avenue any further. It’s disappointing that two much newer programs have more difficulty with this relatively simple task than Excel 2004 does.
Next, I wanted to highlight the absolute best sales month for the combined product line. In Excel, I could do this with a conditional format set up to check if a given cell in the range is equal to the maximum value within that range. The end result makes it easy to spot the top month and representative—and if there’s a tie, Excel will automatically highlight all top results. In Numbers, though, conditional formats can’t contain calculations, so it was impossible to achieve the same result. A formula in a separate cell sort of worked, but it couldn’t show more than one value in the event of a tie.
Finally, I did a fair amount of work tweaking the look of the spreadsheet, to get it ready to present to the boss. Numbers has the edge thanks to its abilities with gradients, transparency, and cell backgrounds, though Excel 2008 is much better than its predecessor. Excel gets the overall win, however, due to its ability to create custom number and date formats—something you can’t do in Numbers.
The final word
In general, I found it much simpler to work with Excel in all phases of my sample project. Although Numbers has some stunning templates, its shortcomings make it more difficult to use when you leave template-land. Those failings include its inability to simply show a formula’s variables as you enter it; its lack of keyboard support in selecting ranges when you’re entering formulas; its lack of custom number and date formats; and its limited conditional formatting abilities. There’s no doubt that I can create a prettier spreadsheet in Numbers, but Excel offers more formulas, features, and tools to help with actually building the spreadsheet.
Next page: Spreadsheet Alternatives