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.

I can't select all the non-empty cells.
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').

score 5 · Answer 1 · edited Jul 25 '13 at 17:13

5

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.

edited Jul 25 '13 at 17:13

Roney Michael

1,056
1
14
22

answered Jul 25 '13 at 16:07

docwebhead

161

This answered my question! I googled 'libreoffice calc fill column with formula' and got straight here. Just what I wanted and saves dragging fill handles hundreds of rows down – John Lawrence Aspden Oct 20 '16 at 18:51
7

In LibreOffice 5 this is under the "Sheet" menu. However, this also fills non-blank cells in addition to blank cells, which is not the desired behavior for the example above or any case where there are some values in the specified range. – Max Candocia Oct 27 '17 at 20:25

score 5 · Answer 2 · edited Aug 20 '13 at 14:53

5

In LibreOffice 4.0 you can do a search and replace:

select the area in which you want to fill empty cells
Edit > Search & Replace
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”
insert into the field of replace the text you want to replace

This may also work in previous versions of LibreOffice.

edited Aug 20 '13 at 14:53

nc4pk

9,117
14
60
71

answered Aug 20 '13 at 14:28

andreas

67

It sort of worked, but there are 2 problems:
major: It replaces the contents with string formatting (e.g., '0 instead of 0) when you replace it, and I can't change that.

minor: It does not change the edit history at all, and it cannot be undone unless you reload a previous version of the file.
– Max Candocia Oct 27 '17 at 20:22
Uffff thank you! Finally something worked after 3 hrs of wasting time. – Gogol Jul 21 '22 at 13:38

score 3 · Answer 3 · answered Mar 08 '13 at 21:46

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

score 2 · Answer 4 · answered Mar 11 '13 at 21:13

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

score 1 · Answer 5 · edited Jan 30 '19 at 22:35

1

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($A2="";$B1;$A2)
With B1 still selected press Ctrl+Shift+End then Ctrl+D to copy this formula for the rest of the column.

edited Jan 30 '19 at 22:35

HackSlash

4,703

answered Jan 30 '19 at 19:46

Mariuss

11

Mahipal Gunawat · Answer 6 · 2020-06-01T09:23:54.980

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

select the area in which you have your data (including blank cells where you want to fill value from nearest above non-blank cell)
do ctrl+H (it will pop up find and replace window )
put ^$ in 'find:' text box (to blank cells and select them when clicking 'Find All'/'Replace All' button)
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)
Click on 'Replace All' button (it will put @=INDIRECT(CONCATENATE("R", (ROW()-1),"C",COLUMN()),0) in all blank cells)
Put @ in 'find:' text box (we want @ to be replaced)
leave blank 'replace:' text box (we want @ removed)
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) )
do ctrl+A (or manually select area which include the cell which were hitherto blank)
copy this area
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

score 0 · Answer 7 · answered May 29 '21 at 20:15

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

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

Make a cell get its value from the one above it:
1. In A2, put: =A1
Copy this cell:
1. Select A2
2. Edit > Copy
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
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

score 0 · Answer 8 · answered Jan 24 '23 at 03:17

0

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

answered Jan 24 '23 at 03:17

abel

1

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Rohit Gupta Jan 24 '23 at 03:37
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Community Jan 24 '23 at 03:38

score 0 · Answer 9 · answered May 29 '13 at 03:01

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`

Quickly fill blank cells in a list in LibreOffice Calc

9 Answers9