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
199 KB
Built-in Filter Controls - Completed.xlsx
188.1 KB

Quick reference

Topic

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 Pivot

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 pivot (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 pivot (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:00 In this video, we're gonna look at a few different ways to filter,
  • 00:06 and we're gonna focus on the values area of the pivot table
  • 00:11 as well as the row and the column labels.
  • 00:14 So, the first thing that I'd like to do is, I've got a nice little pivot that's
  • 00:18 set up here with the category names and nested underneath that is the item names.
  • 00:22 And the weeks across the top and our sales dollars amounts in the values areas.
  • 00:27 And I'd like to filter this out to show all sales greater than $100.
  • 00:31 So what I'm gonna do is, I'm gonna come over and
  • 00:34 I'm gonna click on one of my products over on the side here.
  • 00:38 I'm gonna go to the filter arrow on Items, and
  • 00:42 I'm gonna choose Value Filters, and I'm gonna choose Greater Than Or Equal To.
  • 00:46 And in this particular case, it says, all right.
  • 00:49 Let's see which one, Sales $, which is from the values area of the pivot table,
  • 00:53 is greater than or equal to 100.
  • 00:55 And it should filter to only pull up items where the grand total is greater than or
  • 01:01 equal to $100.
  • 01:02 So that works out nicely, and
  • 01:04 you can see that my subset of data is trimmed down quite a bit.
  • 01:09 You'll also notice when I do that,
  • 01:10 that I've got an indicator on my little drop-down.
  • 01:13 Instead of just the arrow like I have for Week, I've actually got a little filter
  • 01:17 icon, and that tells me that something is being filtered there.
  • 01:19 So if I click on that, and I wanna undo it, well, hang on a second.
  • 01:24 Let's go back into one of the sales items because that's what I'm
  • 01:28 actually working with.
  • 01:29 Now we can filter it and say Clear Filters From Item Name.
  • 01:32 The reason why it didn't do it before is because I'd actually selected a category,
  • 01:36 and a category is not filtered.
  • 01:39 You can also get an indicator of what is filtered
  • 01:42 by looking at your pivot table field well.
  • 01:44 And you can see here that Class and Item Name have been filtered, so
  • 01:47 if I clicked on this guy here, I can clear the filter from Item Name, and
  • 01:51 that'll put everything back to the way it was.
  • 01:54 I find this is a little bit hard to work with in compact view sometimes.
  • 01:57 If we do flip the pivot table tool design report layout into tabular form,
  • 02:04 you'll notice that this would've been a lot easier to figure out because my
  • 02:08 greater than filter was actually over here.
  • 02:11 And at this point,
  • 02:12 I can now see that it's filtered on this column but not this column.
  • 02:16 Sometimes, this particular view is easier to build your pivot table if you're trying
  • 02:20 to get into some more finer points on this stuff.
  • 02:24 So I'm gonna clear that again.
  • 02:26 Now, what about labels?
  • 02:28 What if I wanted to filter by the name here?
  • 02:31 Anything in the row areas or the columns are always text,
  • 02:35 and that's something to, well text or dates.
  • 02:39 Those or the two things generally that we see.
  • 02:41 So if I want to filter for a specific product, let's go and
  • 02:45 look at a labels filter, and I'm going to look for Contains.
  • 02:48 And just for fun, you'll notice down the bottom here that I have the ability
  • 02:52 to actually use some wild cards.
  • 02:54 So I'm gonna look for anything that contains star,
  • 02:57 so anything slash half star.
  • 03:01 Let's see what ends up coming up here.
  • 03:04 You'll notice that immediately all of my beers are all gone.
  • 03:08 I'm just down to wine, and
  • 03:10 you can see that i've picked up the half of the JT Merlot, and
  • 03:14 you can see that it goes half space all the way down to here and then half L.
  • 03:18 And this one's got Cracked Frank's Merlot 1/2.
  • 03:21 So anywhere where it has a half in there, it's pulled all those kinda things up, so
  • 03:25 that's kind of a neat way for being able to deal with that.
  • 03:28 Once again, I wanna get rid of that filter.
  • 03:30 I can just come back here and say clear the filter from the item name.
  • 03:35 Now, another real quick one here, I'm gonna pull Category off of my pivot table.
  • 03:40 I'm gonna pull Item Name off my pivot table.
  • 03:42 And I'm gonna throw the POSChitDate on here.
  • 03:46 We can see our sales by day.
  • 03:47 I probably don't really need week number on this either.
  • 03:51 At this point, I could go and filter by date.
  • 03:54 So if I click this drop-down arrow,
  • 03:56 you'll notice that we have a little contextual change here.
  • 03:59 It gives us date filters.
  • 04:00 And I can go and choose to filter for some dates between, or
  • 04:03 if I'm using current data, next week, this week, last week.
  • 04:06 All these kind of different areas,
  • 04:08 all dates in the period of November or January.
  • 04:11 What I'm going to do is I'm going to pick up some dates between.
  • 04:15 I'm going to filter it and say, you know what,
  • 04:18 give me the days between January 15th, 2013 and January 22nd, 2013.
  • 04:23 Maybe I don't have a week field set up for some reason.
  • 04:26 If I say OK, you'll notice that it filters the entire pivot table down.
  • 04:31 And you can see that my ChitDat over here has been filtered as well as my Class
  • 04:36 up from my report filter as well.
  • 04:37 So lots of different ways to filter based on dates, based on labels, and
  • 04:42 based on values in the pivot table itself.
  • 04:44 Highly encourage you to check those out.
  • 04:47 Again, even though we filtered by labels for dates here,
  • 04:50 we can still monkey with our values areas and
  • 04:54 filter now for, let's say, sales or days that are less than $100.
  • 04:59 Value filter less than $100, and you'll see that we cut
  • 05:04 down even some more records there, so that replaced the date filter for me actually.
  • 05:08 But lots of different ways to filter in a pivot table.

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