FORMATTING A PIVOT TABLE in Office Excel

Drawer QR Code JIS X 0510 in Office Excel FORMATTING A PIVOT TABLE

If you apply a different theme in your workbook, or modify the current theme, your pivot table s appearance and PivotTable styles may be affected. The theme colors and fonts will override the settings in the PivotTable styles, and they could affect the way the pivot table appears. To apply a different theme, follow these steps: 1. On the Ribbon, click the Page Layout tab. 2. In the Themes group, click Themes, and then in the Themes gallery, click the theme you want to apply. The colors and fonts in your file will change, and your pivot table and Excel Table may be wider or narrower, if the fonts are much different. In the Themes group on the Page Layout tab, the icons have changed, to reflect the colors, fonts, and effects of the current theme. In the PivotTable Styles gallery, the styles use the colors from the new theme. Even the font in the row and column buttons changes to the Body font for the current theme.

In your pivot table, the Region and City fields are in the Row Labels area. You want to use a different font size for the Region subtotals, and you d like to select and format all the subtotals at the same time, instead of formatting each one separately. This example is based on the Regions.xlsx workbook.

If the subtotals are at the bottom of the group, in either the Row Labels area or the Column Labels area, you can select them all, and then format them together. To select them, you may have to activate the Enable Selection option. 1. Select a cell in the pivot table, and on the Ribbon, click the Options tab. 2. In the Actions group, click Select, and then check to see if Enable Selection is activated (see Figure 4-7).

Figure 4-7. The Enable Selection option turned on 3. If Enable Selection isn t activated, click it to activate the feature. If it is activated, click the worksheet, to close the menu without making a selection. 4. To select subtotals for a Row Labels field, point to the left edge of a cell that contains a subtotal label, and when the pointer changes to a black arrow shape (see Figure 4-8), click to select all the subtotals for that field.

Figure 4-8. The black arrow pointer at the left of the subtotal in Row 8 5. If the subtotals are for a Column Labels field, point to the top edge of a cell that contains a subtotal label, and when the pointer changes to a black arrow shape, click to select all the subtotals for that field. 6. Format the selected subtotals, using the commands on the Ribbon s Home tab.

You manually adjusted the column widths and applied formatting to the cells in your pivot table. However, your pivot table formatting is lost when you refresh the pivot table or change the pivot table layout. Even changing the report filter causes the formatting to be lost. This example is based on the LoseFormat.xlsx workbook.

Most formatting can be preserved if you change the Format options in the PivotTable Options dialog box. 1. Right-click a cell in the pivot table, and then choose PivotTable Options. 2. On the Layout & Format tab, in the Format options, remove the check mark from Autofit Column Widths on Update. This prevents the column width from changing, if you have manually adjusted it. 3. Add a check mark to Preserve Cell Formatting on Update, and then click OK. Then, when you apply formatting, do the following: 1. Ensure that Enable Selection is turned on (as shown in Figure 4-7). 2. Unless you want to format a single cell, use the pivot table selection technique to select the elements you want to format (point to the top or left edge of the element, and then click when the black arrow appears).