Step 2 :: Create an Excel Pivot Table Report |
Step 2 :: Create an Excel Pivot Table Report |
Create an Excel Pivot Table report using the Excel Pivot Table and PivotChart Wizard
Once you have organised your raw data it is time to create a pivot table report using the Excel Pivot Table and PivotChart Wizard.
The wizard helps you specify the data you want to use and creates the report framework.
This section shows you how to create a basic Pivot Table report. Step 3 will show you how to change
We will use an example scenario to take you through creating a Pivot Table using the source data below.
Source Data:
Once we have organized our data correctly we can create an Excel Pivot Table report using the built-in Excel Pivot Table wizard.
 |
We want to summarize the raw data to show the sum of Sales (Green) categorised by Month (Peach) and Region (Yellow) for the Year 2003 (Turquoise)
The Pivot Table will look like the example below. Note, the colours are for illustrative purposes only, these help identify the relationship between the source data columns and the pivot table areas.
 |
To create an Excel Pivot Table report, follow these steps:
Start the Excel Pivot Table and PivotChart Wizard
1. Open the workbook where you want to create the Excel Pivot Table report. If you're basing the report on an Excel list or database, click a cell in the list or database
2. On the main menu select Data > Pivot Table and Pivot Chart Report…
 |
Step 1 of 3 of the Excel Pivot Table and PivotChart Wizard box will appear...
 |
3. Choose the type of data you will use, (In this example we will use the ‘Microsoft Office Excel list or Database’ option ) and then select ‘Excel Pivot Table’ under ‘What kind of report do you want to create?’ and then select the ‘Next >’ button.
4 . You should now be in In Step 2 of 3 of the wizard.
 |
If the wizard has not done this already then select the area in the worksheet you want to include. You will know if the wizard has selected your data as the cells selected will appear in the ‘Range:’ box.
If the wizard has not selected the correct Range, select the 
button from the Step 2 of 3 dialog box and select the correct range.
Once you have selected the correct range select the 
button from the Step 2 of 3 dialog box.
Click the ‘Next >’ button to take you to Step 3 of 3.
Note. If you're retrieving external data for the Excel Pivot Table report, you can get more information about using Microsoft Query or the Query Wizard from either Excel Help or Query Help.
5. In step 3 of 3 of the wizard select the ‘New Worksheet’ button and select the ‘Finish’ button..
 |
Note. If you want your new pivot table in an existing worksheet, then select the ‘Existing Worksheet’ option instead.
The wizard will open a new worksheet and place the new Pivot Table framework in it.
 |
Define the layout of the Excel Pivot Table report
1. From the PivotTable Field List window, drag field buttons to the labelled areas on the Excel Pivot Table diagram.
Drag the following Pivot Table Field list labels to the labelled areas on the Pivot Table framework diagram
- Year to ‘Drop Page Fields Here’
- Region to ‘Drop Row Fields Here’
- Month to ‘Drop Columns Fields Here’
- Sales to ‘Drop Data Items Here’
Your Pivot Table should look something similar to the illustration below.
 |
The last thing to do is to choose 2003 from the drop down list in the Year field in our Pivot Table.
 |
Note. If your source data is from a large external database, it may be too time-consuming to define the layout manually. In this case, you can use the wizard to define the layout. Click Layout in step 3 of the Excel Pivot Table and PivotChart Wizard to lay out the Excel Pivot Table report.
Anatomy of an Excel Pivot Table Report
Below is taken from the Microsoft 101 Excel website.
You may have noticed that Microsoft Excel uses specific terms to identify the parts of an Excel Pivot Table report. If you're not familiar with these terms, read this section for a quick primer.
 |
- Page field A field from the source data that you assign to a page (or filter) orientation in an Excel Pivot Table report. For example, Year is a page field. You can use the Year field to display summarized data for only 2003, only 2004, and so on.
- Data field A field from the source data that contains values to be summarized. For example, Sum of Sales is a data field. For most types of source data you can choose how to summarize data (for example, by sum, average, or count). A data field usually summarizes numbers, but it can also summarize text. For example, you can count the number of times a specific text entry (such as Yes or No) appears in a field.
- Column field A field from the source data that you assign to a column orientation in an Excel Pivot Table report. For example, Type is a column field.
- Item A subcategory of a row, column, or page field. For example, the Type field contains the following items: Beverages, Dairy, and Meat. The Salesperson field contains these items: Buchanan, Davolio, Dodsworth, and Suyama.
- Row field A field from the source data that you assign to a row orientation in an Excel Pivot Table report. For example, Region and Salesperson are row fields.
- Data area The cells in an Excel Pivot Table report that contain summarized data. For example, the value in cell C5 summarizes Buchanan's beverage sales for the East region in 2003. In other words, it's a summary of the sales figures for every row in the source data that contains the items Buchanan, Beverage, East, and 2003.
| <<< Previous Section :: Step 1 Organise Your Raw Data | Next Section :: Step 3 Customise an Excel Pivot Table >>> |