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:
Controlling formatting and other options on PivotTables.
Where/when to use the technique
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 PivotTable 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 PivotTable fields.)
Changing report layouts
- Click in the PivotTable, go PivotTable Tools --> Design --> Report Layout --> Show in tabular form
- Go PivotTable Tools --> Design --> Report Layout --> Show in outline form
- Go to PivotTable 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 PivotTable, right click it, choose Refresh, and notice the columns expand to fit
- Drag the columns slightly wider, right click the PivotTable and refresh
- Notice it resizes again!
- Right click the PivotTable, choose PivotTable Options, uncheck the box next to “Autofit column widths on update”
- Resize the columns again, right click the PivotTable 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 PivotTable, right click it and refresh, and the column width doesn’t change.
Lesson notes are only available for subscribers.