'LibreOffice Calc and Pivot table with empty cells
I want to process survey results with LibreOffice Calc (for example with data coming from LimeSurvey).
Since some questions were optional, some cells are empty (like A6):
I want to answer questions like:
- How many people have given the answer
2? - How many people did not respond?
So, I've created a pivot table with Insert → Pivot:
but... I was not able to answer the question "How many people did not respond?" (see B6).
Actual bug:
- the cell
B6counting empty cells is empty
Expected result:
- the cell
B6counting empty cells should be1(sum of empty cells =1)
To fix this problem I have a workaround consisting in creating a "temporary table" and replacing empty cells with something weird like § EMPTY VALUE § to then be able to analyze empty values with my pivot table.
How do you handle empty cells in LibreOffice Calc with a pivot table?
Tested on LibreOffice 6.4.7 on Debian GNU/Linux but I can reproduce on Ubuntu 20.04 Focal Fossa and on other operating systems.
Thank you so much!
Solution 1:[1]
Another workaround:
in column B, transform column A to string (the formula for cell B2 is =""&A2)
Then create the pivot table using the values in column B.
Solution 2:[2]
It seems LibreOffice has a known bug since 2012, involving pivot tables with empty cells.
https://bugs.documentfoundation.org/show_bug.cgi?id=47523
AFAIK, the described workaround, involving a temporary table, is the only known solution.
Note: since 2022 it seems there is an available patch under review that you can check:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Claudio Negri |
| Solution 2 |