Receive new articles directly in your inbox!! Register your email address

How to build pivot tables in Excel - Chapter 3: Show Values As.. Part1

Within the realm of Pivot Tables, the "Show Values As" option is a particularly powerful feature that can transform how you interpret your data.

Let's understand this feature and how it can be used to enhance data analysis.

The "Show Values As" option in Excel Pivot Tables enables you to display the values in your Pivot Table in different ways, beyond the simple sum or count.

It provides a variety of ways to calculate and display your data, helping you gain more insights and present your findings more effectively.

In order to discover all the key insights of these calculations we will be referring to a pivot table featuring two items in the rows section

The first item (called parent row) represents the month and year in which the sales occurred; the second item (called child row) indicates which Sales Rep has made the sale

To access the "Show Values As" menu, select a cell in the Pivot Table and right click on the mouse

From the menu select the "Show Values As" option and the different calculations will show

Let's now understand what each calculation does.

% of Grand Total

When using the % of Grand total all the values in the pivot table are shon as the percentage of the total

In the above example each number of the pivot table is divided by the total of 9050

Example

Month Feb 24 AND SalesRep John AND Product A = 500/9050 = 5.52%

% of Column Total

When using the % of Column total each value is displayed as % of the total for its column

Thi is useful when you want to know the contribution of each row to the column total

Example

Month Jan 24 AND SalesRep Claire AND Product C = 500/2050 = 24.39%

% of Row Total

When using the % of Row Total each value is displayed as % of the total for its row

This is useful when you want to know the contribution of each column to the column total

Example

Month Mar 24 AND SalesRep Bob AND Product B = 700/1400 = 50%

% Of ...

When selecting the %Of... calculation you are asked to select a Base Field and a Base Item

Base Field

The Base Field is the grouping you want to use as the basis for comparing the values
It represents which item in the rows or columns field will represent the basis for the % comparison

Base Item

The Base Item is the specific field within the Base Field you want to use for the comparison
It is the reference point for the % Of... calculation

Let's make an example by drawing a pivot table and reporting in the last column how the % Of... calculation reasons

In this example the Base Field is Product and the Base Item is Product A

The calculations performed by % Of... are the following:

East Region
- Product B = 1400/1000 = 140%
- Product C = 500/1000 = 50%
- Product A is the base item therefore it is 100%
West Region
- Product B = 1000/1200 = 83.3%
- Product C = 950/1200 = 79.2%
- Product A is the base item therefore 1200/1200 = 100%
North Region
- Product B: 700/1700 = 41.2%
- Product C: 600/1700 = 35.3%
- Product A is the base item therefore 1700 / 1700 = 100%

Let's now apply the % Of... calculation to the original pivot table

The Product is the Base Field and Product A is the Base Item

The following example describes how the calculation works

January 2024
- Product A (Base Item)
  - Bob 0
  - Claire 500
  - Fred 0
  - John 500
- Product B
  - Bob: 700/ 0 = Blank
  - Claire: 0/500 = Null
  - Fred: 700/ 0 = Blank
  - John 0/500 = Null
- Product C
  - Bob: 0/ 0 = Null
  - Claire: 500/500 = 100%
  - Fred: 0/ 0 = Null
  - John 0/500 = Null

In the next posts we will continue explaining the other calculations available in the Show Values As menu

Page updated

Google Sites

Report abuse