🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Built-in Filter Controls

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Using row and column filters to narrow down results in a PivotTable.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Built-in Filter Controls.xlsx
208.1 KB
Built-in Filter Controls - Completed.xlsx
196.4 KB

Quick reference

Built-in Filter Controls

Using row and column filters to narrow down results in a PivotTable.

Where/when to use the technique

When you wish to give the user the ability to slice into their data and show a subset of the master list, or display only key values.

Instructions

Filtering values

  • Select an item you wish to filter in the rows area
  • Click the drop down arrow in the first column
  • Choose Value filters
  • Choose the type of filter you wish to apply
  • This should filter the values area of the PivotTable

Filtering labels

  • Select an item you wish to filter in the rows/column area
  • Click the drop down arrow for the row/column
  • Choose Label filters
  • Choose the type of filter you wish to apply
  • This should filter the row/columns of the PivotTable (depending on which you chose)

Filtering dates

  • Select an item you wish to filter in the rows/column area
  • Click the drop down arrow for the row/column
  • Choose Date filters
  • Choose the type of filter you wish to apply
  • This should filter the row/columns of the PivotTable (depending on which you chose)

Removing filters

  • Select the filter icon on the pivot, or in the PivotTable field well
  • Choose “Clear Filters from “x””
Login to download
  • 00:04 We're going to continue looking at filters on a pivot table.
  • 00:07 But this time we're going to focus on filters that are actually applied
  • 00:10 right on the actual pivot table themselves.
  • 00:12 So you'll notice that we have a filter field here for week,
  • 00:14 and you'll notice we have label and we have value filters in this area,
  • 00:18 as well as the ability to use the filter pane to search things down.
  • 00:22 Also, on our items, we have the same thing over here as well so
  • 00:26 that we can actually play around with these things.
  • 00:28 What I'd like to do right now is I'd like to filter
  • 00:30 my pivot table to say show me only sales items that are greater than $100.
  • 00:34 To do that, what we're going to do is we're going to go and
  • 00:38 grab one of our sales items and it really doesn't matter which one.
  • 00:42 This is actually the sales item field, which we can see when we go in and
  • 00:45 take a look at the Field List.
  • 00:46 Here it is, Item Name, so I'm going to start, Bag of Chips,
  • 00:50 although it really doesn't matter which one I use.
  • 00:52 What I'm going to do is I'm now going to go click the little drop-down to go
  • 00:55 into a filter.
  • 00:56 Now, I could set up Label Filters for text-based stuff, but
  • 00:59 what I'm interested in is a values filter for sales items that are greater than or
  • 01:03 equal to $100.
  • 01:06 Now the thing I want you to notice here,
  • 01:08 you can change all of the different mathematical operations.
  • 01:11 But in this case the only thing a value filter will let you do is set up against
  • 01:15 the fields that are in the bottom right hand corner of your pivot table frame.
  • 01:18 In this case, the only one we have is Sales $.
  • 01:21 When I click OK it now filters the entire list down to say here's all your sales
  • 01:25 items where the grand total is greater than $100.
  • 01:28 That's pretty darn cool.
  • 01:30 But now I say, all right, well that's cool but you know what?
  • 01:32 I want to change this.
  • 01:33 I can see that there's a nice little filter icon that's on here,
  • 01:35 different than the week and the class which haven't been filtered.
  • 01:38 I'd like to clear this filter.
  • 01:40 So I'm going to go back to it.
  • 01:42 And you'll notice that clear filter is grayed out, go what is this?
  • 01:45 You can only set a filter and never remove it?
  • 01:46 That's not right.
  • 01:48 You'd also think that you would actually have a checkbox next to value filter.
  • 01:51 And as it turns out,
  • 01:52 this is one of the really confusing things about using these filter arrows.
  • 01:56 Our pivot table is in compact form.
  • 01:59 And the challenge is we have both category and item name in the row fields, and
  • 02:03 what have I selected?
  • 02:05 A category.
  • 02:06 What did I apply my filter to?
  • 02:07 An item.
  • 02:08 If I go back and select the item, you'll now notice
  • 02:12 that I can clear the filter here and my value filter is checked.
  • 02:16 This is one of the things that I think is terrifying around working with this
  • 02:19 interface and why I often flip my pivot tables
  • 02:22 directly into tabular form before I release them to my users.
  • 02:26 Reason being, now look, Category has no filter, Item Name certainly does.
  • 02:32 I have actually selected a category, but if I even go over and select my
  • 02:35 item name right now, notice that I can now clear the filter from that field.
  • 02:39 So this is a lot easier to use when you can see where the filters have truly been
  • 02:43 applied.
  • 02:44 Now what if I wanted to filter based on text in here?
  • 02:48 I, somewhere in this data set, have a whole bunch of items that have the term
  • 02:51 one slash two in them, a half a unit of something.
  • 02:55 I'd like to filter to find all of those, but they exist in both food and in liquor.
  • 02:59 So how do I locate them?
  • 03:00 I could click on my little filter for Item Name, and
  • 03:03 I could easily go into Label Filters, Contains.
  • 03:06 This would allow me to actually search for the term contains 1/2.
  • 03:10 But you'll notice that it also has some cool stuff around here about wildcards,
  • 03:15 using the asterisks to represents any series of characters.
  • 03:19 So this would totally work, but I'm going to cancel this and
  • 03:22 show you that we can actually do it right from here with the term *1/2*.
  • 03:26 And at that point it will bring up a list of all of my items that contain the term
  • 03:31 1/2, including Pizza 1/2 Dz Wings, 1/2 Sandwich, and
  • 03:35 a whole bunch of 1/2L of wine.
  • 03:37 So that's pretty nice stuff.
  • 03:40 There's one more filter I want to show you.
  • 03:42 I'm going to clear this out.
  • 03:43 So we're going to go back to Clear, and
  • 03:45 I'm going to take a couple of fields off the pivot table.
  • 03:47 We're going to get rid of Category, and we're going to get rid of Item Name.
  • 03:51 And instead, I'm actually going to slide on the POSChitDate.
  • 03:54 And you'll see that this gives us a whole bunch of sales.
  • 03:58 Now it's all broken down by week here and everything else, which is great.
  • 04:01 I can take that off the pivot table if I wanted to.
  • 04:03 But what I'd like to do is I'd like to actually filter this down to only
  • 04:07 a specific range of dates.
  • 04:09 So what I'll do is I'll go and filter, and I'm going to say, hey,
  • 04:12 now we've got value filters because dates are numbers.
  • 04:15 And we don't have text based filters anymore, label filters,
  • 04:18 we now have date filters.
  • 04:20 Notice I can get filters for this week or this month.
  • 04:24 Now these are sensitive to what the actual time is, so I'm not going to
  • 04:28 use that because in later dates it's not going to work for this video.
  • 04:32 But what I am going to to do is, I'm going to choose a specific set of dates.
  • 04:35 We're going to go between.
  • 04:38 And from the calendar I'll dial myself back over here to January,
  • 04:41 and I'm going to go dates between January 1st, and
  • 04:44 I'll dial this one back as well, we'll go up to January 5th.
  • 04:48 And what you'll see is when I go and click on this,
  • 04:50 it's going to cut the whole pivot table down to just that date range.
  • 04:54 This is pretty useful stuff for
  • 04:55 being able to drill into a specific area of your pivot table as well.
  • 04:59 So that's several different ways to go and
  • 05:02 slice down our pivot tables based on dates, based on text, based on amounts.

Lesson notes are only available for subscribers.

Report Filters
04m:37s
Slicers
05m:17s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement