/

How to Fill Empty Cells with Value from Above or Below

835 views

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.

test data showing empty cells to be filled up from the value above
test data showing empty cells to be filled up from the value above

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.

Set up helper columns
Set up helper columns

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.

Copy the columns and select paste special
Copy the columns and select paste special

Select the option “Skip Empty Cells” in the Paste Special pop-up.

Select the option - Skip Empty Cells
Select the option – Skip Empty Cells

Once you do that, you can see all the cells are filled up using the value above in all empty cells.

Using the skip empty cells to fill all the cells
Using the skip empty cells to fill all the 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.

Final result with cell fill up with the value above
Final result with cell fill up with the value above

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.

Join our Telegram channel and stay informed on the move.

arindam

Creator of libreofficehelp.com. The aim is to help as many people as possible with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments