Join Dennis Taylor for an in-depth discussion in this video Filter single and multiple columns, part of Managing and Analyzing Data in Excel (Office 365/Excel 2019).
- [Instructor] One of Excel's most powerful and yet easy to use data management tools is what we call filter. In this list here I might want to be seeing just some of the records either based on status, or department, on the home tab, the Editing button There's Filter right there we can That puts filter arrows at the top of each column in our list here. We could have gone to the Data tab, do the same thing there, there's the Filter button. So we've got the filter arrows present, let's just look at the ones for status. Maybe we only want to see the full-time people. Click the arrow for status in column F, for the moment, click OK and we're only seeing the full-time records, develop the habit of looking in the lower left corner. Now we're not confined to just the information in job rating as well here. Let's click the arrow for job rating and unselect them all and choose just the fives. So we're about to see just those people who meet two criteria, status is full-time, Lower left corner tells us 102, of 741 records found. So we're simply narrowing down the list for whatever reason. We might also want to narrow this list based on what's happening in another column as well too. in manufacturing, let's use column C here. Unselect them all, we've got two manufacturing departments let's check the box for each of them. Manufacturing and manufacturing administration. Check them both, click OK and we see the list here. We've got 21 people in the different manufacturing departments who are full-time and have a job rating of five. If the question comes up while we're looking at this, how 'about how many people do we have, we want to select all of them there, and so now what we're about to see is a list comprised of all people in either of the manufacturing departments who happen to be full-time. Notice that the status and the department columns have a filter icon there whereas the other columns don't. what their status was, let's turn off the status filter here and we'll select all and now we see how many people We can sort this visible data because maybe we want to bring the people in the manufacturing admin group all together here so we can see them in a cluster. So we'll click the drop arrow for department Sort A to Z we will only be rearranging the visible data as we do this so there are all the manufacturing people and we see those row numbers out there. So I've got 158 people in the different manufacturing groups and toward the bottom here. There's the people here in manufacturing admin. that brings back all the data. We still have the filter arrows in place as I double click the top edge of the cell we zip back up top. At certain points you can also, and it's hardly much better. But if we were looking just say everybody but full-time here, that's 348 we could bring back all the records by double clicking Filters. Probably easier to just click Clear. the filter and then restarting it, here to hide the rows that we do not want to see.
- Prepping data for analysis
- Multiple-key sorting
- Sorting based on custom lists
- Creating single- and multi-level subtotals
- Using text, numeric, and date filters
- Filtering tables using slicers
- Using the Advanced Filter
- Eliminating duplicate data
- Using SUMIF and related functions for quick data analysis