My pivot table only returns whole numbers. I have the source data formatted as decimal numbers with one decimal place, but a value of 0.1 doesn’t show up in the pivot table - there is just an empty cell. It appears to be rounding to zero as the totals for rows also don’t include these values. I know the pivot table is aware of those values being there, as I can filter the data by value 0.1, but it still doesn’t show up - it just gives me an array of empty cells. Is there a way to fix this?
I am using LibreOffice 5.1.6.2
Pivot table problem.ods (29.0 KB)
Yes, enter 0.1 as a number and not as text.
Mark Column B, go to Data → Text in Columns and push OK.
2 Likes
I’ve uploaded a stripped down version of my spreadsheet with the faulty pivot table in it.
I understand what you mean - originally the score for 0.1 was a “+” and so obviously text. So I made sure to reformat it as a number when I changed it to a nominal number. The spreadsheet I uploaded has it formatted as a number, not text. Or have I got that wrong somehow? In any case I tried your suggestion and it made no difference in my spreadsheet.
Changing the format of a cell does not affect its content (number or text).
Press Ctrl+F8 and you will see “who is who” by the font color
1 Like
If it is text you can format the cells however you want, it remains text.
In this case, you can recognize it by the apostrophe at the beginning of the cell.
Version: 7.1.6.2 (x86) / LibreOffice Community
Build ID: 0e133318fcee89abacd6a7d077e292f1145735c3
CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win
Locale: en-US (de_DE); UI: en-US
Calc: threaded
Only if the decimal separator is a dot in the locale.
What version are you using? I might upgrade to the latest, I just realised I am quite a few versions behind.
Of course, it is high time to change your version (5.1), but the cause of the problem is described above.
You might not have Data > Text to Columns in your version, it is quite old. You can remove the apostrophe by:
- Select the column in which the digits are found in text format. Set the cell format in that column as “Number”
- Choose Edit - Find & Replace
- In the Search for box, enter
^. - In the Replace with box, enter
& - Check Regular expressions
- Check Current selection only
- Click Replace All
2 Likes
That little bit of alchemy has changed my text to numbers… no idea how, but thankyou!
That incantation predates LibreOffice, let alone version 5 
1 Like