How to limit the columns in a filtered result set in Microsoft Excel

Microsoft Excel’s Advanced Filter feature displays records that match specific criteria. You can also limit the columns returned by this feature.

Filtering records in Microsoft Excel is easy, and we’re accustomed to seeing entire records that match the expressed criteria. But you might wonder how to limit the number of columns in a filtered result set. You can’t using a simple Excel filter, because the feature filters the source data in place.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

However, using an advanced filter will allow you to limit the columns along with the records. I’ll show you how to use Excel’s Advanced Filter feature to return a filtered set based on two columns of criteria. Then, I’ll show you how to limit the filtered set to only those two columns.

I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use an earlier version. For your convenience, you can download the demonstration .xlsx and .xls files. Excel for the web doesn’t support the Advanced Filter feature.

About Microsoft Excel’s Advanced Filter

Excel’s Advanced Filter feature doesn’t use a dropdown list of filtering options and values. Rather, you enter criteria at the sheet level and the feature copies a filtered set to another location or filters the source data.

The feature requires three components:

• Source data: This is the data you want to filter.
• Criteria: These are the values you want to match to the source data.
• Location: This lets you filter in place or copy the filtered results to another location.

Using an advanced filter in Excel, you can express criteria in more than one column. You can do this with the simple Filter feature, but you must express the criteria one column at a time. An advanced filter lets you express criteria for every column in the data set simultaneously. In addition, you can express more complex criteria using an advanced filter.

Now, let’s use an advanced filter to return all the columns in a filtered result set.

How to use Advanced Filter in Excel

Now, let’s look at a quick example using the Table named Commission shown in Figure A. Let’s suppose you want to see all the records for any employee whose name starts with M and the corresponding commission is less than $250. Furthermore, you want to copy the filtered results to another location instead of filtering the source data.

Figure A

You already have the source data, so let’s add the criteria and location:

1. Copy the header row to another sheet, twice, as shown in Figure B. You don’t have to do this on another sheet, but it’s easy to do so.
2. Enter the criteria string, M* in cell D3 — that’s the Personnel column.
3. Enter the criteria string, <=250 in cell F3 — the Commission column. Because the two criteria expressions are in the same row, Excel treats the filter as an and operation. The filter will return any record where the Personnel value begins with an M and the Commission value is less than or equal to 250. To perform an or operation, put one of the criteria expressions in the next row.

Figure B

With the criteria expressed and both headers in place, let’s run the Advanced Filter feature:

1. Click the sheet tab that contains the criteria and location headers (Figure B). In the demonstration file, that sheet name is Results. The active sheet must be the location sheet.
2. Click the Data tab and then click Advanced in the Sort & Filter group.
3. In the resulting dialog, identify the source data, the criteria, and the location for the result set using Figure C as a guide. To set each range, click the arrow to the right of the control. Then, select the range, including the header, and click the arrow in the dialog to expand the entire dialog. To highlight the source data, you must click the sheet tab first (in the demonstration file, that sheet name is Data). When setting the criteria setting, include rows 3 and 4. The copy to location range is row 3.
4. Click OK.

Figure C

You might be wondering what went wrong, as there’s no filtered result set. Nothing went wrong: If you review the source data carefully, you’ll notice that there isn’t a record that meets the criteria!

Let’s change the criteria a bit and see what happens. In F3, change the <= equality operator to >= and run the filter again. Excel will warn you that the destination range isn’t large enough. Click Yes to continue; there’s plenty of room for the result set. As you can see in Figure D, there are four records that meet the criteria.

Figure D

As you can see, this feature takes a bit of preparation, but it’s easy to run and the results are impressive. But the result set displays the full record, so now let’s limit the columns.

How to limit the columns using Excel’s Advanced Filter

Limiting the columns returned by an advanced filter doesn’t require any magic: You simply change the header cells to match the columns you want to see. Figure E shows a new header row in N2:O2.

Figure E

Run an advanced filter as before, but this time, specify N2:O2 as the Copy to range (Figure E). The result set is shown in Figure F — four rows but only two columns. By reducing the header cells, you can reduce the number of columns copied to the result set.

Figure F

Copying only the columns you want to a filtered result set isn’t the same as a filter, but it does allow you to work with only the data you want.