The Useful Basic Tool of Excel – Data Filter (Easy)

Updated April 13, 2023

Part -19 – Excel Data Filter

Download Excel Templates – Excel Data Filter

Excel Data Filter ( without solution)

Excel Data Filter (with solution)

Transcript For The Video – Excel Data Filter

Video on Excel Data Filter – In this set of video tutorial, we are going to learn another Excel tool which is very useful, and that is basically filters. Filters is available under data, and it is next to sort on the right-hand side, so it enables filtering of the selected cells based on the certain conditions. So as we have this case study which consists of various data points, let us try and apply this filter in our case. So, we have serial number, name, department, etc. So what you can do is, you can actually select the full table and click on filter, and the moment we do that, you will find that there is a down mark arrow which comes on all the headings. So let us see what is there inside of these kind of headings so; when we click on it we find that it contains various sort options, sort options which were also available next to the filter button, and there are different kinds of sort available. One thing which is very important here is to see that all the different or unique values which are inside the names are available here, so say, for example, you can select and deselect various options. Let’s say I want to only see the data of Aaron, so I can deselect all by clicking here and select only Aaron, and I can press ok. So what you will see is this data of Aaron is only displayed, and all the other data which was originally there in the table not deleted but is hidden. So that’s what we can kind of work around when we talk about filter. I want to filter Aaron and look at his data only. This helps in further analysis and provides large meaning to whatever we are doing. So lets us try to analyze this further. I will go back and kind of select all the employees and let’s see another header. Let’s say under the department, I probably want to analyze only IT professionals, so for this, I will deselect all and only select IT, and I would press ok. So as you can see, this time only employees from IT are visible, so I can actually do a lot of analysis here, see their salaries, you know take, home salaries, and find some deeper meanings associated with it. So this is how I can use auto filters. Can I use two filters at once under different headers? Say, for example, I want to choose marketing, and under marketing, I can see that there are males and females. So let’s say if I want to see gender which how many females are working in marketing? I can provide a second filter, so I have already provided the first filter under department and the second filter under the gender, and I would press ok. As you can see, there are three females under the department called marketing. So this helps us in terms of refining to the granular level to which you want. Another useful thing which probably is very important from the point of view of filters is that you can also provide certain conditions, certain conditions like, let’s say I will just show you with respect of annual salaries or percentage of annual salaries and let’s say number filter. You can see under number filter I am not choosing one of these set of options I am providing a condition. So when I select a dropdown and I go to number filter, I see there are different conditions which are available here. I can select top 10 above average, below average the one which, let’s say if I select above average, you will find that it automatically calculates the average and gives the list of employees who are above average in terms of percentage of total annual salary, likewise below average you will find below average, these are the guys who are getting the below average salary, I can also provide a customized condition, let’s say I want to have or I want to see employees whose salary is greater than 9%, so this is a customized condition, and now you can see there are only two employees, so it is basically very useful if you are using or if you are dealing with lot of data and it really helps in terms of organizing your things. So make the most of filters and its associated applications.

Recommended courses