Basic PivotTable Design (Excel 2010 for Windows)

PivotTables allow you to quickly and easily rearrange information from a set of data and to view it in several different ways.  This set of instructions is intended to show how to create a basic PivotTable and PivotTable Report.  A Report is the specific combination of information you arrange using the PivotTable.  Two separate PivotTables Reports will be created from an example data set.

Each step in the instructions are outlined below.  The data set used and the 2 different PivotTable Reports created are also discussed in their respective steps:

Step 1: Create PivotTable - This step utilizes the example data set that is shown in Step 1.  The data set shows sales from a company that owns 2 music stores.  The data shows sales profits from 5 types of instruments, the date of the week sold, the weekday sold, and the store the instrument was purchased at (either the North or South store).

Step 2: Build PivotTable Report - In this step we build PivotTable Report 1 - Sales of Products by Weekday.  This report shows the sales for each individual product (type of instrument) for each day of the week.

Step 3: Rearrange PivotTable to Create New Report - In this step we rearrange the first PivotTable report to create PivotTable Report 2 - Sales each Date by Store.  This report shows the sales on specific dates (such as April 20) by store (either North or South).

Step 1: Create PivotTable

This step shows how to create a PivotTable from a data set:

NOTE: Each step has an accompanying picture.  Click on each picture to view.  Text boxes in each picture specify which step the picture corresponds to.

1. Select any cell in worksheet range
2. Click on the Insert Tab
3. Click on "Pivot Table" button at far left hand side of the Insert Toolbar
4. "Create PivotTable" Box will appear. Ensure that relevant data is enclosed by moving dashed lines.
5. Click "Okay" at bottom right of "PivotTable" Box.

Step 2: Build Pivot Table Report

This step shows how to create a PivotTable Report directly after a PivotTable has been initially created:

The PivotTable Report built in this step shows sales for each individual product for each individual day of the week.  It includes a Report Filter so that the user will be able to view sales at each store location individually if they wish.

NOTE: Each step has an accompanying picture.  Click on each picture to view.  Text boxes in each picture specify which step the picture corresponds to.

NOTE: Remember to read text boxes located in each of the explanatory pictures for more important information.

1. Locate PivotTable Field List at far right side of Excel worksheet.
2. Drag "Weekday" field text from the "Choose fields to add to report" box to "Column Labels" box.  Both are located in the PivotTable Field List.
3. Drag "Product" field text from the "Choose fields to add to report" box to "Row Labels" box.
4. Drag "Amount" field text from the "Choose fields to add to report" box to "Values" box.
5. Drag "Store" field text from "Choose fields to add to report" box to "Report Filter" box.

Step 3: Rearrange PivotTable to Create New Report

This step shows how to rearrange data from an existing PivotTable Report in order to create a new report:

The PivotTable Report built in this step shows sales on each date of the week of April 20 for the North and South stores individually.  It includes a report filter so that the user may view the sales for each individual product type.

NOTE: The image below has the Report Filter set to Guitar.  This shows only guitar sales on each date for each of the stores.

NOTE: Remember to view text boxes on the accompanying image for important information.

Simply drag field text to new boxes shown in the accompanying image in order to create the report described above.