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.

Posted by Yogesh Gupta at 8:35 PM
Labels:

4 comments:

  1. Yogesh GuptaApril 9, 2009 at 4:24 PM

    Check this link if you are looking for macro to find and fill blank cells

    ReplyDelete
  2. Yogesh GuptaApril 9, 2009 at 4:27 PM

    Check this link if you are looking for macro to find blank cells

    ReplyDelete
  3. Pete PumaSeptember 14, 2009 at 7:09 PM

    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.

    ReplyDelete
  4. Pete PumaSeptember 16, 2009 at 1:05 AM

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

    ReplyDelete

Subscribe to: Post Comments (Atom)
Learn how to create Excel dashboards.
Yogesh Gupta's Excel Tips