Search Contextures Sites

 

Pivot Tables -- Custom Calculations

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.

weekly units Difference From

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 from

You 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.

weekly units sold

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

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click Difference From
  3. Difference From command

  4. In the Show Values As dialog box, from the Base field list, choose Date. This is the field that we want to use for comparing the sales.
  5. From the Base item list, choose (previous). Within the Date field, we want to subtract one week's sales from the previous week's.
  6. Base Item Previous

  7. Click the OK button, and the pivot table shows the differences in weekly sales.

Difference From 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.

change headings and layout

% Difference from

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.

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

% of Column Total

In 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.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Column Total
  3. % of Column Total

  4. 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 Column Total and Total Sales

% of Row Total

In 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.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Row Total
  3. 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.
  4. You can change the field heading, e.g. % Sales, to make the data easier to understand.

    % of Column Total and Total Sales

% Of Grand Total

In 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.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Grand Total
  3. 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.
  4. You can change the field heading, e.g. % Sales, to make the data easier to understand.

% of Column Total and Total Sales

% Of

In 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.

  1. Right-click one of the cells in the Values area, and select Field Settings...
  2. In the Field Settings dialog box, type a name for the field, e.g. %Ontario
  3. Click the Options button, to expand the dialog box
  4. From the Show data as drop down list, select % Of
  5. From the Base field list, choose Region
  6. From the Base item list, choose Ontario
  7. 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.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Parent Row Total
  3. % of Parent RowTotal

  4. 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 RowTotal calculations

% 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.

  1. Right-click one of the Units value cells, and click Show Values As
  2. Click % of Parent Column Total
  3. % of Parent Column Total
  4. 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.

    % of Parent Column Total calculations

Running Total in

In 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.

  1. Right-click one of the cells in the Values area, and select Field Settings...
  2. In the Field Settings dialog box, type a name for the field, e.g. Sales
  3. Click the Options button, to expand the dialog box
  4. From the Show data as drop down list, select Running Total in
  5. From the Base field list, choose Date
  6. 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 Total calculation, 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 Units sold 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 Total for Date. This is the total units sold, up to and including each date.
  • In column D, the sum is shown, as % Running Total for 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.

running totals in pivot table

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:

  1. Right-click a cell in the Values area, and then click Show Values As.
  2. 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 Calculation

To remove a custom calculation from a pivot table:

  1. Click the drop down arrow on the Data field button
  2. 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 code CONTEXTURES for a 10% discount

More Pivot Table Resources

Tutorials:

 

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

 


Table of Contents


Table of Contents 

 

 

 

 

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: September 30, 2014 12:03 AM