Click the arrow beside the X, then click one of these three options. You can also modify the Task Pane options: Move, Size, or Close. Just click the arrow beside the gear box and choose one of the five different layouts. Tip: You can change the layout of the Pivot Table Fields panel if the current layout is unsatisfactory. Double-click the spreadsheet tab at the bottom and name your report, then save your file. JD Sartain / PC WorldĬhoose a colorful PivotTable Style to enhance your tableĢ0. Select the latter, and the New Pivot Table Style dialog window opens with dozens of Formatting features to help you design your own custom Styles. Notice the last two menu options on the Pivot Table Styles submenu: Clear and New PivotTable Style. All you had to do was click the checkboxes beside the fields you wanted in this report, and Excel did the rest. Excel creates a beautiful report sorted by the Drivers’ License Numbers, with the Violation Types sorted alphabetically by Type beneath each License Number, with the fees recorded beside each Violation Type, and then subtotaled by License Number with a Grand Total at the bottom. We’ll cover these features in a future installment.ĩ. Note the options provided for each field. Click the arrows to open the field boxes under Rows and Values. Note: Notice the options at the bottom of this panel: Filters, Columns, Rows, and Values. Note that Excel builds the report and summarizes the Number fields as you type. For this first report, click the check box for these four fields: License Number, Violation Type, Violation Fee, and Violation Date. Click the down arrow beside the second Input box, and select $200.00 from the dropdown list.Įxcel displays the Pivot Table Fields list with a message helper box that says: “To build a report, choose fields from the Pivot Table field list.”Ĩ. In the Custom AutoFilter dialog window, the field name Violation Fee is displayed under the prompt that says Show Rows Where- Violation Fee: is Greater Than (displays in the first Input box).

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

Click the down arrow (right side), choose Sort Smallest to Largest, and click OK.Ģ. Place your cursor on column C – Violation Fee. Open the database file, then click the VIOLATIONS tab. We’ll follow up with coverage of the multi-file “relational” Pivot Table reports.ġ. Therefore, this article addresses the single “flat file” Pivot Table reports. This powerful function, which includes some very user-friendly filtering features, is a vast topic that cannot be covered adequately in a single article. 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.

Excel collection report