• Excel Book Excerpt

Excel Fill Blanks With Value Above

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

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.

LE10000601.jpg 

Figure 581 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.

LE10000602.jpg 

Figure 582 Equals, Up Arrow.

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

LE10000603.jpg 

Figure 583 All of the blank cells are filled in.

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.

This method is a fast way to solve this common problem.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: