Checklist Before Creating a Pivot Table

Make sure your data is in a Data List format

• Each column has a header (no blanks)

• No empty columns in your data table

• No summations embedded in your data table

BEST method is to transform data to an Excel Table

• Ensures Pivot Table is updated when new data is added

• Ensures data is properly organized and each column has a header

Tips for Pivot Tables

Use Tabular Design For Pivot Table Layout

To get the header for the category field on your Pivot Table report, use Tabular Design.

Go to Design Tab, Report Layout and select Show in Tabular Form.

Rename Pivot Headers

Instead of Sum of Quantity in the Pivot field header, change this to Quantity and add a space character either before or after.

Use Number Format Instead of Format Cells

To change the number format for the values in a Pivot Table, use Number Format instead of Format Cells.

This ensures the field keeps the formatting as the Pivot Table expands.

Double-click on Values to Get the Full List

To get a list of each line of data making up the value shown on the Pivot Table, you can double-click the cell value. This creates a new sheet with all the rows of data that make up the aggregated value.

Remove the Check-mark for Autofit

One Pivot Table default setting that can get annoying is to automatically autofit the Pivot Table columns.

In case you want this turned off, go to Pivot Table options: PivotTable Analyze > Options and un-check Autofit column widths on update.

Remember: Your downloadable course notes cover the most important points. Make sure you keep them handy.

Here's how far you've come

Keep Going!