Calculate a percentage for subtotals in a PivotTable

I'm looking to calculate each line's percentage with respect to the subtotal. So far I've only been able to do this vs the grand total. Is there an option in excel for this or do I have to do some manual work around. Appreciate any help, thanks!

Easy steps to show difference or percent in Excel pivot table. No formulas! Use simple built-in tool, Show Values As. Written steps, examples, videos, workbook.

Author: Debra Dalgleish

Introduction

Instead of creating your own calculated field in an Excel pivot table, you can use built-in custom calculations. For example, with the Show Values As command, you can add these calculations:

• difference between amounts in two columns
• rank in a column
• percentage of a column, or percentage of a subtotal
• and many more calculations -- there are 14 different settings!

For example, in the screen shot below, the pivot table has 2 columns in the Values area:

• Units: sum of units sold for each product
  • No Calculation
• % Total: product's units sold
  • % of Column Total

Benefit of Using Show Values As

With the custom calculations available in the Show Values As settings, you can make a pivot table easier to understand at a glance. These settings are especially helpful if the original data has large numbers

Custom Calculation Tips

When you add a custom calculation to your pivot table, using the Show Values As commands, try the tips that I share with you in the following sections:

Improve Custom Calculation Fields

-- Add Second Copy of Pivot Field

-- Change Pivot Field Heading

-- Change Number Format

% Of Grand Total

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

In this example, the pivot table has the following pivot fields in the layout:

• Item in the Row area
• Region in the Column area,
• Units in the Values area.

The Units field 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.

In this example, about 10% of the overall sales are for Binders in the East region.

Note: Number format was changed to zero decimal places. See the steps above.

% of Column Total

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.

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

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

% of Row Total

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.

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

% Of

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.

% Of Region

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.

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

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

% Of Item

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.

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

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

% of Parent

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.

% of Parent Row Total

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.

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

3. The field changes, to show the percentage for each colour's sales, compared to the item's total, in each month.For example, 195 Binders were sold in January, and 51% (100) of those were Black.
   
   

% of Parent Column Total

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.

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

% of Parent Total

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.

1. Right-click one of the Units value cells, and click Show Values As
2. Click % of Parent Total
3. From the Base field list, choose Item

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

Difference From

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

See more examples and details on the Pivot Table Show Difference page

Difference from

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:

1. Right-click one of the Units value cells, and click Show Values As
2. Click Difference From
3. In the Show Values As dialog box, from the Base field list, choose Date. That field will be used to compare the weekly sales.
4. From the Base item list, choose (previous). Within the Date field, each week's sales will be subtracted from the previous week's sales.
   

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

Difference From Tips

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

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

NOTE: See more examples and details on the Pivot Table Show Difference page

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

Running Total in

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.

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
   

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

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

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
   

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

Rank

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.

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

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

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

How It Works

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.

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

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 is removed from the pivot table. To replace it, you can drag it back from the pivot table field list.

Enhance Show Values As Custom Calculations

In the next sections, see my tips for improving the custom calculations that you added to your pivot table, using the Show Values As commands.

-- Add Second Copy of Pivot Field

-- Change Pivot Field Heading

-- Change Number Format

These steps and suggestions are optional, and will help enhance the clarity and appearance of many pivot tables.

Add Second Copy of Pivot Field

In the pivot table shown above, there are two copies of the Units field in the pivot table values area.

• first copy of Units field lets people see the original numbers
• second copy shows % of total units sold, for each product, using a Show Values As setting.

I find that technique helpful in many pivot table reports, to help people understand the data.

To add a second copy of a pivot field, follow the steps below:

• Select any cell in the pivot table
• In the PivotTable Field List, find the pivot field that you want to add as a second copy
• Drag that pivot field name into the Values area, in the PivotTable Field List layout grid

Change Pivot Field Headings

After adding pivot fields to the Values area, you can change the Pivot Field headings, as I did in the example shown above. This optional step can help people understand what data is shown in each column, in the Pivot Table layout on the worksheet.

For example, the screen shot below shows the original names for the two copies of the Units field.

• Sum of Units
• Sum of Units2

Name Restriction

When changing a heading, you cannot enter a heading that is exactly the same as a field name in the source data table. Instead, use one of these naming techniques:

• add a space character to field name - at the start, at the end, or anywhere within the text
• use a different name - e.g. type Units Sold as the new heading, instead of the original name (Units)

In the example pivot table, I changed the Unit field headings to the following:

• "Units " (with space character)
• "% Total" (different name from source data field)

Change Pivot Field Heading

Follow these steps to change a pivot field heading

• Select the heading cell that you want to change
• Type a new name, e.g. % Total
• Press Enter, to complete the name change

Change Number Format

After changing the Show Values As setting, you can change the number format for the pivot field. This is especially helpful when using any of the percentage settings in Show Values As.

For the "% Of" settings, I usually format the value field with a zero decimal places, or a small number of decimal places. This helps to ensure that the percentages are properly aligned, and easy to read.

Format Pivot Table Value Field

To format the pivot table value field, follow these steps to get started:

• Right-click one of the value cells in the pivot field you want to format
  • NOTE: Do not click the Number Format command, in the pop up menu
• Near the bottom of the pop-up menu, click the Value Field Settings command

The Value Field Settings dialog box opens, where you can see the following details about the pivot field:

• Source Name: field name in the source data table - entry in that box cannot be changed
• Custom Name: field heading in the pivot table layout - you can make name changes in that box
• Summarize Values tab - shows the Summarize By setting that is currently applied - can be changed here
• Show Values As tab - shows current setting - can be changed here
• Number Format button - opens the Format Cells window

Change Number Format

To change the number format, follow these steps:

• In the Value Field Settings dialog box, click the Number Format button
• In the Format Cells dialog box that opens, click the Percentage category, in the list at the left
• At the right, select a number of decimal places
  • Tip: for most pivot fields, I select zero decimal places
• Click OK, twice, to close the two dialog boxes

Get the Sample Files

Show Values As: All of the Show Values As examples (custom calculations) 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.

Rank Example: This is the Food Sales sample file used to show how to add a Rank column for Cases sold. The zipped file is in xlsx format, and does not contain macros.

Pivot Table Links

Pivot Table Show Differences

Hockey Player Data Analysis

Pivot Table Introduction

pivot tableRunning Totals

Summary Functions

Clear Old Items in Pivot Table

How do I calculate percentages in a PivotTable?

How do I add %age to a pivot table?