Excel Pivot Tables - Complex Reports Made Easy

By Debra Dalgleish

With a pivot table in Microsoft Excel you can summarize thousands of rows and columns of data into a concise summary.

For example, you might have an Excel worksheet with a long list of food orders. Each row shows what product was sold, the region and city where it was sold, the sale date, order quantity, discounts given and other details.

With some hard work, you could manually create a report from this data, and enter complex formulas to calculate the number of orders per region and category. If you create a pivot table instead, you'll have a completed report with just a few clicks of the mouse.

Quickly Creating Reports

With a pivot table, you simply drag the data into one of the four areas -- Row Labels, Column Labels, Values and Report Filter. The row and column labels become the report headings, such as Region and Category. Sales quantity data could go into the Values area, and the total quantities will be automatically calculated. A filter can be added, to view only the orders with a discount, instead of all the orders.

After you have created a pivot table, with a few more mouse clicks, you could quickly pivot the data, to see a different summary. For example, remove the regions from the pivot table, and put in the cities. To focus on cookie sales, move the product category to the report filter area, and select cookies. In a minute or two, you'll have a completely different report from the same data.

Once the pivot table is arranged the way you want it, a few more clicks will create a stunning pivot chart with a graphical snapshot of the sales summary.

Preparing Your Data

Before you can create a pivot table, you need to organize your data in a way that Excel can use. The data can be in an Excel workbook, or in an external database, or in other sources, even in a text file. Follow these guidelines to ensure that you get the best results from your pivot tables.

*Organize the data in rows and columns with each row containing information about one record, such as a sales order.

*The first row must contain column headings, with a short, descriptive, unique heading for each column.

*Each column should contain only one type of data, such as dates, numbers or text.

*Separate data, such as addresses, into multiple columns, such as Street, City and State, instead of all in one column.

*Do not include any completely blank columns within the data.

*Separate the data from other items in the Excel worksheet, and if possible, store it on a separate sheet.

Create Your Own Pivot Tables

Spend an hour or two learning the basics of pivot tables, and you can impress your boss or your clients with amazing reports, with a minimum of effort! To help you get started, there are instructions in Excel's Help and on the Microsoft website.

Debra Dalgleish is a computer consultant, author of three Excel Pivot Table books, and owner of Contextures website, which features Excel tips and tutorials.

 
photo