■ The report contains blank cells instead of zeroes. In Figure 12.10, customer BCD Ltd bought only from the Western region. Excel produces a pivot table where the sales values for Central and East are blank instead of zero. This is simply bad form. Excel experts rely on being able to "ride the range," using the End and arrow keys. Blank cells ruin this ability.

■ The title is boring. I've never tried to pass a report off to a manager with the title of "Sum of Revenue." It is an annoying title.

■ Some captions are extraneous. The word "Region" floating in cell L2 of Figure 12.10 really does not belong in a report.

■ The default alphabetical sort order is rarely useful. Product line managers are going to want the top customers at the top of the list. It would be helpful to have the report sorted in descending order by revenue. The default region sequence of Central, East, West does not correspond to how those regions actually appear on a map. It would be helpful to re-sequence them as West, Central, East.

■ The borders are ugly. Excel draws in a myriad of borders that really make the report look awful.

■ The default number format is General. It would be better to set this up as data with commas to serve as thousands separators or perhaps even data in thousands or millions.

■ Pivot tables offer no intelligent page break logic. If you want to be able to produce one report for each product manager, there is no fast method for indicating that each product should be on a new page.

■ Because of the page break problem, you may find it is easier to do away with the pivot table's subtotal rows and have the Subtotal method add subtotal rows with page breaks. You need a way to turn off the pivot table subtotal rows offered for Product in Figure 12.10. These show up automatically whenever you have two or more row fields. If you would happen to have four row fields, you would want to turn off the automatic subtotals for the three outermost row fields.

Even with all these problems in default pivot tables, they are still the way to go. Each complaint can be overcome, either by using special settings within the pivot table, or by entering a few lines of code after the pivot table is created and then copied to a regular dataset.

0 0

Post a comment