Locked lesson.
About this lesson
We examine how to filter using numerical criteria, conditional formatting, identify duplicate items, and use SUBTOTAL to add values in a filter.
Quick reference
Auto Filters: Combined with Conditional Formatting
Auto Filters is a function in Excel that allows you to extract records that meet certain criteria.
When to use
Use the Auto Filter when you need to look at specific information from your larger list.
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
Conditional formatting
- Highlight the column values that should be unique, go to home, styles, conditional formatting, highlight values, highlight duplicates
Filter by color
- Click the auto filter button, choose filter by color
- You will have your list of duplicates
- CLEAR FILTER
Number filters – top 10
- Click on the button, choose number filters, top 10 – change that to top 3 if you wish
Other number filters
- Click on the auto filter button, number filters and try a few, greater than, smaller than, and / or
- CLEAR FILTER
What happens if you don’t CLEAR FILTER?
- If you need to filter “Oregon” sales for May that exceeds a certain amount ($), then you will filter “Oregon”, not clear the filter, add a filter for May, and add another in the amount column
Subtotal
- Using the SUBTOTAL function to add will add only visible values – not like SUM that will continue to show the total of the range, hidden or not
- 00:04 Let's take a look at some more things you can do with filters and your data.
- 00:08 To activate the auto filter, click on any item in the data set.
- 00:13 Click on your data tab, go to sort and filter grouping and
- 00:17 click on the filter button.
- 00:20 One thing I'd like to show you is how to deal with duplicates when
- 00:23 we don't want them.
- 00:24 In the transaction field, all the transaction numbers should be unique.
- 00:28 But sometimes errors slip in and we might find duplicate numbers.
- 00:32 So let's see if we can find them.
- 00:34 On the Home tab, we use conditional formatting to highlight
- 00:38 the duplicate values, and we can stick with the default format.
- 00:43 With big databases, you probably won't want to scroll up and
- 00:46 down to find all these dupes.
- 00:48 So what you can do is click on the auto filter button and
- 00:52 filter by color and choose that conditional formatting color.
- 00:58 And now you've got a list of all the transactions that are dupes.
- 01:02 So let's clear the filter.
- 01:05 Now if I wanted to list of my top five sales transactions,
- 01:09 let's click on the auto filter under total sales,
- 01:13 number filters, top 10 and I can change top 10 to top five only.
- 01:19 Hit OK, and I have my top five.
- 01:23 Now if I want to make a copy of this,
- 01:26 I can select my data Ctrl+C and then Ctrl+V to paste.
- 01:31 And it's best to paste an extract like this below your data set not next to it
- 01:36 because with another filter, the visible rows might change.
- 01:41 So let's say I want to sum the total sales for this extract.
- 01:46 The top five equal over 16,000.
- 01:49 Now let's sum the top group and see what we get.
- 01:53 Same idea.
- 01:56 If we do that, we get a total over 23,000, and why is this?
- 02:02 Well, if we clear the filter, you'll see that 23,000 adds up the first value from
- 02:07 the filter list through the last value of the filtered list.
- 02:12 Even the hidden ones are added up.
- 02:14 So a sum is not a good calculation to do with an auto filter.
- 02:20 If you do need to add the values, use a subtotal function.
- 02:26 You'll see from this list, some total choose number nine,
- 02:31 to do a sum and then choose your list.
- 02:37 Close parentheses.
- 02:38 And there you have it.
- 02:43 Over 28,000 for the full list, and I can check down below, tt also says 28,000.
- 02:51 Now if I filter to only show California transactions,
- 02:55 it shows my California transactions and it shows a sub total of 5473 for
- 03:01 these California transactions above.
- 03:04 Down below the top five overall sales are still intact.
- 03:10 Let's clear the filter.
- 03:12 Other number of filters you can use.
- 03:14 Let's go to the auto filter, select number of filters.
- 03:17 You can use any of these.
- 03:19 For example, let's try a custom filter where we want values
- 03:23 greater than 1000 or values less than 500.
- 03:27 We're looking for extreme transaction values, very high, very low.
- 03:32 We'll hit OK, and now we have our values.
- 03:36 So there's another way to do a number filter.
- 03:40 Let's clear that.
- 03:43 And remember if you do simple filters,
- 03:45 you have to clear the filter in between each filter that you do.
- 03:50 But for more complex filters, you can leave the filters in place.
- 03:54 Let's say for example, I want Oregon transactions, and
- 03:59 Washington transactions and I also want sales for
- 04:03 backpacks, and car racks and sleeping bags.
- 04:09 And I want sales where the unit sold are greater than two.
- 04:18 Now I have one, two, three active filters in place,
- 04:22 all Oregon and Washington transactions that sell three specific
- 04:27 items with a minimum number of units sold, that is my list.
- 04:33 So as you can see filters are very versatile, you can use them
- 04:37 separately on a field or you can combine the filters on different fields.
- 04:42 Filters are very handy and
- 04:44 easily manageable tool that I hope will save you lots of time.
Lesson notes are only available for subscribers.