Quickly fill blank cells in a list in LibreOffice Calc

Question

For example:

Cell A1 = 2/3/2012
       A2 is empty
       A3 is empty
       A4 = 3/7/2012
       A5 is empty
       A6 is empty
       A7 is empty

What I want to do is:

• select the complete list (A1 to A245),
• take some action (e.g., click a button) that will automatically select only blank cells,
• enter a formula (or a constant),
• press Alt+Enter

and have the blank cells filled with that value.

But this doesn't happen.

1. I can't select all the non-empty cells.
2. Even if I select the non-empty cells manually, I can't fill them with Alt+Enter; the error message is displayed as "Insert into multiple selection not possible" (LibreOffice 3.6).

I have to use this feature for audit. (It can be done in Microsoft Excel by the "Go To" function, then select 'blanks').

Answer 1

Upgrade to LibreOffice 4:

Then you'll find under the Edit menu, a Fill sub-menu, and under that, an item called Down; this gives the contents of the first cell selected to all the other cells.

Answer 2

In LibreOffice 4.0 you can do a search and replace:

1. select the area in which you want to fill empty cells
2. Edit > Search & Replace
3. search for nothing but check “[x] Entire cells“ and if you have done step 1 (selection) check also in “More options” “[x] current selection only”
4. insert into the field of replace the text you want to replace

This may also work in previous versions of LibreOffice.

Answer 3

What you can do is the following:

• Select an empty adjacent column, let's say B (insert a new column if you have data in B already)
• In the B1 enter =IF(A1,A1,<your formula or constant>)
• With B1 still selected press Ctrl+Shift+End then Ctrl+D to copy this formula for the rest of the column

The column B should have the data you were looking for

Answer 4

This is a lot more difficult in LO than in Excel! Even using Find Replace with Regular Expressions with ^$ doesn't work.... There is a solution, but it's not elegant.

Select the whole area (cells with content and blank cells, ex : A1:A50)
Ctrl+C
Select the area from 1st blank cell till the end, ex : A3:A50
Press F2, then write =A2 (or A49) 'cell above cell with focus
Alt+Enter
Select again the whole area
Ctrl+Shift+V
Check 'text', 'number', may be 'format' (what you want to copy) and 'skip empty cells'.

Found this at: OpenOffive Forum

Answer 5

1. Select an empty adjacent column, let's say B. (insert a new column if you have data in B already)
2. In the B1 enter: =IF($A2="";$B1;$A2)
3. With B1 still selected press Ctrl+Shift+End then Ctrl+D to copy this formula for the rest of the column.

Answer 6

i'm using libreoffice 6.4.4.2 . To quickly fill down multi hop blank cells do the following steps:

1. select the area in which you have your data (including blank cells where you want to fill value from nearest above non-blank cell)
2. do ctrl+H (it will pop up find and replace window )
3. put ^$ in 'find:' text box (to blank cells and select them when clicking 'Find All'/'Replace All' button)
4. put @=INDIRECT(CONCATENATE("R",(ROW()-1),"C",COLUMN()),0) in 'Replace:' text box ( to replace blank cells with this formula - I added @ because formula was being treated as text if I did not put @ and later replaced it with blank)
5. Click on 'Replace All' button (it will put @=INDIRECT(CONCATENATE("R", (ROW()-1),"C",COLUMN()),0) in all blank cells)
6. Put @ in 'find:' text box (we want @ to be replaced)
7. leave blank 'replace:' text box (we want @ removed)
8. Click on 'Replace All' button (it will replace cells with @=INDIRECT(CONCATENATE("R", (ROW()-1),"C",COLUMN()),0) with =INDIRECT(CONCATENATE("R",(ROW()-1),"C",COLUMN()),0) )
9. do ctrl+A (or manually select area which include the cell which were hitherto blank)
10. copy this area
11. paste as unformulated text

As the @ symbol is removed from the cells INDIRECT formula gives output same as value of nearest non-blank above cell

Answer 7

recently I have to faces the same problem.

Thanks to Alex P I solved my problem.

in cell B1 enter

=IF(A1="A1","2/3/2012", IF(A1="A2","empty", IF(A1="A3","empty",IF(A1="A4", "3/7/2012", "do not match"))))

the recursion will be very very deep cause your have 200+ values in cell 'A1`

Answer 8

You can change each empty cells to reference the value in the cell above it.

If A1 is filled in and A2 is not...

1. Make a cell get its value from the one above it:
   1. In A2, put: =A1
2. Copy this cell:
   1. Select A2
   2. Edit > Copy
3. To select only blank cells that you want filled:
   1. Select the area to fill (both cells with content and blanks) (e.g. A1 to A245)
   2. Edit > Find and Replace
   3. In the 'Find:' text box, put: ^$ (this is the regular expression meaning there's no content).
   4. Under 'Other options', select Current selection only and Regular Expressions
   5. Press 'Find All', then return to your sheet by closing the dialog boxes
4. Paste what you copied earlier. The formula in each of the selected cells will now say to display the value from the cell above it:
   1. Edit > Paste

Answer 9

From dozens answers I have read and tried, only this method is the easiest one!

Have a look at this: https://www.youtube.com/watch?v=AeVBH0ClCoA