Creating Dynamic Worksheets by Using PivotTables in Microsoft Excel 2010

Formatting PivotTables

PivotTables are the ideal tools for summarizing and examining large data tables, even those containing more than 10,000 or even 100,000 rows. Even though PivotTables often end up as compact summaries, you should do everything you can to make your data more comprehensible. One way to improve your data’s readability is to apply a number format to the PivotTable Values field. To apply a number format to a field, right-click any cell in the field, and then click Number Format to display the Format Cells dialog box. Select or define the format you want to apply, and then click OK to enact the change.

Analysts often use PivotTables to summarize and examine organizational data with an eye to making important decisions about the company. For example, chief operating officer Lori Penor might examine monthly package volumes handled by Consolidated Messenger and notice that there’s a surge in package volume during the winter months in the United States.

Excel extends the capabilities of your PivotTables by enabling you to apply a conditional format to the PivotTable cells. What’s more, you can select whether to apply the conditional format to every cell in the Values area, to every cell at the same level as the selected cell (that is, a regular data cell, a subtotal cell, or a grand total cell) or to every cell that contains or draws its values from the selected cell’s field (such as the Volume field in the previous example).

To apply a conditional format to a PivotTable field, click a cell in the Values area. On the Home tab, in the Styles group, click Conditional Formatting, and then create the desired conditional format. After you do, Excel displays a Formatting Options action button, which offers three options for applying the conditional format:

• Selected Cells. Applies the conditional format to the selected cells only

• All Cells Showing Sum of field_name Values. Applies the conditional format to every cell in the data area, regardless of whether the cell is in the data area, a subtotal row or column, or a grand total row or column

• All Cells Showing Sum of field_name Values for Fields. Applies the conditional format to every cell at the same level (for example, data cell, subtotal, or grand total) as the selected cells

In Excel, you can take full advantage of the Microsoft Office system enhanced formatting capabilities to apply existing formats to your PivotTables. Just as you can create Excel table formats, you can also create your own PivotTable formats to match your organization’s desired color scheme.

To apply a PivotTable style, click any cell in the PivotTable and then, on the Design contextual tab, in the PivotTable Styles group, click the gallery item representing the style you want to apply. If you want to create your own PivotTable style, click the More button in the PivotTable Styles gallery (in the lower-right corner of the gallery), and then click New PivotTable Style to display the New PivotTable Quick Style dialog box.

Type a name for the style in the Name field, click the first table element you want to customize, and then click Format. Use the controls in the Format Cells dialog box to change the element’s appearance. After you click OK to close the Format Cells dialog box, the New PivotTable Quick Style dialog box Preview pane displays the style’s appearance. If you want Excel to use the style by default, select the Set As Default PivotTable Quick Style For This Document check box. After you finish creating your formats, click OK to close the New PivotTable Quick Style dialog box and save your style.

The Design contextual tab contains many other tools you can use to format your PivotTable, but one of the most useful is the Banded Columns check box, which you can find in the PivotTable Style Options group. If you select a PivotTable style that offers banded rows as an option, selecting the Banded Rows check box turns banding on. If you prefer not to have Excel band the rows in your PivotTable, clearing the check box turns banding off.

In this exercise, you’ll apply a number format to a PivotTable values field, apply a PivotTable style, create your own PivotTable style, give your PivotTable banded rows, and apply a conditional format to a PivotTable.

Set Up

You need the Formatting_start workbook located in your Chapter09 practice file folder to complete this exercise. Open the Formatting_start workbook, and save it as Formatting. Then follow the steps.

1. On the Sheet2 worksheet, right-click any data cell, and then click Number Format.

   The Format Cells dialog box opens.

2. In the Category list, click Number.

   The Number page is displayed.

   

3. In the Decimal places field, type 0.

4. Select the Use 1000 Separator (,) check box.

5. Click OK.

   Excel reformats your PivotTable data.

   

6. If necessary, on the Design contextual tab, in the PivotTable Style Options group, select the Banded Rows check box.

7. On the Design contextual tab, in the PivotTable Styles group, click the More button. Then, in the top row of the gallery, click the third style from the left. (When you point to it, Excel displays a ScreenTip that reads Pivot Style Light 2.)

   

   Excel applies the PivotTable style.

   

8. In the lower-right corner of the PivotTable Styles gallery, click the More button.

   The gallery expands.

9. Click New PivotTable Style.

   The New PivotTable Quick Style dialog box opens.

   

10. In the Name field, type Custom Style 1.

11. In the Table Element list, click Header Row, and then click Format.

    The Format Cells dialog box opens.

12. On the Font page, in the Color list, click the white square.

13. On the Border page, in the Presets area, click Outline.

14. On the Fill page, in the Background Color area, click the purple square at the lower-right corner of the color palette.

15. Click OK.

    The Format Cells dialog box closes, and the style change appears in the Preview pane of the New PivotTable Quick Style dialog box.

16. In the Table Element list, click Second Row Stripe, and then click Format.

    The Format Cells dialog box opens.

17. On the Fill page, in the middle part of the Background Color area, click the eighth square in the second row (it’s a light, dusty purple).

18. Click OK twice.

    The Format Cells dialog box closes, and your format appears in the PivotTable Styles gallery.

    

19. Click the new style.

    Excel formats your PivotTable using your custom PivotTable style.

20. On the Design contextual tab, in the PivotTable Style Options group, clear the Banded Rows check box.

    Excel removes the banding from your PivotTable and from the preview of the custom style.

    

21. Select the cell ranges K6:K17 and K19:K30.

22. On the Home tab, in the Styles group, click Conditional Formatting, point to Color Scales, and in the top row, click the second three-color scale from the left.

    

    Excel applies the conditional format to the selected cells.

    

Clean Up

Save the Formatting workbook, and then close it.