Use Goto to populate blanks

I copy data into Excel from our system, but the data isn’t complete. One column has an entry followed by blanks cells, then another entry followed by blank cells all the way down the column. The blank cells need to have the entry from above. Is there a way to populate only the blank cells with the entry above it?

By Neale Blackwood

You can use Excel’s Go To feature to solve this common problem. First enter the required value in the very last data cell in the column involved. Then select the range you need to populate down to that last data cell. A quick way to select data in a column is to select the first cell in the column then hold the Ctrl and Shift keys, then press the End key. This will select the column down to the last entry.

With the range selected press the F5 key to open the Go To dialog and click the Special button. Click the Blanks options and click OK. This will highlight all the blank cells in the range. Then press the = key and click on the data cell above the very first blank cell. Then hold the Ctrl key and press the Enter key. This will enter the formula in every blank cell. Every formula will refer to the cell above itself.

Select the whole range and copy it. Use the drop down arrow on the Paste icon and select Paste Values. This captures the values in the column and removes all the formulas. You need to avoid using formula in data lists that refer to other rows as these usually become corrupted when you sort the data lists.