Fix Excel Pivot Table Time Rounding Problem

If you add times to an Excel Pivot Table, and format the time to show tenths of a second or hundredths of a second, zeros might appear after the decimal point.

The decimals for tenths of a second or hundredths of a second are rounded to zero, and changing the pivot table number format does not fix the problem.

Pivot Table Zero Decimals

In the screen shot below, I’ve highlighted the problem in the pivot table time column.

  • maximum time for the A team is 5:15:25 – 5 minutes, and 15 and 25 hundredths seconds
  • pivot table shows the time as 5:15:0 – 5 minutes, and 15 seconds

Instead of rounding the decimal to 2, it rounded to zero.

Pivot Table Time Rounding Problem

Video: Fix Pivot Table Time Rounding Problem

In the video below, I show how this time rounding problem can happen, and the steps to fix the problem.

There are written steps below the video, and more details on my Contextures site, on the Pivot Table Time Fields page.

Video Timeline

  • 0:00 Introduction
  • 0:12 Build a Pivot Table
  • 0:55 Format the Time
  • 1:38 Fix the Time Problem
  • 2:43 Format the New Field

Fix Pivot Table Time Rounding Problem

To fix the pivot table times, so they show tenths of seconds and hundredths of seconds, you can use a simple workaround.

To begin, follow these steps to add a column in the source data:

  • First, add a new column in the pivot table source data–in this example, TimeCalc
  • Next, in the new column, enter a formula with a simple link to the original time value cell in that row.
    • In the screen shot below, cell D2 has this formula: =C2
    • If the source data is in a named Excel table, the formula should automatically fill down to the last row.
  • Leave the new column in General format – do NOT change it to a time format

formula with link to original time cell

Add New Field to Pivot Table

After you add the new column to the source data, follow the steps below, to update the pivot table.

  • To refresh the pivot table, right-click on a pivot cell, and click the Refresh command
  • The new field will appear in the pivot table field list, where you can drag it to the pivot table’s Values area.
  • To show the maximum times, right-click on one of the new values, click Summarize Values by, and then click Max

Format as Time With 2 Decimals

Next, follow the steps below to format the times:

  • First, right-click on any cell in new pivot value column, and click the Value Field Settings command
  • Click the Number Format button, and click on the Custom category at the left.
  • To show the times with 2 decimal places, format the values with this custom number format: m:ss.00
  • This time format shows tenths of a second, or hundredths of a second.
  • Click the OK button to apply the formatting

The numbers are formatted correctly in the new field, tenths of a second, and hundredths of a second

To complete the changes, you can remove the original time field, which had the rounded tenths of a second or hundredths of a second, showing a zero instead of the correct numbers.

numbers are formatted correctly

Get the Excel Workbook

To get the sample Excel file that I used in the video, go to my Contextures site, on the Pivot Table Time Fields page. The zipped file is in xlsx format, and does not contain any macros.

____________________

Fix Excel Pivot Table Time Rounding Problem

Fix Excel Pivot Table Time Rounding Problem

____________________

Copy PivotTable Format to Different Excel File

You can create custom PivotTable Styles in an Excel workbook, to fine tune the appearance of your pivot tables. Later, if you want to use those custom styles in a different Excel file, you’ll have to manually copy them, using the steps shown below.

Continue reading “Copy PivotTable Format to Different Excel File”

Fix Pivot Table Row Heading Cells Blank Label

After you build an Excel pivot table, you might see a few row labels or column labels that contain the text “(blank)”.

This happens when data is missing from the source data. For example, in the source data, you might have a few sales orders that don’t have a Store number entered.

Video: Fix Pivot Table Headings

Watch this short video to see how to fix those “(blank)” labels, and there are written steps below the video.

Video Timeline:

Labels Show (Blank)

In the pivot table shown below, there are three City heading cells, in column B, that shows (blank), instead of a city name. There is also one Region heading cell in column A that has the same problem.

When working with a pivot table, I find that text distracting, and usually remove it, to get a cleaner look in the pivot table.

  • Sometimes I replace the “(blank)” with other text, such as “N/A”
  • In most cases, I want the cell to look blank, so I replace it with a space character

The steps to do that are below this screen shot.

row label cells with (blank) in pivot table

Pivot Option Setting Does NOT Change Labels

First, here’s a solution that you might have tried, to remove the “(blank)” text, and found that it didn’t do what you needed.

  • In the PivotTable Options dialog box, on the Layout & Format tab, there is a setting, “for empty cells show:”
  • Maybe you typed “N/A” in that box, or a space character, hoping it would solve the “(blank)” label problem
  • After making that change in the Options window, you clicked the OK button.

for empty cells show setting in pivot options

Why That Does NOT Work

After the PivotTable Options dialog box closed, you’d be disappointed to see that the “(blank)” headings were still in the row labels.

That solution does not work for “(blank)” cells, because: that “empty cells” setting has these limitations:

  • it only affects cells in the Values area
  • it does NOT affect the Row or Column Labels areas.

For example, in the screen shot below, you can see that:

  • missing data in the Values area has been replace by N/A
  • row labels and column labels haven’t changed – they still show “(blank)”.

pivotoptionsemptycells02

Manually Change Blank Labels

Instead of using the PivotTable Options, you can manually change the (blank) labels in the Row or Column Labels areas by typing over them in the pivot table.

However, this technique has a couple of limitations too:

  • You cannot type an existing item name, to replace the (Blank) entry
    • If you type an existing name, the (blank) label will move into the place where that item was in the pivot table layout
  • You cannot clear the cell and leave it empty – it must have a text entry

How to Change Label Text

Here are the steps for manually changing a pivot table row label text, or heading labels in the column areas.

  • Note: I used “N/A” in this example, but you could use a different text string, or a space character.

To change a blank label cell to “N/A”, follow these editing steps:

  1. First, select one of the Row or Column Labels that contains the text (blank)
    • Even if there are multiple cells with a “(blank)” label, you only need to select one of them.
    • You DO NOT need to press Ctrl and select all of them
  2. Next, on your keyboard, type N/A in the cell, and then press the Enter key.

Pivot Table Changes

After you press the Enter key, you’ll see the following changes in the pivot table, shown in the screen shot below:

  • All other (Blank) items in that same pivot field will change to display the same text
    • In this example, all “(blank)” cells in the City column have changed to “N/A”.
  • Blank items in other pivot fields are NOT affected
    • In this example, the “(blank)” cell in the Region column has NOT changed to “N/A”.

pivotrowlabelsblank02

Pivot Table Sample Data

To get Excel sample data that you can use for pivot table practice, go to the Excel Sample data page on my Contextures website.

  • For the example in this page, I used the Food Sales Data, and cleared a few cells in the source data.
  • There are several other sample files to download, such as Workplace Safety, Insurance Policy Sales, and Stationery Sales.

___________________________

More Pivot Table Tips

For more information on pivot tables, see these pages on my Contextures site:

Manually Move Pivot Items

Clear Old Items in Pivot Table

Pivot Table Options

__________________________

Fix Pivot Table Row Heading Cells Blank Label

pivotheadingsfixblanklabels01b

____________________

Conditional Formatting Excel Pivot Table Color Scale

It’s November, a month when we expect cooler temperatures here in Canada. However, it was more like summer last weekend, and we enjoyed an afternoon beverage on the patio. Was that normal? What were the November temperatures over the past few years? Let’s use a pivot table with conditional formatting, to find out!

Continue reading “Conditional Formatting Excel Pivot Table Color Scale”

Pivot Table Highlight Weekend Data Conditional Formatting

You can use conditional formatting in an Excel pivot table, to highlight specific data, such as months with high sales numbers. This example uses conditional formatting to highlight the pivot table values that are connected to weekend dates. Continue reading “Pivot Table Highlight Weekend Data Conditional Formatting”

Remove All Selected Pivot Table Value Fields With Macro

Pivot tables make it easy to work with your data, and see the summaries in different layouts. To add or remove fields from the layout, you can check or uncheck fields in the PivotTable Field list. In large pivot tables, with lots of fields, this macro makes it easy to remove specific fields from the Values area. Just select one or more fields, and run the macro!

Continue reading “Remove All Selected Pivot Table Value Fields With Macro”

Show Sales Amounts as Data Bars in Excel Pivot Table

Most pivot tables show numbers, so you can see a detailed summary of the source data. To add a visual element to the pivot table, add data bars that are similar to a bar chart. This works well in a simple pivot table, with only one or two columns of data.

Continue reading “Show Sales Amounts as Data Bars in Excel Pivot Table”

Pivot Table Number Format Used Most Often

After you add number fields to a pivot table’s Values area, you can format those numbers. To find out which number formats are used most often, I did a 3-question survey. Thanks to everyone who replied, and these charts show the results of that survey, compiled on two different dates.

Continue reading “Pivot Table Number Format Used Most Often”

Excel Pivot Table Shortcut to Expand and Collapse

After you set up a pivot table, you can use the plus and minus buttons to show or hide the pivot table details. For example, hide details for the East region, so only its totals are showing, and leave all the West region details visible. Do you know the pivot table shortcut to expand and collapse the details? Keep reading, to see a quick and easy way to do this. Continue reading “Excel Pivot Table Shortcut to Expand and Collapse”