Online Excel PivotTables Tutorial by Microsoft
LESSON WORKBOOK: Level 3 Lesson 3 2007.xlsx
About PivotTables
In this lesson we will start to look at arguably one of Microsoft Excel most powerful and useful feature, that is PivotTables. In a nutshell a PivotTable takes two dimensional data (your spreadsheet) and creates a three dimensional table (the PivotTable itself). They are a great way to produce statistical information from a table of data.
I won't try and hide the fact from you that I am a big fan of PivotTables. I use them a lot in the development of Spreadsheets for my clients. Once the client sees the PivotTable, they nearly always ask "could I do that?" the answer of course is yes! Unfortunately most people tend to shy away from PivotTables, as they see them as too complex. to be honest with you, when you first use a PivotTable the whole thing can seem a bit daunting. Don't be put off by this as persistence will almost certainly pay off. I find the best part about PivotTables is their ability to be manipulated via 'Trial-and-Error' and immediately show the result. If its' not what you expect, simply use the Undo feature and/or have another go! What you must always remember is that you are not changing the structure of your original table in anyway at all, so you can do no harm!
Why are they called PivotTables ? - Well, basically they allow us to pivot our data via drag-and-drop to produce meaningful information. This makes PivotTables interactive in that once the table is complete we can very easily see what effect moving (or pivoting) our data has on our information. This will become patently clear later on, once we have produced a PivotTable. Believe me, no matter how experienced you get at PivotTables there will always be an element of trial-and-error involved in producing the desired results! What this means is you will find yourself pivoting your table a lot!
What would we use them for ? - We would use a PivotTable to produce meaningful information from a table of information. You will recall in lesson 2 we looked at Excel's Advanced Filter feature and how it could be used to extract information from a table of data based on a set of criteria. A PivotTable could be used on that same table to create a table that could tell us much statistical information about all the data contained within it. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, postcodes etc. With a PivotTable we could very easily and quickly find out:
How many people have the same names.
How many postcodes are the same.
A count of a particular occupation.
See only people that match a particular occupation.
Find out the addresses of people that match a postcode
In fact the list can go on and on!
What is the advantage ? - Perhaps the biggest advantage to using PivotTables is the fact that we can generate and extract meaningful information from a large table of information within a matter of minutes. Or perhaps it is because they will not use up a lot of memory from your PC. In a lot of cases we could get the same results from a table of data by using Excels built in functions, but this would take more time and use far more memory. On top of this, if we wanted some new information we can simply drag-and-drop (pivot). We can also opt to have our information update each time we open the Workbook and/or by clicking refresh.
Pivot Charts - In Excel 2000 Microsoft introduced Pivot Charts. These are simply charts that are read from the table created via the PivotTable Wizard. In fact PivotTables are really no longer just PivotTables, they are now PivotTables and PivotChart Reports. These two features go hand-in-hand with each other. By this I mean, when you create a PivotTable you can also create a Pivot Chart at the same time with no extra effort needed on our part. Pivot Charts allow us to create professional complex charts that were previously not possible without either VBA or using Excel's Controls.
Tables and Lists Guidelines for PivotTables
Possibly one of the most important factors when considering using a PivotTable is to have our data set up in what could be termed as a table and/or list. As our PivotTable will be basing all its data on this list or table it is vital that we set our tables and lists up in a uniform way.
In the context we are discussing here, a Table is no more than a List with more than one column of data. A List is often referred to in the context of a Table as well. The 'good practice' that applies to setting up a List will aid us greatly when we need to apply a PivotTable to our data. When we extract data via the use of Lookup functions or Database functions we can be a little less stringent in how we set up our Table or List. This is because we can always compensate with the aid of a function and probably still get our result. However, having said this we should always set up our List or Table as well as possible. When we use Excels built-in features they will and do assume a lot about the layout and setting up of your data. They do have a degree of flexibility but more often than not you will find it easier to follow the guidelines for setting up your Table or List.
Let's look at what I believe to be the most important aspects of setting up a Table or List.
Headings. This is a must! They should always be in the row directly above your data. Never have a blank row between your data and the headings. Make them distinct in some way eg; Bold them.
Leave at least three blank rows above your headings. These can be used for formulas, critical data etc. You can hide the Rows if you wish.
If you do have more than one List or Table on the same Worksheet leave at least one blank column between your List and Tables. This will aid Excel in recognizing them as separate.
Avoid blank cells within your data.
Sort your List or Data. This in not so much for PivotTables, but for the person reading the data.
If we follow these guidelines as close as possible, using PivotTables will be a relatively easy task.
Creating a Simple PivotTable
Ok, enough talk about PivotTables let's jump right in and create one! What we will do is create a PivotTable in it's simplest form. Before we start set up open the attached workbook Level 3 Lesson 3 2007.xlsx and click in the Table/List on the Data tab. Notice while you are here that the heading is bolded to make it different from the rest of the data and there are no blank rows.
Select a cell within your data and go to the Insert tab and select PivotTable under Tables options.
When the dialog pops us, the default option Select a table or range should be selected by default under Choose the data you want to analyze. This is what we want. The other available option External data source creates a PivotTable or PivotChart report from data stored in a file or database outside the current workbook or Microsoft Excel.
Choose where you want the PivotTable to be placed is the next option and the default is New Worksheet. Again this is what we want. If you want to place the PivotTable elsewhere, select Existing Worksheet and navigate to the location.
Click OK and you will see on a new worksheet the PivotTable field list and the PivotTable outline.
To complete the PivotTable layout you can either drag and drop your column headings (in this case we only have one - Names) onto either the PivotTable layout or the different areas of the dialog; Report Filter, Column Labels, Row Labels or Values. We will use the latter method. Column headings can also be referred to as Fields
As we are only using a single column list, we only have one field , called Names. If we had more than one column, each column would have it's own field. While dragging field buttons to the Report Filter, Column Labels or Row Labels areas is optional, it is always mandatory that at least field be placed in the Values area of your PivotTable. Sometimes the terms Column and Row can be confusing, when one is using PivotTables, but all it really means is that by dragging a field to the column area our data will span across our worksheet columns and by dragging to the row area, our data will span down Excel's rows.
Drag your Names field button to the right to the Row Labels area of the dialog. Then drag the same Names field button to the Values area. Notice how the PivotTable layout populates when you do this and you will notice at this point that as our data is text, Excel will default to using Count of Names. This can be changed either at this stage, or at a later stage. Just so you have a feel for what can be done now, click on the Count of Names button and select Value Field Settings. Excel gives you the choice of how you would like to summarise your data. The other tab here is the Show Data As option, which basically allows us to use a field in our source data as the base field or comparison field in a custom calculation. Something we will be covering later.
You should now have a very simply PivotTable on a new Worksheet displaying a count of all our names in the list. Hide the PivotTable field list by selecting the X at the top right hand side.
This PivotTable would be considered a PivotTable in its simplest form.
Working with the PivotTable
You should have noticed that once the PivotTable was created another tab, the PivotTable tab, popped up on the right hand side of the ribbon. Underneath this tab are another two tabs, Options and Design. If you click off the Pivot Table the extra tabs will disappear, click on it again and they will reappear. The most common options from these tabs are also on your right mouse button if you click it from within the PivotTable.
Left click on the Names field button and drag it over the Column Labels and Row Labels area of the dialog. You should notice a very small diagram pop up near your mouse pointer showing you which area you will be dropping your field button to. Drop the Names field in the Column Labels area on the dialog to see the table spread across your spreadsheet. Now drag it to the Report Filter area of the dialog. As soon as you do this, you will notice that all our individual names disappear and all we have under Count of Names is a total of all the names. Now drag the Names field back to the Row Labels section.
One of the additional options we have with any field is that we can hide particular items from showing and being included in our total. For example, click the arrow on the Names button and uncheck the name Dave. This will remove it from our PivotTable and change our Grand Total accordingly. Select the arrow again and check the name Dave so it is again displaying, or alternatively at the very top of this list is the option Select All which simply means return a total for all names.
Let's go back to our original data on the Data worksheet now and delete one of the occurrences of the name Dave. The one I would like you to delete is the one in cell A2 directly under the heading. Delete the whole row. Now go back to your PivotTable and as you will see the change has not yet been reflected. This is because we need to Refresh our PivotTable. There are two options here that we have for Refreshing a PivotTable, 3 if our data is from an external data source. One is by manually clicking a Refresh button, the second is setting an option (we will look at this later) that will automatically refresh the PivotTable each time the Workbook containing the PivotTable is opened.
There are two ways to use the Refresh button. On the Pivot Table Tools tab, under Options you could select Refresh under Data options. Or you could right click on the PivotTable and select Refresh from the list. You should now have only one occurrence of the name Dave. In addition to this you should have down the very bottom of your PivotTable the word (blank). This, if you haven't guessed already, represents the blank cell in our list. Whether this is left showing or not is purely optional. If you did wish to hide it, simply select the arrow on the Names heading and deselect (blank) from the list and click OK. Just be aware that by deselecting (blank) this will automatically apply to any blank cells within our Names field.
Handy Options
To finish off this lesson, we will run through some of the more common settings for our PivotTable. Click anywhere within your PivotTable to see the ribbon and from left to right, here are some of them.
OPTIONS
PivotTable Name
Here you would simply type a meaningful name for your PivotTable.
Field Settings
Displays the Field settings dialog where you can decide whether to sum, count average etc., your data.
Group Selection
Allows you to group certain items together and then to apply other settings to those items.
PivotChart
Select PivotChart, then any chart type and OK to see a chart of the data you have in your PivotTable
DESIGN
SubTotals
Will display or not display Sub Totals for the fields specified.
Grand Totals
Will display or not display Grand Totals for the fields specified.
Report Layout
Gives three different options for displaying your reports.
Blank Rows
Allows the option of a blank row after each item (or not)
PivotTable Styles
Hover your mouse over any of the designs in this area to see how they would be applied to your PivotTable. Click to select your preferred design.
This is as far as we would like to go in the first lesson on PivotTables. As you have no doubt seen, or soon will, PivotTables are a very powerful feature of Microsoft Excel. What I would like you to do before looking at the next lesson on PivotTables, is to actually create some of your own dummy PivotTables based on some data that makes sense to you. I have found that one of the most effective ways of training in PivotTables is to actually let the student use trial and error. In other words, jump in head first and try out all the different options and settings for PivotTables to see how it affects the PivotTable. Just remember at the end of the day, no matter what you do to the PivotTable it will not affect your underlying data. If worse comes to worse, you can always start again, or use the Undo feature.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.