Pivot Table "Show Values As" Explained

Pivot table Show Values As changes how the numbers in a pivot are displayed: as a share of a total, a change from last month, a running total or a rank. The data and the sums underneath don't change, only the view. This guide explains every common option on one small pivot and shows the formula that gives the same number, so you can check the pivot and explain it in a meeting or a job test.

What Show Values As does#

A pivot table normally shows a plain summary, such as the sum of sales. Show Values As takes that summary and shows it relative to something else: a total, another month, or the other items in the list.

To use it, right-click any number in the pivot and choose Show Values As, then pick an option. The longer route works the same on Windows and Mac:

1. In the PivotTable Fields pane, click the field in the Values area (for example Sum of Sales).
2. Choose Value Field Settings.
3. Open the Show Values As tab, pick an option, and fill in the Base field and Base item if Excel asks.
4. Click OK.

Choose No Calculation at any time to go back to the plain sums. The Pivot tables 2 lesson lets you try this on a live pivot.

All the examples below use this pivot, with Month in Rows, Region in Columns and Sum of Sales in Values:

The percent of total options#

These options divide each number by a total. They answer "what share of the whole is this?"

Pick the one that matches the question. "What share of North's year came in January?" is % of Column Total. "How did January split between regions?" is % of Row Total. The guide to the pivot table percent of total covers these four options and the parent totals in more depth.

Difference From and % Difference From#

These options compare each item with another item in the same field. They are the quickest way to show month-over-month change.

Choose % Difference From, set the Base field to Month and the Base item to (previous). The pivot now shows:

January is blank because it has no previous month. Each cell uses the percent change formula, (new - old) / old. For North in February, that is (5,000 - 4,000) / 4,000 = 25%. In a normal cell you would write:

=(B3-B2)/B2

Difference From shows the change in units instead of percent: North in February shows 1,000, in March -500, and in April 2,000.

You can also pick a fixed base item instead of (previous). With Jan as the base item, every month is compared with January. The related option % Of shows each item as a percentage of the base item, so February North shows 125% of January. The percent change lesson practises the formula behind these views.

Months must be in the right order for (previous) to make sense. If your dates are daily, group them into months first, as shown in grouping pivot table dates by month.

Running Total In and % Running Total In#

Running Total In adds each item to all the items before it. Set the base field to Month:

% Running Total In shows the same running total as a share of the final total. It answers "how far through the year's sales were we by March?" The last item is always 100%.

The formula version of a running total is =SUM($B$2:B2) filled down. The $ locks the start of the range while the end moves.

Rank Smallest to Largest and Rank Largest to Smallest#

The rank options number the items in order of size within the base field. With Rank Largest to Smallest and Month as the base field, North shows:

April is 1 because it had the biggest North sales (6,500). Rank works inside each column, so South has its own ranking. The rank doesn't move the rows. To put the biggest month at the top, sort the pivot as well; see how to sort a pivot table.

The Index option#

Index shows how important a cell is compared with what you'd expect from its row and column totals. Excel calculates it as:

(cell value × grand total) / (row total × column total)

For January North: (4,000 × 48,000) / (10,000 × 20,000) = 0.96. A value above 1 means the cell is stronger than its totals predict, and below 1 means weaker. It's used less often, but it can show which region over-performs in which month.

Tips for using Show Values As well#

A few habits make these views easier to read and harder to misread:

• Show the amount next to the percentage. Drag Sales into Values twice. Leave one as No Calculation and set the other to a percentage. Then rename the headers, for example "Sales" and "% change".
• Rename the field. Excel calls it "Sum of Sales2" by default. Click the header and type a clear name.
• Set the number format in Value Field Settings > Number Format, so percentages keep two decimals after a refresh.
• Refresh after the data changes. Right-click the pivot and choose Refresh (Windows: Alt+F5).
• Show empty cells as 0 if blanks confuse readers; the guide to pivot table empty cells explains the setting.

Show Values As only works on the summary that is already there. When you need a new measure built from other fields, such as profit margin, use a pivot table calculated field instead.

Practise it#

Show Values As questions are common in data analyst and finance Excel tests, because the same pivot can tell very different stories. Try the exercise on this page to check a % Difference From value with a formula. Then practise pivot tables in a live spreadsheet: you build the pivot and it checks each step instantly. The percent change lesson covers the maths behind every percentage view.