Locked lesson.
About this lesson
In a dataset, use auto filters to extract lists based on text or date criteria. Let's look at how to sort using auto filters.
Quick reference
Auto Filters
Auto Filters is a function in Excel that allows you to extract records that meet certain criteria.
When to use
Auto filters can help you look at specific data records that meet a certain criterion or multiple criteria.
Instructions
Prepare the data
- A single row headings and no breaks in the data
- Select the top tow, go to the data tab, sort & filter, auto filter
- Excel adds auto filter buttons to the headings
Filter text
- Click on the auto filter button, deselect all values, scroll down the list and select California
- Your dataset should now only show California records
- CLEAR FILTER
Wild cards text search
- Use the search box and type in “Ca*” – all entries CA and California will show (just "CA" will also work fine)
- CLEAR FILTER
Sort using the Auto filter button
- Click on the button, pick “sort by” – one of the first two options
Blank rows
- Click on the auto filter button, deselect all options and scroll to the bottom of the list, select <blanks>
- CLEAR FILTER
Select a date
- Click on the auto filter button in a date field, look at the options available to select – you should be able to drill down to a day level
- 00:04 Okay, have you ever wanted to look at a database but
- 00:07 you only need to see certain transactions.
- 00:09 Like, say transactions from California, or
- 00:12 Washington, or only the transactions from the month of May?
- 00:16 Well, if that's the case, the quickest way to do that is to use an AutoFilter.
- 00:21 AutoFilters sit on the data tab under the Sort & Filter grouping, and
- 00:25 you can use this Filter button.
- 00:28 The easiest way to activate the AutoFilter,
- 00:31 is to click inside your dataset, then click on AutoFilter, and it's done.
- 00:37 And then to get rid of it, click on Filter again and it's gone.
- 00:42 However, be careful of blank lines in your dataset because in this case,
- 00:46 the AutoFilter thinks that I've got two separate datasets here.
- 00:51 If I apply my AutoFilter, it will treat the first row of data as the heading.
- 00:56 So if you have breaks in your dataset,
- 00:58 you just have to select all the data in your dataset.
- 01:03 Then click on the AutoFilter, and it will automatically include everything.
- 01:09 You can get rid of the blanks by clicking on the AutoFilter button next to
- 01:12 a heading.
- 01:14 The AutoFilter provides a list of unique values that can be found in that column.
- 01:18 So let's click on blanks only to filter our blank lines together.
- 01:26 Now I can delete the empty row from here.
- 01:29 Clear the filter.
- 01:31 And now my blank line is gone from my data.
- 01:35 Let's look at another text filter.
- 01:36 In the location field, let's choose deselect all, and then choose Washington.
- 01:44 Now we can see all of our Washington transactions.
- 01:47 On the side, you'll see that the numbers for rows seven, eight and
- 01:51 nine are in blue, and the rest of the numbers look like normal row numbers.
- 01:56 The blue numbers indicate that these rows are part of an active AutoFilter, and
- 02:00 the double lines above and
- 02:02 below them tell you other rows in the dataset have been hidden away for now.
- 02:08 When you're done with the filter, make sure you clear the filter from
- 02:11 the location or from whatever field you filtered.
- 02:15 Something else that's quite useful to know with text filters is
- 02:19 an instance like this where you have an abbreviation for California, CA,
- 02:24 alongside data with California fully written out.
- 02:29 Now I'm not happy that this data is like this.
- 02:31 I would like it to be consistent.
- 02:33 So maybe I can identify all the records from California and CA, and
- 02:38 we can do this with a text filter.
- 02:41 So let's use the search field within the filter.
- 02:43 Type in CA with a wildcard asterisk.
- 02:47 And it will find all the relevant options below and check them off.
- 02:51 You can see both CA and California are now checked.
- 02:55 So a filter works with wildcard characters.
- 02:59 Let's clear that filter.
- 03:01 Something else that's nice to see is a date filter.
- 03:05 On the date field, if Excel recognizes this as a date type,
- 03:08 you can drill down to the month, and even the day.
- 03:12 If we click on the plus sign to expand May,
- 03:15 we can see all the individual May dates where we have transactions.
- 03:20 Let's deselect May, and then just check the first, and the third.
- 03:24 Deselect June, hit OK.
- 03:26 And now we see transactions only for the first and third of May.
- 03:32 Let's clear the filter, you can also sort alphabetically.
- 03:35 Click on the AutoFilter button here for items,
- 03:38 and you can sort alphabetically from A to Z.
- 03:44 Or if you want, sort in the reverse order from Z to A.
- 03:49 And we can sort the data by transaction number if we want it in
- 03:52 the original order.
- 03:55 So more on number filters and
- 03:56 combining AutoFilters with conditional formatting in the next lesson.
Lesson notes are only available for subscribers.