About this lesson
Learn to control formatting and other options in PivotTables.
Download this lesson’s related exercise files.Formatting PivotTables - Begin.xlsx
51 KB Formatting PivotTables - Complete.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.