Search Contextures Sites
Custom Search

 

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.

Use custom calculations in a pivot table, 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

    Difference From command

  3. 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.
  4. From the Base item list, choose (previous). Within the Date field, we want to subtract one week's sales from the previous week's.

    Base Item Previous

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

    % of Column Total

  3. 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 Date is grouped by Year and Month. The pivot table has Month in the Column area, Customer in the Row area, and Quantity (Qty) in the Values area. There is a pivot table tutorial here for pivot table grouping.

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

The total will be changed to a custom calculation, to calculate a running total of quantity for each customer, over the months..

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

pivot table without running totals

The pivot table now shows a running total for each Customer, across the month columns.

running total by date across

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

Rank

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

Use the Rank Custom Calculation

To apply the Rank calculation,

  1. Right-click a cell in the field that you want displayed as Rank
  2. Point to the Show Values As command
  3. 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, there is only one row field, Product, so that is selected.

Then, click OK, to see the ranked numbers. In the screen shot below, it is easy to see that the order is reversed for ranks 3 and 4, and for ranks 6 and 7.

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.

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

More Pivot Table Resources

Tutorials:

 

 

 

More Tutorials

 

 

 

Contextures
Excel news
by email

 

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 


Table of Contents


Table of Contents 

 

 

 

__

 

Privacy Policy

 

Contextures Inc., Copyright 2015
All rights reserved.

 

Last updated: June 27, 2015 3:49 PM