Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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`

share|improve this answer

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.

share|improve this answer

You must log in to answer this question.

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