Excel Pivot Tables: How To Create Better Reports

Generating reports for single tables takes a few clicks. 

- JD Sartain

Excel created pivot tables to improve upon its convoluted, weak reporting features (which are still available). The pivot table is actually a collection of tools that Excel uses to help you create better reports from complex, multi-file spreadsheet data. You filter, sort, reorganize, calculate, and summarize your spreadsheet databases, then extract specific information into a report.

For example, your spreadsheet may contain 25 field columns, but you only need four of these fields for your report. The Pivot Table tools allow you to sift that data in, literally, seconds—a huge improvement over Excel’s previous reporting capabilities.

To make it easier for you to practice the tasks we’re about to describe, go to (go. pcworld.com/crtx) and follow the instructions to create a sample relational database.

SINGLE “FLAT-FILE” PIVOT TABLE REPORTS

Pivot Tables are the “defined as tables” spreadsheets that the Pivot Table tools use to create custom reports for both flat-file and multi-file (relational) databases. This powerful function, which includes some very user-friendly filtering features, is a vast topic that cannot be covered adequately in a single article. Therefore, this article addresses the single “flat file” Pivot Table reports. We’ll follow up with coverage of the multi-file “relational” Pivot Table reports.

1. Open the database file, then click the VIOLATIONS tab. Place your cursor on column C - Violation Fee. Click the down arrow (right side), choose Sort Smallest to Largest, and click OK.

2. Click the arrow again, select Number Filters > Greater Than.

3. In the Custom AutoFilter dialog window, the field name Violation Fee is displayed under the prompt that says Show Rows Where—Violation Fee: is