• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel PivotTable Show Values As

You are here: Home / Excel PivotTables / Excel PivotTable Show Values As
September 7, 2016 by Mynda Treacy

The Excel PivotTable Show Values As menu has a load of handy instant calculations you can use. For example you can choose to show the values as:

  • Percentages of the row, column or grand totals
  • The difference from or % difference from
  • Running totals
  • Ranking
  • And index

Kevin emailed me with a vehicle log book and he wanted to calculate the distance of each trip. It’s pretty complicated with formulas but with the PivotTable Show Values As tool it’s a doddle. Let’s take a look.

Excel PivotTable Show Values As Example

Let’s say you have a list of vehicles and you record the odometer reading at the end of each trip like so:

vehicle odometer reading

From this data I can create the PivotTable below using the Show Values As > Difference From > Base Field: Date, Base Item: Previous, to calculate the KMS Travelled shown in the third column:

create Excel pivottable using show values as

Steps to build this PivotTable

Step 1: Insert a PivotTable

insert a pivottable

Step 2: Drag the KMS Reading column to the values area again so you have it in the PivotTable twice:

KMS Reading column

Step 3: right-click the Sum of KMS Reading2 column > Show Values As > Difference From:

KMS Reading2 column

Note: in Excel 2007 you'll find the Show Values As menu in the Value Field Settings > Show values as tab.

In the Calculation Difference From menu choose Base Field: Date and Base Item: (previous):

Calculation Difference From

Step 4: Remove up the subtotals for the vehicles; right-click Vehicle 1 > deselect ‘Subtotal “Vehicle Name”. And remove the Grand Total; right-click Grand Total>‘Remove Grand Total’.

Remove Grand Total

Step 5: Give the ‘Sum of KMS Reading2’ column a new name; simply type over the header cell with a new name. Just make sure it’s different to any of the existing source data column names:

Sum of KMS Reading2

So you can see it’s pretty easy to calculate the difference from one odometer reading to the next using Show Values As.

Let’s look at some other things we can do.

Excel PivotTable Show Values As Examples

The PivotTable below uses Show Values As to display the % of Grand Total (for the Order Amount) and Sales Person Rank (based on Order Amount) in ascending order:

% of Grand Total

And if we add another grouping for the countries the % of Grand Total adapts, as you can see below.

add another grouping

In the image above the last column, the % of Parent Total, calculates the % Order Amount within each country.

Also, in the examples above I’ve left the Sum of Order Amount columns in the PivotTables for reference, but you can remove them if you prefer to just see the result of the Show Values As fields. For example, in the log book analysis you might only want to see the KMS travelled like so:

using show values as

More Show Values As

A while back I wrote a tutorial on how to use Show Values As to calculate the year on year change.

Download the workbook and try it yourself

There are loads of ways you can use the PivotTable Show Values As tool so download the workbook below and have a play around with it.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Thanks

Thanks to Kevin for asking the vehicle log book question which prompted me to write about this topic.

Have you got a favourite use for Show Values As? Please share it in the comments below.

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.


Category: Excel PivotTables
Previous Post:Excel Table Running Total Formula
Next Post:Creating a UDF (User Defined Function) in Excel

Reader Interactions

Comments

  1. Lionel

    September 8, 2016 at 1:37 pm

    Hi Mynda,

    That’s a great tool!
    What if we needed to display the total numbers of KM traveled by Vehicle? So showing somewhere that vehicle 1 has traveled 110 KM so far?

    Reply
    • Mynda Treacy

      September 8, 2016 at 2:14 pm

      Hi Lionel,

      Great question. It’s not straight forward.

      You can either add columns to your source data to populate the Max and Min for each vehicle. e.g. with MIN(IF and MAX(IF array formulas. You can then create a PivotTable with Vehicle in the rows and your Min & Max values in the columns, then add a Calculated Field to subtract the Min from the Max.

      Or you can use Power Pivot and write a DAX Measure to perform the calculation if you have Excel 2010, 2013 or 2016 with Power Pivot enabled.

      Let me know if you need more specific instructions.

      Mynda

      Reply
  2. Renny Schweiger

    September 7, 2016 at 11:31 pm

    This is super. After investing much time with PowerPivot, I’ve not really leveraged the power of good old native (non-PP) Pivot Tables. This article helped demystify some of this capability. I need to explore some more!

    Thanks very much.

    Reply
    • Mynda Treacy

      September 8, 2016 at 9:56 am

      Awesome. Glad I could reignite your interest in the humble PivotTable 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.