Fahim Shahriyar Dipto
Microsoft Excel provides extraordinary features and formulas to fill empty cells with the last value. While dealing with a massive dataset, you may find some empty cells where you want to put the previous value you entered earlier. Do you want to fill the empty cells in the blink of an eye? No worries, here we will show you the straight and effective way to fill the empty cells with the last value. So, let’s get started.
How to Fill Empty Cells with Last Value in Excel: 4 Methods
We have discussed 4 quite easy and simple methods to fill empty cells with the last value in Excel. These methods are very straightforward for you to visualize. Here we use a Sample Dataset of Products and their Date of Sales with Product ID.
Not to mention, we use the Microsoft 365 version. You can also use any version at your convenience.
1. Applying Go To Special Feature and a Formula
The Go To Special feature under the Home tab shows you the path to quickly pick all the cells that meet your assertive criteria such as (empty cells, errors, logical text, etc.). Here in our dataset, we can see there are some empty cells in the Date of Sales column where we want to put the last value we entered. For doing these we’ll use the Go To Special feature. We have demonstrated the following steps.
📌 Steps:
- In the very beginning, select the entire dataset e.g. B4:D10, and go to Find & Select under Home tab >> then click on Go to Special.
- A dialog box named Go To Special will appear. From there, check the Blanks option and hit OK.
- By doing this, the empty cells will be highlighted.
- Now, press the Equal to (=) button from the keyboard in the active cells and select the last value you entered.
- Eventually, press CTRL + ENTER for the other empty cells. It will be filled with the last value of previous cells.
- Finally, your empty cells will be filled with the last value just like the snapshot below.
Note: You can format your empty cells according to your preference.
Similar Readings
2. Employing Find and Replace Feature
The Find and Replace will find the empty cells and you can easily fill them with the last value. It is a feature under the Home tab which allows you to find values for certain criteria. Let us show you the necessary steps to use it.
📌 Steps:
- First of all, go to the Home tab >> under the Editing group pick Find & Select >> then select Find command.
Note: You can also open the Find command by using Ctrl + H from the keyboard.
- The Find and Replace dialog wizard will pop out. Under the Find ribbon, go to Options<<, remain the Find what box blank >> then check Match entire cell contents >> select the Within box Sheets. >> and then go to Find All.
- It will show you all the empty cells of your dataset and then select all of them by CTRL + A. Lastly hit the Close option.
- All the empty cells will be highlighted and then press (=) in the active cell D10 and click on the last value you entered in cell D9.
- Press CTRL + ENTER for other empty cells to be filled with the last value.
- After that, you will get your results.
Read More: How to Find and Replace Blank Cells in Excel
3. Using Nested LOOKUP Formula
The LOOKUP function looks for a certain row or column. It finds out the value for the exact same position and fills them. This formula will help you to fill the empty cells automatically by creating a new dataset where the empty cells are filled with the last value. Here we also use the IF and LEN functions to complete the data table.
📌 Steps:
- Firstly, you have to create a Table. For this reason, select the entire data range>> go to the Insert tab>> choose the Table option.
- A Create Table dialog box will pop out. Check My table has headers >> press OK.
- A table has been created and now go to the F4 cell and write down the formula
=LOOKUP(ROW(B4:B10),IF(LEN(B4:B10),ROW(B4:B10)),B4:B10)Formula Breakdown:
The syntax of the formula:
=LOOKUP(lookup_value, lookup_vector, [result_vector])
- Here, lookup_value takes the data we want to find out. Since we have multiple rows in our data set the ROW function is working here which takes the range of the column. In our dataset, the ROW function will take the B4:B10 column value.
- lookup_vector is using the IF function nested with the LEN function and the ROW function. Both take the range of columns to create a vector form. The LEN function counts the character length, and the ROW function returns the column value. Then the IF function will give the output (4,5,6,7,8,9,10) which will be row numbers.
- result_vector is the result value taken in the form of a vector to get the desired result. Here it returns the result of the Products column.
- Then go to the G4 cell and write down the same formula changing the column.
=LOOKUP(ROW(C4:C10),IF(LEN(C4:C10),ROW(C4:C10)),C4:C10)- This will create a new column of Product ID.
- Furthermore, go to cell H4 and enter the same formula changing the reference column. It will create the Date of Sales column where the empty cells are filled with the last value. The formula is
=LOOKUP(ROW(D4:D10),IF(LEN(D4:D10),ROW(D4:D10)),D4:D10)
- As you can see, the date Number Format is General by default. For changing it go to the Home tab >> Under Number option select Short Date.
- Finally, you will get a newly created dataset where the empty cells are filled with the last value.
4. Using VBA Macro to Fill Empty Cells with Last Value
The VBA macros can also fill the empty cells with the last value. Though it might take a few minutes to run the code, this method works pretty well for lengthy datasets. Follow the steps to do that.
📌 Steps:
- Initially, select the entire range of your dataset and go to the Developer tab >> select Visual Basic.
- A new dialog box will appear. Choose Insert tab >> go to Module >> select Module1.
- A new window box will show and in that box write down the code stated below.
Sub Fill Cell From Last Value ()
For Each cell In Selection
If Cell. Value = "" Then
Cell. Value = Cell. Offset(-1, 0).Value
End If
Next cell
End Sub
- Finally, you fill your empty cells with the last value.
Read More: How to Fill Blank Cells with Value Above in Excel VBA
How to Fill Blank Cells with 0 in Excel
Sometimes you may want to fill your empty cells with 0 (zero). You can do that by the Find and Replace command. Here we take a dataset where you can see some cells under the Product column are missing. We will replace the cells with 0. This is amazingly simple. Follow the steps below.
📌 Steps:
- Firstly, select the entire dataset range and go to the Home tab >> move to Find & Select command >> click on Replace.
- A dialog window of Find and Replace will take place. From there initially go to the Replace option>> go to Options>>check the Match entire cells content >> keep the Find what box blank >> Select Sheets in the Within box >> put a 0 in the Replace with box.
- Lastly, click on Replace All.
- Finally, the empty cells will be replaced by 0.
How to Fill Empty Cells with Default Value in Excel
You can also fill your empty cells with a default value by using simple IF functions. The IF function will check the dataset and return the value for empty cells as per your given order. Here is our dataset of Student’s Attendance Sheet where we want to find out the missing values. The steps are described for your better understanding.
📌 Steps:
- Firstly, select the cell D5 and write down the formula
=IF(C5="","Missing",C5)The syntax =IF(C5=””,”Missing”,C5) will check if cell C5 contains a blank cell. If the logic is TRUE then it will return Missing. Otherwise, it will return the same value in C5.
- Press ENTER.
- Later, you may drag down the Fill Handle tool for the other cells.
- Finally, you will get the desired result you want.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Conclusion
That’s all about today’s session. These are some easy methods on how to fill the empty cells with the last value in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.
