Excel: Using PivotTables for Reporting

PivotTables seem like a great tool to use to build reports—except once you start building the report you get a huge stack of metrics like the one shown below.

PivotTables looking bad

This doesn’t help people pick out natural groups of metrics and doesn’t match the formatting people expect. We’re going to show a simple PivotTable hack today that lets you create blank lines between metrics. This allows a report using PivotTables to look a lot more professional.

To get started, find the PivotTable menu, and add a calculated field.

Adding a calculated field to a PivotTable

Call the Calculated Field "Blank Line" and make the formula 1/0.

Creating the Blank Line formula

Now drop the "Blank Line" formula in to the data area of your PivotTable. You can drop it in multiple times to create several blank lines.

PivotTables looking bad

Go to PivotTable Options (right click on the PivotTable and choose Table Options..."). Turn on the "For error values, show:" check box. This will display an empty cell instead of all the alarming #DIV/0! messages.

PivotTable settings to hide errors

After a little more prettification, the table looks like this.

Final PivotTable

A total of 5 blank lines were added. Three were given titles and bolded: "Revenue and Cost", "Warranty Details", and "Profit." Two are blank. To make a completely empty row, change the title from "Sum of Blank Line" to a single space key " ". Every title in the PivotTable must be unique, so the title of the other empty row is two spacebar keys.