Pivot Table Tricks: Subtotals, Filters and % of Total

To show a pivot table percent of total, open the value field's Value Field Settings, go to Show Values As and choose % of Grand Total. Each number then shows its share, and the shares add up to 100%. This guide covers that and the other tricks you need once you can build a basic pivot: subtotals, report filters, and refreshing when new rows arrive. If you have never made a pivot, start with our pivot tables beginner's guide.

The examples use a table of 20 orders on a sheet called Sales, with the columns Quarter, Region, Product, Units and Sales, and a pivot of Sum of Sales by Region.

How to show a pivot table percent of total#

There are two ways to open the same setting:

1. In the PivotTable Fields pane, click the ▾ on Sum of Sales in the Values area and choose Value Field Settings.
2. Open the Show Values As tab.
3. Choose % of Grand Total and click OK.

Or, the quicker way on Windows and Mac: right-click any number in the pivot and choose Show Values As > % of Grand Total. Keep Sum as the summary. Show Values As changes how the sums are displayed, not how they are calculated.

With the pivot filtered to Q2 (more on filters below), the regions look like this:

Read 37.33% as "West made 37.33% of all Q2 sales in this pivot". It compares the parts of one total. It says nothing about growth over time.

Percentages show with two decimals by default. To change that, open Value Field Settings, click Number Format, choose Percentage and set the decimal places. Show Values As also works with counts: set the value field to Count and then % of Grand Total to see each region's share of the number of orders instead of the dollars.

% of Grand Total, Column Total, Row Total or Parent Total?#

Each option divides by a different total, so pick the one that matches your question:

With Region and then Product in Rows, % of Parent Row Total shows that Laptops were 57.14% of West's Q2 sales ($4,800 of $8,400). To go back to plain numbers, choose No Calculation.

Show the amount and the percentage side by side#

A percentage alone hides the size of the numbers. To see both, drag Sales into the Values area a second time. You now have Sum of Sales and Sum of Sales2. Leave the first as it is and set the second to % of Grand Total. Rename it in Value Field Settings, in the Custom Name box, to something clear like "Share of sales".

Add subtotals with two row fields#

Put two fields in Rows, and the first one makes groups with a subtotal for each. Drag Product into the Rows area, below Region. Each region becomes a bold line with its subtotal, and its products are listed underneath:

Order matters: the field on top makes the outer groups. To swap them, click the ▾ on a field in the Rows area and choose Move Up or Move Down. To change where subtotals appear, click inside the pivot and go to Design > Subtotals: Do Not Show Subtotals, Show All Subtotals at Bottom of Group or at Top of Group. Design > Grand Totals turns the grand totals on or off.

Filter a pivot by quarter, region or product#

A report filter limits the whole pivot to some records. Drag Quarter into the Filters area. A filter appears above the pivot, showing (All). Click its arrow, tick Select Multiple Items if you want more than one, choose Q2, and click OK. Percentages recalculate for the filtered data, which is why West is 37.33% of Q2, not of the year.

Two other ways to filter:

• Slicers: PivotTable Analyze > Insert Slicer adds buttons you click to filter. They are easy for other people to use in a report.
• Row label filters: click the arrow next to Row Labels to show only some items, or use Value Filters > Top 10.

To filter the source data itself instead of the pivot, see how to filter data in Excel.

Refresh and update the source range#

A pivot table doesn't update when its data changes. After you edit values, right-click the pivot and choose Refresh, or use PivotTable Analyze > Refresh (Windows: Alt+F5).

New rows are different. If two late orders are typed below a source range that ends at row 21, a refresh won't include them. Click inside the pivot, choose PivotTable Analyze > Change Data Source, change the range to Sales!$A$1:$E$23 and click OK. To avoid this, build pivots on an Excel table (Ctrl+T, Mac: ⌘T), which grows as you add rows.

When a formula is better than a pivot#

Pivots are great for exploring. For one number in a fixed cell, like a dashboard total, a formula is often better: it is always live and never needs a refresh. This SUMIFS, on a Report sheet, adds Q2 sales in the West from the Sales sheet:

=SUMIFS(Sales!E:E,Sales!A:A,"Q2",Sales!B:B,"West")

It returns $8,400, the same as the pivot. Whole columns like Sales!E:E include new rows automatically. The Sales! part points at another sheet; see how to reference another sheet in Excel. For the full trade-off, read pivot table vs SUMIFS. To make the biggest shares stand out, add conditional formatting to the pivot's values. Both tools are everyday Excel skills for data analysts.

Practise it#

These settings are easy to forget unless you click through them yourself. ExcelReady's Pivot tables 2 lesson starts with a ready-made pivot of sales by region. You nest Product under Region, add a Quarter report filter, show % of Grand Total, extend the source when late orders arrive, and finish with a SUMIFS that matches the pivot. Practise pivot subtotals, filters and % of total.