Sometimes, in excel, large datasets contain blank cells randomly located, which may be required to be deleted later. However, it might seem time-consuming to detect each of the empty cells one by one and delete them. This tutorial will guide you on how to exclude these blank cells using Advanced Filter in Excel.
You can download the practice workbook that we have used to prepare this article.
What is Advanced Filter in Excel?
We all are more or less familiar with the regular Filter option in excel. But the regular Filter option has some limitations. To illustrate, data cannot be filtered to a different location or you cannot filter data based on complex criteria. Luckily, the Advanced Filter in excel solves all the abovementioned problems. Let’s assume we have a dataset (B5:D12) having several electronic products along with their corresponding state-wise sales. Below here, I have filtered sales data (>$7,000) for the state CA using Advanced Filter. Moreover, I have filtered the data to a new location.
3 Easy Tricks to Use Excel Advanced Filter to Exclude Blank Cells
1. Remove Empty Cells from One Column Using Excel Advanced Filter
Firstly, I will show you how to exclude blank cells from a particular column. Suppose I have some blank cells in column D (highlighted below). Now I will remove these empty cells.
Steps:
- Type the below formula in Cell F6.
=D5<>""
- Once you hit Enter, the formula will give the below result.
- Then, from Excel Ribbon, go to Data > Advanced Filter.
- As a result, the Advanced Filter dialog box will appear. Choose Copy to another location from the Action section. Now, specify List range (B5:D12), Criteria range (F5:F6), and Copy to location (B14) as below. After that press OK.
- Finally, you will see that all the blank cells are excluded from the dataset, and here is our filtered data.
⏩ Note:
While selecting the Criteria range make sure you have selected a cell range (here, F5:F6). If you select only one cell, the filter won’t work as excel demands a range here.
Read More: Apply Advanced Filter Based on Multiple Criteria in One Column in Excel
2. AND Function with Advanced Filter to Exclude Blank Cells from Multiple Columns
This time I will show you how to delete empty cells from multiple columns in excel. To perform my task, I will take the help of AND function. Let’s consider the below dataset for the current method. Here we have some blank cells spread over columns D and E.
Steps:
- First, type the below formula in Cell G6 and hit Enter.
=AND(D5<>"",E5<>"")
- Consequently, we will get the below result.
- Now go to Data > Advanced Filter.
- Then specify the List range, Criteria range, Copy to location as below and press OK (see screenshot).
- Upon pressing OK, we will get the below output; excluding the empty cells in columns D and E.
⏩ Note:
Using the AND function argument, you can add as many columns as you want to the formula and thus delete the blank cells.
Read More: Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)
Similar Readings:
- How to Apply the Advanced Filter to Copy to Another Location in Excel
- How to Use Advanced Filter to Copy Data to Another Sheet in Excel
- Excel VBA Examples of Advanced Filter with Criteria (6 Criteria)
- Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]
3. Delete Empty Cells Using ‘<>’ Symbol Along with Advanced Filter in Excel
You can delete empty cells from multiple cells using a not equal to (<>) symbol and Advanced Filter. For instance, from my previous dataset (B5:E12), I will delete blank cells (highlighted in blue) based on two criteria (Date and Sales).
Steps:
- Firstly, type ‘<>’ in Cell G5 and H5.
- Next, go to Data > Advanced Filter.
- As a consequence, the Advanced Filter dialog will show up. Enter List range, Criteria range, and Copy to location as below. Then press OK.
- Finally, we will get all the blank cells excluded from the dataset.
⏩ Note:
- You can extract data cells that have blank cells using =”=” as criteria.
- While applying this method, make sure the headers of the Criteria range (Date, Sales) is exactly similar to the parent dataset (here B5:E12).
Read More: Advanced Filter with Multiple Criteria in Excel (15 Suitable Examples)
Conclusion
In the above article, I have tried to discuss several examples to exclude blank cells using Advanced Filter in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
Related Articles
- Dynamic Advanced Filter Excel (VBA & Macro)
- How to Use the Advanced Filter in VBA (A Step-by-Step Guideline)
- Advanced Filter with Criteria Range in Excel (18 Applications)
- Excel Advanced Filter Not Working (2 Reasons & Solutions)
- Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)