Home > Pivot > Calculation > Show Values As ## Pivot Tables Show Values AsEasy steps to show difference or percent in a Microsoft Excel pivot table. No formulas! Use simple built-in tool, Show Values As. Written steps, examples, videos, workbook.
Author: Debra Dalgleish |

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

With the custom calculations available in the **Show Values As** settings, you can make a pivot table easier to understand at a glance.

These custom settings are especially helpful if the original data has large numbers.

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

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.

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

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 for formatting changes, below.

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.

In this example, the pivot table has:

- Item field in the Row area,
- Region in the Column area
- Units in the Values area.

In the sections below, I'll show you two variations on the **% Of** option.

**Note**: When you use this option, 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

After you click OK, 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
- 25 desks sold in the East region

The East has 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

After you click OK, the pivot table changes, to show each item's number sold, as a percent of the Binder sales in that region, or the grand total.

For example, in the Central region, there were:

- 12 desks sold
- 103 Binders sold

The **% Of** value for Desk shows as 12%, compared to Binder sales, for the Central region.

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.

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

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.

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.

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

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

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

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

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

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)

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

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 all of 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.

See the step-by-step instructions below.

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
**Warning**: 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

To change the number format for a percentage column, 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

- Tip: for most pivot fields, I select
- Next, check the Sample display, to make sure that percentage format is what you wanted
- Finally, click OK, twice, to close the two dialog boxes

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

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

Last updated: June 27, 2024 12:24 PM