How to Run the Excel PivotTable Wizard - dummies

By Stephen L. Nelson, E. C. Nelson

You create a pivot table — Excel calls a cross-tabulation a pivot table — by using the PivotTable command (seems easy to remember). To run the PivotTable command, take the following steps:

1Click the Insert tab’s PivotTable command button.

Excel displays the Create PivotTable dialog box.

2Select the radio button that indicates where the data you want to analyze is stored.

If the to-be-analyzed data is in an Excel table or worksheet range, for example, select the Table/Range radio button. And if you’re just starting out, you ought to use this approach because it’s the easiest.

If the data is in an external data source, select the Use an External Data Source radio button. Make sure that you’ve already grabbed any external data and placed that data into a worksheet list.

If the data is actually stored in a bunch of different worksheet ranges, simply separate each worksheet range with a comma. (This approach is more complicated, so you probably don’t want to use it until you’re comfortable working with pivot tables.)

If you have data that’s scattered around in a bunch of different locations in a worksheet or even in different workbooks, pivot tables are a great way to consolidate that data.

3Tell Excel where the to-be-analyzed data is stored.

If you’re grabbing data from a single Excel table, enter the list range into the Table/Range text box. You can do so in two ways.

You can type the range coordinates: For example, if the range is cell A1 to cell D225, type $A$1:$D$225.

Alternatively, you can click the button at the right end of the Table/Range text box. Excel collapses the Create PivotTable dialog box.

Now use the mouse or the navigation keys to select the worksheet range that holds the data that you want to pivot. After you select the worksheet range, click the button at the end of the Range text box again. Excel redisplays the Create PivotTable dialog box.

4After you identify the data that you want to analyze in a pivot table, click OK.

Excel displays the new workbook with the partially constructed pivot table in it.

5Select the Row field.

You need to decide first which field from the list that you want to summarize by using rows in the pivot table. After you decide this, you drag the field from the PivotTable Field List box to the Rows box (beneath the PivotTable Field List). For example, if you want to use rows that show product, you drag the Product field to the Rows box.

6Select the Column field.

Just like you did for the Row field, indicate what list information you want stored in the columns of your cross-tabulation. After you make this choice, drag the field item from the PivotTable Field List to the box marked Columns.

7Select the data item that you want.

After you choose the rows and columns for your cross-tabulation, you indicate what piece of data you want cross-tabulated in the pivot table. For example, to cross-tabulate sales revenue, drag the sales item from the PivotTable Field List to the Values box.

Note that the pivot table cross-tabulates information from the Excel table. Each row in the pivot table shows sales by product. Each column in the pivot table shows sales by state. You can use column E to see grand totals of product sales by product item. You can use row 11 to see grand totals of sales by state.

Another quick note about the data item that you cross-tabulate: If you select a numeric data item — such as sales revenue — Excel cross-tabulates by summing the data item values. If you select a textual data item, Excel cross-tabulates by counting the number of data items.

Although you can use pivot tables for more than what this simple example illustrates, this basic configuration is very valuable. With a table that reports the items you sell, to whom you sell, and the geographic locations where you sell, a cross-tabulation enables you to see exactly how much of each product you sell, exactly how much each customer buys, and exactly where you sell the most. Valuable information, indeed.