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

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Calculate Total Cost with Excel VLOOKUP Function

In today's article we will show you how to calculate the total cost for a given weight using the Excel VLOOKUP function. This function will help us to find the appropriate unit price for that weight and then we can ...

Excel Formulas To Calculate The Bond Valuation

Assume that you've been assigned a task of calculating bond valuation; so if you are new to Ms Excel or do not have much experience with it, then I am pretty sure about it that doing this task manually might ...

Calculate Cumulative Totals with Excel SUM Function

Today, through a simple example, we will show you how to use one of the most common-used Mathematical functions in excel, the SUM function, to add up the sum. In our daily life, we keep an account of what we ...

BMI Calculation Formula In Ms Excel

You might have come across a task in which you were assigned to make BMI calculations of the supplied numbers, and you may be looking for an efficient approach to accomplish this process rather than doing BMI calculations manually, by ...

Calculate The Period of Loan or Investment in Excel

In our daily life, most of us will invest or take a loan, so we need to master some simple financial functions to calculate the period needed for loans or investments. If you work as an accountant, you need to ...

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Sidebar