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.
Table of Contents
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.
Step2# Go to “HOME” Tab, then click “Editing” -> “Find & Select”->”Go To Special…”, the Go To Special dialog box will appear.
Step3# select Blanks radio button and then click “OK” button.
Step4# you will see that all of the empty cells have been highlighted in the selected range.
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
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.
Step3# Press “CTRL + Enter” to apply the formula to all the selected blank cells.
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
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.
Step6# click “OK” button. All formulas are replaced with their values.
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
You must be logged in to post a comment.