In the video below, I show how to get started with the Excel FILTER function, which is available in Office 365.
You can enter a formula in one cell, and the results spill down onto the worksheet, to show all the results. And unlike other filters in Excel, the FILTER result list will update automatically, if you change the criteria.
Video: Get Started With Excel FILTER Function
In this video, see how to use the new FILTER function to create a list of cities in a specific region, sorted A-Z.
- Then, see how to create a list of cities and sales reps in a specific region, with both items sorted A-Z
- Next, create a filtered list using 2 criteria – region and sales price
The video transcript, with screen shots, is below the video.
Get Started With Excel FILTER Function
- This is the full transcript for the video above, if you’d rather read, than watch!
- I’ve added a few screen shots, to help you understand the steps
- You can get the sample file, and more details, on my Contextures site.
Video Transcript
In this video, we’ll get started with the new Excel FILTER function, which is available in Office 365.
You can enter a formula in one cell, and the results just spill down onto the worksheet, sheet, and the list will update automatically if you change the criteria.
This is Debra Dalgleish from Contextures.com.
Cities in Selected Region
In this workbook, we have a food sales list that shows region, city, and several other fields, and you can get this file on my Contextures website.
First Formula
And our first FILTER function will get us a list of the cities, after we select one of the regions.
- I’m going to this sheet, and I’ve got a drop down list here where I can select one of the five regions.
- And in this formula, I’m going to create a FILTER formula.
- I’ll start with equals, FILTER.
Now you should see FILTER, and if not, your version of Excel doesn’t have these functions yet.
Once I see that, I can press tab, and I’m ready to fill in the arguments.
FILTER Function Arguments
And the first argument is the array, which is where we want the information to come from.
- So I’m going to my food sales list, and I would like the cities.
- So I’ll click that, and it puts the name of this table and the name of this field.
And I’ll type a comma and go to the next argument, which is include.
- This is the filtering that we want this to do.
- We want it to only show the cities where the region matches our criteria cell.
- So I’ll click on the region column, equals, and then I’ll go back to my sheet here.
- And I want this cell where I’m selecting a region.
- Now I don’t need the sheet name here, so I’m just going to delete that.
Then I’ll type a comma, and the last argument is if empty.
- So if it can’t find any values that match our criteria, what should this cell show?
- And I’m going to use double quote and then two dashes double quote.
- Close that bracket and press enter.
List of Cities
So now it’s given a list of all the cities.
- If we scroll down, it goes down quite a way.
- So every row here that was in that region is showing up.
So it’s a good start, but it’s not exactly what we want.
- We just want a list of the city names.
- We don’t want each one repeated several times.
UNIQUE List
So we’re going to use another function with this, another one of the new Excel functions, which is UNIQUE.
And I’ll put a bracket at the end and now when I press enter, there’s our list of cities, but each one is only in that list once
SORT the List
And there’s just one more thing we could do to improve this.
Right now, the lists are showing up in the order in which they’re first found in that sales data.
I’d like them in alphabetical order instead, so I’m going to use one more of the new functions, which is SORT.
Bracket at the end, and there’s our list in alphabetical order.
Spill Area
Now this cell is where we put the formula, and we can see that up in the formula bar.
- And then there’s this thin blue border around the other cells in the list.
- This is the spill area.
If I click on one of these other cells, I can see the formula up in the formula bar, but I can’t edit it.
- If I click up there, it just disappears.
So this cell has the formula, and if I move that cell somewhere, the whole list comes with it.
Now if I typed something in here that doesn’t exist, now there’s nothing found, and then I see my two hyphens in this cell.
Cities and Sales Reps
For the next example, we’re going to start with the same formula that we just finished on the other sheet.
But this time, instead of just a list of cities, I would like to see the sales reps from those cities in whatever region we’ve selected.
- To do that, we’re going to change the first argument in the FILTER function, which is the array.
- So I’ve highlighted the current argument, which is SalesDataCity.
- I’m going back to FoodSales, and I’m going to select City and Last Name.
And when I press enter, it shows us all the cities, and they’re still in alphabetical order.
And then for each city, it shows the reps, and those names are not in alphabetical order.
Add Another SORT
So we’re going to make one other change to the formula to put these in order.
So I’m going back to the cell that has the formula because that’s the only place I can make a change
The SORT function – the first argument is array and this is our array that we’re sorting
- Then it’s got some optional arguments, which we didn’t have to use last time, the sort index.
- So if things are being sorted, should this be the first thing sorted or the second?
And then the sort order, which would be ascending or descending.
- So I’m going down to the end here, and this is the bracket that’s closing the SORT.
- I’m going to type a comma and then the index.
- This is going to be the list of rep names and they should be sorted second.
- So I’m going to type a two, then comma, and now do I want this ascending or descending?
- So I’ll double click on ascending and press enter.
- So now it’s just sorting these names.
We’re going to put another SORT in, to sort those city names again.
- So type SORT, open bracket, and at the end, we’re going to type a comma, and the sort index for the city names is one, comma, and we want that in ascending order as well.
- Close the bracket and press Enter.
So now the city names are alphabetical, and then the secondary sort is the rep name,
And now the rep names are sorted alphabetically within each city.
FILTER with 2 Criteria
In this example, we’re going to use two criteria.
We’re going to continue to use Region, but now we’ve added Price.
We’re looking for sales where the amount was greater than whatever number we’ve typed in this cell.
I’ve put our first formula into this cell where we were just looking for region.
- And in that example, we wanted the city returned.
- But this time, we want product, so I’m going to select that, go back to my sales data, and click on the product column, and press enter.
So now we can see all the products that we sold in that region.
- We’re going to add criteria for this price now.
- So if we come back to the formula cell, here we can see the region has to be equal to B4.
- We also want the price column to be greater than whatever’s in B7.
To use two criteria, we’re going to put brackets around each one.
- So I’ll start with a bracket here, and then at the end of that first criterion.
- Before we start the next one, we’re going to be multiplying these.
- So I’ll put in the asterisk, and my next criterion, I’ll start with the open bracket.
- And for this, we want to check the price column in our sales table, which is our sale price.
- And that should be greater than and back to the sheet with our formula.
- And I’m going to click on the price cell.
- And again, it puts the sheet name in for us, and I don’t want that.
- So it has to be greater than that number.
- Close the bracket and press Enter.
And now we’ve got two criteria.
So our products are those from the Southwest, where the total order price was greater than this number.
If I change that to a hundred, then we get a different list of products here.
Thanks for watching this video.
Get the Sample File
You can go to my website, contextures.com, to get this workbook, and please subscribe to my Contextures YouTube channel so you can see the latest videos as I post them.
–END OF TRANSCRIPT–
More FILTER Function Examples
_____________