How to Display Time Differences on an Excel PivotTable Y-Axis
Question details
The user needs to correctly plot and display time differences, especially those containing milliseconds, on the Y-axis of an Excel PivotTable without them rounding to zero.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a PivotTable to analyze and aggregate time difference data that includes very small fractional units like milliseconds.
- Observed behavior
- Time differences appear as zero or fail to plot correctly because standard PivotTables round milliseconds and cannot sum date-time values natively.
Ensure your raw time difference data is calculated correctly using a standard subtraction formula, and verify whether your data set includes extremely small values such as milliseconds.
Format Time Differences as General and Apply Custom Format
Converting time differences to general numerical formats before building the PivotTable prevents Excel from caching and rounding milliseconds to zero.
Standard PivotTable caches struggle with extremely small time values formatted as date-time data natively, often rounding milliseconds down to zero. By processing the source data as standard decimals (General format) and applying the time format only to the final aggregated result, you bypass the rounding limitation completely.
In your source data, select the column containing the calculated time differences. Press Ctrl+1, navigate to the Number tab, and select 'General' to display the times as decimal numbers.
Select your entire data range, go to the Insert tab on the ribbon, click PivotTable, and choose where you want to place the report.
Drag your General-formatted time difference field into the 'Values' area of the PivotTable Fields pane. Ensure the aggregation method is set to 'Sum'.
Right-click the newly aggregated values in the PivotTable, select 'Number Format', go to the 'Custom' category, and type 'hh:mm:ss.000' to correctly display the time with milliseconds.

Use an Excel Data Model PivotTable
The Excel Data Model manages extremely small numeric values, such as fractions of a second, more efficiently than standard PivotTable caches.
Easily Aggregate Complex Time Data in WPS Spreadsheet
WPS Office offers robust PivotTable functionality out of the box. By formatting your time calculations as general numbers, you can seamlessly sum and display exact millisecond differences without unexpected rounding errors directly in WPS Spreadsheet.
- 1. Open Your Data in WPS: Launch WPS Spreadsheet and open your workbook containing the time difference data.
- 2. Convert Source to General Format: Highlight your time difference column, right-click, select 'Format Cells', and change the category to 'General'.
- 3. Insert a PivotTable: Navigate to the 'Insert' tab on the top menu ribbon and click on 'PivotTable' to create a new report.
- 4. Customize Value Formatting: Drag your time field to the 'Values' area, right-click the result, choose 'Value Field Settings', click 'Number Format', and apply the custom 'hh:mm:ss.000' format.

Frequently Asked Questions
Why are my time differences showing as 0 in the PivotTable?
Standard Excel PivotTables cache and round very small numeric values, such as milliseconds. If your time differences are stored natively as dates or times containing milliseconds, the PivotTable's internal aggregation may round them down, resulting in a display of zero.
How do I display milliseconds in my spreadsheet cells?
You can display milliseconds by applying a custom number format. Select the target cells, press Ctrl+1 to open the Format Cells dialog, select 'Custom' from the list, and input the exact code 'hh:mm:ss.000'.
Can Excel PivotTables natively sum date and time values?
No, Excel cannot natively sum date-time values directly in a PivotTable without causing calculation issues. It is highly recommended to store the time differences as raw numeric (decimal) values, sum them in the PivotTable, and then apply a date-time format to the final PivotTable results.




