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

How to Fill Missing Values in Excel (5 Useful Tricks)

Working on a long dataset sometimes results in missing values in excel. Then it becomes a really complex process to find each blank cell and fill them. But excel has made it easy for us to fill missing values. In this article, we will discuss how to fill missing values in excel with 5 useful tricks.


Download Workbook

Get the sample file to practice by yourself.


5 Useful Tricks to Fill Missing Values in Excel

Let us take an example for illustration. Here, the dataset shows the information of sales amount of some cities in 3 different countries.

How to Fill Missing Values in Excel

As you can see, there are some blank cells in the dataset. So we will apply the tricks below to fill them with values.


1. Use Fill Series Tool to Fill Missing Values

In this first method, we will use the fill series tool to fill up the blank cells in the dataset. Follow the process below:

  • In the beginning, select cell range D6:D8 as they are the first and last cells between the missing values.

Use Fill Series Tool to Fill Missing Values

  • Next, go to the Home tab and click on the Fill icon under the Editing group.

  • Here, select Series from the drop-down menu.

  • Following, the Series window will appear.
  • In this window, select Columns and Growth under the Series in and Type sections respectively.
  • Along with it, mark the Trend box.

Use Fill Series Tool to Fill Missing Values

  • After this, press OK.
  • That’s it, you will see the missing value in the blank cell.

  • Follow the similar process to get the final output like this:

Note: This method is applicable for numeric values. Use the AutoFill tool for values in text format.

Read More: How to Deal with Missing Data in Excel (6 Suitable Ways)


2. Fill Missing Values in Excel with Keyboard Shortcut

Keyboard shortcuts are the fastest way to fill missing values. This is mostly applicable to repetitive text values in your dataset. Let’s see how it works.

  • In the beginning, press the Ctrl + Down key on your keyboard to select all the blank cells at once.

Fill Missing Values in Excel with Keyboard Shortcut

  • Then, insert this formula in cell B6.
=B5

Here, we used cell B5 as the reference cell for the selected blank cells. Therefore, cell B6 will get value from cell B5.

  • After this, press Ctrl + Enter.
  • That’s it, you have all the missing values in one shot.

  • Lastly, copy column B and paste it as Value to transform it into individual texts.

Note: This method is not preferable for independent numeric values. Otherwise, the result will show repetitive values like this:

Read More: How to Find Missing Values in Excel (3 Easy Ways)


3. Use Go To Special Tool to Fill Missing Values

Another process to fill missing values is to use the Go To Special tool in excel. Let’s check the steps below:

  • Firstly, select the cell range B5:B13.

Use Go To Special Tool to Fill Missing Values

  • Secondly, go to the Home tab and click on Find & Select.

  • Thirdly, select Go To Special from the drop-down section.

  • Following, you will see the Go To Special window.
  • Here, select Blanks and hit OK.

  • Therefore, the blank cells will be separated.
  • After this, insert this formula in cell B6.
=B5

  • Next, press Ctrl + Enter.
  • Finally, you will get the missing values in blank cells.

How to Fill Missing Values in Excel

  • In the case of repetitive numeric values, separate the blank cells as described above.
  • Then, insert your desired value on the formula bar.
  • Lastly, press Ctrl + Enter and you will see the final output.

Read More: How to Find Missing Values in a List in Excel (3 Easy Methods)


4. Fill Missing Values with Excel VBA Code

In this section, we will apply excel VBA Macro codes to fill the missing values in our dataset. Let’s check the following steps:

  • First, go to the Developer tab and select Visual Basic.

Fill Missing Values with Excel VBA Code

  • Then, click on Module under the Insert tab.

  • After this, insert this code on the blank page.
Sub Fill_blank_cells_with_missing_value()
Dim wk As Worksheet
Dim rg As Range
Set wk = Worksheets("VBA")
For Each rg In wk.Range("D5:D13")
If IsEmpty(rg) Then
rg.Value = 525
End If
Next
End Sub

  • Next, click on the Run Sub button or press F5 on your keyboard.

  • Then, press Run on the Macros window.

  • Finally, you can see the missing value in the dataset.

Read More: How to Filter Missing Data in Excel (4 Easy Methods)


5. Apply INDEX Function to Interpolate Missing Values in Excel

In this last section, we will apply the INDEX function to compare two sets of data and input the missing one. Follow the process here:

  • In the beginning, we prepared two lists of seller cities based on the month of June and July.
  • Here, you can see the second list has some missing city names.

Apply INDEX Function to Interpolate Missing Values in Excel

  • Therefore, insert this formula in cell D11.
=INDEX($B$5:$B$13,MATCH(TRUE,ISNA(MATCH($B$5:$B$13,$D$5:D10,0)),0))

Here, we used the INDEX function for comparing the selected cells. Then, we applied the MATCH function to search the specified missing value with the condition TRUE. Lastly, we applied the ISNA function to avoid error with a 0 in the end for getting an exact match.

  • Next, press Shift + Ctrl + Enter.
  • That’s it, we have our first missing value.

  • Lastly, drag the bottom corner of cell D11 up to cell D13 to get the final output.

Read More: How to Compare Two Columns in Excel for Missing Values (4 ways)


Conclusion

I hope it was a helpful article for you on how to fill missing values in excel with 5 useful tricks. Get the sample file and try any of them. Follow ExcelDemy for more excel blogs.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo