Using Running Total Options
So far, every pivot table created has used the Normal option. When you want to create running totals or compare an item to another item, you have eight choices other than Normal.
The nine options are on the second tab of the Data Field Settings dialog box. To access them, follow these steps:
- Select a cell in the values area of your pivot table. Or, select the Sum of Revenue cell.
- On the Options ribbon, click the Field Settings icon in the Active Group field.
- Click the Show Values As tab in the Data Field Settings dialog box.
Initially, the Show Values As drop-down is set to Normal, and the Base Field and Base Item list boxes are grayed out, as shown in Figure 3.34.
Figure 3.34 Access the second tab of the Data Field Settings dialog box to see the running total options.
The capability to create custom calculations is another example of the unique flexibility of pivot table reports. With the Show Data As setting, you can change the calculation for a particular data field to be based on other cells in the values area.
When you click the Show Values As drop-down, you have eight choices other than Normal. The choices are
- % of Row—Shows percentages that total across the pivot table to 100%.
- % of Column—Shows percentages that total up and down the pivot table to 100%.
- % of Total—Shows percentages such that all the detail cells in the pivot table total to 100%.
- Difference From—Shows the difference of one item compared to another item or to the previous item.
- % of—Expresses the values for one item as a percentage of another item.
- % Difference From—Expresses the percentage change from one item to another item.
- Running Total In—Calculates a running total.
- Index—Calculates the relative importance of items.
The following sections illustrate a number of these options.
Display Change from Year to Year with Difference From
Companies always want to know how they are doing this month compared to last month. Or, if their business is seasonal, they want to know how they are doing this month versus the same month of last year.
To set up such a report, double-click the Sum of Revenue field and click the Show Values As tab. In the Show Data As drop-down list, select Difference From. Because you want to compare one year to another, select Years from the Base Field option. In the Base Item field are several viable options. If you always want to compare one year to the prior year, select the (Previous) option, as shown in Figure 3.35. If you have several years' worth of data and want to always compare to a base year of 2007, you could select 2007.
Figure 3.35 The Difference From option allows you to compare two different time periods.
Figure 3.35 shows both the dialog box settings and the report that results from the settings. The report shows that January 2008 revenue was $55K higher than the same month in 2007.
Compare One Year to a Prior Year with % Difference From
The % Difference From option is similar to Difference From. This option displays the change as a percentage of the base item. In Figure 3.36, the report shows 2008 as a percentage change from 2007.
Figure 3.36 The % Difference From option shows that revenue from January 2008 is up 8.02% over January 2007.
Track YTD Numbers with Running Total In
If you need to compare a year-to-date (YTD) total revenue by month, you can do so with the Running Total In option. In Figure 3.37, the Revenue field is set up to show a Running Total In with a base field of Invoice Date. With this report, you can see that the company earned a total of $2.6 million through March 2007.
Figure 3.37 The Running Total In option is great for calculating YTD totals.
Determine How Much Each Line of Business Contributes to the Total
The head of the company is often interested in what percentage of the revenue each division of the company is contributing. You can use the % of Row option, as in Figure 3.38, to show such a report. Each row totals to 100%. You can see that Maintenance contributed 70.97% of the revenue in February, but only 59.82% in December.
Figure 3.38 The % of Row option produces percentages that total to 100% in each row.
Create Seasonality Reports
A seasonality report is great for seeing the seasonality of your business. The % of Column option produces percentages that total to 100% in each column. Figure 3.39 shows a report in which Jan+Feb+Mar+...+Dec add to 100% for each year.
Figure 3.39 The % of Column option produces percentages that total to 100% in each column. This option is great for measuring seasonality.
Measure Percentage for Two Fields with % of Total
The option % of Total can be used for a myriad of reports. Figure 3.40 shows a report by Region and Product Line. The values in each cell show the percentage of revenue contribution from that region and line. Cell F8 shows that sales from Maintenance account for 66.53% of the total revenue. The South region's sales of Maintenance account for 21.95% of total sales.
Figure 3.40 The % of Total option produces a report in which every cell is a percentage of total sales. The manager of the South Region Maintenance department can use this report to explain why his department should get a raise this year.
Compare One Line to Another Line Using % Of
The % Of option allows you to compare one item to another item. This comparison might be relevant if you believe that housekeeping and landscaping should be related. You can set up a pivot table that compares each product line to landscaping revenue. The result is shown in Figure 3.41.
Figure 3.41 This report is created using the % Of option with Landscaping as the base item.
Track Relative Importance with the Index Option
The final option, Index, creates a fairly obscure calculation. Microsoft claims that this calculation describes the relative importance of a cell within a column.
Look at the normal data at the top of Figure 3.42. To calculate the index for Georgia Peaches, Excel first calculates Georgia Peaches x Grand Total Sales. This would be $180 x $848. Next, Excel calculates Georgia Sales x Peach Sales. This would be $210 x $290. It then divides the first result by the second result to come up with a relative importance index of 2.51.
Figure 3.42 Using the Index function, Excel shows that peach sales are more important in Georgia than in Tennessee.
The index report is shown at the bottom of Figure 3.42. Excel explains that peaches are more important to Georgia (with an index of 2.51) than they are to California (with an index of 0.49).
Even though Georgia sold more apples than Tennessee, apples are more important to Tennessee (index of 1.51) than to Georgia (index of 0.34). Relatively, an apple shortage will cause more problems in Tennessee than in Georgia.