Filling in the blanks

Some worksheets contain cells that have been left blank, e.g., in order to make the headings and subheadings easier to read. However, if for example you want to sort or filter the list or you want to use the table for analyses, you need to fill in the blanks. You could manually copy the value from the first filled cell above the blanks. An easier and faster way to do this is described on this site. The technique they describe requires the Go To function, which unfortunately is not available in Openoffice’s Calc. However, there is an alternative way (which works also in Excel).

Start by selecting the empty cells:Step 1. Select column with empty cells

  1. Select the cells in the column, including the column heading
  2. Choose menu Data | Filter | Auto filter
  3. Select -empty- from the auto filter drop down menu
  4. Note – you could obviously use the normal filter instead

Enter the formula to copy the value:Select -empty- auto filter from drop down menu

  1. Type the reference to the cell above. For example, when the first empty cell is A3, fill in =A2
  2. Copy that cell and paste in the other empty cells
  3. Select ‘All’ from the auto filter drop down menu

Change the formulas to values:

In order to be able to sort or filter the data, the formulas must be changed to values.Fill in reference to cell above

  1. Select the entire column
  2. Choose Edit | Copy
  3. With the column still selected, choose Edit | Paste Special (Ctrl + Shift + v)
  4. De-select Formulas (and Formats if you want to retain the original formatting) and click OK (Note: Do this carefully if there are other cells in the range which contain formulas)

SpecialCells add-in

If you want to replace all blank cells by the same value you can use the Calc add-in ‘Special-Cells’, which you can download from OOoMacros. This add-in makes it very easy to, amongst others, select all blank cells in a column, row or table. Suppose for example that we want to fill in all blank cells with 999 ( I am assuming you have installed the SpecialCells add-in already).

  1. Select / highlight your row, column, or table of data containing the blank cells.
  2. Tools | Add-ons | Special Cells | Contents
  3. In the SpecialCells window select Blank Ranges and click OK
  4. Open the Find & Replace window (Edit | Find & Replace or Cntrl + F), leave the ‘Search for’ box blank, enter 999 in the ‘Replace with box’ and click the ‘Replace all’ button.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s