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').

9 Answers 9


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.

  • 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 Oct 20, 2016 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. Oct 27, 2017 at 20:25

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.

  • 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. Oct 27, 2017 at 20:22
  • Uffff thank you! Finally something worked after 3 hrs of wasting time.
    – Gogol
    Jul 21, 2022 at 13:38

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


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)
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
Select again the whole area
Check 'text', 'number', may be 'format' (what you want to copy) and 'skip empty cells'.

Found this at: OpenOffive Forum

  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.

i'm using libreoffice . 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


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`


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

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

  • 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 Jan 24 at 3: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 Bot
    Jan 24 at 3:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .