This tutorial explains a method to fill empty cells with the value from the above or below cells in LibreOffice Calc.
It’s one of the common problems in your day-to-day spreadsheet work. You always need to format data to do pivots or filters. But those can only work when LibreOffice Calc rows are filled up consecutively.
For example, in the below data, column A and column B have some missing entries. It’s highlighted in yellow.
You can always do manual copy and paste Or paste special values. But if you have thousands of rows, it’s obviously not an optimized way to do it.
So, how can you fill the items from the above values in one go?
Fill empty cell from the value from above in LibreOffice
We need a paste special feature called “skip empty cells” and some helper columns. Helper columns are those which you need temporarily for interim work.
In the above example, add the following formula in cell D2.
=D1
And in E2, add below:
=E1
And drag the cell handle to fill all the cells until E20.
Explanation: Each cell in both D and E columns now points to the immediate cell above. Since this example has two columns of data which need to fill up, we need two helper columns. If you have one column to fill from above, then you need one helper column. And so on.
Once it is done, select columns A and B and copy them entirely.
Then click on the column D header and right-click.
From the right-click context menu, select Paste Special.
Select the option “Skip Empty Cells” in the Paste Special pop-up.
Once you do that, you can see all the cells are filled up using the value above in all empty cells.
Explanation: When you choose the “skip empty cells” option, the blank cells from columns A and B will not overwrite the corresponding cells in columns D and E. Since there is already a formula in each cell of the D and E columns which points to the above cell, the values are filled up in all the cells.
And now, you can copy columns D and E back to columns A and B. And you can have the final data ready.
Wrapping Up
I hope this clarifies the concept of filling cells with unique combinations. You can similarly modify it for the cells below using the formula from the bottom cell in columns D and E.
Also, remember to remove all the formulas from columns A and B before copying them over.
Feel free to comment below if this helps you or have any questions.
Cheers.
