Last updated: September 30, 2014 12:03 AM

Search Contextures Sites

## Pivot Tables -- Custom Calculations

- Video: Difference From
- % Difference from
- % of Column Total
- % of Row Total
- % of Grand Total
- % Of
- Video: Percent of Subtotal
- Running Total in
- % Running Total In
- Index
- Remove a Custom Calculation
- Download the Sample Files
- More Pivot Table Tutorials
In an Excel Pivot Table, you can use custom calculations to summarize the data, by using the values in other cells in the Values area.

For example, you can show each Region's total as a percentage of the national total. Or, calculate the difference between last week's sales and this week's.

## Video: Difference From

To see the steps for creating a Difference From custom calculation, please watch this short video tutorial. The written instructions are below the video.

Difference fromYou can use the Difference From custom calculation to subtract one pivot table value from another, and show the result.

In this example, we'll compare each region's weekly unit sales to the previous week's unit sales. Here is the pivot table showing the weekly total units sold.

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 fieldlist, chooseDate. This is the field that we want to use for comparing the sales.- From the
Base itemlist, choose(previous).Within the Date field, we want to subtract one week's sales from the previous week's.- Click the OK button, and the pivot table shows the differences in weekly sales.
## Tips

- To make the data easier to understand, you can change the heading from "Sum of Units" to "Units Change".
- You can 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 the arrangement that will be easiest to read and understand.

% Difference fromIn 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
Optionsbutton, to expand the dialog box- From the
Show data asdrop down list, select% Difference From- From the
Base fieldlist, chooseYears- From the
Base itemlist, choose(previous)- Click the OK button

% of Column TotalIn this example, the pivot table has Region in the Row area, and Total in the Values area. A custom calculation will be added, to show the percentage for each region's sales, compared to the national total.

- Right-click one of the Units value cells, and click
Show Values As- Click
% of Column Total- The field changes, to show the percentage that each region's sales contribute to the overall sales.
You can change the column heading, e.g. % Sales, to make the data easier to understand.

% of Row TotalIn this example, the pivot table has Item in the Row area, Region in the Column area, and Total 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 item total.

- Right-click one of the Units value cells, and click
Show Values As- Click
% of Row Total- The field changes, to show the percentage that each region's sales contribute to the item's overall sales. For example, about 40% of the Binder sales are in the Central region.
- You can change the field heading, e.g. % Sales, to make the data easier to understand.

% Of Grand TotalIn this example, the pivot table has Item in the Row area, Region in the Column area, and Total 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 Pen Sets in the Central region.
- You can change the field heading, e.g. % Sales, to make the data easier to understand.

% OfIn this example, the pivot table has Item in the Row area, Region in the Column area, and Total in the Values area.

The total will be changed to a custom calculation, to compare each Region's sales to Ontario'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. %Ontario
- Click the
Optionsbutton, to expand the dialog box- From the
Show data asdrop down list, select% Of- From the
Base fieldlist, chooseRegion- From the
Base itemlist, chooseOntario- Click the OK button
## Video: Show Percent of Subtotal

Introduced in Excel 2010, the % of Parent Row Total and % of Parent Column Total custom calculations shows a value as a percentage of the item parent's subtotal.

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

## % of Parent Row Total

Introduced in Excel 2010, the % of Parent Row Total custom calculation shows a value as a percentage of the item parent'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. A custom calculation will be added, 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.
For example, 51% of the Binders sold in January were black, and 35% were blue.## % of Parent Column Total

Introduced in Excel 2010, the % of Parent Column Total custom calculation shows a value as a percentage of the item parent's subtotal, across columns.

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. A custom calculation will be added, 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 Column Total- The field changes, to show the percentage for each item's sales, compared to the month's total, for each colour. For example, 17% of the Blue items sold in February were pens.

Running Total inIn this example, the pivot table has Region in the Column area, and Total in the Values area. Date is in the Row area, grouped by Year and Quarter. There is a pivot table tutorial here for pivot table grouping.

The total will be changed to a custom calculation, to calculate a running total of sales for each Region, over each Year.

- 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. Sales
- Click the
Optionsbutton, to expand the dialog box- From the
Show data asdrop down list, selectRunning Total in- From the
Base fieldlist, chooseDate- Click the OK button
For more on Running Totals, see Excel Pivot Table -- Running Totals.

## % Running Total In

In Excel 2010 and later versions, you can use the

% Running Totalcalculation, to show the current running total amount, divided by the grand total.In the pivot table shown below, there are three Value fields in the pivot table, showing the

Sum of Unitssold on each date.

- In column B, the sum is shown, with
no calculation. This is the number of units sold on each date listed.- In column C, the sum is shown, as a
Running Totalfor Date. This is the total units sold, up to and including each date.- In column D, the sum is shown, as
% Running Totalfor Date (new in Excel 2010). This is the total units sold, up to and including each date, divided by the grand total of units sold.By November 1st, a running total of 399 units have been sold, and the % Running Total is 18.8% of the 2121 overall total units sold.

## Index

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.

In this example, the pivot table shows the total number of cancelled policies per region, for both Auto and Property insurance. The highest amount -- Auto policies in the Central 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
Now the pivot table shows the index value for each amount, in the pivot table shown at the right, in the screen shot below. Although Central Auto is the highest value in the table at the left, East Property has the highest Index value.

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 in its row and column
- If an index is greater than 1, it's of greater importance in its row and column.
## Index Custom Calculation Formula

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

The Index formula is:

For example, in the West region, the values for Auto and Property are almost equal, but the index for the Auto is 1.02 and Property is 0.98.

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

## Compared to % of Grand Total

The Index Custom Calculation gives different results from the % of Total Custom Calculation, as you can see in the screen shot below.

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.

Remove a Custom CalculationTo 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's removed from the pivot table. To replace it, you can drag it back from the pivot table field list.## Download the Sample Files

- The region examples shown in this tutorial are based on this zipped sample file.
- The % of parent examples are based on this zipped sample file. Excel 2010 or later version is required.
## Xtreme Pivot Table Course

Pivot table skills are essential, if you want to be an Excel master. To raise your skills to the expert level, I recommend the

Xtreme Pivot Table course, from John Michaloudis, at My Excel Online.This course has more than 200 videos -- beginner, intermediate and advanced level -- along with practice workbooks, finance business cases, and 12 months of personal support. Each short lesson is clear, and easy to follow. Work through the lessons at your own pace, and track your progress. The course is an excellent value, at a surprisingly low price.

See the course details, and watch sample videos here:

Xtreme Pivot Table Course. When you buy the course, use the coupon codeCONTEXTURESfor a10% discount

## More Pivot Table Resources

Add-In: PivotPower Premium- Beginning Pivot Tables Book
- Pivot Tables Recipe Book
- Xtreme Pivot Table Course

Tutorials:

- FAQs - Pivot Tables
- Pivot Table Introduction
- Grouping Data
- Multiple Consolidation Ranges
- Running Totals
- Summary Functions
- Clear Old Items in Pivot Table

Contextures Inc., Copyright ©2014

All rights reserved.