It’s pivot table time! First, we’ll take a look two common problems with time values in pivot tables. Then I’ll show you a couple of ways to save time when working with pivot tables.
Pivot Table Time Values
If you’re showing time values in a pivot table, here are a couple of things that can go wrong:
- The total times are way too low
- The times are rounded, and don’t show the tenths or hundredths of a second
There are easy fixes for both of those problems, and the details and a sample file are on the Pivot Table Time Values page on my Contextures site. The quick instructions are below.
Incorrect Time Totals in Pivot Table
If time totals look wrong, change the number format for the pivot table. Use a custom format that totals the hours, such as [h]:mm
Rounded Times in Pivot Table
If a pivot table shows times formatted with tenths of a second, or hundredths of a second, they might be rounded, and show zeros instead.
To fix that, add another field in the source data, linked to the original time field. Format the new field as General, and use that field in the pivot table. Then, format the new pivot field, to show the tenths of a second, or hundredths of a second.
Add a Pivot Table Value With Slicers
Last week, I showed you how to use Slicers to filter your data. That makes it easy to see the specific data that people need, and keeps your data safely hidden.
I’d never thought of using Slicers to select fields for a Pivot table though, and Krisztina Szabó, from The Frankens Team’s blog, explains how to do that.
Kris created a list of fields that can be added to the pivot table’s Value area, and made a pivot table based on that list. With a Slicer, you can select one or more of those fields, and they are added to the pivot table. That’s a great way to save time when working with pivot tables! No more scrolling through a long list in the Pivot Table Field List.
You can download Kris’ sample file to see how it works, and modify the code slightly to use this technique in your pivot tables. I liked Kris’ technique so much that I added another Slicer, to change the summary function for the Value fields.
Pivot Table Builder
And speaking of time, it can take lots of time to build your pivot tables, and then rebuild them, if someone messes them up.
To help you save time, my new Pivot Table Builder add-in lets you create and store a variety of pivot table layouts in a workbook. Then, you can quickly build a new pivot table, or update an existing pivot table, based on any one of those layouts.
It’s a lot quicker, and less frustrating, than rebuilding from scratch every time.
_________