 |
|
|
|
Online Excel Pivot Tables Tutorial by Microsoft
In this lesson we will start to look at arguably one of Microsoft Excel most powerful and useful feature, that is Pivot Tables. In a nutshell a Pivot Table takes two dimensional data (your spreadsheet) and creates a three dimensional table (the Pivot Table itself). They are a great way to produce statistical information from a table of data.
We won't try and hide the fact from you that we are a big fan of Pivot Tables. We use them a lot in the development of Spreadsheets for our clients. Once the client sees the Pivot Table, they nearly always ask "could I do that?" the answer of course is yes! Unfortunately most people tend to shy away from Pivot Tables, as they see them as too complex. to be honest with you, when you first use a Pivot Table the whole thing can seem a bit daunting. Don't be put off by this as persistence will almost certainly pay off. We find the best part about Pivot Tables 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 Pivot Tables ? - Well, basically they allow us to pivot our data via drag-and-drop to produce meaningful information. This makes Pivot Tables 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 Pivot Table. Believe me, no matter how experienced you get at Pivot Tables 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 Pivot Table 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 Pivot Table 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 Pivot Table 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 Pivot Tables 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 Pivot Table Wizard. In fact Pivot Tables are really no longer just Pivot Tables, 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 Pivot Table 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 Pivot Tables
Possibly one of the most important factors when considering using a Pivot Table is to have our data set up in what could be termed as a table and/or list. As our Pivot Table 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 Pivot Table 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 Pivot Tables, but for the person reading the data.
If we follow these guidelines as close as possible, using Pivot Tables will be a relatively easy task.
Pivot Table and Pivot Chart wizard
Whenever we create a Pivot Table and Chart we use what is known as the Pivot Table and Pivot Chart wizard. This guides us through the creation of our Pivot Table using a three step process. In a nutshell these steps are:
How is our data set-up and do we also want a Pivot Chart
Where is our data stored eg, range
Where do you want to put your Pivot Table eg, new Worksheet or existing one.
Once we have done this three step process we end up with a empty Pivot Table ready to accept our data.
There are also many side-steps we can take along the way to manipulate our Pivot Table, but most users may find it easier to do this after step 3. There is however a slight difference with Excel 97 and 2000 in that there are 4 steps in Excel 97. Step 3 in Excel 97 is where you can set-up your data, this can still be done in Excel 2000, but you have a choice of doing this after the wizard has completed.
A Simple Pivot Table
Ok, enough talk about Pivot Tables let's jump right in and create one! What we will do is create a Pivot Table in it's simplest form. Before we start set up some data:
In cell A1, type a heading Names (make sure it is bolded)
In cells A2:A10, type the following; Dave, Jill, Joan, James, Adam, Bill, Dave, Joe, Kate, Fred
What I will now do is guide you through the steps of setting up a very simple Pivot Table. I purposely do not use screen shots in my lessons as my aim is to teach you, not show you! This forces you to do it for yourself and take in far more than you realize.
Step 1
Select a cell within your data and go to Data>PivotTable and PivotChart Wizard... This will get us to step 1 of the wizard. you will notice that we have three options under: "Where is the data you want to analyze?"
Microsoft Excel list or database Creates a PivotTable or PivotChart report from data organized in labeled rows and columns on a Microsoft Excel worksheet. This is the one we want!
External data source Creates a PivotTable or PivotChart report from data stored in a file or database outside the current workbook or Microsoft Excel.
Multiple consolidated ranges Creates one PivotTable or PivotChart report from multiple Microsoft Excel worksheet ranges.
Another Pivot Table or Pivot Chart Creates a PivotTable or PivotChart report from another PivotTable report in the same workbook. This option will be disabled as we do not have another Pivot Table or Pivot Chart, yet!
Below this (Excel 2000 only) is "What kind of report do you want to create?"
Pivot Table Creates a PivotTable report.
Pivot Chart (with PivotTable) Creates a PivotChart report and an associated PivotTable report that shares the same data and reflects all changes to the PivotChart report. A PivotChart report must have an associated PivotTable report in the same workbook.
Step 2
The two options we will be using are: Microsoft Excel list or database and Pivot Table, so select these and click Next.
By default Excel should have detected our list Range: $A$1:$A$10. This is because we initially selected a cell within our list and all our data is in a contiguous range. You will notice that there is a Browse button, but as our data resides in the same Workbook as our Pivot Table, we will not be using it. In other words, we could if we wished have our underlying data based in another Workbook. Click Next
Step 3
If you are using Excel 2000 or higher, this could be your final step. However, if you are using Excel 97, this will be step 3 of 4, with Step 4 in Excel 97 being almost the same as Step 3 in Excel 2000.
At this point I would like to go off on a bit of a tangent and explain why there is a difference between Excel 97 and Excel 2000. In versions prior to Excel 2000, the layout of our Pivot Table was determined in Step 3 of 4. This, however, was considered to be less user-friendly as the user was unable to see the direct effect caused by changing the table layout. In Excel 2000 we are given the choice of doing this as part of the Wizard, or doing it after we have finished using the Wizard. For the sake of consistency, we will do this as part of the Wizard so it matters not which version of Excel you are using. Although this may all sound confusing, all it simply means is Excel 2000 introduced an additional means of laying out our data.
So if you are using Excel 2000 or higher, at Step 3 I would like you to click the Layout... button. You will notice here that Excel instructs us to construct our Pivot Table by dragging the field buttons on the right to the diagram on the left. As we are only using a single column list, we only have one field button, called Names. If we had more than one column, each column would have it's own field button. While dragging field buttons to the column or row or page is optional, it is always mandatory that at least one field button be placed in the data area of your Pivot Table. Sometimes the terms COLUMN and ROW can be confusing, but all it really means is that by dragging a field button 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. This becomes particularly important if your list or table consists of more than 256 rows of data. This is because we would then be unable to use our field buttons in the COLUMN option, or if we did Excel would tell us it could not display all of the data and ask us if we would want to display as much as possible or not. This basically means you will find most of the time you will be dragging field items to the ROW area.
We will look at what happens when you drag field buttons to the PAGE area soon, but for now just drag our Names field button to the row area. Then drag the same Names field button on the right to the DATA area. 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, double click on the Count of Names button and Excel gives you the choice of how you would like to summarise your data. To the right of this, other than the OK and Cancel button you will notice there are three other buttons. Click the Hide button and this will remove our Names field from the DATA area. As the Names field is the only field that we have, we would not be able to do this, because remember we must always have at least one field button in our DATA area. If you click the Numbers option, Excel will display the Format Cells dialog but with only the Number tab available. This is where we can change the formatting of our displayed data. Again, for such a simple Pivot Table, there is no need for us to change anything here.
If you click the Options>> button, Excel displays a 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.
For now click the Options>> button again to hide this option. In the top of our PivotTable Field box, we also have the option to type a meaningful heading. In this case, Count of Names is fine. So just click OK. Then click OK again and if you are using Excel 2000 click New Worksheet and then Finish. If you are using Excel 97, click Next then New Worksheet and then Finish. You should now have a very simply Pivot Table on a new Worksheet displaying a count of all our names in the list.
This Pivot Table would be considered a Pivot Table in its simplest form.
Working with the Pivot Table
All information from now on is geared around Excel 2000. If you wish information to be geared specifically around Excel 97 or another version, please let me know.
You should now also have a floating Pivot Table toolbar showing with a grey area below it displaying a single field button called Names. If you select any cell outside of your Pivot Table, your Pivot Table toolbar should contract and show only the top section. If you accidentally hide your toolbar, you can display it again at any time, by right clicking on any toolbar and select Pivot Table from the drop down list. Basically, whether you have this toolbar showing all the time or not is purely optional. The reason why I say it is optional is because you can also select all of the same options by right clicking anywhere within your Pivot Table.
While you are clicked within your Pivot Table you should see at the top of your sheet in light grey font "Drop Page Fields Here". Lets do just that now and drag and drop our Names field from the ROW field to the PAGE field area. 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. If you select the arrow to the right of the Names field, you will see a list of all our names. When you select any name and then click OK. Excel will display a count of only the name displayed in the page field. At the very top of this list is the option (All) which simply means return a total for all names. Left click on the Names field button and drag it over to your Pivot Table and 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. Hover the mouse button around the Pivot Table to see the diagram change. The diagram is virtually the same as the one we had in Step 3, when we clicked the Layout>> option. What we are going to do is simply drag and drop our Names field to the COLUMNS area of our Pivot Table. So drag it and place it directly to the right of the Count of Names heading over the top of Total, then release. All we have done here is pivoted our table to display our data in columns instead of rows. Now drag our Names field back to the ROW section where we initially started. The other option we have with any row or column 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 Pivot Table and change our Grand Total accordingly. Select the arrow again and check the name Dave so it is again displaying.
Let's go back to our original data 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. Now go back to your Pivot Table and as you will see the change has not yet been reflected. This is because we need to Refresh our Pivot Table. There are two options here that we have for Refreshing a Pivot Table, 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 Pivot Table each time the Workbook containing the Pivot Table is opened. There are two ways to use the Refresh button. On the floating Pivot Table toolbar, the Refresh button is the bolded exclamation mark. Or if you prefer, you can simply right click in your Pivot Table and select Refresh Data from the pop up menu. Do this now. You will notice that Excel pops up an information message telling us the Pivot Table report has changed. Simply select OK. You should now have only one occurrence of the name Dave. In addition to this you should have down the very bottom of your Pivot Table 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.
To finish off this lesson, I would like to show you some of the option settings for our Pivot Table. Right click anywhere within your Pivot Table and select Table Options from the pop-up menu. Let's quickly run through these different options.
FORMAT OPTIONS
Name
Here you would simply type a meaningful name for your Pivot Table.
Grand Totals for Columns
Will display or not display Grand Totals for any column fields.
Grand Totals for Rows
Will display or not display the Grand Totals for any row fields.
AutoFormat Table
Leaving this option selected allows us to apply any one of Excels AutoFormats to our Pivot Table. This can be accessed via the Format Report option on the Pivot Table Toolbar. If you already have an AutoFormat applied to your Pivot Table, deselecting this option will remove the formatting. If you have this option deselected and then apply an AutoFormat to your Pivot Table, this option will automatically checked.
Sub Total Hidden Page Items
Selecting this option would mean hiding any items in a PAGE field would not affect any sub totals in the Pivot Table. Leaving it deselected means hiding PAGE field items will affect any sub totals.
Merge Labels
Selecting this option will merge cells for all outer row and column labels. Only really applicable to Pivot Tables with more than one row or column field.
Preserve Formatting
Having this option checked means any formatting we apply to our Pivot Table will be retained each time our Pivot Table is refreshed. If you deselect this option and applied formatting (other than AutoFormats) each time you refreshed your Pivot Table your formatting would be lost.
Repeat Item Labels on Each Printed Page
Select this option to print outer row field item labels at the top of each printed page. Item labels are repeated for all row fields to the left of the field for which a page break separates a group of items. Clear this check box to print the item labels on only the first printed pages where they appear.
Mark Totals with *
Displays an asterisk after every sub total and grand total in Pivot Table Reports that are based on OLAP sourced data to indicate that these values include any hidden items as well as displayed items. (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.)
Page Layout
Allows you to select the order in which you want Page Fields to appear.
Fields per Column
Allows you to select the number of PAGE fields you want included in a row or column before starting another row or column of PAGE fields.
For Error Values, Show
If your Pivot Table was reading from data containing formulas, some may be displaying error values, in which case you could nominate what to display in your Pivot Table for any error values.
For Empty Cells, Show
Allows you to nominate text or a value to show in place of any blank cells.
Set Print Titles
Select this option to use the field and item labels in the PivotTable report as row and column print titles. If you change the report layout so that the field and item labels are in different rows and columns, the new label rows and columns are automatically used as the print titles. Before you select this check box, click Page Setup on the File menu, click the Sheet tab, and clear the Rows to repeat to top and Columns to repeat at left check boxes, and make sure the current report is the only one in the print area.
DATA OPTIONS
Save
Data with Table Layout
Select this option
to save a copy of the internal data for the report in the workbook file so that
you do not have to refresh the report before you can work with it when you open
the workbook file. If you do not select this option, your workbook file
will be much smaller. However, you must either select the Refresh on open check
box or click Refresh Data on the PivotTable toolbar when you open the workbook
before you can work with the report. If you do not save this data, it will also
be discarded from other reports in the workbook that are based on this
PivotTable report or the PivotTable report associated with the current
PivotChart report. This option is ignored in report template
files.
Enable Drill
Down
Clearing this check box will prevent a user from seeing detail when they double click a cell in the DATA area of a Pivot Table report.
Refresh on Open
Updates your Pivot Table report each time you open the Workbook containing the Pivot Table.
Refresh Every ............
Allows you to automatically refresh your Pivot Table according to the number of minutes set. This is only available if your Pivot Table report is based on an external data source.
Save Password
If you are using an external data source that requires a password select the Save Password checkbox to store the password as part of the report. This means that whenever you update your report you do not need to re-enter the password.
Background Query
If your Pivot Table source is based on an external data source, and you check this box, Excel will run a query in the background while you to continue to work. Note, this option is not available for data from OLAP databases.
Optimize Memory
Select to save memory when you refresh your report from an external data source. This option is not available for reports based on source data from Microsoft Excel lists or from OLAP databases.
This is as far as I would like to go in this lesson on Pivot Tables. As you have no doubt seen, or soon will, Pivot Tables are a very powerful feature of Microsoft Excel. Before looking at the next lesson on Pivot Tables, try to actually create some of your own dummy Pivot Tables based on some data that makes sense to you. I have found that one of the most effective ways of training in Pivot Tables is to actually to use trial and error. In other words, jump in head first and try out all the different options and settings for Pivot Tables to see how it affects the Pivot Table. Just remember at the end of the day, no matter what you do to the Pivot Table it will not affect your underlying data. If worse comes to worse, you can always start again, or use the Undo feature.