👍 EXCEL ADVANCED FILTER - MULTIPLE CRITERIA
Advanced Filter vs Auto-Filter, Multiple Criteria, And Or Criteria Range, Filter by Text that contains, Date, Numbers, Wildcards, Filter Operators, Unique Records, Dynamic Filter ...
00:00 Introduction
00:30 Comparison to Autofilter
01:18 Demonstration - How to use and Create
02:32 And - Or Criteria Range
03:42 Filter Operators and how to use them
04:15 Filter by Date & Numbers & Text
05:11 Extract Certain columns instead of the whole table
05:42 Final Note
************************************
Check my other Excel Videos in this Playlist:
• 👍 EXCEL ADVANCED FILTER - MULTIPLE C...
*********Video Description******
Hallo and welcome to this new Excel Tutorial, In this video, i am going to show you how to use and benefit form the Advanced filter feature in Excel. A VERY Powerful filtering and extracting capability. I m gonna start by comparing it to Autofilter Demonstrate how to use and create Multiple filter Criteria How to Extract and filter Certain columns instead of the whole table
At the end of the video, a final note on using the Advanced filter feature and possibilities to automate. So let's get started.
A. Intoduction & Comparison to Autofilter
Basic Autofilter in Excel is a very powerful tool to sort and filter data.
You can apply the filter to the columns by pressing Ctrl + Shift + L or going to the Data Tab - Sort and Filter section. Excel has built-in filter Criteria for Date, numbers, and Text. Simply click on the Filter drop-down menu,
Excel will show you the built-in Criteria for your column format.
These Criteria will Cover most of the Scenarios you have,
but not all of them. In addition to that, auto filter has its limitation.
"Excel Offers an Advanced filter option that allows you to create a much complex filter. Not only that, you have the option to Extract the filtered Data to a new independent list, this is very helpful especially if your data contains formulas that might be corrupted by filtering
i will show how it works in the next section
****B. Demonstration - How to Use and Create - Multiple Criteria
"To Use Advanced Filter you need to create a Criteria Range that includes
Header Labels at the top that are Exactly the same as the table you want to filter, And then apply the Advanced filter to it. So it is a 2 step process.
This means if i want to filter the Date column, i must Enter at the Top the Header label as my table and below it the Filter Criteria.
Best practice is to copy the headings and paste them as headings for the criteria list. Let's Say for Example i would like Excel to filter dates between the 15th of January and the 15th of February. Select the Criteria Range and click on the Advanced Filter button in the Data Tab. Here you can Choose if you want to filter the Table in place or copy the filtered data to another location. For the first Example, i will filter in place.
Excel usually automatically recognize your Data table, and since we selected the criteria range before clicking on the advanced options button,
it also recognizes the criteria Range. If not, you can Select the Ranges yourself. Press Ok when you finish. As you can see, the data selected is filtered based on the criteria we choose.
*** And - OR Criteria Range
when you filter based on multiple criteria. All Criteria Listed on the Same Row work with the AND Logic. this means all of the criteria entered must be met.
In the first Example i showed, since both criteria were on the same row,
Excel filtered for Dates that are bigger than the first value AND smaller than the second Value.
Now Criteria Entered on Different Rows work with the OR logic
this means, the data will be filtered if only the first criteria is met,
or only the second is met or the third criteria is met. lets apply an example.
i want to filter data where either wine was Sold or where the Revenue was over 5000 €. Select the Criteria Range, click on Advanced Filter,
For this Example, i would like Excel to Extract the filtered data to a separate location. Select the Cell you want the filtered table to be extracted to.
Press ok. As you can see in the table Extract, all wine sales are listed as well as sales above 5000 €. As a Practice tip, before we continue, leave some distance between your criteria list and the lcoation of the extracted Table. At least 2 rows should be chosen. More are better.
*** Filter Operator and How to Use them (Date, Nrs., Text, Unique Records ..) For Numbers and Dates, you can use the in excel available logic operators that are normally used in Functions and Formulas.
Equal to, greater than, smaller then etc etc.
"
Смотрите видео 👍 EXCEL ADVANCED FILTER - MULTIPLE CRITERIA онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь SWAK Academy - Investing Simplified 31 Май 2021, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 5,208 раз и оно понравилось 87 людям.