Filter and exclude columns

In this example, the goal is to use a single formula to extract high-value projects and list them in a simple table. We also want to remove unnecessary columns to create a clean, uncluttered view. The solutions explained below are based on a combination of several functions in Excel, including FILTER, SORT, TAKE, and CHOOSECOLS. This is a useful technique for creating a simple dashboard report with a dynamic set of data. The beauty of this approach is that the original data remains untouched and can be easily updated at any time. In addition, the source data can be located on a different worksheet. You can use this same idea in many ways, for example:

• Create a summary of top-selling products for the current month.
• Create a sales performance dashboard to highlight top-performing sales representatives.
• Create a list of the largest outstanding invoices over 30 days due.
• Create a leaderboard to display the most productive groups or employees.

The article below describes two methods to solve this challenge. The first method uses the FILTER function to extract data of interest. This is the best approach when the logic needed to select important data is more complex. The second approach uses the TAKE function to grab the most important records after sorting. This is an elegant way to build a summary of the "Top n projects", "Top 5 outstanding payments", etc.

Method 1: FILTER, SORT, and CHOOSECOLS

The first method to filter and exclude columns is based on the FILTER, SORT, and CHOOSECOLS functions. This is the solution shown in the workbook above, where the formula in cell F4 is:

=CHOOSECOLS(SORT(FILTER(B9:G20,E9:E20>90000),4,-1),1,4)

Working from the inside out, the FILTER function is configured to extract projects with a value over 90,000 like this:

=FILTER(B9:G20,E9:E20>90000)

• array - provided as all data in B9:G20.
• include - provided as the logical expression E9:E20>90000.
• if_empty - Not provided.

The advantage of using FILTER is that we can configure the logic used to select data to apply even complex multiple criteria. This is a more powerful way to isolate important information compared to sorting only.

In the next step, FILTER returns a filtered array of data to the SORT function, which is configured to sort the data by value in descending order:

SORT(FILTER(...),4,-1)

• array - delivered by FILTER.
• sort_index - provided as 4 to sort by Value.
• sort_order - provided as -1 to sort in descending order.

The last step is to remove any columns we don't want in the final output, which is done with the CHOOSECOLS function:

=CHOOSECOLS(SORT(...),1,4)

• array - provided by SORT.
• col_num1 - provided as 1 to extract Project.
• col_num2 - provided as 4 to extract Value.

CHOOSECOLS is a simple function designed to select specific columns from a set of data by index number. In this case, we want to retain column 1 (Project) and column 4 (Value) so we provide 1 and 4.

Note: We could sort the data before filtering with the same result. However, using FILTER first is more efficient since we are only sorting records that meet our criteria.

Method 2: SORT, TAKE, and CHOOSECOLS

Another way to filter and exclude columns is to use the TAKE function instead of the FILTER function. This approach makes sense when sorting alone is enough to "surface" the most important data. Once we have data sorted in the preferred order, we use the TAKE function to collect the number of rows desired. For example, to list the top 3 projects by value, we can use a formula like this:

=CHOOSECOLS(TAKE(SORT(B9:G20,4,-1),3),1,4)

You can see the result in the worksheet below:

In the first step, the SORT function is configured to sort all projects by value in descending order like this:

SORT(B9:G20,4,-1)

• array - provided as B9:G20 (12 rows)
• sort_index - provided as 4 to sort by Value.
• sort_order - provided as -1 to sort in descending order.

In the next step, SORT returns a sorted array of data to the TAKE function, which is configured to extract the first 3 rows:

TAKE(SORT(...),3)

• array - delivered by SORT.
• rows - provided as 3.

The last step is to remove any columns we don't want in the final output, which is done with the CHOOSECOLS function as before. Because we want to retain column 1 (Project) and column 4 (Value), we provide 1 and 4:

=CHOOSECOLS(TAKE(...),1,4)

• array - provided by TAKE.
• col_num1 - provided as 1 to extract Project.
• col_num2 - provided as 4 to extract Value.

Conclusion

In this article, we looked at two ways to filter and exclude columns from a dataset in Excel using a combination of FILTER, SORT, TAKE, and CHOOSECOLS functions. These techniques are useful for creating simple, dynamic summaries that update automatically as the source data changes.

• Use the FILTER method when you need to apply more complex logic to select data. FILTER is an excellent tool for identifying and isolating important information based on multiple criteria.
• Use the TAKE method when sorting alone is enough to surface data of interest. This method is ideal for creating summaries like "Top 3 projects" or "Top 5 outstanding payments." TAKE is also a good choice when you want to limit the number of records displayed.

In the example shown, both methods work well. But note that if more rows were returned by FILTER, more rows would appear in the final result, so we would need to make room for this additional information.

Wait, what about Pivot Tables?

Yes, you can definitely solve this challenge with a Pivot Table as well. In fact, the workbook attached to this article has a working Pivot Table on Sheet3. Until a couple of years ago, Pivot Tables were the best way to solve this challenge. These days, the main reason to use a Pivot Table instead of a formula is that you are working in an older version of Excel without modern functions like FILTER, SORT, TAKE, and CHOOSECOLS. The main disadvantage of a Pivot Table is that it won't refresh automatically when data changes; you will need to refresh manually. In contrast, a formula-based table will recalculate when any data changes. For more information on Pivot Tables, see:

• Excel Pivot Tables (overview)
• Why Pivot Tables (video)

Note: The video above is now somewhat dated because new functions have made it much easier to summarize data in Excel. In addition, two brand new functions, GROUPBY and PIVOTBY, directly mimic Pivot Table functionality.