If you are using excel you might have used an Excel basic filter which we use to filter any specific data or some other criteria but when it comes to filter multiple columns at same time, basic filter no longer works on multiple columns so in this case we can use excel advance filter.
Filter Multiple Columns With Advance Filter
You can use advance filter to add filter on multiple columns at same time. Let’s say you have sales record of month of Jan, Feb and Mar, and you want to filter only the branches having sales value equal to $50,000 or greater in any of these three months.
In this case if you try to use basic filter it will not bring the results you want. It will filter only the values from column you will apply it and the rest columns will be ignored. Hence you can use advance filter instead. The following are steps you need to follow;
- Copy the headings of table and paste anywhere you want
- Set criteria: write >=50,000 in Jan, Feb and Mar cells (make sure you write it in different rows)
Go to data tab and click on advance or use the combination of (ALT+A+Q)
Select the list range, criteria range and click on ok
As you can see the below result, all the branches having sales value equal or greater than $50,000 in any of these three months are visible. Branch 5 had no sales value equal to $50,000 in any of these three months hence, it is not visible.
Filter Unique Values
By using advance filter you can extract unique values as well. Let’s say you have data (As shown in below image) as there are some duplicate rows which might be due to data entry error and now you want to extract only the unique records.
Go to data tab and open advance filter
- Select Copy to another location (By selecting this option you can choose the location where you want to get unique records)
- Select list range and choose the location where wanna get results
- Check the box (Unique records only) and click ok
Once you’ve done the above steps, all the unique values will be extracted in new location you chosen.
Filter Data With Different Criteria
Let’s filter data with different criteria which I think will be helpful for you to understand it properly. You can also check the Office Support for deep understanding of advance filter.
For instance you have below sales record and this record is from January 5, 2021 till April 12, 2021. You want to get the sales record only from February 1, 2021 till March 31, 2021.
In order to get the records from any specific date to another date, you need to set the criteria as follows:
- Copy and paste the table headings in another location and make two headings as a date
- Write starting date >=02-01-2021 and ending date <=03-31-2021
- Go to advance filter, select the criteria range and list range so it will give you all the sales record between these two dates.
Now let’s say you want to get record for only central branch and item is pencil, so for that you’ll set the criteria as shown in below image.
If you want to filter sales value equal or greater than $10,000, you can write the criteria >=10,000 in sales cell.
Set Filter Criteria Using Wildcard Characters
You can also use wildcard characters to set the criteria for advance filter. Let me give you explanation for better understanding.
- Asterisk (*) – It typically represents to any number of characters.
Example: Ad* could find advance, advise, adjust, etc.
Let’s suppose you want to get sales representatives whose name is beginning with G and K. In this case you can use asterisk (*) to set the criteria.
To get the names starting with G and K, you need to set the criteria as per below image so when you will apply advance filter it will show only the names beginning with G & K.
2. Question Mark (?) – It represents to any single character.
Example: me?t could find meat or meet
3. Hash (#) – It represents any single numeric character.
Example: 1#8 could find 108, 118, 128, 138, etc.
I hope the above examples and explanation will help you to know about excel advance filter as well as to filter multiple criteria and columns. If still you require any kind of information, do let me know in below comment section. I would be happy to assist you any further.
Thank you
