How to Display Rows with Non-Blank Values in Excel Advanced Filter

Using the Advanced Filter in Excel, you can display rows with non-blank values in a particular column. Here's how:

1. Open your Excel workbook and select the sheet containing the data you want to filter.
2. Click on the "Data" tab in the Excel Ribbon.
3. In the "Sort & Filter" group, click on the "Advanced" button.
4. In the "Advanced Filter" dialog box, select the "Filter the list, in-place" option.
5. For the "List range," click the "Select Range" button and select the range of data you want to filter, including the header row.
6. In a blank cell outside of your data range, type a header that matches the header of the column you want to filter for non-blank values. For example, if you want to filter for non-blank values in the "Name" column, type "Name" in a blank cell.
7. In the cell below the header you just typed, enter the following formula, replacing "A" with the column letter of the column you want to filter:

<>""

8. For the "Criteria range," click the "Select Range" button and select the header and formula cell you created in steps 6 and 7.
9. Click "OK" to apply the Advanced Filter.

Excel will now display only the rows with non-blank values in the specified column.

Example

Let's say you have the following data in your Excel sheet:

A       B
--------------
Name    Age
John    25
Sara    
Emily   30
Mike    40

You want to filter the data to display only rows with non-blank values in the "Name" column.

1. Click on the "Data" tab in the Excel Ribbon.
2. In the "Sort & Filter" group, click on the "Advanced" button.
3. In the "Advanced Filter" dialog box, select the "Filter the list, in-place" option.
4. For the "List range," click the "Select Range" button and select the range A1:B5.
5. In a blank cell (for example, cell D1), type "Name."
6. In the cell below the header you just typed (cell D2), enter the formula: <>""
7. For the "Criteria range," click the "Select Range" button and select the range D1:D2.
8. Click "OK" to apply the Advanced Filter.

Excel will now display only the rows with non-blank values in the "Name" column:

A       B
--------------
Name    Age
John    25
Sara    
Emily   30
Mike    40