How to use the pivot table "Show Values As" options, to create custom calculations such as running total or % of column.

Use custom calculations (Show Values As) in a pivot table, to compare each amount to other amounts.

For example, show each Customer's total as a percentage of the grand total.

Use the % of Grand Total custom calculation to compare each value to the overall total.

In this example, the pivot table has Item in the Row area, Region in the Column area, and Units in the Values area. The total will be changed to a custom calculation, to show the percentage for each region's sales of an item, compared to the Sales Grand Total for all Items.

- Right-click one of the Units value cells, and click
*Show Values As* - Click
*% of Grand Total* - The field changes, to show the percentage that each item's sales per region contribute to the item's overall sales. For example, about 10% of the overall sales are for Binders in the East region.

NOTE: You can change the field heading, e.g. % Sales, to make the data
easier to understand.** ** Top

In this example, the pivot table has Item in the Rows area, Region in the Columns area, and Units in the Values area. Follow these steps, to show the percentage of sales for each item, within each Region column.

- Right-click one of the Units value cells, and click
*Show Values As* - Click
*% of Column Total* - The field changes, to show the percentage of sales for each item, within each Region column. For example, Pens were 47% of the overall sales in the East region. Top

In this example, the pivot table has Item in the Rows area, Region in the Columns area, and Units in the Values area. Follow these steps, to show the percentage of sales for each region, across the each Item row.

- Right-click one of the Units value cells, and click
*Show Values As* - Click
*% of Row Total* - The field changes, to show the percentage of sales for each region, across the each Item row. For example, 46% of the Desk sales are in the West region.

Note: You can change the field heading, e.g. % Sales, to make the data easier to understand. Top

The % Of custom calculation lets you compare all amounts to a specific amount. The pivot table has Item in the Row area, Region in the Column area, and Units in the Values area. We'll see two variations on the % Of option.

NOTE: The Base field will always show as 100%, because it is being compared to itself.

In the first example, each region's item sales will be compared to the Central region's sales of the same item, as a percentage.

Follow these steps to change Units to a custom calculation, using the % Of option.

- Right-click one of the cells in the Values area, and click
*Show Values As* - Click
*% Of...* - From the
*Base field*list, choose**Region** - From the
*Base item*list, choose*Central* - Click the OK button

The pivot table changes, to show each item's sales as percent of the Central region's total for that item. For example, there were 12 desks sold in the Central region, and 25 desks sold in the East region. That's about twice as many desk sales, and the % Of amount shows as 208%.

In the second example, each item's sales will be compared to the Binder sales in the same region, as a percentage.

Follow these steps to change Units to a custom calculation, using the % Of option.

- Right-click one of the cells in the Values area, and click
*Show Values As* - Click
*% Of...* - From the
*Base field*list, choose**Item** - From the
*Base item*list, choose*Binders* - Click the OK button

The pivot table changes, to show each item's sales as percent of the Binder sales in that region, or the grand total. For example, in the Central region, there were 12 desks sold and 103 Binders sold. The % Of amount for Central Desk shows as 12%.

To show the percent of a subtotal, use the following options (Excel 2010 and later):

- % of Parent Row Total - use this if the parent field is in the Row area
- % of Parent Column Total - use this if the parent field is in the Column area
- % of Parent Total -

For example, what % of binders sold were red? What % of January sales were Pens?

Watch this video to see the steps, and the written instructions are below the video.

If a parent field is in the Rows area, use the % of Parent Row Total option to show each item's percentage of its parent field's subtotal.

In this example, the pivot table has Item and Colour in the Row area, Month in the Column area, and Units in the Values area. Follow these steps, to show the percentage for each colour's sales, compared to the item's total, in each month.

- Right-click one of the Units value cells, and click
*Show Values As* - Click
*% of Parent Row Total* - The field changes, to show the percentage for each colour's sales,
compared to the item's total, in each month.

If a parent field is in the Columns area, use the % of Parent Column Total option to show each item's percentage of its parent field's subtotal.

In this example, the pivot table has Colour in the Row area, Month and Item in the Column area, and Units in the Values area.

Follow these steps to show the percentage for each item's sales, compared to the month's total, for each colour.

- Right-click one of the Units value cells, and click
*Show Values As* - Click
*% of Parent Total* - The field changes, to show the percentage for each item's sales, compared to the month's total, for each colour. For example, 40 Brown items were sold in January, and 38% (15) of those were Desks.

The % of Parent Total option allows you to select a base field for the calculations. Then, the percentages are based on the row totals for that field, instead of using each field's immediate parent.

Follow these steps to show the percentage for each item's sales, compared to the month's total, for each colour.

- Right-click one of the Units value cells, and click
*Show Values As* - Click
*% of Parent Total* - From the
*Base field*list, choose**Item** - The field changes, to show the percentage of sales, compared to the item's total, for each region and each colour.

For example, 28 Blue Binders were sold in the East region. That is 10% of the total number of Binders sold (289).

The % Parent Row calculation is also shown, to highlight the differences. For the Colour field, instead of showing the percent of the immediate parent (Region), the % of Parent Total column shows the percent of the selected Base Field -- Item. The orange cells in each column add up to 100%.

Watch this short video to see the steps for creating a "Difference From" custom calculation. Written instructions are below the video.

Use the "Difference From" custom calculation to subtract one pivot table value from another, and show the result.

In this example, each region's sales is compared to the previous date's sales. Here is the pivot table showing the total units sold on each date.

To change the total to a Difference From calculation, follow these steps:

- Right-click one of the Units value cells, and click
*Show Values As* - Click
*Difference From* - In the Show Values As dialog box, from the
*Base field*list, choose*Date*. That field will be used to compare the weekly sales. - From the
*Base item*list, choose*(previous).*Within the Date field, each week's sales will be subtracted from the previous week's sales. - Click the OK button, and the pivot table shows the differences in weekly sales. Top

- To make the data easier to understand, change the heading from "Sum of Units" to "Units Change".
- Add another copy of the Units field to the pivot table, and show both the total sales and difference in weekly sales
- Experiment with the pivot table layout, to find an arrangement that is easy to read and understand.

In this example, the pivot table has Item in the Row area, and Total in the Values area. Date is in the Column area, grouped by Year. There is a pivot table tutorial here for grouping pivot table data.

The total will be changed to a custom calculation, to compare the current year's sales for each Item, to previous year's sales, as a percentage.

- Right-click one of the cells in the Values area, and select
*Field Settings...* - In the Field Settings dialog box, type a name for the field, e.g. %Change
- Click the
*Options*button, to expand the dialog box - From the
*Show data as*drop down list, select*% Difference From* - From the
*Base field*list, choose*Years* - From the
*Base item*list, choose*(previous)* - Click the OK button

In this example, the Date is in the Row area, Region is in the Column area, and Units is in the Values area.

For more on Running Totals, see Excel Pivot Table -- Running Totals.

The total will be changed to a custom calculation, to show a running total of units for each region, over the dates.

- Right-click one of the cells in the Values area, and click Show Values as
- Click
*Running Total in* - From the
*Base field*list, choose*Date* - Click the OK button

The pivot table now shows a running total for each Region, down through the list of dates.

In Excel 2010 and later versions, you can use the **% Running Total**
calculation, to show the current running total amount, divided by
the grand total.

In this example, the Date is in the Row area, Region is in the Column area, and Units is in the Values area.

The total will be changed to a custom calculation, to show a percent running total of units for each region, over the dates.

- Right-click one of the cells in the Values area, and click Show Values as
- Click
*% Running Total in* - From the
*Base field*list, choose*Date* - Click the OK button

The pivot table now shows a % running total for each Region, down through the list of dates. This shows that the East region had sold 83% of its total units by 29

Use the Rank custom calculation (Excel 2010 and later) to show each number's rank in the set of numbers. The rank can be highest to lowest, or lowest to highest. Watch this short video to see the steps, and the written instructions are below the video.

To apply the Rank calculation,

- Right-click a cell in the field that you want displayed as Rank
- Point to the Show Values As command
- Click on one of the Rank options:
- Rank Smallest to Largest or
- Rank Largest to Smallest

In the Rank Values window, select a Base field from the drop down list. In this example, the Region field is selected.

Then, click OK, to see the ranked numbers. The West region ranked highest in Binder and Desk sales, and also ranked highest in the Grand Total.

Use the Index custom calculation to show the relative weight of each cell when compared to its row total, its column total, and the grand total. It uses a special formula to calculate the index values, and is different from the % of Grand Total calculation.

In this example, the pivot table shows the total units sold per region, for each product. The highest amount -- Binders in the West region -- is highlighted.

To apply the Index Custom Calculation:

- Right-click a cell in the Values area, and then click Show Values As.
- In the submenu, click Index

In the next screen shot, the lower pivot table shows the index value for each amount. Lamp sales in the East have the highest index.

Using the Index custom calculation gives you a picture of each value's importance in its row and column context.

- If all values in the pivot table were equal, each value would have an index of 1.
- If an index is less than 1, it's of less importance than other items in its row and column
- If an index is greater than 1, it's of greater importance than other items in its row and column.

Even if two cells have the same value, they may have a different index.

The Index custom calculation formula is:

For example, in the West region, many more binders were sold than desks, but the index for those products is close.

Because the grand total is higher for the Binders column, the Grand Column Total in the Index formula is larger. The West Binder amount is divided by this larger number, and its resulting index is smaller.

The Index Custom Calculation gives different results from the % of Total Custom Calculation, as you can see in the screen shot below. These tables show Auto and Property insurance policy sales, in three regions.

The table at the right shows the % of Grand Total, and the highest value is the same as the highest value in the original pivot table -- Auto policies in the Central region.

To remove a custom calculation from a pivot table:

- Click the drop down arrow on the Data field button
- Remove the check mark from the custom calculation.

**Note**: If you remove an item from the Values area, it is removed
from the pivot table. To replace it, you can drag it back from the
pivot table field list.

The Custom Calculation (Show Values As) examples shown in this tutorial are based on this zipped sample file.

**NOTE**: Excel 2010 or later version is required for the Rank options, % of Parent options, and % of Running Total.

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

- copy the formatting from one pivot table, and apply it to another pivot table.
- change all the values from Count to Sum
- remove the "Sum of" from all the headings

and much more!

Last updated: October 15, 2018 12:53 PM