Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Instead of writing your own formulas in calculated fields, you can use Show Values As to quickly present values in different ways. It also provides several new calculation options, such as % of Parent Total or % Running Total In.

Tip: You can use this feature to try different calculations in a value field. However, because you can add the same value fields to a PivotTable more than once, you can also use this feature to show the actual value and other calculations, such as a running total calculation, side by side.

  1. To add two or more of the same value fields to the PivotTable so that you can display different calculations in addition to the actual value of a specific field, do the following:

    1. In the Field List, drag the value field that you want to add to the Values area that already contains that value field, and then place it right below that field.

      Note: The value field is added to the PivotTable and a version number is appended to its field name. You can edit the field name as needed.

    2. Repeat step 1 until you have displayed all the value fields you want to calculate by using Show Values As.

  2. In the PivotTable, right-click the value field, and then click Show Values As

    Note: In Excel for Mac, the Show Values As menu doesn't list all the same options as Excel for Windows, but they are available. Select More Options on the menu if you don't see the choice you want listed.

    Show Values As
  3. Click the calculation option that you want to use.

    The following calculation options are available:

Calculation option

Result

No Calculation

Displays the value that is entered in the field.

% of Grand Total

Displays values as a percentage of the grand total of all the values or data points in the report.

% of Column Total

Displays all the values in each column or series as a percentage of the total for the column or series.

% of Row Total

Displays the value in each row or category as a percentage of the total for the row or category.

% Of

Displays values as a percentage of the value of the Base item in the Base field.

% of Parent Row Total

Calculates values as follows:

(value for the item) / (value for the parent item on rows)

% of Parent Column Total

Calculates values as follows:

(value for the item) / (value for the parent item on columns)

% of Parent Total

Calculates values as follows:

(value for the item) / (value for the parent item of the selected Base field)

Difference From

Displays values as the difference from the value of the Base item in the Base field.

% Difference From

Displays values as the percentage difference from the value of the Base item in the Base field.

Running Total in

Displays the value for successive items in the Base field as a running total.

% Running Total in

Calculates the value as a percentage for successive items in the Base field that are displayed as a running total.

Rank Smallest to Largest

Displays the rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value with a higher rank value.

Rank Largest to Smallest

Displays the rank of selected values in a specific field, listing the largest item in the field as 1, and each smaller value with a higher rank value.

Index

Calculates values as follows:

((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

To show calculations side by side with the values they’re based on (for example, to show the % of Grand Total next to the subtotal), first you need to duplicate the value field by adding it more than once. In the Field List, drag the field you want to duplicate to the Values area, and place it right below the same field.

Duplicate values in the Values area

In the example shown above, three value fields were added a second time to the Values area; they have "_2" appended to their names. Below, the "Sum of 1/1/14" field is shown twice in the PivotTable report, so you can see the value for each city and their parent (the East or West region). You also see the percentage of the grand total for each region (in rows 6 and 9).

The same field, with values and percentages

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×