This week we will look at formatting pivot tables. We will continue with the Texas Vaccination data used in the last several entries. The initial pivot table as shown in the first figure below is rather plain. The numbers are not formatted. There are no thousands separators making large numbers hard to read. The number of decimal places varies from none to two. Finally, these numbers represent the cost of the vaccinations, but without formatting the data as currency, the numbers could just as easily be counts of the number of vaccinations provided.
First, you need to know that there are two types of formatting pivot data measure data. The first type allows you to format the entire table at one time. The second type allows you format specific selected cells. Therefore, your challenge is to determine which format should be the overall formatting of the data and apply that format first to all cells in the pivot table. Then by selecting individual cells or groups of cells to customize the formatting for specific needs. In this case, I want to format the overall table as currency with no decimal places. To do that, begin by right clicking in any cell in the table and choose the option Value Field Settings from the menu.
The dialog that appears is actually not the dialog that we want because it does not allow us to change the data format. However, the button in the lower left corner, Number Format, is the option that takes us to what we want.
Clicking the Number Format button displays the Format Cells dialog. From your other work in Excel, you should be familiar with this dialog. Select Currency from the Category list and then specify zero decimal places. Notice that the currency format automatically inserts commas as the thousands separator (the separator is actually defined by your Windows configuration settings). You can also choose the currency symbol to appear at the beginning of the value.
After setting the format, you will need to click the OK button twice (once in each of the resulting dialogs) to return to the Pivot table. As you can see in the figure below, the currency format has been applied to all cells in the pivot table.
Let’s say though that we only want the currency formatting to apply to the Grand Total column and row. The other cells do not need the dollar sign before the number. To change a subset of the table’s cells, select the cells just as you would in a normal Excel spreadsheet by clicking in one corner and dragging through the opposite corner to select a rectangular area of cells.
Then from the Home ribbon, locate the Number group of options. In dropdown box, select the overall format type. Initially this will say Currency because that is how we just formatted the entire table. However, you might want to select General to return the format to a simple number. Then you can use the other options beneath the dropdown box to add the thousands separator again and to change the number of decimal places displayed.
The following figure shows how we changed the formatting of just a selected group of cells.
You could also achieve this same result by first selecting the cells where you want to change the format and then right clicking anywhere within the selected cells to display a popup dialog. From this dialog, selecting either the Format Cells… or Number Format… option will open the same Format Cells dialog we saw before.
With the measure cells (values) formatted, we can now turn our focus to the labels in the table. In particular, you may not like the labels Row Labels, Column Labels, and Sum of VCost. These are actually easy to change since they are just labels. Click on any of these cells, Row Labels for example and then in the expression box, simple delete the current label and type in a new label. In this case, we may simply want to call the row label: Months.
Similarly, I changed the following labels:
Column Labels –> Years
Sum of VCost –> Total $ Spent
However, when I try to change the Grand Total column label to Total by Month, I noticed that the Grand Total row label also changed to Total by Month. In fact, these aggregate column and row labels are connected and will always have the same value. I have to say, this is one of the few disappointing features I have encountered in working with pivot tables. Unless I am missing something and one of my readers can enlighten me with a comment. Perhaps Microsoft, if they are readying this, can look into this ‘problem’ for a future version.
Although I will not do it here, you can even change the column headers such as changing 2002 to: Year 2002. (BTW, if you are wondering how I got the months to display correctly in chronological order rather than alphabetical order, please refer back to my blog of November 12, 2011 (https://sharepointmike.wordpress.com/2011/11/12/create-your-own-custom-list-for-sorting-pivot-rows-and-columns/).
You can also format the text for these labels changing the font, font size, and font style. You can even adjust the alignment using other options in the Home ribbon.
After making these formatting changes, my pivot table looks like the following figure. In fact, it now looks more like something that I can include in a report. But we can dress it up even further as we will see next time.
Next week I’ll finish formatting this pivot table. For those of you in central to northern Florida, next week is also SQL Saturday in Jacksonville, FL. I hope to see you there.