Pivot Table Time Problems

It's pivot table time! First, we'll take a look two common pivot table time problems - rounding and totals. Then I'll show you a couple of ways to save time when working with pivot tables.

Pivot Table Time Problems

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 pivot table time 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

pivot table time problems

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.



You may also like...

4 Responses

  1. Doug Glancy says:

    Hi Debra, I’ve seen a few posts lately (including yours) about using slicers and pivots as filters. Very cool.

    Speaking of pivot time issues, I regularly run a subroutine that deletes unused items from pivot table item lists (gleaned from this site a few years back). I’d say the most common time I do that is when I’ve imported dates that are still formatted as integers and create a pivot table from them. Then at some point I want to see actual dates in the pivot field so I format them in the source and refresh the pivot. Your code then gets rid of all the “dates-shown-as-numbers” items. So thanks for that.

  2. Qasim Noor Ellahi says:


    I have a problem. When i sort my data by using pivotable the new entries remain uncheck and did not show in pivotable.

    Can you help how to resolve this issue.

Leave a Reply

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