This chapter is from the book
This chapter is from the book
This chapter is from the book
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.
Case Study: Producing Revenue by Line of Business Report
You have been asked to produce a report that provides a comprehensive look at revenue by product line. This analysis needs to include revenue dollars by product line for each market, the percent of revenue that each product line represents within the markets, and the percent total company dollars that each market represents within the product lines. Here are the steps to follow:
- Place your cursor inside your data source. Choose the Insert ribbon and then click the Pivot Table icon.
- When the Create PivotTable dialog box appears, simply choose OK. A new worksheet is created with the beginnings of a pivot table report and the PivotTable Field List, as shown in Figure 3.43.
Figure 3.43 After clicking OK, you get this blank pivot table report.
- Click the Market field in the fields section of the PivotTable Field List to automatically move Market to the Row Labels drop zone. Then drag the Product Line field into the Column Labels drop zone, as shown in Figure 3.44.
Figure 3.44 Setting up the row and column fields.
- Drag the Revenue field into the Values drop zone three times to create three separate revenue data items. There is a new Sum Values item in the Column Labels drop zone. Move this to the Row Labels drop zone, as shown in Figure 3.45.
Figure 3.45 Having three copies of Sum of Revenue doesn't look useful...yet.
- In the Values drop zone, click on the first Sum of Revenue field and select Field Settings, as shown in Figure 3.46.
Figure 3.46 Use the drop-down in the Values drop zone to access Field Settings for a particular data field.
- Change the name of the data field to Total Revenue, as shown in Figure 3.47. Then click the Number Format button to open the Format Cells dialog box. Change the format of the data item to Currency and then close both dialog boxes by clicking OK.
Figure 3.47 Set up the first Revenue item as normal.
- Open the Data Field Settings for the Sum of Revenue2 field.
- Change the name of the data field to Percent of Market. On the Show Values As tab, in the Show Values As drop-down, select the % of Row option. After you do that, choose the Number Format button to open the Format Cells dialog box. Change the format of the cells to Percentage, as shown in Figure 3.48. Close both dialog boxes by clicking OK twice.
Figure 3.48 Use % of Row to get the Percentage of Market.
- Open the Date Field Settings dialog box for Sum of Revenue3.
- Change the name of the data field to Percent of Company. On the Show Values As tab, choose the % of Column option. After you do that, choose the Number button to open the Format Cells dialog box. Change the format of the cells to Percentage and then close both dialog boxes by clicking OK.
)
)
)
)
)
)
As shown in Figure 3.49, your product line analysis is complete! You have the Total Revenue field, which gives you the revenue dollars by product line for each market. Then you have the Percent of Market field, which shows you the percent of revenue that each line represents within the markets. Finally, you have the Percent of Company field, which shows line of business revenue as a percentage of total company dollars.
)
Figure 3.49 The completed report shows three calculations for every data cell.