You can also create a Pivot Table in Excel using an outside data source, such as Access. You can insert the Pivot Table in a new Excel spreadsheet.
Your spreadsheet should include at least one column with duplicate values. This basically just means that at least one column should have repeating data. In the example discussed in the next section, the “Product Type” column has two entries: “Table” or “Chair”. It should include numerical information. This is what will be compared and totaled in the table. In the example in the next section, the “Sales” column has numerical data.
If you are using Excel 2003 or earlier, click the Data menu and select PivotTable and PivotChart Report. . .
If you are using an external source for your data, click the “Use an external data source” option and click Choose Connection. . . . Browse for the database connection saved on your computer.
When you are satisfied with your choices, click OK. Your Pivot Table will be placed and the interface will change.
For example, your company sells two products: tables and chairs. You have a spreadsheet with the number (Sales) of each product (Product Type) sold in your five stores (Store). You want to see how much of each product is sold in each store. Drag the Store field from the field list into the Row Fields section of the Pivot Table. Your list of stores will appear, each as its own row.
For all of the above steps, you can drag the fields into the corresponding boxes below the Fields list on the right side of the window instead of dragging them onto the table.
Drag the Model field onto the Column Fields section. The columns will now display the breakdown of sales per model and overall type. You can change the order that these labels are displayed by clicking the arrow button next to the field in the boxes in the lower-right corner of the window. Select “Move Up” or “Move Down” to change the order.
You can add the same field to the Value box multiple times to take advantage of this. In the above example, the sales total for each store is displayed. By adding the Sales field again, you can change the value settings to show the second Sales as percentage of total sales.
Sum - This is the default for value fields. Excel will total all of the values in the selected field. Count - This will count the number of cells that contain data in the selected field. Average - This will take the average of all of the values in the selected field.
This is where the pivot table gets its name. Moving the data to different locations is known as “pivoting” as you are changing the direction that the data is displayed.
