Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Fill Empty Cells with Last Value in Excel (4 Handy Ways)

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.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


4 Methods to Fill Empty Cells with Last Value in Excel

We have discussed 4 quite easy and simple methods to fill empty cells with 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.

Dataset

Not to mention, we use 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.

Applying Go To Special Feature and a Formula to fill empty cells with last value in Excel

  • A dialog box named Go To Special will appear. From there, check the Blanks option and hit OK.

Go To Special Window

  • 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.

Applying Go To Special Feature and a Formula to fill empty cells with last value in Excel

Note: You can format your empty cells according to your preference.

Read More: How to Fill Blank Cells with Formula in Excel (2 Easy Methods)


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.

Employing Find and Replace to fill empty cells with last value in Excel

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.

Find and Replace window box

  • 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.

Applying formula to fill empty cells with last value in Excel

  • After that, you will get your results.

Read More: How to Fill Blank Cells with Value Below in Excel (4 Easy Ways)


3. Using Nested LOOKUP Formula

The LOOKUP function looks for a certain row or column and 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 and go to the Insert tab and choose the Table option.

Creating Table to fill empty cells with last value in Excel

  • 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.

Using Nested LOOKUP Formula to fill empty cells with last value in Excel

  • 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 entered 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)

Using Nested LOOKUP Formula

  • 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.

Formatting Date

  • Finally, you will get a newly created dataset where the empty cells are filled with the last value.

Read More: How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)


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.

Using VBA Macro to Fill Empty Cells with Last Value in Excel

  • 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

VBA code

  • Finally, your fill your empty cells are with the last value.

Read More: How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)


How to Fill Blank Cells with 0 in Excel

Sometimes you may want to customize 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.

How to Fill Blank Cells with 0 in Excel

📌 Steps:

  • Firstly, select the entire dataset range and go to the Home tab >> move to Find & Select command >> click on Replace.

Using Find and Replace to fill empty cells with last value in Excel

  • A dialog window of Find and Replace will take place. From there initially go to Replace option and 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 returns 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.

How to Fill Empty Cells with Default Value in Excel

  • Later, you may drag down the Fill Handle tool for the other cells.

Fill Handle tool

  • 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.

Practice Section


Conclusion

That’s all about today’s session. And these are some easy methods on how to convert time zones in excel Please let us know in the comments section if you have any questions or suggestions. For your better understanding please download the practice sheet. Visit our website Exceldemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.


Related Articles

Fahim Shahriyar Dipto

Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo