Category Archives: Pivot Tables

Excel – Using Report Filters in Pivot Tables

In a previous blog I showed you how to create a basic Pivot table. We looked at how to add fields to columns, rows and values areas to quickly summarise information from a list of data.

One part of the grid I did not cover last time was the FILTERS segment. This works in exactly the same way as the other segments we use to build our Pivot table, i.e. click and drag field names into it.

ScreenHunter_211 Feb. 21 15.50

The FILTERS area

As the name suggests, fields dragged into this area can be used as a filter. These can be very useful to keep your Pivot tables relatively simple and can help to reduce the overall size of the Pivot table.

Using some basic data, I will build up a Pivot table, but first without applying a FILTER field.

ScreenHunter_211 Feb. 21 15.48

Pivot with no FILTERS applied

This produced a Pivot table 118 lines long, including totals and subtotals. It’s OK, it does its job, but perhaps we can improve it or at least simplify it a bit.

By moving the location and subject fields into the FILTERS area, we then get this;

ScreenHunter_211 Feb. 21 15.52

Same Pivot with FILTERS added

We may have lost some of detail that was visible before but the table is now only 8 rows long. No more incessant scrolling up and down to see results. Now…to see the detail, I can be very specific in what I see by clicking on the drop downs next to my FILTER fields.

ScreenHunter_211 Feb. 21 15.54

Location filter items

I can now see and select any one or more of the locations that appear in my location field. Note that I have ticked the SELECT MULTIPLE ITEMS option at the bottom of the list in order to be able to pick more than one location. If I don’t tick it, then I can only pick one location at a time. Personally, I would always tick this option whether I am picking one or several items.

Once you’ve made your choice, click on OK.

ScreenHunter_211 Feb. 21 15.58

Single item selected

Here, I have picked just one location (Birmingham) and below I have chosen three;

ScreenHunter_211 Feb. 21 15.59

Multiple items selected

Note that when multiple items are selected that’s all it tells you in the drop down – MULTIPLE ITEMS. If you need reminding of which ones you picked just click on the drop down again.

ScreenHunter_211 Feb. 21 16.01

Checking when multiple items selected

Also note that with each FILTER applied the size of my Pivot table remains unchanged. This won’t be the case every time, but at least you shouldn’t end up with the table being 10 rows and then jumping to 150 rows with the next filter, but this will be entirely dependent on your data and how you structure your base Pivot table.

If you are going to apply lots of filters, you can control how these are laid out above your Pivot table. If I keep on adding more and more FILTERS I get this;

ScreenHunter_211 Feb. 22 13.54

Five filter fields added

There’s nothing really wrong with this, but if you prefer you can distribute the filters over several columns and rows which might suit you better.

To customise this, click in your Pivot table and then go to the OPTIONS or ANALYZE tab (depending on which version of Excel you have) in the PIVOTTABLE TOOLS tabs and click on OPTIONS.

ScreenHunter_211 Feb. 22 16.43

Pivot table options window

In the OPTIONS window, select the LAYOUT & FORMAT tab. Here, there are two settings you can use;

  • Display fields in report filter area
  • Report filter fields per row

The first option determines whether it fills rows before moving across to the next column (DOWN, THEN OVER) or fill across the columns first, then move to the next row (OVER, THEN DOWN).

Use the second option to set the number of fields you want to see per row. So going back to our earlier example, with five FILTERS, if I set the options to OVER, THEN DOWN using three fields per row we get this;

ScreenHunter_211 Feb. 22 14.23

Customised filters layout

As with any settings, there is no right or wrong, only what suits you and the number of FILTERS you want to create.

As a word of advice, rather than anything else, try to stick to top level fields to have in your FILTERS. Although I have put date in the example above, this is generally not a good field to use in FILTERS as it is too granular. I used to manage inventory across a number of warehouses across Europe, and good top level FILTERS were things like product line, warehouse number, consumable/non-consumable flags, rather than part number. It meant I could quickly narrow down my output to consumable items for a specific product line, in a specific warehouse. As with everything, the choice is yours.

So, other than enable you to filter your Pivot table results, what else can you do with FILTERS?

Lurking under the OPTIONS button, is something called SHOW REPORT FILTER PAGES.

ScreenHunter_211 Feb. 22 15.24

Show report filter pages option

To get to this, make sure you click on the arrow next to OPTIONS rather than on the OPTIONS button itself. Note that this option is greyed out if no fields are present in the FILTERS area.

You should then see a window with a list of any FILTERS you have in place.

ScreenHunter_211 Feb. 22 15.58

List of filters in the current Pivot table

(I’ve reverted back to the original Pivot table I created….simpler to view)

Pick any one of the FILTERS shown and click on OK.

Then check out the new tabs that appear in your workbook!

ScreenHunter_211 Feb. 22 16.01

New tabs in the workbook

What this does is create a separate worksheet for each item in the selected filter. In this example, it has created a separate sheet for each course. Had I selected location, then a separate sheet would have been created for each location.

Each sheet contains a Pivot table in its own right. But why do this, create a whole load of new sheets when the information is already nicely packaged in a single Pivot table? Let me ask you this…are the people you send your Pivot tables to happy using Pivots and do they know what they are doing when using drop downs etc. within those Pivots? Probably not. By sending the information like this, all your users have to do, is go to the tab that interests them and view it. No need to apply filters, or click on anything that is likely to cause panic or confusion. This may seem like a damning indictment of the average Excel user, but you don’t know what you don’t know, and if Pivots are a mystery, it’s easy to click or even worse, double click on something and a new sheet appearing or alter the structure of the Pivot with the user not knowing why something happened or how to correct it.

So that’s FILTERS for you. Useful to quickly narrow down output in a Pivot and also making Pivot data more accessible to non-Pivot users.



Excel – Basics of Pivot Tables

If you are familiar with Pivot tables then you will know just how amazing they are. If not, then read on.

Knowing how to use Pivots is one of the MUST KNOW features of Excel. Since Office 2010 there has been a new add-in called PowerPivot which takes the humble Pivot table and turns it into an all singing and dancing monster of a BI tool. This blog will look at the basic Pivot table, but don’t despair, this is a very powerful tool in its own right, and if you have to analyse data on a regular basis this is the tool for you.

Are you the sort of person who dreads month end reporting? Your boss gives you a huge file and asks you to run through it and asks for some sort of analysis to show what’s happened in the last month/quarter etc. and of course expects it in next to no time because apparently it’s quite simple and quick to do.  If that were true then;

  1. Your boss would be able to do it
  2. It wouldn’t take you several hours or even days of stress to get even close to what is required

This blog will show you how to reduce this time frame down to a matter of seconds to produce a summary report/analysis using a Pivot table.

First of all…some pre-requisites in order to be able to use a Pivot table;

  • Your data must be in a LIST or DATABASE format. In other words you can only have columns of data with NO ROW HEADINGS. Each row of data is a distinct record.
  • Each column must have a unique HEADING and there must be no completely empty columns within your data table. You can have an empty column as long as it has a heading (not that that is very useful…but it will work)
Dos and don'ts of data to build a pivot table

Dos and don’ts of data to build a pivot table

As long as your data meets these basic requirements then you are good to go to build your Pivot table.

Before we create a Pivot table…let’s look at what it does.

If we look at some very basic data, we can see a number of people, the regions they work in and the sales they have achieved.

Excel will look down each column and will “group” the labels it finds. So in the salesperson column it will group all the names together to create a list of unique values. It will repeat this in each column that is selected to be part of the Pivot table. So in this example, if we look at Bob we can see that Bob appears 3 times in two regions. The Pivot table grabs all the information and will group, count or sum up any values associated with Bob in each region. So in the end result we can see that Bob has total sales of 912 in the North and 326 in the South. Depending on which columns you select and perform summary calculations on, the Pivot table will “group” all the relevant labels and values to give you an instant summary of your data table. This grouping effectively allows you to convert a list into a two-way table and to review and analyse data in almost any number of ways.

How a Pivot groups information

How a Pivot groups information

Now that we have a basic understanding of what the Pivot table can do, let’s create one.

Click anywhere in your data table.

Go to the INSERT tab, and click on PIVOT TABLE in the TABLES group. You will notice there is a RECOMMENDED PIVOT TABLES in Excel 2013. I will mention those a bit further on.

ScreenHunter_124 Feb. 24 11.38

You will then see this window;

The create pivot table window

The create pivot table window

If you were clicked somewhere in the data table it works out the boundaries of your list and automatically populates the TABLE/RANGE information. If you weren’t in your table then click on the selector button and manually select the data range. Alternatively, if you use named ranges type in the range name.

Decide where you want your Pivot table to appear – NEW WORKSHEET or in an EXISTING WORKSHEET. If you opt for EXISTING, click on the range selector button and select a cell somewhere in your workbook. The cell you select will be the top left hand cell of your Pivot table area. Make sure that you leave enough room to the right and below to ensure the Pivot table does not overwrite any existing data. If an overlap is detected, then you should see this message box appear:

Warning - overlap of Pivot and existing data

Warning – overlap of Pivot and existing data

I would suggest at this point to select CANCEL and choose somewhere else to start your Pivot table from.

You will now see two new windows appear;

Build your Pivots area

Build your Pivots area

Click and drag the column or FIELD names from the top window into the appropriate area in the grid below the list.

Click & drag field headings into your Pivot building grid

Click & drag field headings into your Pivot building grid

You are not limited to one field per segment in the grid, you can have as many as you have actual field names available…although that can start to look messy and can make your Pivot table difficult to read.

Here’s an example of multiple row fields being selected;

Multiple Row values added

Multiple Row values added

Pivot showing multiple fields added in

Pivot showing multiple fields added in

The great thing about Pivots is that if you don’t like the end result or prefer a different layout you don’t have to start again. Just click and drag fields from one area to another or remove them completely and add new ones until you get the type of table you need.

Something to note:

  • By default anything you put in the VALUES area will have the SUM or COUNT function applied to it. If the data you put in this area contains numbers and no blanks it will automatically use SUM. If the data has blanks or contains TEXT values then it will apply the COUNT function.

You can change the summary function to any fields sitting in the VALUES area by clicking on the little arrow next to the field name;

Getting to your Value Field Settings

Getting to your Value Field Settings


Pick a calculation method from the SUMMARIZE VALUE FIELD BY list.

Changing the summary calculation method

Changing the summary calculation method

You can drag the same field several times into the VALUES area and apply a different function to each instance.

Field name plus a number to create a unique label

Field name plus a number to create a unique label

In this example I have applied the SUM, AVERAGE and MAX functions to the same field (Cost). Notice how it has named them Cost2 and Cost3. This is because when you add a field several times, it will apply the default the summary calculation first (SUM or COUNT) and as Pivot tables can only work with unique field names it adds a number to any repeated field names in the VALUES area to give each one a unique name. As you apply the new function it will change from SUM or COUNT to whatever function you have selected.

You can create as many Pivot tables as you like off a single data table, meaning you can summarise your data in a variety of ways. If you are moving from Excel 2003 or earlier you will no longer be prompted about using the same cache or a new one. All Pivot tables since 2007 are created using the same Pivot cache if the same data list is used each time.

If you are wondering what a Pivot cache is, it’s a virtual copy of your data list that is automatically created when you get past the Create PivotTable window and sits inside the workbook as a virtual data table. It’s important to understand a little bit about the cache.

Your Pivot table is not directly linked to your data list. When you first create a Pivot table it takes a snapshot of your data and stores it in the cache. Your Pivot table then interacts directly with the cache. The downside to this is that as this cache is a snapshot in time of your data. If you update/edit any data in your table, it means the cache is no longer the same as it is frozen in time when the pivot was set up (that could be 10 seconds ago or 1 week ago!). To update the cache, simply right click anywhere in your Pivot table and select REFRESH. Your Pivot table will now update itself showing the latest data values. And because you can have multiple Pivot tables all running off a single cache, ALL the tables linked to that cache will update at the same time.

At the beginning I mentioned RECOMMENDED PIVOT TABLES which was a new  feature in Excel 2013. Basically, Excel looks at your data using the field names it makes a number of suggested layouts.

Recommended Pivots from Excel 2013

Recommended Pivots from Excel 2013

Pick the layout that suits your needs and click OK. Job done! Although this is quick and easy (i.e. requires virtually no thought) I would still try to create a Pivot table manually…as you’ve seen, it’s not that difficult, and by doing it manually you get exactly what you want…the choice as they say, is yours!

And that is how you create a basic Pivot table. As you get used to clicking and dragging fields into the various ROW/COLUMN/VALUES areas you will find that you can create a Pivot table (on familiar data) in 5-10 seconds…flat! And if your boss does not like your initial layout, click and drag the fields until they are happy. Each change will only take a matter of seconds so gone are the hours or even days of stressing over those month end reports.

In other blogs I will show you how to use the REPORT filter, customise your Pivots, create Pivot charts, group data and add calculated fields….to name but a few of the wondrous things that Pivots can do.