GoSkills
Help Sign up Share
Back to course

Report Filters

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Using Report 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.

Report Filters.xlsx
205 KB
Report Filters - Completed.xlsx
205.2 KB

Quick reference

Report Filters

Using Report 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.

Instructions

Creating report filters

  • Drag the desired field form the fields list into the Filters area of the PivotTable

Multi-selection

Enabling multi-selection
  • Click the drop-down filter arrow for a field
  • Check the box next to “Select Multiple Items”
Disabling multi-selection
  • Click the drop down filter arrow for a field
  • Uncheck the box next to “Select Multiple Items”

Clearing filters

  • Click the drop-down filter arrow
  • Choose (or check the box next to) “All”

Arranging filters horizontally

  • Select a cell in the PivotTable
  • Right-click > PivotTable Options > Layout & Format
  • Change the “Display fields in report filter area:” setting to Over then Down
  • Toggle the “Report filter fields per column” as needed to control how many rows are displayed in each column
Login to download
  • 00:04 In this video we're going to focus on how we can slice and
  • 00:07 dice a pivot table to drill into just the relevant items.
  • 00:11 The reason this is important is because we've got a lot of information showing on
  • 00:14 this worksheet right now.
  • 00:16 The report is configured to have item names on the left and
  • 00:18 our weeks across the top on columns.
  • 00:21 And then we've got our sales dollars as values.
  • 00:22 And it's great, because it does show us our total sale by product, by week.
  • 00:26 But the problem is, if I press End+Down Arrow, there is over 200 rows of data.
  • 00:31 That's an awful lot to read.
  • 00:33 So let's go back up to the top here, and just scroll my top rows back into view.
  • 00:37 This is what the Filters area is for, it gives us the ability to start filtering
  • 00:41 down into our pivot table to look at a smaller subset of data.
  • 00:45 So I'm going to go here, I'm going to drag Class onto my Filters area.
  • 00:49 And you'll notice it gives me this new field at the top of the pivot table,
  • 00:52 classes, and this is all.
  • 00:54 And if I check the box here, you'll notice that I can select Alcohol.
  • 00:58 And it will drill into just the alcohol items.
  • 01:00 What kind of effect does that have?
  • 01:01 If I press End+Down Arrow, now there's only 117 as our last row.
  • 01:06 So we've actually cut half of the data out of this dataset.
  • 01:09 If I go back and take a look at food, and say,
  • 01:12 okay, I'm going to go down to the end again.
  • 01:15 Now we're at 106 rows, different rows, because this is just the food items.
  • 01:19 So this allows us to drill in to look at a lot
  • 01:22 smaller subset of data that may be relevant to us.
  • 01:25 I can also click All to bring everything back.
  • 01:29 And I also have the ability to choose to select multiple items that will allow me
  • 01:32 to actually check the individual boxes for what I need or uncheck them as well.
  • 01:36 Now, I'm going to add another component in here as well, and that's Category.
  • 01:40 We'll slide that under Class on the Filters area.
  • 01:43 And now what I could do with this, I could say, hey, you know what,
  • 01:46 let's filter into Alcohol, so we'll get rid of Food.
  • 01:48 So okay.
  • 01:50 And now maybe I want to see just Wine.
  • 01:52 And I'm going to make my list a little bit bigger, and holy smokes,
  • 01:55 there's a lot of stuff here.
  • 01:55 So you know what, let's just type in wine and see what happens.
  • 01:58 Hey, look at that, there we go, there's Wine.
  • 02:00 So it actually picks it up for me.
  • 02:01 So I can now say, OK, and it will drill into just my wine items.
  • 02:06 And there's still quite a lot of them, only about 40, I guess.
  • 02:09 But still more than I might want to be digging into right now.
  • 02:12 What I'm interested in at the moment though, is beer.
  • 02:15 Let's go and take a look at that.
  • 02:16 So I'm going to go and say, let's bring the all back and
  • 02:20 what I'll do is I'll type in beer.
  • 02:23 And what you'll see here, if I expand this list just a little bit,
  • 02:26 is it's actually picked up three different categories of beer.
  • 02:29 So it's not like it's actually has the beers in the beginning.
  • 02:32 It can have it anywhere in the actual text name.
  • 02:34 So this picks up all three of my categories which is fantastic.
  • 02:37 When I say OK now, I can see all of my different beers,
  • 02:41 all 20 of them or so, all listed in one report, nice and easy.
  • 02:45 And of course, if I want to, I can go back and clear this back to all and
  • 02:50 show everything I need here and clear my alcohol and
  • 02:53 food to get it back to all as well.
  • 02:56 What if I wanted to go even deeper with this?
  • 02:58 What if I wanted to add one more level?
  • 03:00 I'm going to add the Hour.
  • 03:02 So maybe I want to see exactly what's happening in the 10 o'clock hour on any
  • 03:05 one of these given days.
  • 03:07 There we go, easy to do.
  • 03:09 And now of course, I can filter into just Alcohol or just Food or
  • 03:12 whatever I want to do.
  • 03:13 The next thing I want to talk about is just the layout of this because as we add
  • 03:17 new fields, it's stacking something vertically for us and
  • 03:20 that obviously takes a little bit of space.
  • 03:22 Is it possible to actually have these arranged horizontally across the top of
  • 03:26 the pivot table?
  • 03:27 So let's see how we can do that.
  • 03:29 We'll go right-click > Pivot Table Options.
  • 03:32 And what you'll notice is that it's got a section here, right on the layout and
  • 03:35 format for Display fields in the report filter area.
  • 03:38 And right now it goes Down, Then Over.
  • 03:40 Well, right now it's just going down.
  • 03:42 It says how many filter fills you want per column?
  • 03:44 And it says 0.
  • 03:45 What, 0?
  • 03:45 It says, just use whatever you feel the default should be.
  • 03:49 We're going to go over and then down instead of going down and over.
  • 03:52 And we'll say 0 and let's take a look at what happens.
  • 03:56 At this point, it actually gives us a nice little layout horizontally for
  • 04:00 our filters.
  • 04:01 So that takes a little bit less space at the top of the page.
  • 04:04 Can we tweak this even more?
  • 04:06 Absolutely.
  • 04:07 We can come back and say, hey, you know what, if we showed one field per row,
  • 04:10 then obviously that's going to give us the original view we saw because it's
  • 04:13 only going to have one and then it'll wrap the next row.
  • 04:15 But we could say two.
  • 04:17 And now, it's going to wrap that POSCHitHour under class because it's only going to
  • 04:21 put two of these on every row.
  • 04:23 So that's just a nice little formatting option that we can play
  • 04:27 around with in order to make this look a little bit better.

Lesson notes are only available for subscribers.

Sorting Values
05m:37s
Built-in Filter Controls
05m:18s
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