Filtering

Quick reference

• 00:04 In this video, we're going to look at filtering in Excel and
• 00:08 what filtering is, is it's the ability to take a list of data or
• 00:12 transactions and drill into specific aspects that we actually wanna see.
• 00:17 So we can get rid of a lot of extra noise and
• 00:20 really focus on things that are relevant to us.
• 00:23 To get started with filtering like sorting and also other powerful
• 00:28 tools inside excel, we need to work with what's called a contiguous list of data.
• 00:32 Which basically means that we need our data preferably in tabular form
• 00:37 with a nice set of headers across it.
• 00:39 And it's really,
• 00:40 really important with this that we don't have any blank rows or headers.
• 00:44 Blank cells are okay but not an entirely blank row or
• 00:48 blank column in the middle of our data.
• 00:51 So this data is looking like it's in pretty good shape here.
• 00:54 Let's go and start to filter down this list of transactions and
• 00:57 see what we can actually learn about it.
• 00:59 The way that we do that is, the easiest way is to grab our header row,
• 01:04 go to the data tab and turn on the filter icon.
• 01:09 You'll notice that when we do that,
• 01:10 we get these little drop down arrows that appear here at the top of each column.
• 01:14 Now, these things are really cool because when you click on them,
• 01:17 it actually very quickly scans the entire list of data in that table.
• 01:22 And it returns a list of the unique values within that column.
• 01:26 So if you're just looking to figure out how many unique values there
• 01:29 are in the column this is actually one way you can do it.
• 01:32 Now if I want to select just the charges that have gone into say,
• 01:36 beach bar, I could uncheck select all.
• 01:39 Check beach bar, and you'll notice that it immediately filters all the transactions
• 01:43 down to show me just the ones that are there.
• 01:45 And we don't have to worry, nothing's disappeared.
• 01:47 You can see that the rows have just been hidden, okay.
• 01:49 That's what the little green bars are on the side here.
• 01:52 So that's kinda cool.
• 01:53 I can either dismiss this by clicking the X or just clicking back on worksheet grid.
• 01:58 Now what is I want to look for something that might show up with some text in it.
• 02:04 Specifically what I'm actually looking for right now is any card that actually
• 02:08 has the letters IS in it because that would return visa and discover.
• 02:12 So I can do this in a couple of ways.
• 02:15 I can either go in and select contains and
• 02:19 type in is, and you'll notice my list has filtered down now.
• 02:26 Or I could clear the filter on this again, bring those transactions back.
• 02:29 I could go into the search box right here, and type in IS, and it
• 02:34 narrows down my list of transactions, or different list of items, in here as well.
• 02:39 But the filters being applied immediately.
• 02:41 So, if I'm happy with this, I could now go and click in here, and that window will go
• 02:46 away, everything looks like it's okay here about working with dates.
• 02:51 Actually the other thing I should probably point out before I move on here is that
• 02:54 you'll notice these filters are successive.
• 02:56 I first filtered the beach bar and
• 02:58 then I also filtered down to my different card types, but
• 03:02 it didn't bring me back any lobby bar chart just here at all, that's kinda nice.
• 03:05 So this is drilling further and further into my data.
• 03:07 What if I wanted to get into a specific date?
• 03:10 Well, it's easy enough, you'll notice that the date filters,
• 03:14 most of the time will show up probably collapsed in year.
• 03:16 But you can drill into them nicely to get right into the individual date.
• 03:20 So I could just select June 13th and
• 03:22 filter my data right down to the transactions for that day.
• 03:26 Or if I wanted to get a little more technical and
• 03:28 advanced, I could click on choose one and there is a whole pile of options in here.
• 03:33 So if I wanted to go for between certain dates, I could come back here and say,
• 03:37 well let's just go and click on this little calendar.
• 03:39 And I can set my charges between October 5th, 2015 and another date.
• 03:46 Now obviously, with the date I have that's not gonna earn anything here.
• 03:49 So I'm just going to go and
• 03:51 click back over here and ignore that particular filter right now.
• 03:57 Now, one of the really great things about this is that as I say, we can apply
• 04:01 successive filters to just drill further and further into our data all the time.
• 04:06 And that's awesome because if we have massive, massive lists with lots of
• 04:09 columns and lots of rows, this could be really, really useful.
• 04:12 But sometimes you'll also find that you can get a little bit lost and
• 04:14 you need to clear your filters.
• 04:16 So, there's a few ways to do that.
• 04:18 To clear a filter on a specific column,
• 04:20 you just click on one of the icons that has the filter icon showing.
• 04:23 Notice that our arrows are different here.
• 04:25 We've got an arrow with a filter,
• 04:26 that shows us there's an active filter on this column.
• 04:29 So we click on that, we could say clear filter.
• 04:32 And it will bring back the charges for that specific column.
• 04:34 See these two guys over here are still filtered.
• 04:38 We could also go and click in a column and
• 04:42 if we click the clear button, you'll notice that it clears all the filters.
• 04:46 The date filter got cleared as well.
• 04:48 And if I'm really lost and I don't know what's going on,
• 04:51 maybe I've got 900 columns of data going across this thing.
• 04:54 And I can't really tell what's going on, I could also come back and
• 04:57 just grab this guy here.
• 04:58 I'm gonna filter something for
• 05:01 a second here just into a couple, just so we can see what it's doing in here.
• 05:04 Then I'm gonna turn filters off all together by highlighting the row and
• 05:09 clicking filter again.
• 05:11 And that will unfilter all the things, so we never lose anything or
• 05:15 leave anything hidden.
• 05:16 But it allows us to clear those filters out.
• 05:18 So that's how to work with filters in a nutshell to drill and
• 05:20 out of your data as well.

Lesson notes are only available for subscribers.