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.
How to Remove Missing Values in Excel: 7 Easy Methods
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.
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.
- A dialog prompt will appear named Go To Special. After that select Blanks and hit OK.
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.
- So, all the blank cells will be highlighted.
- Then click on any cell and right-click.
- Afterward, select Delete from the Context Menu.
- 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
2. Using Filter Command
Also, you can filter your data using the Filter command. It filters your data according to your preferences. 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.
- Click on the Drop-down icon from any column and select (Blanks).
- Press OK.
- Finally, the result will look like the one below, and your missing values are gone.
Read More: How to Fill Missing Values in Excel
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.
- 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.
- 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.
- Finally, all your missing data will be disappeared.
Read More: How to Compare Two Excel Sheets to Find Missing Data
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.
- 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.
- Finally, you are able to remove your missing data as per our snapshot.
Read More: How to Cross Reference in Excel to Find Missing Data
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.
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.
- 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.
- It will send all the blank rows at the bottom of the data table and you can ignore them.
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.
- Create a table and select My table has headers. Then press OK.
- 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.
- 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
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 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.
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.
- Finally, the output will look just like the one below.
Read More: How to Filter Missing Data in Excel
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.
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.
- 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 Count Missing Values in Excel
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 realize the topic more clearly.
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 a better understanding, please download the practice sheet. Thanks for your patience in reading this article.
<< Go Back To Missing Values in Excel | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
