How to Remove Missing Values in Excel (7 Easy Methods)

Sometimes, while entering data in an Excel worksheet, there are some missing values like missing columns or missing rows. We need to remove those empty cells from our worksheet to give the worksheet a decorative look. You can simply select the blank cell and manually press the CTRL+ – key to remove the missing value. It is a pretty easy and handy task while you are dealing with a small dataset. But if you have a large dataset on your worksheet, this method will be time-consuming, and boring too. You can remove the missing values automatically from your worksheet by applying some easy methods. Here, we will describe 7 easy and simple methods to remove missing values in Excel.


Download Practice Workbook

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


7 Methods to Remove Missing Values in Excel

Here, we use a dataset of Student’s CGPA with their Student ID. You can see there are some missing values in Row 8 and Row 11. We need to remove those missing columns from our dataset.

Dataset

Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Applying Go To Special Command

Microsoft Excel has introduced us to many features for removing missing values. One of those features is the Go To Special command. This feature helps you remove the blank cells and missing values from the whole dataset. You have to follow some steps.

📌 Steps:

  • Firstly, select the range of your dataset, and then select Find & Select under the Home tab >> click on Go To Special.

Applying Go To Special Command to Remove the missing values in Excel

  • A dialog prompt will appear named Go To Special. After that select Blanks and hit OK.

Go To Special dialog box

Alternatively, you may press CTRL + G to get Go To window. Then choose the Special option or press ALT + S. Immediately, you’ll get the Go To Special window as found earlier.

Inserting Go To Special

  • So, all the blank cells will be highlighted.
  • Then click on any cell and right-click.
  • Afterward, select Delete from the Context Menu.

Deleting the blank rows

  • Next, pick the Entire row from the Delete window.
  • Lastly, press OK.

  • Finally, your blank cells will disappear and your worksheet will look like the one below.

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


2. Using Filter Command

Also, you can filter your data using the Filter command. It filters your data according to your preference. So, you can remove the missing values in Excel by using the Filter command. Follow the steps.

📌 Steps:

  • Select the entire range of your data and go to Sort & Filter >> select Filter.

Using Filter Command to remove missing values in Excel

  • Click on the Drop-down icon from any column and select (Blanks).
  • Press OK.

Drop down filter

  • Finally, the result will look like the one below, and your missing values are gone.

Filtered missing values

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


3. Utilizing Find Feature

The Find feature figures out the blank cell and helps us to remove the missing values too. You have to leave a blank cell if you want to remove them. Here, we will give some steps to do that.

📌 Steps:

  • Initially, select the whole data range and go to Find & Select >> Select Find.

How to remove missing values in Excel by utilizing Find Feature

  • A dialog box will pop out (Find and Replace). Go to Options >> leave the Find what box empty. Check the box before Match entire cell contents.
  • Then, select By Rows under the Search box, and select Values in the Look in box. And select Find All.

Find and Replace

  • Later, it will show you all the missing rows. You can easily select all the cells by pressing CTRL + A and then delete the cells.

Utilizing Find Feature

  • Finally, all your missing data will be disappeared.

Read More: How to Compare Two Excel Sheets to Find Missing Data (7 Ways)


4. Applying Advanced Filter

Advanced Filter allows you to make a new dataset that will not have the missing value. It differs from the other methods because it will open a new dataset and you can customize it according to your preference.

📌 Steps:

  • Primarily after selecting the whole dataset, go to Data tab >> select Advanced in the Sort & Filter command.

Applying Advanced Filter to remove missing values in Excel

  • Enter a value before going to the next steps. Like here we enter B5<>” ”. The syntax will return TRUE if you have a value in the entire row you have selected.

  • A dialog box will appear named Advanced Filter, select Copy to another location. The List range will be $B$4:$D$12. The Criteria range will be $F$4:$F$5 and Copy to will be $G$4:$I$4.
  • Press OK.

Advance Filter dialog box

  •  Finally, you are able to remove your missing data as per our snapshot.

how to remove missing values in Excel by using Advanced Filter

Read More: How to Cross Reference in Excel to Find Missing Data (6 Ways)


5. Utilizing FILTER Function

Basically, the FILTER function filters a range of data according to the criteria you have entered in the function formula. According to your defined criteria, this function will filter all the data and give you the output.

📌 Steps:

  • Firstly, select a blank cell where you want to enter the table. Then write down the formula.
=FILTER(B4:D12,(LEN(C4:C12)>0)*(LEN(D4:D12)>0))

Formula Breakdown:

LEN function→ Generally, it counts the length of the character you entered in the cell. If the character length value is greater than 0, it will return TRUE. Otherwise, it will return FALSE. As there is no character in a blank row. So, it will return FALSE.

FILTER function→ This function will filter the output of the LEN function and sort it. When your character length is less than 0, that means a blank cell, the LEN function will return 0. Then the FILTER function will make a serial where the blank cell will disappear.

  • Press ENTER.

Utilizing FILTER Function to remove missing values

  • It will import the whole data table without the missing value just like the image below.


6. Applying Sorting Feature

Applying the Sorting feature is quite an easier and time-saving task to remove the missing values. Sorting the date helps you find and sort the missing values, ascending or descending, according to how you want to set them.

📌 Steps:

  • First of all, select all the cells and go to the Data tab, and then select the A→Z sort.

Applying sorting feature to remove missing values in Excel

  • It will send all the blank rows at the bottom of the data table and you can ignore them.

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


7. Employing Power Query Feature

The Power Query feature is generally used to import a data table from an external dataset. You can also modify and give an attractive look to your imported data by customizing it. Simple steps have to be followed to employ the feature.

📌 Steps:

  • Firstly, select all the cells, go to the Insert tab >> go to Table.

how to remove missing values in Excel by employing Power Query Feature

  • Create a table and select My table has headers. Then press OK.

Creating a table

  • After entering the table, go to the Data tab >> select From Table/Range.

  • A new window named Power Query Editor. Select Reduce Rows >> go to Remove Rows >> click on Remove Blank Rows.

Power Query editor

  • Later your edited table will be made so the rows with no value will disappear. You can also customize your table.

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


How to Find Missing Values in Excel

Sometimes you need to find out some specific value in your dataset, whether it already exists or not. To remove the missing values in Excel, you can use the IF and COUNTIF functions. Here, we give you an example of using the IF and COUNTIF functions to find out the missing values.

📌 Steps:

  • Firstly, select cell F5 where you want to find out whether the value exists or not. then write the below formula.
=IF(COUNTIF(B5:C10,E5),”Ok”,”Missing”)

Formula Breakdown:

COUNTIF function→ checks the entered data in E5 exists between the entire dataset.

IF function→ checks if the value exists in the dataset, it gives the output OK. Otherwise, it will return Missing.

  • Press ENTER.

How to Find Missing Values in Excel

  • Finally, the output will look just like the one below.

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


How to Deal with Missing Data in Excel

While dealing with missing data in Excel, you can use the IFERROR and VLOOKUP functions. Follow the steps.

📌 Steps:

  • Select cell D5 and write the formula.
=IFERROR(VLOOKUP(C5,$C$5:$C$12,1,0),”Not Present”)

Formula Breakdown:

The VLOOKUP function considers C5 as the lookup_value and $C$5:$C$12 as the lookup array, 1 as col_index_num, and 0 as range lookup.

The IFERROR function returns Not Present if it finds an error or the value of column C if it doesn’t find an error. So, when the LOOKUP function doesn’t find a value and makes an error, the IFERROR function returns Not Present, otherwise, it will return the same value as column C.

  • Press ENTER.

How to Deal with Missing Data in Excel

  • Finally, you will get the missing values. Here we declare the missing value as “Not Present”. You can also highlight them by using a suitable color.

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


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 to remove missing values in Excel. Please let us know in the comments section if you have 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 about 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