How to Fix WPS Spreadsheets Pivot Table Showing Too Many Decimal Places
Question details
User needs to understand why a PivotTable in WPS Spreadsheets displays more decimal places than the source cells and how to correct the displayed number format.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has source data formatted to two decimal places and creates a PivotTable for analysis.
- Observed behavior
- PivotTable values display seven or more decimal places even though the source cells appear rounded to two decimals.
Open the workbook in WPS Spreadsheets and confirm that the source cells are formatted to two decimal places. Then click any value cell inside the PivotTable so the field formatting options become available.
Change the PivotTable value field number format in WPS Spreadsheets
Use this when the displayed PivotTable values have too many decimal places but the underlying calculation is acceptable.
PivotTable number formatting controls only the displayed value. It does not change the stored value or remove the small floating-point differences that Excel can create during calculation.
Click any numeric value cell inside the PivotTable. This activates the PivotTable tools and ensures the formatting is applied to that value field.
Right-click the selected value cell and choose Value Field Settings or Field Settings from the context menu.
In the dialog, click Number Format or Format Cells, select Number, set Decimal places to 2, and click OK to apply the change.
Use ROUND to store exact rounded values before creating or refreshing the PivotTable
Use this when you need the underlying values to be exactly rounded and not only formatted for display.
Set PivotTable Decimal Places with WPS Office Spreadsheets
WPS Office Spreadsheets includes familiar PivotTable and number formatting tools, so you can quickly set decimal places, refresh fields, and create exact ROUND formulas without extra add-ins.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook or WPS Spreadsheets file that contains the PivotTable.
- 2. Select any PivotTable value cell: Click a numeric value in the PivotTable so the PivotTable field tools become active.
- 3. Open Value Field Settings: Right-click and choose Value Field Settings or Field Settings from the context menu.
- 4. Set the number format: Click Number Format or Format Cells, choose Number, set Decimal places to 2, and confirm with OK.
- 5. Refresh or edit the source if needed: If exact stored values are required, add a ROUND helper column and refresh the PivotTable to use the rounded field.
Frequently Asked Questions
Why does a PivotTable show more decimal places than the source data?
Excel and WPS Spreadsheets use floating-point arithmetic for calculations, which can produce very small rounding differences that are not visible in source cells but appear when PivotTable values are aggregated and displayed.
How do I reduce decimal places in a WPS PivotTable?
Right-click any value cell in the PivotTable, open Value Field Settings or Field Settings, click Number Format or Format Cells, select Number, set the decimal places, and click OK.
Does changing the PivotTable number format change the stored value?
No. Number formatting changes only how the value is displayed. The underlying value may still contain small floating-point differences unless you use ROUND in the source data or calculated field.
Can I prevent this by rounding my source data?
Yes. Add a helper column such as =ROUND(A2,2) in the source data and update the PivotTable to use that rounded field, or use a calculated field that rounds the calculation result.
Does WPS Office open Microsoft Excel PivotTables without losing formatting?
WPS Office Spreadsheets supports .xlsx and .xls workbooks, so you can open Excel PivotTables, refresh data, and update number formatting in a familiar interface.
