Building Effective Microsoft Excel Pivot Tables

Microsoft Excel pivot tables are a great way to reduce large amounts of data into an easily readable format. Using a drag-and-drop interface, you can combine columns from your spreadsheet into a "data view" that will automatically compile the data for you. Pivot tables are great for taking large amounts of data and creating an easy-to-read summary report.

Creating Microsoft Excel Pivot Tables
Although pivot tables seem complex, the process for creating them is straightforward: Select a data set and choose the data items you wish to display. The data items can be described as dimensions, and a successful pivot table needs at least two dimensions. For example, say you want to find out how many of your customers have the same ZIP Code. This requires two dimensions of information: customers and ZIP codes. To create a Microsoft Excel pivot table, follow these steps:

• Start with data. Excel pivot tables can get data from many different sources, including databases, spreadsheets or external data files. Spreadsheets are an easy way to enter and access small to moderate volumes of information.
• Select the pivot table. From the main menu, select Data, Pivot Table. This will call up the pivot table wizard.
• Select your data source. Select the location of the data you want to analyze. After you've picked your data source, click Next.
• Refine your data source. If you've selected a spreadsheet, you'll be asked to specify the range of cells you want to use for the pivot table. You can drag the selection box inside your spreadsheet or type in the range of cells. When you've selected the range of cells, click Finish to continue.
• Select your data dimensions. The Pivot Table Wizard will create a new spreadsheet with a number of drop target areas. The pivot table window will list the columns of the range of cells you selected. To find the number of customers per ZIP Code, drag the ZIP Code column and drop it on the area labeled "Drop Row Fields Here." Drag the customer name column and drop it on the area labeled "Drop Data Items Here."
• Watch the magic! Excel will automatically collate the data fields to create a new table where each ZIP Code gets its own row and the total number of customers for each ZIP Code will appear in a separate column. The new table will also display the total number of customers in the table.