Now that you have a good understanding of the importance of a well-structured data source, let's look at the one you'll be using to build your first pivot table report. Figure 2.6 shows your data source.
Figure 2.6. The data source.
This data source will be used throughout the book and is available for download at http://www.MrExcel.com/pivotbookdata.html.
Introduction to the PivotTable Wizard
To get started, click on any single cell in your data source. This will ensure that the pivot table captures the range of your data source by default. Then go up to the application toolbar and select PivotTable and PivotChart Report from the Data menu.
This will activate the PivotTable Wizard, as shown in Figure 2.7.
Figure 2.7. Step 1 of the PivotTable Wizard allows you to choose from a variety of data sources.
The first step of the PivotTable Wizard allows you to select the location of your data source. Other types of data sources will be discussed later in the book. For now, use the default "Microsoft Excel list or database." Choose Next.
Figure 2.8 shows step 2 of the wizard. If your data is properly structured without any blank rows or columns, Excel will properly sense the correct range for your data.
Figure 2.8. Provided you selected a single cell in your data source, the range se nsed in step 2 is usually correct.
Step 2 of 3 allows you to specify the range of data. Note that the PivotTable Wizard is smart enough to read your data source and accurately auto-fills the range. However, you always want to take note of this to ensure you are capturing all your data. Choose Next.
In step 3 of the PivotTable Wizard, shown in Figure 2.9, you can select whether Excel should create the pivot table on a new worksheet or in an out-of-the-way location on the existing worksheet.
Figure 2.9. Choose a location for the pivot table in step 3. Experienced pivot tablers often also adjust the settings in the Layout and Options buttons during this step.
Step 3 of 3 allows you to choose where you want the pivot table to be placed. Keep the default "New worksheet" selection. Choose Finish.
After you choose Finish, you will see a layout that looks like Figure 2.10 or Figure 2.11, depending on which version of Office you have. You will also see your field list enumerating the fields that exist in your data source.
Figure 2.10. A blank pivot table as presented in Office 2002 and later. The pivot table field list is presented in a separate window from the PivotTable toolbar.
Figure 2.11. In Office 2000 and earlier, the list of available fields is incorporated in the PivotTable toolbar.
Drag Fields to the Report
The idea is to drag the fields you need into the pivot table drop areas. Let's pause a moment here and go over some fundamentals of laying out your pivot table report. This is generally the point where most new users get stuck. How do you know which field goes where?
Before you start dropping fields into the pivot table drop area, ask yourself two questions: What am I measuring, and how do I want to see it? The answer to the first question will tell you which fields in your data source you will need to work with, and the answer to the second question will tell you where to place the fields.
In the first example, you want to measure the amount of revenue by region. So you automatically know that you will need to work with the Revenue field and the Region field. If you take a quick glance at Figure 2.10, you will be able to spot those two fields easily in your field list.
How do you want to see it? You want regions to go down the left side of the report and revenues to be calculated next to each region.
To get this effect, choose the Region field from your field list and drag it to the row area (the area that reads "Drop Row Fields Here").
Then choose the Revenue field from your field list and drag it to the data area (the area that reads "Drop Data Fields Here").
As shown in Figure 2.13, you will have created your first pivot table report!
Figure 2.13. This report quickly summarized 41,549 rows of data to produce a powerful summary report.
A pivot table report must have at least one data item to work.
Adding Fields to the Pivot Table
Now you can add another layer of analysis to your report. What do you want to measure? You want to measure the amount of revenue each region earned by line of business. Since you already have the Region and Revenue fields, choose the Line of Business field from your field list and drag it on top of "Region" in the pivot table report, as illustrated in Figure 2.14.
Figure 2.14. Changes to an existing pivot table report are easysimply drag a field to a new location.
Your pivot table report has automatically added a layer for Line of Business and refreshed the calculations to include subtotals for each region. Because the data is stored efficiently in the pivot cache, this calculation took less than a second and is shown in Figure 2.15.
Figure 2.15. Before pivot tables, making a change such as this would have required an hour to set up complex array formulas.
Rearranging the Pivot Table
Say that this view doesn't work for your manager. He wants to see the lines of business going across the top of the pivot table report. Simply drag the Line of Business field on top of "Total" in C3. Instantly, the report will reformat as shown in Figure 2.16.
Figure 2.16. Rearranging a pivot table is as simple as dragging the gray field headings to a new location.
If you want to produce a report for one particular regional manager, you can move the Region field to the Page Field area. Drag the Region field above "Sum of Revenue" to drop it in the Page Field area. From the Region drop-down, select one region to see totals for just that region. Figure 2.17 shows the totals for just the North region.
Figure 2.17. With this setup, not only can you see revenues by line of business clearly, but you can click on the Region drop-down to focus in on one region.
Lest you lose sight of the analytical power you just displayed, keep in mind that your data source has almost 42,000 rows and nine columns, which is a hefty set of data by Excel standards. Despite the amount of data, you produced a Region Revenue report, a Region and Line of Business Revenue report, and a Line of Business Revenue report with region filters in a matter of minutes. This would have taken hours by hand.
CASE STUDY: Revenue by Market and Model
Your organization has 21 markets that sell products and services revolving around nine models of copiers and printers. You have been asked to build a report that will break out each market and show how much revenue was earned by model. This report must be compact and easy to read, as it will be placed into the end-of-month board presentation. Here are the steps to follow:
In five easy steps, you have calculated and designed a report that satisfies the requirements given to you. Select a cell outside of the pivot table in order to hide the pivot table field list, as shown in Figure 2.22.
Figure 2.22. This summary can be created in less than a minute.
You can go the extra mile and add one more dimension to your pivot table report in order to allow for analysis by region.
Choose any cell in your pivot table report to redisplay the pivot table field list. Select the Region field from your field list, as shown in Figure 2.23.
Figure 2.23. The field list will reappear whenever you select a cell in the pivot table.
With the field selected as shown in Figure 2.23, you could use the drop-down next to the Add To button to select Page Area. Or, you could simply drag the Region field into the page area, as shown in Figure 2.24. By default, Excel will show the data for all regions.
Figure 2.24. When a field is first dropped in the page field area, Excel chooses the option to display the total for all regions.
With the Region field placed into the page area, you can now create separate reports by region if needed. Select the drop-down in cell B2 to see a list of available regions, as shown in Figure 2.25.
Figure 2.25. Select any region from the drop-down to filter the report to sales for just that region.
Watch the Mouse Pointer to Learn Where You Are Dropping a Field
In a hands-on class about pivot tables, the most difficult concept for students is to learn where to drop a field in order to get it to appear as a row field or a data field. Literally, just a few pixels in either direction can cause the dropped field to appear in the wrong place.
The technique is to watch the mouse cursor. When you are dragging a field in a pivot table, the mouse cursor changes to a picture of a pivot table. One section of the mouse cursor will turn blue. The section in blue indicates where the field would be added if you dropped it. Figure 2.26 shows the five cursor types.
Figure 2.26. Watch the blue portion of the mouse cursor to learn if you are dropping a field in the row, column, data, or page area of a pivot table.
Redisplay the Pivot Table Field List
If clicking on the pivot table does not activate the pivot table field list, select the Show Field List icon or the Display Fields icon on the pivot table toolbar.
Redisplay the Pivot Table Toolbar
If clicking on the pivot table does not activate the pivot table toolbar, right-click on the pivot table and select Show Pivot Table Toolbar.
If you use Excel 2000 and clicking on the pivot table does not activate the pivot table toolbar, go up to the application toolbar and select Toolbars from the View menu, then select Pivot Table.
Activate the PivotTable Wizard
Right-click anywhere inside the pivot table and select PivotTable Wizard.