If you have ever gotten a report that looks like this, you know it can be a pain to work with. It's like a PivotTable without the Pivot. Luckily there is a quick step we can take to turn this report into something usable, by filling down all the blank cells with the correct values.
Contents:
1.1. How to Select Empty Cells
How to Fill Down Blanks with the Value Above
There are just a few steps needed to take a report that looks like the top image and fill out all of the blank cells with the correct information. This will save you time copy pasting or dragging each cell down by hand.
First, we'll select all of the empty/blank cells and then enter in single formula into all of the blank cells to copy the values down.
How to select empty cells in Excel worksheets
1. The first step is to select all of the empty or blank cells in the table. The easiest way to do this is press Ctrl + A on the table to select the entire table:
2. Next, press Ctrl + G or F5 to open the "Go To" menu:
3. Click "Special...":
4. Then press "Blanks":
5. This will select all the blank cells in the table:
The next step will be to place a formula in each cell to copy the values in each cell down into the selected blank cells.
Excel formula to fill in blank cells with value Above
With all of the blank cells in the table selected, we can use a simple formula to copy down the values into the blank cells.
1. Type "=" and then select the cell directly above the first blank in the table. As a shortcut, you can type = and then press the up arrow key to automatically select the correct cell
2. Next, instead of pressing "Enter", we're instead going to press Ctrl + Enter. This will place the formula in every selected cell
Now every blank cell will be properly filled with the above cells. But, the table is full of formulas instead of values so we'll need to value out the table
3. Select the entire table again using Ctrl + A And copy it using Ctrl + C
5. Now paste as values by right clicking and pasting as values or using the shortcut Alt + E + S + V , now you have a valued out table ready to use.
