How to find out empty, blank cells or rows in LibreOffice Calc

Find out empty rows, cells
Find out empty rows, cells

This tutorial shows three ways in which you can find out all blank cells, empty rows in a Calc sheet.

When you work with a large spreadsheet with huge volume of data, it is very difficult to find out the empty cells. Let’s find out how you can identify them.

We have a table below of data in LibreOffice Calc. And it contains some empty cells in various rows/columns.

A table of data with empty cells
A table of data with empty cells

Method 1

We will use the built-in ISBLANK function to check whether a cell is empty or not. This function takes a cell as an argument and returns TRUE if a cell is empty. Otherwise, returns FALSE.

=ISBLANK(A1): Returns TRUE if A1 is blank. 
Test using ISBLANK Function
Test using ISBLANK Function

In the example enter the above formula and drag it down till your last data item.

ISBLANK-Result
ISBLANK-Result

You can see the cells having no data in column F returned TRUE.

This is the most basic way you can check. Let’s see how you can check with the IF statement.

Method 2

The similar result you can achieve by using the IF function. You can check for empty string check which is "". If the condition is true, you can return any text you want as per your needs.

Test-using-IF-Function
Test-using-IF-Function

Method 3

While the above steps are good, it is always worth to try out a way to highlight the empty cells or rows or columns with some colors. That makes very easy for data analysis. And you can do it using conditional formatting in LibreOffice Calc.

SEE ALSO:  SUMIF and SUMIFS Function with Examples in LibreOffice Calc

To do that, select from menu: Format -> Conditional -> Condition.

In the conditional formatting window, select the entire range of data in Cell range section that you want to highlight for empty cells. Then from the Condition 1 drop-down, select Formula is and enter =ISBLANK(A1) in the formula.

Note that even if you selected the entire range, you can specify only one cell in the condition, LibreOffice calc will take care of the rest of the cell.

Now select the style which highlights the empty cells i.e. satisfy the above formula.

Conditional-Formatting-and-highlight-cells
Conditional-Formatting-and-highlight-cells

Once you do that, press OK. You can see the blank cells are highlighted with the Red style which we have selected.

Highlight-Blank-and-Empty-Rows-Cells-in-LibreOffice-Calc
Highlight-Blank-and-Empty-Rows-Cells-in-LibreOffice-Calc

References

ISBLANK function reference.

  • Let us know using the comment box below if this article helped you or if you are facing any problem.


Be an expert on LibreOffice.

Small and quality tutorials. Delivered. Thrice a week.


Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top