247
Creating a Report Showing Revenue by Product
Here are just a few of the annoyances that most pivot tables present in their default state:
The Outline view is horrible. In Figure 11.8, the value Cleaning & Housekeeping
Services appears in the product column only once and is followed by 12 blank cells.
This is the worst feature of pivot tables, and there is absolutely no way to correct it.
Although humans can understand that this entire section is for Cleaning sales, it is radi-
cally confusing if your Cleaning section spills to a second or third page. Page 2 starts
without any indication that the report is for Cleaning sales. If you intend to repurpose
the data, you need the Cleaning sales value to be on every row.
The report contains blank cells instead of zeros. In Figure 11.8, the Florida market had
no cleaning sales in 2006. Excel produces a pivot table where cell T9 is 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. Most people would agree that Sum of Revenue is an annoying title.
Some captions are extraneous. Invoice Date floating in cell T2 of Figure 11.8 really
does not belong in a report.
The default alphabetical sort order is rarely useful. Product line managers are going to
want the top markets at the top of the list. It would be helpful to have the report sorted
in descending order by revenue.
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 Line of Business manager, there is no fast method for indicating that
each product should be on a new page.
11
Figure 11.8
Use the power of the
pivot table to get the
summarized data,but
then use your own com-
mon sense in formatting
the report.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 247
To Next Page
Loading...
Need help?
General
