A friend sent me the following Excel question:
I am sorting a list, multiple of thousand entries. When I use the magnifying glass and enter a search term I get a pop-up window that says there are a few hundred results. Great! Now I want that distilled result list moved to a new spreadsheet… and I cannot see a way that that can be done!
Have you had this issue before? How did you solve it? Here are a couple of ideas that came to mind. Follow along with the madness:
For our example, let’s filter the included mpg dataset so that only the records are left where origin equals usa.
Using Power Query
My first idea for a solution on this, like most things in Excel, is Power Query:
To do this, go ahead and load the mpg table into the Query Editor. Check out this post if you need help.
Once your data has been loaded, you will use the filter drop-down on the data similar to how you would in Excel. After you’re done filtering, click the Home tab on the ribbon, then Close & Load:
The results of your filter have been loaded to another worksheet. That’s it!
If you want to redo the filter to add more criteria or whatever else, you can always edit the query. Learn how to do that here.
Why not use Power Query?
With all due respect to Staples, now that was easy. So why not use Power Query to set up this filter worksheet? The biggest issue is compatibility: Power Query is not available for every version of Excel. I’m particularly looking at you, Mac users! So let’s look at a more available alternative.
Using the FILTER() function
With this approach, we’ll use the FILTER() function. Go ahead and open a new worksheet to place the data on. If you start typing =FILTER() into a cell you’ll see the following:
=FILTER(array, include, [if_empty])
Let’s break down each argument:
| Argument | Meaning |
|---|---|
array |
What range of data do you want to filter? I’d suggest that your array be an Excel table. That’s what we’ll do in this demo. |
include |
What criteria should the filter meet? Remember, you want this statement to return as TRUE for it to be included in the query. |
if_empty |
What should the statement return if the filter catches no rows? This argument is optional and I’ll leave it blank here. |
If we want to filter the mpg table so that all origin records equal usa our formula will look like this:
=FILTER(mpg, mpg[origin]="USA")Please note that because we are filtering off a table here, we are using structured or table rather than regular cell references. Check out the documentation here if you’re not familiar.
Adding a header column
The FILTER() function is pretty cool and avoids using Power Query which is less available in Excel. But we do have one serious problem: the FILTER() function simply returns the matching rows, not their header columns:
Let’s add the header to Row 1, again using a dynamic, structured reference to the table’s headers. Insert a new row and place the following formula into A1:
=mpg[#Headers]You should now have a dataset with a proper header column:
Editing the FILTER() results
To change your filter statement, you can click inside cell A2 and edit as you would any Excel formula. The changes will update across the resulting dataset. If any changes are made to the source data, such as the number of columns or their names, those will instantly reflect in the dynamic reference.
To learn how to add multiple criteria to a FILTER() statement, check out this post.
You can also compare your completed file to mine:
What questions do you have about working with filtered data, Power Query or dynamic arrays? Let me know in the comments.
You can learn more about the FILTER() function and other dynamic array functions in my book Modern Data Analytics in Excel:
Leave a Reply