3. Formatting Pivot Tables

Overview

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.

Summary

  1. 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

  2. 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.

Transcript

In the previous lesson, we used 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.

Formatting your pivot table correctly can make your data much easier to read, helping you quickly identify trends and reducing the likelihood of errors.

Let's start by changing both the cell format and number format in our pivot table. To change the cell format, we'll press alt J Y to go to the pivot table design tab, 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.

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. Let's press enter on this particular 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 improve readability. we'll again press alt J Y to go back into the design tab and R for banded rows. Now it's much easier to read across the revenue numbers for each salesperson.

After adding the pivot table style, we still have complete freedom to implement cell format changes on top of the style. For example, if we'd like to bold the names of all the salespeople, we can select them and then press control B to bold.

We also have the option to change the fill color of these cells if so desired. 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. Let's add some comma separators to all our data points.

To do this, we'll select a cell in the pivot table, then press the right mouse button key, and T for number. 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 dropdown, we'll press alt and the underlined letter, which is C.

We'll now move the arrow down to select number.

We'll press alt D to change the decimal places, and then alt U to include comma separators. This is the format we want, so we'll press enter to make the changes.

Now the numbers in our pivot table are much easier to compare.

Contrasting our current format with our original pivot table just shows you the impact that a few simple formatting changes can have on making your data much more readable.

Excel Excel for Business Analytics Learning Plan
Data Analysis
Pivot Tables

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial