Nazmus Sakib
Blank cells cause problems when we want to do different operations in a dataset. We will use an Advanced Filter to exclude blank cells in Excel with 3 convenient approaches here.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Use Advanced Filter to Exclude Blank Cells in Excel with These 3 Suitable Approaches
Today we will use the following sample dataset to show how to apply Advanced Filter to exclude blank cells in Excel.
1. Excluding Blank Cells from Single Column
In our first approach, we will describe how to apply Advanced Filter to exclude blank cells from a single column.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell D5 and write Items as the header.
- Then, in cell D6, type in >”” as the criterion to filter out the blank cells.
- Next, navigate to Data tab and click Advanced from Sort & Filter group.
- The Advanced Filter dialog box will appear shortly.
- Click the Copy to another location button.
- Afterward, put $B$5:$B$16 as the List range and $D$5:$D$6 as the Criteria range.
- After that, select the cell from where you want to get filtered column. In our case, we selected cell F5. Click OK.
- Voila! The Advanced Filter has removed all the blank cells from the list.
Note:
- The header must match the original list in the Criteria column. Otherwise, Advanced Filter will not work.
- You can also use it for multiple columns with blank cells or multiple columns with blank cells in a single column. However, you can filter out only one column with blank cells. Just use the header of the column you want to filter out as we did. You will get a similar outcome to the approach described below.
2. Eliminating Blank Cells from One Column of List
In this approach, we will learn how to eliminate blank cells from one column of a list using a simple criterion.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell F7 and Enter the formula below. If cell D6 is not blank, it will show TRUE in cell F7. Keep cell F6 blank as Advanced Filter wants a Criteria range.
=D6<>””
- Just like the previous approach, navigate to the Data tab and click Advanced from the Sort & Filter group.
- Next, click Copy to another location button.
- Later, select cells B5:D13 as the List range and F6:F7 as the Criteria range.
- Then, select cell B16 in the Copy to box. Click OK.
- Done! Consequently, we receive a list without the blank cells and their corresponding rows.
3. Removing Blank Cells from Multiple Columns
Now, we will demonstrate how to exclude blank cells from multiple columns of a dataset by implementing the Advanced Filter.
3.1 Using AND Function
First of all, we will use the AND function as the criterion to remove blank cells.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select cell F7 and type in the following formula, and press Enter. If both C6 and D6 cells are not blank, it will show TRUE in cell F7. Just like before, keep cell F6 empty.
=AND(C6<>””,D6<>””)
- As always, go to the Data tab to select Advanced option.
- Next, click Copy to another location and select cells B5:D13 as the List range. Also, select cells F6:F7 as the Criteria range. Then, select B16 to Copy to. Click OK.
- You’re all set! Eventually, we have our blank cells-free dataset.
3.2 Applying Specific Symbol
This time we will remove blank cells from multiple columns of a list using elementary criteria.
⬇️⬇️ STEPS ⬇️⬇️
- First, put the headers of the columns where you have blank cells. We wrote Delivery Type and Hub in cells F5 and G5 respectively.
- Next, type <> symbols in both F6 and G6 cells.
- Later, go to the Data tab to select Advanced from the Sort & Filter group.
- Afterward, click Copy to another location and select cells B5:D13 as the List range in the Advanced Filter dialog box.
- Next, select cells F5:G7 as the Criteria range. Then, select B16 to Copy to. Click OK.
- There you go! We have got only three rows that do not have any blank cells.
📝 Takeaways from This Article
📌 Firstly, we excluded blank cells from a single column using the Advanced Filter.
📌 Secondly, we learned how to remove blank cells from one column and corresponding rows utilizing the Advanced Filter.
📌 After that, we simply removed blank cells from multiple columns using the AND function as the criterion for Advanced Filter.
📌 Finally, we used <> symbols to eliminate blank cells by executing the Advanced Filter.
Conclusion
That concludes the discussion for today. These are some convenient methods to use Advanced Filter to exclude blank cells. Should you have some queries or recommendations, kindly notify us in comments box. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
