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.…
Related Posts
If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

Sidebar