Forum Discussion
Pivot table option issues in MS office 365
I am using Microsoft Office 365 on my machine and need to use the PivotTable option to summarize the Late Hours and Extension Hours in my report. However, recently the PivotTable is not displaying the Sum option/value correctly for these fields. The values are currently not being summarized as expected. Could you please review this issue and advise me on how to resolve it? I would appreciate your guidance on the required Excel settings or data-format changes needed to display the Sum of Late Hours and Extension Hours correctly in the PivotTable.
4 Replies
Hello,
I’m seeing the same behavior on my 64-bit beta version (2611 Build 16.0.20601.20000)!
Workarounds found:
1- For the first PivotTable, first change the data format to "General," create the table while checking the "Add to Data Model" box, then switch the format back to "Time."
Subsequent PivotTables should then work.
2- Use Power Pivot.
The PivotTable is probably treating Late Hours and Extension Hours as text rather than numeric time. Excel uses Sum for numeric fields but switches to Count when a source column contains text, blanks, or Boolean values. Test several source entries with =ISNUMBER(cell). If any return FALSE, create helper columns that convert imported text to real Excel time values, remove stray spaces, and replace errors only after checking the source. Format the helpers as [h]:mm so totals can exceed 24 hours. Refresh the PivotTable, remove the original hour fields, add the helper fields to Values, then open Value Field Settings and choose Summarize Values By > Sum. Apply [h]:mm to the PivotTable values too. If Sum remains unavailable, confirm whether the PivotTable uses an OLAP or external model, where aggregation may be defined by the source. Also verify that its source range includes every new row before refreshing.
Hello Ravindar1,
I would first check whether Late Hours and Extension Hours are stored as numbers or as text.
If Excel treats these values as text, the PivotTable may use Count instead of Sum.
Select the source columns and check the cell values. If they are stored as text, convert them to numbers, then refresh the PivotTable.
In the PivotTable, right-click the field under Values and select Summarize Values By, then choose Sum.
If the values are already numeric but Sum still does not work, check the PivotTable source and whether it is using the Data Model.
Microsoft Support has a topic called "Sum values in a PivotTable" that covers this behavior.
Ravindar1 Well, if you don't see the Sum option in the list of suggested calculations it means that the "Early_Out" field contains text. Can't be more precise without seeing the data.
