About this lesson
Learn to control formatting and other options in PivotTables.
Multiple versions of this lesson are available, choose the appropriate version for you:
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Formatting PivotTables.xlsx
51.1 KB Formatting PivotTables - Completed.xlsx
Formatting Pivot Tables
Controlling formatting and other options on Pivot Tables.
When to use
Having your data in tabular format is all very well, but we need to format it to display it in the most readable and approachable way.
- Create a Pivot Table with at least one field in each of the ROWS, COLUMNS and VALUES area
Formatting numeric columns
- Select any cell in the worksheet that represents a value from the VALUES area
- Right click, choose “Value Field Settings”, and click the Number Format box at the bottom
- Choose Accounting, with no symbol and no decimals, then click OK until you return to the Pivot
- Select the header for the VALUES field in the worksheet and change the name to match the name of the original column
- After you receive the error, try again but add a space at the end. (Notice it renames the item in the values area of the Pivot Table fields.)
Changing report layouts
- Click in the Pivot Table, go Pivot Table Tools --> Design --> Report Layout --> Show in tabular form
- Go Pivot Table Tools --> Design --> Report Layout --> Show in outline form
- Go to Pivot Table Tools --> Design --> Subtotals --> Show all Subtotals at Bottom of Group
Controlling column widths
- Go back to the source data and update one of the values to a very large number
- Return to the Pivot Table, right click it, choose Refresh, and notice the columns expand to fit
- Drag the columns slightly wider, right click the Pivot Table and refresh
- Notice it resizes again!
- Right click the Pivot Table, choose Pivot Table Options, uncheck the box next to “Autofit column widths on update”
- Resize the columns again, right click the Pivot Table and refresh. It stays the same size
- Return to the source data, set the cell you changed back to its original value
- Return to the Pivot Table, right click it and refresh, and the column width doesn’t change.
Lesson notes are only available for subscribers.