Sign in or start a free trial to avail of this feature.
3. Formatting Pivot Tables
Pivot Table data initially appears without any formatting. Banded rows, comma separators and other techniques can be used to make your data much more readable.
Cell format changes
- Pre-formatted styles for your Pivot Table can be access in Design tab of ribbon
- Consider adding banded rows if you're reading from left-to-right
- Consider adding banded columns if you're reading top-to-bottom
Number format changes
- Numbers are not automatically formatted in Pivot Tables
- Use the Right-Mouse button key to access the Number format dialog box
- Otherwise, only the selected cell will have an updated format
ALT + J, Y, S: Change Pivot Table style
ALT + J, Y, R: Insert banded rows
RMBK, T: Open Format Cells dialog box
ALT + Underlined letter: Access related fields in dialog boxes
Formatting your Pivot table correctly can make your data much easier to read, helping you identify trends quickly, and reducing the likelihood of errors.
For Pivot tables, we can easily change both the cell format and the number format.
To change the cell format, we press Alt + J Y to go to the Design tab in the Pivot table and then press S to expand this menu.
Here we have a set of pre-formatted styles, one of which was automatically chosen for our Pivot table.
Using the arrow keys, we can see what different designs would look like for our Pivot table.
Once you’re happy with the chosen design, you can simply press Enter.
If you’re not happy with these pre-formatted styles, it’s also possible to create your own pivot style by accessing this option at the bottom of the menu.
I’ll press Enter on this particular style.
As well as changing the Pivot table style, it might also be worth adding banded rows or banded columns to our Pivot table, depending on how you read the data from the table.
I tend to read this Pivot table from left to right so banded rows might help the readability here.
So again I’ll press Alt + J Y to go into the design tab and R for banded rows.
And now it’s much easier for me to read across the revenue numbers for each sales person.
After adding your Pivot table style, you still have complete freedom to implement cell format changes on top of this style.
For example, if I’d like to bold the names of all my sales people, I could just select them and then press Ctrl + B to bold.
I could also change the fill colour of these cells if I wished.
Now that we’ve made it easier to read across our rows, let’s change the number format to make the values themselves more readable.
Without comma separators, our eyes can find it quite difficult to distinguish between two very different numbers.
So let’s add some comma separators to all our data points.
To do this, we’ll select a cell on the Pivot table, then press the right mouse button key and T for number format.
This brings up the Format Cells dialogue box.
If you don’t access the Format Cells dialogue box in this way, only the selected number will be updated and not the full Pivot table.
To access the category drop down I’ll press Alt and the underlined letter, which is C.
And now I’ll move my arrow down to Select Number.
I’ll press Alt + D to change the decimal places, and then Alt + U to include comma separators.
This is the format that I want, so I’ll press Enter to make the changes.
And now the numbers in our Pivot table are much easier to compare.
Contrasting our current format with our initial Pivot table just shows you the impact that a few simple formatting changes can have on making your data much more readable.