Show All

Hide All

A Microsoft Office Excel 2003 worksheet is the perfect tool for organizing and analyzing financial data. You can probably create a worksheet with, say, travel expenses in your sleep. If you need subtotals by quarter, you know how to modify your worksheet accordingly.

But what happens when you need to see the expense category totals by quarter? And what if you want to look at only one class or year at a time? Can you do that in your Excel worksheet? Sure. Is there an easier way? Absolutely.

An easier way

Microsoft PivotTable® reports are the answer. Instead of writing complicated formulas, you can use a wizard to create an interactive table that automatically extracts, organizes, and summarizes your data. You can then use the report to analyze the data and make comparisons, detect patterns and relationships, and analyze trends.

For example, you can use a worksheet that includes travel expense transactions...

Excel worksheet source

...to create a PivotTable report that summarizes total employee travel expenses by category and quarter.

Expenses by Category, Year, and Quarter

Now without changing your original worksheet, you can create another PivotTable report to see your data in other ways; for example, you can see travel expenses by employee or by month, and you won’t have to enter a single formula.

Here’s how to quickly create a PivotTable report.

Create your data

First, create your data in an Excel worksheet. You can import data from an existing spreadsheet or database into a new Excel worksheet. To make sure your data is ready for a PivotTable report:

1. Delete any empty rows or columns.
2. Remove any automatic subtotals.
3. Make sure the first row contains descriptive headers for each column.
4. Make sure each column contains only one kind of data — for example, text in one column and numeric values in a separate column.

Start the PivotTable and PivotChart Wizard

1. In the Excel worksheet, select a cell within the table from which you want to create a PivotTable report.
2. On the Data menu, click PivotTable and PivotChart Report to start the PivotTable and PivotChart® Wizard.

   What if I can’t select PivotTable and PivotChart Report?

   The PivotTable and PivotChart Report command is not available if the workbook is shared (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.).

   

3. In PivotTable and PivotChart Wizard – Step 1 of 3, under Where is the data that you want to analyze?, click Microsoft Office Excel list or database.
4. Under What kind of report do you want to create?, click PivotTable.
5. Click Finish.

Excel inserts a new worksheet into your workbook that contains the layout area for the report, the PivotTable toolbar, and the PivotTable Field List.

Start building your PivotTable report

• Drag the Category field from the PivotTable Field List to the Drop Row Fields Here area of the PivotTable report.

Don't worry about putting fields in the wrong place — you can easily move them around later.

Note that the wizard automatically populates the rows with the data from the Category column in your worksheet. In addition, fields in the PivotTable Field List that have been used are shown in bold.

PivotTable with Categories in rows

What if I can't see the PivotTable Field List?

Add data to your report

• Drag the field containing the data you want to summarize, such as the Amount field, from the PivotTable Field List to the Drop Data Items Here area of the PivotTable report.

The PivotTable report now shows the expense totals for each category.

PivotTable with categories and expense totals

Add a page sort field to your report

To filter your data by Class, you can create a drop-down arrow at the top of the page.

1. Drag the Class field from the PivotTable Field List to the Drop Page Fields Here area.
2. Click the Class drop-down arrow and select a class.

You can now see the expense categories for one class at a time.

Results filtered by Class

To see the data from all Classes, click the Class drop-down arrow and then click(All).

What else can I do with this report?

Rank expense categories by amount

You can view your largest expense categories first with the AutoSort function.

1. Click the Category field, click PivotTable on the PivotTable toolbar, and then click Sort and Top 10.
2. Under AutoSort options, click Descending.
3. In the Using field box, click Sum of Amount, and then click OK.

Expense categories ranked by Amount

See expense categories by quarter

To see how much was spent in each category by quarter:

1. Drag the Date field into the Row area.
2. Right-click the Date field, point to Group and Show Detail, and then click Group.
3. In the By box, click Quarters. If your source worksheet contains data from more than one year, also click Year.
4. Clear the Month selection if necessary, and then click OK.

Expenses by Year, Quarter, and Category

See how each expense category varies by quarter

You can change the rows and columns in your PivotTable report in seconds.

• Drag the Category field to the left of the Years field.

Expenses by Category, Year, and Quarter

Filter the results by employee

To filter your data by employee, you can create a second drop-down arrow at the top of the page.

1. Drag the Employee field into the Page Field area, above or below the Class field.
2. Click the Employee drop-down arrow, select an employee, and then click OK.

Results filtered by Employee

Show the results for each employee on a different worksheet

Instead of viewing the results of just one employee, you can display the results for each employee on a different worksheet.

1. On the PivotTable toolbar, click PivotTable, and then click Show Pages.
2. In the Show Pages dialog box, click Employee, and then click OK.

The various pages will appear as worksheet tabs.

Employee results on separate worksheets

Refresh the PivotTable report when the source data changes

Make the PivotTable report look great — instantly

What else can PivotTable reports do for me?

Basic PivotTable reports are fast and easy to set up. If you're willing to invest a little more time, however, you can learn even more ways to handle and analyze your data with PivotTable reports. For example, you can import or connect to data from many different types of sources, custom format your report, and present your results in meaningful, easy-to-understand ways.