Excel: Fill Blanks With Value Above

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: Someone set up data in an outline view. I need to sort by columns A, B, and C, so I need all of the blanks filled in.

  1. Fill in the blanks.

    Strategy: Use Go To Special to select the blank cells. Then, 3 simple keystrokes will fill in the blank cells with the value above. Follow these steps.

    1. Select from A3 down to the last blank in column C.
    2. Select Home, Find and Select, Go To Special.
    3. In the Go To Special dialog, choose Blanks and then click OK.
    4. Type and equals sign and press the up arrow. This will create a formula that points up one cell.

  2. Equals, Up Arrow.
    1. Press Ctrl+Enter to fill all of the selected cells with a similar formula.

  3. All of the blank cells are filled in.
    1. Before you can sort, you need to convert the formulas to values. Paste Values does not work on a non-contiguous selection, so you have to re-select columns A:C
    2. Ctrl+C to copy
    3. Home, Paste dropdown, Paste Values to convert the formulas to values.