15.3 Value Display Modes: % of Total, Running Total, & Differences

Microsoft Excel Expert Microsoft 365 Apps

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Exam Overview & MO-211 Fundamentals

1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture

2 Chapter 2: Managing Workbooks & File Options

2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution

3 Chapter 3: Workbook Protection & Collaboration Settings

3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations

4 Chapter 4: Advanced Data Entry & Series

4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)

5 Chapter 5: Custom Formatting & Data Validation

5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing

6 Chapter 6: Data Grouping, Summarization, & Cleansing

6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing

7 Chapter 7: Advanced Conditional Formatting & Filtering

7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges

8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions

8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS

9 Chapter 9: Advanced Lookup Functions & Variable Optimization

9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET

10 Chapter 10: Date, Time, Text, & Auditing Functions

10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools

11 Chapter 11: Dynamic Array Functions & Spill Operations

11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping

12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions

12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic

13 Chapter 13: Macro Automation & VBA Fundamentals

13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor

14 Chapter 14: Advanced & Specialized Visualizations

14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts

15 Chapter 15: PivotTable Construction & Field Configuration

15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences

16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts

16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts

15.3 Value Display Modes: % of Total, Running Total, & Differences

Key Takeaways Explain This Topic AI

The 'Show Values As' tab in Value Field Settings performs advanced comparative math on aggregated data without requiring manual worksheet formulas or modifying source datasets.

Proportional calculations (% of Grand Total, % of Column Total, % of Row Total, % of Parent Row/Column Total) calculate relative percentage distributions across tabular dimensions.

Variance modes—Difference From and % Difference From—quantify performance against a specified Base Item or dynamic markers like (previous) or (next) within a designated Base Field.

Cumulative tracking via Running Total In and % Running Total In computes progressive period-to-date figures across sequentially sorted dimensions such as dates or fiscal quarters.

Combining multiple instances of the same measure side-by-side—such as absolute sum, % of column total, and running total—builds robust multi-dimensional analytical reports.

Last updated: September 2026

14.3 Value Display Modes: % of Total, Running Total, & Differences

While the Summarize Values By engine handles fundamental arithmetic aggregations, enterprise financial analysis demands relational intelligence. Executives rarely evaluate raw dollar totals in isolation; they require proportional shares, period-over-period variances, cumulative milestone tracking, and competitive rankings. The Show Values As engine inside Value Field Settings executes these advanced comparative calculations dynamically, eliminating the need to construct manual worksheet formulas outside the PivotTable.

The Mechanics of "Show Values As": Base Fields & Base Items

The Show Values As tab in the Value Field Settings dialog transforms raw aggregated numbers into relative analytical metrics. Right-clicking any value cell and navigating to Show Values As reveals fifteen specialized display modes: Value Field Settings ──► [Show Values As Tab] ──► Select Calculation

│
┌────────────────────┴────────────────────┐
▼ ▼
[Global Calculations] [Contextual Calculations]
% of Grand Total - % Difference From
% of Column Total - Running Total In
% of Row Total - Rank Largest to Smallest
(No Base Parameters) (Requires Base Field & Item)

Calculations fall into two structural classifications:

Global Proportional Calculations : Require no secondary parameters (e.g., % of Grand Total , % of Column Total , % of Row Total ). Excel computes proportions against overall matrix boundaries.

Contextual Relational Calculations : Require the analyst to specify a Base Field (the dimensional axis across which the comparison is evaluated) and, in many cases, a Base Item (the reference benchmark against which all other items are compared).

Proportional & Share Analysis Calculations

Proportional modes express values as percentages of different structural totals across the PivotTable grid:

Calculation Mode Mathematical Formula Base Parameters Required Primary Business Application

% of Grand Total (Cell Value / Grand Total) * 100 None Overall portfolio share; corporate revenue contribution.

% of Column Total (Cell Value / Column Total) * 100 None Product sales mix within a specific geographic territory.

% of Row Total (Cell Value / Row Total) * 100 None Geographic distribution of sales for a specific product line.

% of Parent Row Total (Child Value / Parent Row Subtotal) * 100 None Share of subcategory sales within its parent department.

% of Parent Column Total (Child Value / Parent Col Subtotal) * 100 None Proportional distribution within nested column hierarchies.

% of Parent Total (Item Value / Selected Base Subtotal) * 100 Base Field Custom contribution share across multi-tier hierarchies.

Nested Hierarchies: % of Parent Row Total

In a multi-level row hierarchy (such as Department > Category > Product ), using % of Grand Total can dilute insights because individual product shares might register as tiny fractions (e.g., 0.15% of total company sales). Selecting % of Parent Row Total evaluates each item relative to its immediate parent subtotal:

If the Electronics department generates $1,000,000 and its subcategory Laptops generates $650,000, Laptops displays 65.0% .

The subcategories within each parent block sum to exactly 100%, allowing department managers to analyze internal category mix immediately.

Variance & Deviation Modeling: Difference From & % Difference From

Evaluating performance variances against historical benchmarks or baseline targets is a core requirement of executive reporting. Excel provides two dedicated modes:

Difference From : Calculates the absolute mathematical variance: Value - Base Item Value .

% Difference From : Calculates the percentage variance: (Value - Base Item Value) / Base Item Value .

Configuring the Base Field and Base Item

When configuring variance calculations, selecting the correct Base Item determines the analytical perspective:

Fixed Baseline Item : Choosing an explicit item (e.g., Base Field = Year , Base Item = 2024 ) measures all years against 2024. The 2024 row displays 0 (or 0.0% ), while subsequent years show expansion or contraction relative to that base year.

Dynamic Relative Item (previous) : Measures period-over-period changes (such as Month-over-Month or Year-over-Year). Each row compares itself to the preceding chronological row in the table. Because the initial row has no prior record, it displays #N/A or blank.

Dynamic Relative Item (next) : Compares each record to the subsequent row.

Worked Analytical Model: Year-over-Year Revenue Variance

Fiscal Year Absolute Sales % Difference From (Base: Year, Item: (previous) ) Analytical Interpretation

2024 $1,200,000 [Blank / Baseline] Initial baseline year; no prior comparator.

2025 $1,440,000 +20.0% ($1,440,000 - $1,200,000) / $1,200,000
2026 $1,368,000 -5.0% ($1,368,000 - $1,440,000) / $1,440,000
Cumulative Tracking: Running Total In & % Running Total In

Tracking cumulative progress toward annual targets requires calculating running accumulations down a timeline:

Running Total In : Progressively sums values sequentially down a specified Base Field: Cumulative Sum = Previous Total + Current Item .

% Running Total In : Expresses that running accumulation as a percentage of the overall total for the Base Field, culminating at 100% on the final record.

Exam Trap & Sorting Dependency: Running total calculations depend strictly on the visual sort order of the Base Field. If Month is sorted alphabetically ( April , August , December... ), Excel accumulates values in that chaotic alphabetical sequence! Analysts must ensure chronological fields are sorted sequentially (Jan, Feb, Mar...) for running totals to yield valid business intelligence.

Ordinal Ranking & Concentration Indexing

Rank Smallest to Largest : Assigns ordinal ranking starting with 1 for the lowest numeric value within the Base Field (ideal for cost, defects, or golf scores).

Rank Largest to Smallest : Assigns ordinal ranking starting with 1 for the highest numeric value (standard for sales leaderboards and profit rankings). Tied values receive identical ranks.

Index : Calculates the relative concentration or weighting of a cell relative to row and column totals: Index = Cell Value / Row Total Column Total / Grand Total \text{Index} = \frac{\text{Cell Value} / \text{Row Total}}{\text{Column Total} / \text{Grand Total}} Index = Column Total / Grand Total Cell Value / Row Total An index value equal to 1.0 denotes average performance. An index greater than 1.0 reveals a disproportionate concentration of revenue (e.g., an index of 1.45 indicates sales in that category/region cell are 45% higher than expected based on overall regional and categorical distribution).

Deploying Multi-Metric Side-by-Side Architectures

To build comprehensive executive dashboards, analysts frequently drag the same numeric field into the Values quadrant three times, configuring each instance with a distinct display mode:

Instance 1 : Sum of SalesAmount ──► Summarize by Sum ──► Formatted as $#,##0 ──► Custom Name: "Total Revenue " .

Instance 2 : Sum of SalesAmount ──► Show Values As: % of Column Total ──► Formatted as 0.0% ──► Custom Name: "% of Regional Sales" .

Instance 3 : Sum of SalesAmount ──► Show Values As: Running Total In (Base Field: Order Date ) ──► Formatted as $#,##0 ──► Custom Name: "Cumulative YTD Sales" .

This multi-display architecture provides absolute volume, relative regional mix, and cumulative trajectory within a single unified view—all powered natively by the PivotCache without external formulas.