Excel Agility: Pivot Tables - Intermediate

Adding percentage of total to a pivot table is accomplished through the Show Values As feature without modifying source data or writing formulas. The most effective technique is to add the same numeric field to the Values area twice—once showing the raw Sum and once showing the percentage. For the second instance, right-click the value field, select Show Values As, and choose % of Grand Total for each value as a share of the overall total, % of Column Total for percentages within each column, or % of Row Total for row-level proportions. For hierarchical breakdowns, % of Parent Row Total shows each item as a percentage of its group subtotal rather than the overall grand total—ideal for showing how each product contributes to its category's total. These calculated percentage views update automatically whenever the pivot table is filtered or refreshed, eliminating the need to manually recalculate shares each reporting period. Adding a percentage column alongside the raw value is a standard practice in financial dashboards, sales mix analyses, and budget-to-actual reports built on Excel pivot tables.

A running total (cumulative sum) in a pivot table is created using the Show Values As feature without any formulas. Add the value field to the Values area, right-click it, select Show Values As > Running Total In, and then choose the field to accumulate across—typically a date field like Month or Quarter. This adds a column showing the cumulative total as it grows across each period, perfect for year-to-date views. When combined with date grouping, running totals automatically reset at the start of each year if Year is included as a grouping level. For a running percentage—showing the cumulative share of the total—use % Running Total In instead. Running totals in pivot tables are especially useful for tracking cumulative revenue against annual targets, monitoring budget consumption over time, or presenting progress-to-goal metrics. Because the running total is a Show Values As setting rather than a formula, it updates automatically with every filter change or refresh, making it far more maintainable than manual cumulative formula columns built outside the pivot table.

Expanding and collapsing pivot table fields lets you control the level of detail visible at any given time, making large hierarchical pivot tables much easier to navigate and present. When multiple row fields are stacked—such as Region > Department > Employee—each group has a small expand/collapse button (+ or −) on the left. Clicking these toggles the detail for that specific group. To expand or collapse all items in a field at once, right-click any item in that field and choose Expand/Collapse > Expand Entire Field or Collapse Entire Field. You can also use the Expand Field and Collapse Field buttons on the PivotTable Analyze tab. In presentations, a common technique is to start with all fields collapsed to show only top-level totals, then expand specific groups on demand to drill into areas of interest. For reports distributed as PDF or printed, collapsing to the appropriate summary level before exporting gives stakeholders a clean, appropriately detailed view. Field expand/collapse behavior is preserved when the pivot table is refreshed, maintaining the display state between reporting cycles.

One common frustration with pivot tables is that column widths automatically resize to fit the data after every refresh, undoing any manual column width adjustments. This behavior is controlled in PivotTable Options: right-click anywhere in the pivot table, select PivotTable Options, then on the Layout & Format tab, uncheck Autofit column widths on update. With this setting disabled, column widths remain at whatever you manually set them, even after filtering or refreshing the pivot table. Similarly, checking Preserve cell formatting on update ensures that any custom formatting—bold headers, custom number formats, background colors—is retained after a refresh. These two settings together are essential for pivot tables in formal reporting templates where consistent presentation is required. Without them, pivot table–based reports can look different each time they are refreshed, requiring manual reformatting before distribution. For teams producing standardized monthly reports from pivot tables, configuring these options is a one-time setup step that eliminates recurring reformatting work and is covered in Aurora Training Advantage's Excel Agility pivot table training series.

The Timeline feature, available in Excel 2013 and later, is a specialized date filter for pivot tables that provides an interactive, calendar-style interface for filtering by any date range. To insert a Timeline, click anywhere in the pivot table, go to PivotTable Analyze > Insert Timeline, and select the date field. A horizontal timeline bar appears showing all available time periods; you drag the handles to select a specific range, and the pivot table instantly filters to show only data within those dates. Timelines can be set to filter by Days, Months, Quarters, or Years using the period selector in the top right corner. Like Slicers, a single Timeline can be connected to multiple pivot tables via Report Connections, allowing one date range selection to filter an entire dashboard simultaneously. Timelines require the date field to contain actual Excel date values—text-formatted dates will prevent the Timeline from being inserted. For financial dashboards, executive reports, and budget tracking tools, the Timeline feature provides an intuitive, non-technical way for stakeholders to explore data across different time periods without any Excel formula knowledge.