How to Show Percentages, Running Totals, and Differences in Pivot Tables {A tutorial on Excel’s “Show Values As” feature, which transforms raw numbers into more meaningful insights. Covers percentage of total, percentage of pare

July 4, 2026 admin

Percentages, running totals, and differences turn basic pivot table numbers into clear insights. You can use Excel’s “Show Values As” feature to instantly shift from raw data to meaningful comparisons. Want to calculate change between periods? See How do I create a pivot table column to show the difference for practical examples.

Key Takeaways:

Use the “Show Values As” feature in Excel pivot tables to quickly convert raw numbers into percentages of grand total, percentages of parent row or column, or running totals-making trends and proportions easier to interpret at a glance.
Calculating differences or percentage changes between time periods is simple with “Show Values As” options like “% Difference From,” helping highlight growth or decline without manual formulas.
Right-clicking on a value in the pivot table and selecting “Show Values As” opens a menu of transformation options, allowing flexible, on-the-fly analysis tailored to your data story.

Accessing the Value Field Settings

To begin transforming your data, click any value in your pivot table’s data area. Right-click and select “Show Values As” from the context menu, or go to the PivotTable Analyze tab and open “Field Settings.” This opens the Value Field Settings dialog, where you can switch from basic sums to insightful calculations.

Navigating to the Show Values As menu

After opening Value Field Settings, go to the “Show Values As” tab. A dropdown menu lists all available calculation options like “% of Grand Total” or “Difference From.” Selecting one instantly changes how your numbers appear, letting you compare values across categories with ease.

Understanding the difference between raw data and calculated displays

Raw data shows actual values, such as sales totals or headcounts. Calculated displays, like percentages or running totals, reinterpret those numbers to reveal trends and relationships. Your source data stays unchanged-only the presentation shifts to give deeper insight.

What you see in a pivot table isn’t always what’s stored in your original dataset. When you apply a “Show Values As” option, Excel performs real-time calculations based on the structure of your table. For example, choosing “% of Column Total” divides each cell by its column’s sum, helping you compare proportions within categories. These displays update dynamically with your layout, making it easier to interpret shifts in data without altering a single cell in the source.

How-to calculate percentages for relative analysis

Excel’s “Show Values As” feature lets you shift from raw numbers to meaningful comparisons in seconds. You can instantly see each value as a share of a larger total, making trends and outliers easier to spot. This relative perspective turns isolated figures into actionable insights.

Displaying percentage of column and row totals

You can view each cell as a percentage of its column or row total by selecting “% of Column Total” or “% of Row Total” in the Show Values As menu. This helps you compare proportions across categories and quickly assess the weight of each item within its group.

Using percentage of parent totals for hierarchical data

When your pivot table includes grouped levels like regions and subregions, choose “% of Parent Total” to see each item’s share within its immediate category. This keeps performance in context, showing how child items contribute to their parent’s overall result.

Working with multi-level data? “% of Parent Total” adjusts dynamically based on the hierarchy. If you’re looking at sales across continents and countries, each country shows as a percentage of its continent’s total-not the global sum. This maintains relevance within nested structures and supports accurate comparisons at every level.

Tracking progress with running totals

You can quickly monitor performance over time by turning static figures into a cumulative view. Running totals in pivot tables reveal how values build progressively, making it easier to spot trends and measure momentum across periods.

Setting up cumulative sums across time periods

Select a value field, right-click, and choose “Show Values As” > “Running Total In.” Pick the time-based field like Month or Quarter to see each entry add up sequentially, giving you an instant timeline of growth or decline.

Visualizing growth patterns in large datasets

Patterns emerge clearly when raw totals become a steady climb on a timeline. You begin to see seasonal spikes, slowdowns, or steady gains that aren’t obvious in isolated numbers, especially when managing thousands of rows across multiple categories.

With large datasets, small changes often hide in plain sight. Applying running totals across time-based rows transforms scattered data into a clear upward or downward trajectory. You’re no longer reviewing disjointed figures-you’re following a story of growth, one period at a time. This method works especially well with sales, inventory, or user acquisition data where momentum matters. When combined with charting tools, the running total becomes a powerful visual narrative of progress.

Analyzing variance using difference from calculations

Use Excel’s “Show Values As” feature to reveal how numbers change over time or across categories. You can compare each value to a base item or track shifts between periods. Learn more with this guide on how to Analyze Sales with Excel Pivot Table Running Total Percent.

Comparing values against a specific base item

Determine performance gaps by measuring each entry against a fixed reference point. This method highlights deviations from a chosen baseline, such as a flagship product or top-performing region.

Option	Use Case
Difference From	Compare sales of all regions to one selected region
Base Item	Set the benchmark category for comparison

Showing percentage difference from previous periods

Track growth or decline by converting raw changes into percentages relative to the prior period. This view makes it easier to spot trends in monthly or quarterly data.

Your pivot table can automatically calculate how much a metric has increased or decreased compared to the last recorded period. This percentage-based change removes scale bias, letting you compare performance across different timeframes with clarity and consistency.

Factors that influence calculation accuracy

Select the correct base field and base item
Account for active filters and slicers
Ensure consistent data types across source columns

Any miscalculation in pivot table percentages or differences often traces back to overlooked details in how the data is structured or filtered.

Selecting the correct base field and base item

You control how comparisons are made by choosing the right base field and item in “Show Values As” settings. A wrong selection skews percentages or differences. Always verify that the base reflects the intended reference point-like a specific year or category-so your results align with the story the data should tell.

Impact of filters and slicers on calculated values

Filters and slicers change which data is included in calculations, directly affecting percentages and running totals. What you see isn’t always based on the full dataset. Any adjustment to these controls recalculates visible results, so interpret them within the current filtered context, not as absolute values.

When filters or slicers are applied, Excel recalculates “Show Values As” metrics using only the visible subset of data. For example, “Percentage of Grand Total” will reflect the filtered total, not the original. This behavior ensures relevance to your current view, but demands awareness to avoid misreading trends or proportions as fixed when they’re actually context-dependent.

Expert tips for professional pivot table presentation

Polish your pivot tables with these expert techniques to enhance readability and insight.

Use consistent number formatting across all values
Apply conditional formatting to highlight key trends
Remove unnecessary grand totals when they clutter the view
Freeze column headers for easier scrolling in large tables

Recognizing how small design choices impact interpretation helps your audience grasp insights faster.

Renaming custom fields for clarity and context

Custom field names like “Sum of Sales” often confuse viewers. Replace them with intuitive labels such as “Total Revenue” or “YoY Growth %” to reflect actual meaning. Clear naming reduces confusion and makes your table self-explanatory. Always edit field names immediately after creating calculated items.

Combining multiple calculation types in a single view

You can display different calculations side by side to tell a richer data story. Show actual sales, % of total, and difference from average all in one pivot table. This layered approach reveals patterns a single metric might miss.

Add multiple instances of the same field to “Values”
Set each to a different “Show Values As” option
Use custom names to distinguish each calculation
Align number formats to match each metric’s purpose

Knowing which combinations answer your business question ensures clarity over clutter.

Technique	Application
Renaming fields	Replace “Sum of Profit” with “Gross Margin” for better context
Multiple calculations	Show “Sales”, “% of Column Total”, and “Difference From Previous Month” together
Conditional formatting	Apply color scales to percentage fields to highlight outliers
Field organization	Group related calculations visually using blank rows or labels

Displaying various calculation types in one view transforms a basic summary into a dynamic analytical tool. You might show unit sales as raw numbers while adding a parallel column for “% of Parent Total” to expose contribution by region. This method supports comparative analysis without switching tables.

Right-click value fields to access “Show Values As” independently
Use decimal precision that matches each metric’s scale
Test readability by sharing with a colleague unfamiliar with the data
Hide unnecessary labels to reduce visual noise

Knowing how to balance depth with clarity ensures your audience stays focused on insights, not confusion.

Conclusion

As a reminder, you can transform raw data in Excel pivot tables by using the “Show Values As” feature to display percentages, running totals, and differences. You gain clearer insights by showing values as a percentage of a total or another base item. For step-by-step guidance on these custom calculations, visit Excel Pivot Table Show Values As Easy Custom Calculations.

FAQ

Q: How do I show each value as a percentage of the grand total in a pivot table?

A: Right-click any value in the pivot table, select “Show Values As,” then choose “% of Grand Total.” This converts all numbers into percentages relative to the overall sum. For example, if a sales figure is $500 and the grand total is $2,000, it will display as 25%. The pivot table recalculates automatically if you filter or rearrange fields.

Q: Can I display a running total across time periods like months or quarters?

A: Yes. Click any value in the amount field, go to “Show Values As,” and pick “Running Total In.” Then select the date-based field, such as Month or Quarter, from the dialog box. The pivot table will show cumulative values that build up sequentially. January appears as-is, February shows January plus February, and so on. This helps track growth trends without extra formulas.

Q: How can I calculate the difference between consecutive periods in a pivot table?

A: Use the “Show Values As” option and select “% Difference From” or “Difference From” depending on whether you want percentages or actual values. Choose the base field (like Month) and set the base item to (previous) to compare each period against the one before. For instance, March will show the change from February. This highlights increases or decreases clearly across time-based data.