How to Fill Blank Cells with Value above

,

This post will guide you how to fill the blank cells with value above in MS Excel. And you will also learn that how to select all blank or empty cells in the selected range of cells in Excel.

Fill Blank Cells with Value above

Suppose that you have a list of data in excel worksheet, you may be leave an empty cell if the value of that cell is the same as with the above value of cell.  And if you want to sort or filter this data, you are not able to get the correct result, since there are lots of empty cells. You need to fill in all the blank cells at this moment. And this post will show you one very quick way to fill blank cells with above value using Excel formula.

Select All Blank Cells

Before filling in all the blank cells, you need to select all them firstly. If there is a large number of empty cells in your big table, you are not likely to choose each blank cells by manually, and how to select empty cells quickly? You can do it as the following steps:

Step1# Select the range of cells that contains blank cells you want to fill in.

fill blank cell with above vaue1

Step2# Go to “HOME” Tab, then click “Editing” -> “Find & Select”->”Go To Special…”, the Go To Special dialog box will appear.

fill blank cell with above vaue2

Step3# select Blanks radio button and then click “OK” button.

fill blank cell with above vaue3

Step4# you will see that all of the empty cells have been highlighted in the selected range.

fill blank cell with above vaue4

Fill in Empty Cells with Value using Excel Formula

After you select the blank cells following the above steps, you can use a very simple formula in one of blank cells, then press “Ctrl +Enter” to apply this formula to all selected blank cells. Let’s refer to the below steps:

Step1# select all the blank Cells

fill blank cell with above vaue4

Step2# you can see in the above screenshot that the active Cell is A3. Then move the cursor in the Formula bar and enter the formula “=A2”. It will fill in Cell A3 with the value of Cell A2.

fill blank cell with above vaue5

Step3# Press “CTRL + Enter” to apply the formula to all the selected blank cells.

fill blank cell with above vaue6

You will see that all blank cells have been filled with value above. But if you want to sort or filter data, you have to change the formulas to values, otherwise, you will end up with a mess. Just continue to follow the next steps.

Step4# select the whole range of cells, then click “CTRL+C” key

fill blank cell with above vaue7

Step5# Press “CTRL + Alt + V” shortcut, the Paste Special dialog box will appear. Then select “Values” radio button from Paste box, and select “None” radio button from Operation box.

fill blank cell with above vaue8

Step6# click “OK” button. All formulas are replaced with their values.

fill blank cell with above vaue9


Related Posts

  • Apply a formula in entire column or row without/with dragging
    Assuming that you want to apply a formula to large number of column cells or entire row. If you want to apply a specific formula to hundreds of rows or columns and it should be a very boring or tedious job. So you can use apply formula without dragging by using short cut or Fill Command.…

Leave a Reply