Thursday, December 25, 2008

How to fill blank cells in excel data range with cell above it

Many times we need to compile data using pivot table. However when you copy data from a pivot table we find blank cells below a row label and it is big challenge to fill all those blanks with row lables. There is very simple way to fill all blank cells.

Step 1 - Select the entire range you need to fill

Step - 2Edit > Goto > Special > Blanks.

Once you click OK button, excel will select the blank cells.

Step -3 Enter the cell reference of immediate above cell in active cell without changing the selection. Hold control key and press enter

See video attached.


  1. Yogesh -

    I can't get this to work. After I hit Ctrl+Enter, it just copies the cell reference to all the blank cells (they all say =A3 in the cells). I am using Excel 2003.

  2. Nevermind - I got it. Macros weren't enabled. Thanks for the great tips - this saved me a lot of time!