MrExcel.com - Your One Stop for Excel Tips & Solutions

Fill Blanks With Value Above

July 15, 2022 - by

Fill Blanks With Value Above

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.

Labels in A through C are in an outline format. Product ABC appears in C2, XYZ appears in A6, and ABC in A10. The assumption is that all of the blank cells below ABC should also say ABC. SImilar structures appear in B for Customer and in A for Region.
Figure 574. 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.

After Go To Special, Blanks, all of the blank cells in A:C are selected. Type an equals sign, then the up arrow. This builds a formula that points at the cell above. Follow this with Ctrl+Enter.
Figure 575. Equals, Up Arrow.

  • 5. Press Ctrl+Enter to fill all of the selected cells with a similar formula.

  • 6. 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

  • 7. Ctrl+C to copy

  • 8. Home, Paste dropdown, Paste Values to convert the formulas to values.

All of the blank cells from a few figures ago are now filled in with the label from above.
Figure 576. All of the blank cells are filled in.

This article is an excerpt from Power Excel With MrExcel

Title photo by Kelly Sikkema on Unsplash