Showing Percentages, Running Totals, and Differences in Excel PivotTables

Course: Advanced Excel

Topic: Showing Percentages, Running Totals, and Differences

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction

Microsoft Excel PivotTables provide powerful tools for summarizing and analyzing large datasets. In addition to displaying totals, counts, and averages, PivotTables can show values as percentages, running totals, and differences from other values. These calculations help analysts understand how individual categories contribute to an overall result, how performance accumulates over time, and how one period compares with another.

For example, a sales manager may want to know what percentage of annual sales comes from each region, how revenue accumulates month by month, or how much sales increased compared with the previous month. Excel provides built-in options under Show Values As to calculate these results without changing the original source data.

Understanding these features is an important Advanced Excel skill because they make PivotTables more useful for financial analysis, sales reporting, inventory management, budgeting, and business dashboards.

2. Learning Objectives

• Understand the purpose of Show Values As in PivotTables.
• Display values as percentages of grand totals, row totals, and column totals.
• Calculate running totals by month, quarter, or another category.
• Display differences between items and comparison periods.
• Calculate percentage differences from a previous period.
• Choose the correct base field and base item.
• Use multiple value fields to display actual values and analytical calculations together.
• Format results clearly and troubleshoot common PivotTable issues.
• Build a practical sales analysis report using these calculations.

3. Understanding Show Values As

Show Values As is a PivotTable feature that changes how summarized values are displayed. Instead of showing only the original calculation, such as Sum of Sales, Excel can display the result as a percentage, cumulative total, difference, or other analytical calculation.

These calculations affect the displayed results, not the underlying source data. The original sales records remain unchanged.

Common Show Values As Calculations

The exact options available depend on the PivotTable layout, the field arrangement, and the Excel version.

4. Sample Dataset for Practice

Consider the following example sales dataset. Each record represents the sales amount for a product in a particular month.

This dataset will be used to demonstrate percentage contribution, cumulative sales, and changes between months.

5. Creating a PivotTable for Analysis

Before using Show Values As, create a PivotTable from the source data.

Step-by-Step Procedure

1. Select any cell inside the source dataset.
2. Open the Insert tab.
3. Select PivotTable.
4. Confirm the source range and choose a new or existing worksheet.
5. Click OK.
6. Drag Month into Rows.
7. Drag Sales into Values.
8. Confirm that Sales is summarized by Sum.

The resulting PivotTable displays total sales for each month. You can now change the calculation using Show Values As.

6. Showing Values as a Percentage of Grand Total

The % of Grand Total calculation shows how much each value contributes to the overall total. It is useful when analyzing sales contribution by product, region, salesperson, or month.

Formula Concept

Percentage of Grand Total = Individual Value / Grand Total

For example, if January sales are 80000 and the total sales for all months are 440000, the percentage contribution is:

=80000/440000

The result is approximately 18.18%.

How to Apply % of Grand Total

1. Place Month in Rows and Sales in Values.
2. Click the drop-down arrow beside Sum of Sales in the Values area.
3. Select Value Field Settings.
4. Open the Show Values As tab.
5. Select % of Grand Total.
6. Click OK.
7. Format the results as percentages if necessary.

Example Output

Business use: This calculation helps identify which month or category contributes the largest share of total revenue.

7. Showing Values as a Percentage of Row Total

The % of Row Total calculation shows the proportion of each value within its row. It is especially useful when a PivotTable has categories in Rows and another dimension in Columns.

Example: Product Sales by Region

For the Laptop row, North represents 60% of the total and South represents 40%.

Steps

1. Place Product in Rows.
2. Place Region in Columns.
3. Place Sales in Values.
4. Open Value Field Settings for the Sales field.
5. Select Show Values As and choose % of Row Total.
6. Click OK.

This report shows the regional composition of sales for each product. Each nonempty product row should add up to approximately 100%, subject to rounding and the current filter selections.

8. Showing Values as a Percentage of Column Total

The % of Column Total calculation shows the contribution of each value to the total of its column. It is useful when comparing categories within each period, region, or other column grouping.

Example

For January, Laptop contributes 62.5% of sales, while Mobile contributes 37.5%.

Steps

1. Place Product in Rows.
2. Place Month in Columns.
3. Place Sales in Values.
4. Open Value Field Settings.
5. Select Show Values As and choose % of Column Total.
6. Click OK and review the percentage distribution for each month.

This calculation helps compare the composition of each month's sales. Each populated month column should total approximately 100%.

9. Understanding Running Totals

A running total, also known as a cumulative total, adds each new value to the total of all previous values. It is useful for tracking cumulative sales, expenses, revenue, inventory movement, or project progress over time.

Running Total Formula

Running Total = Previous Running Total + Current Value

For example, if monthly sales are 80000, 100000, 120000, and 140000, the running totals are 80000, 180000, 300000, and 440000.

How to Create a Running Total in a PivotTable

1. Place Month in Rows.
2. Place Sales in Values.
3. Click the drop-down arrow beside the Sales field in Values.
4. Select Value Field Settings.
5. Open the Show Values As tab.
6. Choose Running Total In.
7. Select Month as the Base Field.
8. Click OK.
9. Ensure that the months are sorted in chronological order.

Example Output

Business use: Running totals help managers monitor progress toward quarterly or annual targets. They are particularly useful for cumulative revenue, expenditure, and sales performance reports.

10. Important Considerations for Running Totals

• Sort the base field correctly: Months should appear in chronological order, not alphabetical order.
• Choose the correct base field: Select the field that defines the sequence, such as Month or Date.
• Check date grouping: If the source contains valid dates, grouping by month or year can make the report easier to interpret.
• Understand filter effects: A running total generally reflects the records included in the PivotTable after filtering.
• Use a suitable time period: Separate years or quarters when cumulative totals should restart at a reporting boundary.
• Verify the first value: The first item in the sequence normally has a running total equal to its own value.

For a year-to-date report, ensure that the selected dates cover the intended year. A running total does not automatically reset each year unless the report layout and base-field configuration support the intended grouping.

11. Showing Differences Between Items

The Difference From option displays the numerical difference between each value and a selected comparison item. It is commonly used to compare current sales with the previous month, current year with the previous year, or one region with another.

Difference Formula

Difference = Current Value - Comparison Value

For example, if February sales are 100000 and January sales are 80000, the difference is:

=100000-80000

The result is 20000, meaning sales increased by 20000.

Steps to Calculate Difference From the Previous Month

1. Place Month in Rows.
2. Place Sales in Values.
3. Open Value Field Settings for Sales.
4. Choose Show Values As.
5. Select Difference From.
6. Set Month as the Base Field.
7. Choose the previous month as the Base Item, or select (previous) if available.
8. Click OK.

Example Output

The first month has no earlier month in the displayed sequence, so its difference is not available. Depending on the PivotTable setup, Excel may show a blank or another result for that first item.

12. Showing Percentage Differences

The % Difference From calculation shows how much a value increased or decreased relative to a selected comparison item. It is useful for measuring growth rates and identifying trends.

Percentage Difference Formula

Percentage Difference = (Current Value - Comparison Value) / Comparison Value

For example, if sales increased from 80000 to 100000:

=(100000-80000)/80000

The result is 25%, which means sales increased by 25% compared with the previous month.

Steps to Calculate Percentage Difference

1. Place Month in Rows and Sales in Values.
2. Open Value Field Settings for Sales.
3. Choose Show Values As.
4. Select % Difference From.
5. Set Month as the Base Field.
6. Select (previous) as the Base Item if available.
7. Click OK.
8. Format the results as percentages if necessary.

Example Output

Although sales increase by the same amount each month, the percentage growth declines because the previous month's sales value becomes larger.

13. Difference Between Difference and Percentage Difference

Use Difference From when the amount of change matters most. Use % Difference From when relative growth or decline is more meaningful.

14. Displaying Actual Values and Analytical Calculations Together

In many reports, it is helpful to show the original values alongside the percentage, running total, or difference calculation. This makes the report easier to interpret because users can see both the underlying result and its analytical context.

Procedure

1. Drag Sales into the Values area.
2. Drag Sales into Values a second time.
3. Keep the first field as Sum of Sales.
4. Open Value Field Settings for the second field.
5. Select the required option under Show Values As.
6. Rename the fields with descriptive names such as Monthly Sales and Running Sales.
7. Repeat the process if you need a third calculation, such as % Difference From.

You can add the same source field multiple times to Values and configure each instance differently. For example, a single PivotTable can display monthly sales, percentage of grand total, and running total side by side.

15. Choosing the Correct Base Field and Base Item

The Base Field and Base Item settings tell Excel which category should be used for a comparison or cumulative calculation.

For month-over-month comparisons, use Month as the Base Field and the previous item as the Base Item when supported. For year-over-year comparisons, arrange the data so the year is the relevant comparison field and select the correct comparison year.

Always check the order of items before using previous or next. If months are sorted alphabetically, the calculated differences may not represent the intended chronological comparison.

16. Using Running Totals with Date Fields

Running totals are especially useful when working with daily, monthly, quarterly, or yearly data. Correct date grouping helps maintain a meaningful cumulative sequence.

Example: Quarterly Revenue

This report helps managers track accumulated revenue against an annual target. If the report contains multiple years, use Year as an additional grouping or filter and verify whether the running total should continue across years or restart for each year.

17. Using Percentages for Contribution Analysis

Percentage calculations help identify which products, departments, regions, or employees contribute the largest share of total results.

Example: Regional Contribution

This analysis reveals that North contributes 30% of the total sales. The same method can be used for expenses, units sold, customer counts, or other measures.

18. Using Differences for Business Performance Analysis

Difference calculations are useful for evaluating performance changes across reporting periods or categories.

• Month-over-month analysis: Compare sales for the current month with the previous month.
• Year-over-year analysis: Compare the current year with the previous year.
• Budget variance: Compare actual results with a budget or target when both measures are available.
• Regional comparison: Compare one region with a selected reference region.
• Product comparison: Compare sales or profit across product categories.

When using differences to evaluate performance, interpret positive and negative results according to the measure. An increase in revenue may be favorable, while an increase in costs may be unfavorable.

19. Using Calculations in a Business Dashboard

Percentages, running totals, and differences can be combined in an Excel dashboard to provide a broader view of business performance.

Suggested Dashboard Components

• Total sales and total profit KPIs.
• Percentage contribution by region or product.
• Monthly sales trend with running total.
• Month-over-month difference and percentage growth.
• Filters for year, region, department, or product.
• Charts that display monthly trends or category comparisons.
• Conditional Formatting to highlight significant increases or decreases.

Keep the report focused on the business questions that matter. Too many calculations and visual elements can make the dashboard difficult to understand.

20. Common Mistakes and Troubleshooting

21. Best Practices

• Use % of Grand Total for overall contribution analysis.
• Use % of Row Total to analyze the composition of each row.
• Use % of Column Total to analyze the composition of each column.
• Use Running Total In for cumulative reporting over time.
• Use Difference From for absolute changes between items.
• Use % Difference From for relative growth or decline.
• Choose the correct Base Field and Base Item.
• Sort date fields chronologically before calculating running totals or period differences.
• Display original values alongside analytical calculations when useful.
• Apply suitable number formats and clear field names.
• Check how filters affect the report's totals and comparisons.
• Verify results against sample calculations before sharing the report.

22. Practical Exercise: Monthly Sales Analysis

Objective: Build a PivotTable that shows monthly sales, percentage contribution, running total, and percentage change from the previous month.

Dataset

Tasks

1. Create a PivotTable with Month in Rows and Sales in Values.
2. Add Sales to Values a second time and show it as % of Grand Total.
3. Add Sales to Values a third time and show it as Running Total In using Month as the Base Field.
4. Add Sales to Values a fourth time and show it as % Difference From the previous month.
5. Rename the value fields to Monthly Sales, Sales Contribution, Cumulative Sales, and Monthly Growth.
6. Format the contribution and growth calculations as percentages.
7. Confirm that the running total increases or decreases according to the monthly sales values.
8. Identify the month with the highest sales and the month with the strongest percentage growth.
9. Write two observations about the trend.

Expected Learning Outcome

You should be able to display several analytical calculations in a single PivotTable and explain the difference between contribution, cumulative performance, absolute change, and percentage change.

23. Mini Project: Monthly Business Performance Report

Project Goal: Build a PivotTable-based report that helps a business monitor revenue, contribution, and growth.

Project Requirements

• Prepare a dataset with dates, regions, products, and sales amounts.
• Create a PivotTable showing sales by month.
• Display monthly sales as a percentage of grand total.
• Add a running total to show cumulative sales.
• Add a difference-from-previous-period calculation.
• Add a percentage-difference calculation.
• Include filters for region and product.
• Use clear number formats and descriptive labels.
• Write a short analysis of the strongest and weakest periods.

Deliverable: An Excel workbook containing a PivotTable report with percentage contribution, cumulative totals, and period-to-period comparisons.

24. Interview Questions and Answers

Q1. What does Show Values As do in a PivotTable?

It changes how summarized values are displayed, allowing calculations such as percentages, running totals, differences, and percentage differences.

Q2. What is % of Grand Total?

It displays each value as a proportion of the total across the entire PivotTable.

Q3. What is the difference between % of Row Total and % of Column Total?

% of Row Total calculates each value as a proportion of its row total, while % of Column Total calculates each value as a proportion of its column total.

Q4. What is a running total?

A running total is a cumulative sum that adds each value to the total of all preceding values in a selected sequence.

Q5. What does Difference From calculate?

It subtracts a selected comparison item's value from the current item's value.

Q6. What does % Difference From calculate?

It calculates the change relative to the comparison value by dividing the difference by the comparison value.

Q7. Why is the first month often blank in a Difference From calculation?

The first month has no preceding month in the displayed sequence to use as a comparison.

Q8. What is the Base Field?

The Base Field identifies the field used for the comparison sequence or running total, such as Month or Year.

Q9. Can the same field be used more than once in Values?

Yes. The same source field can be added multiple times and each instance can use a different summary calculation or Show Values As option.

Q10. Why might a running total be incorrect?

The base field may be incorrect, the sequence may be sorted improperly, or the report may not include the intended records.

Q11. When should percentage difference be used instead of absolute difference?

Percentage difference is useful when comparing relative growth across values of different sizes. Absolute difference is useful when the amount of change matters most.

Q12. Do Show Values As calculations change the source data?

No. They change the way PivotTable results are displayed; they do not modify the underlying source records.

25. Quick Revision Notes

• % of Grand Total: Contribution to the entire report total.
• % of Row Total: Contribution within a row.
• % of Column Total: Contribution within a column.
• Running Total In: Cumulative sum across a selected sequence.
• Difference From: Current value minus comparison value.
• % Difference From: Difference divided by comparison value.
• Base Field: Field used to define the comparison sequence.
• Base Item: Specific item used as the comparison reference.
• Previous item: The preceding item in the selected order.
• Multiple value fields: Allow actual values and analytical calculations to appear together.
• Correct sorting: Essential for meaningful chronological comparisons.
• Formatting: Percentage calculations should be displayed as percentages for readability.

26. Final Checklist

• Prepare clean source data with correct dates and numeric values.
• Create the PivotTable and place fields in the appropriate areas.
• Confirm that the source measure uses the correct summary calculation.
• Select the appropriate Show Values As calculation.
• Set the correct Base Field and Base Item for running totals or differences.
• Sort months and dates in chronological order.
• Use percentage formatting for percentage calculations.
• Compare calculated results with manual examples.
• Review filters and their effect on displayed totals.
• Rename value fields clearly and keep the report readable.

27. Conclusion

Showing percentages, running totals, and differences in Excel PivotTables helps transform basic summaries into meaningful business analysis. Percentages explain how individual values contribute to a total, running totals show cumulative progress, and difference calculations measure changes between categories or periods.

By using the Show Values As options correctly, selecting suitable base fields, sorting data appropriately, and formatting the results clearly, you can build PivotTable reports that support sales analysis, financial reporting, trend monitoring, and management decisions.

28. Learn More with JustAcademy

Strengthen your spreadsheet and reporting skills through the JustAcademy Advanced Excel Course.

To learn more about the training program, register for a course demo.

Visit the JustAcademy Official Website to explore additional courses and learning opportunities.