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;
- Your boss would be able to do it
- 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)
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.
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.
You will then see this 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:
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;
Click and drag the column or FIELD names from the top window into the appropriate area in the grid below the list.
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;
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;
Then click on VALUE FIELD SETTINGS.
Pick a calculation method from the SUMMARIZE VALUE FIELD BY list.
You can drag the same field several times into the VALUES area and apply a different function to each instance.
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.
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.
You are probably used to using AUTOFILTER and in the more recent versions of Excel, this has improved a lot with the option to filter or sort by colour or conditional format icon as well as give you a number of pre-set filter conditions that are data sensitive, so the wording of the filter conditions change depending on whether you are looking at dates, numbers or text.
Using AUTOFILTER you can build up some fairly clever filters but there are limitations. With each new filter you apply you are creating an AND type filter where ALL filter conditions have to be met. Yes, you can add in an OR condition within each filter, but you are limited to just the one per column filter.
For example, I want to filter out staff in either the IT or Training departments – easy enough. I then want to filter out people whose salaries fall between certain values. Again simple enough, but it applies the salary range to BOTH of the selected departments. What if I want to see people in IT who fall in one salary range and trainers who fall in a different salary range? I could run two separate filters but I want to see this in one table, not two separate outputs.
This is where your ADVANCED FILTER comes in.
First of all copy all your headings and paste them somewhere on the same worksheet. Personally, I prefer to paste them a few rows below my data table. The rows below your copied headings are where you are going to build the criteria for your ADVANCED FILTER.
Now type in your criteria: under each copied heading type text, dates numbers etc. which are the values you want to filter out. So, taking our example above again, I would type in IT in one cell and Training in the row directly below that.
Similar to our AUTOFILTER, each column I type a value into, equates to an AND condition. Values entered on subsequent rows represent OR. So by typing IT in one row, and Training in the next row I am creating an OR type filter – show me people in either IT OR Training.
Once your criteria are set up, it’s time to run the ADVANCED FILTER. Click back into your data table – this is a MUST. Go to the DATA tab, and click on the ADVANCED button in the SORT & FILTER group.
This will open the following window;
Decide whether you want to apply the filter directly to the data table or to show it as a separate output elsewhere in the SAME worksheet. Personally I prefer to do it in place, but your choice. Excel should automatically work out the LIST RANGE (that’s why you click into your data table before running the filter). Then select your CRITERIA RANGE i.e. the values and the column labels (you MUST include the column labels here) from the table you pasted below your data list. You only need to select the columns that contain criteria, although selecting the entire table will not cause any problems.
Click on OK, your table will now only display the filtered information.
So if I want to extend my filter to include salaries then I can add criteria in the SALARY column of my criteria table. Now, this is where you need to be a bit careful. If I enter a criteria such as <25000 on the same line as the IT department cell but don’t put anything against Training, it will filter anyone in IT with a salary under 25000 but will display all salaries for training as no criteria have been applied. By writing this I would be looking to filter the following information:
- People in IT AND who have salaries under 25000
- OR people in Training
If I want to set a salary criteria for the Training department then I would need to add something on that row too. This is where the possibilities start to dramatically increase with the ADVANCED FILTER because unlike the standard AUTOFILTER I can apply different criteria against each item filtered, or even multiple criteria.
In this example I am filtering for:
- People in IT with a salary greater than 25000 OR…
- People in Training who do not have the job title of “Trainer” OR…
- People in Finance who’s surname begins with “P”
So in your criteria you can use all the normal operators (>, <, >=, <=, <>) as well as wildcards (*, ?). The possibilities are endless!
A word of caution though. If you apply a filter and yet you still see the entire table, either your filter is not precise enough or, most likely, you have included a blank line in your CRITERIA RANGE. A blank line basically says show me data with no special criteria – in other words show me everything. If that happens, just modify the CRITERIA RANGE references and re-run the filter.
In another blog I’ll show you how to create filter RANGES as well as how to extract unique values out of a list using the ADVANCED FILTER.