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.
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.
Call the Calculated Field “Blank Line” and make the formula 1/0.
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.
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.
After a little more prettification, the table looks like this.
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.