LibreOffice Calc and Pivot table with empty cells

Question

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 B6 counting empty cells is empty

Expected result:

• the cell B6 counting empty cells should be 1 (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?

I can reproduce this problem on LibreOffice 6.4.7 on Debian GNU/Linux, on Ubuntu 20.04 Focal Fossa and on other operating systems.

Thank you so much!

Answer 1

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:

• https://gerrit.libreoffice.org/c/core/+/129088

Update 2023: the patch was abandoned and the bug is still there.

Answer 2

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.