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 the following setup:
- Rows: Inventory Item
- Columns: Sold By
- Values: Commission
Formatting numeric columns
- Select any cell in column B (the Sum of Commission column)
- Right click, choose “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
- Drag Sold By into the ROWS area, above Inventory Item
- Select B3 and enter “Commission”.
- 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
- Go PivotTable Tools --> Design --> Report Layout --> Show in tabular form
Controlling column widths
- Go back to the source data and update F4 to read 1,000,000,000
- Return to the PivotTable, right click it, choose Refresh, and notice column C expands to fit
- Drag column C slightly wider, right click the PivotTable and refresh
- Notice it resizes again!
- Right click the PivotTable, choose PivotTable Options --> Layout --> “Autofit column widths on update”
- Resize Column C, right click the PivotTable and refresh. It stays the same size
- Return to the source data, set F4 back to 1.35
- Return to the PivotTable, right click it and refresh, and the column width doesn’t change.
Lesson notes are only available for subscribers.