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.
Formatting the Table (00:04)
Formatting a Pivot Table correctly makes it easier to read, and reduces the likelihood of errors. There are many different formatting options available. You can change the style of the table by pressing Alt, J, Y, S, and selecting the style you want to use.
You may also want to add banded rows or columns to make it easier to read data from the table. To add banded rows, you press Alt, J, Y, R
Formatting Cells (01:30)
In addition to the style formatting options for the Pivot Table, you can also format cells in the normal way, for example by bolding the text in certain cells.
Another option is to insert comma separators into numeric values. This makes it easier to understand a table of large numbers. To add comma separators, we select a cell in the Pivot Table, then open the Format Cells window by pressing Right Mouse Button Key, T. We need to access the window this way to ensure that all the numbers in the Pivot Table are updated. Within the window, we can select a cell format that includes comma separators. This change makes it much easier to compare numbers in the Pivot Table.
Formatting your Pivot Table correctly can make your data much easier to read, helping you identify trends quickly and reducing the likelihood of errors. In the previous lesson, we use our Pivot Table to analyze the company's performance. In this lesson we'll improve the appearance of the Pivot Table by making several formatting changes. For Pivot Tables, we can easily change both the cell format and the number format. To change the cell format, we'll 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 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 salesperson. After adding your Pivot Table style, you still have complete freedom to implement cell format changes on top of the style. For example, if I'd like to bold the names of all my sales people, I could Just select them and then press Control+B to bold. I could also change the fill color of these cell 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 commas 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 dialog box. If you don't access the Format Cells dialog 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 I will 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.