How to Prepare an Excel PivotTable

Using Excel Data
1. Open the Excel file that has the information that you want to make into a PivotTable.
2. Edit the range of data that you will use to remove any blank rows. Also place a column name in the top row, for any column that doesn't have one.
3. Select any cell in the range of data that you want to use.
4. Click on the 'Insert' tab at the top of the screen, and then click the 'PivotTable' button that appears on the toolbar. A small window will appear, and Excel will automatically select the entire range of data. Press 'OK' to continue, and the PivotTable will be created on a new worksheet.
Using External Data
5. Open the Excel 2010 program by double-clicking the Excel icon.
6. Click the 'Insert' tab at the top of the screen and then click the 'PivotTable' button on the left side of the toolbar.
7. Select the 'Use an External Data Source' radio button and then press the 'Choose Connection' button just below the radio button. A list of available connections will show up.
8. Click the connection you want to use and then click 'Open.' If you do not see the connection that you need, click on 'Browse for more' and then navigate to the connection file and click 'Open.'
9. Select 'New Worksheet' to place the PivotTable on a new worksheet, or select 'Existing Worksheet' and then input the cell where you want the PivotTable to go. Click 'OK' when you are done.
Placing Information in the PivotTable
10. Select a cell anywhere in the PivotTable placeholder graphic to bring up the field list on the right side of the screen.
11. Place checkmarks on the field list to the right side of the window, next to the fields you want to add to the PivotTable. Each field that you see is equal to one column of data from your original data set. When you place a checkmark, you will see the field appear both on the PivotTable in the main window, and in one of four boxes to the right of the field list. Each box corresponds to a different area on the PivotTable.
12. Drag and drop fields between the boxes on the right of the screen to place them where you want them on the PivotTable. Fields in the 'Column Labels' box will appear at the top of the PivotTable, while fields in the 'Row Labels' box will appear along the left side. Any field in the 'Values' box will have its data make up the body of the PivotTable, and any field in the 'Report Filter' will appear in a small drop-down box above the PivotTable, where you can filter the entire table.