Applying Your Own Style

Although the AutoFormat feature is a convenient way to apply formatting to your pivot table reports, you may run into situations where the available options don't give you the effect you're looking for. In these cases, you can manually apply formatting that will suit your needs.

When applying your own style, you can pinpoint an individual cell in your pivot table report or an entire section with your report.

For example, let's say that each market in the pivot table report in Figure 4.6 needs to be formatted to have a background color of black with white lettering. If you move your cursor over the Market field, your cursor image will change into a cross.

Figure 4.6. Notice the cross-style mouse pointer in cell B4. This will select only a single cell.

Moving your cursor a little higher will change the cursor image to a black arrow (see Figure 4.7), pointing toward the data items in the Market field. This indicates that any action taken at this point will affect all data items in the Market field. If you click your mouse here, all the data items in the Market field will be selected.

Figure 4.7. Move the mouse pointer up a bit and it will change into the black arrow. This will select all Market cells.

Now, when you apply your formatting using the standard toolbar, it will be applied to all data items under the Market field. Figure 4.8 shows the pivot table after selecting a white font and black background.

Figure 4.8. After you've selected the Market range, formatting changes apply to all the cells that contain a market.

Using this method, you can format all data items in a given field, all instances of a single data item, subtotals, and grand totals.

Let's take the same pivot table and format all the "Region" field subtotals. Simply move your mouse to the left edge of one of the subtotals, as shown in Figure 4.9, until your cursor image turns into a black arrow. Then click your mouse to select all the Region subtotals.

Figure 4.9. Move your mouse to the left of any subtotal and click, and all of that level of subtotals will be selected.

Choose a gray background and a bold font to change the subtotals as shown in Figure 4.10.

Figure 4.10. After using the preceding technique to select just the totals, it is a snap to format all the subtotals at once.

TIP

You can apply formatting to multiple sections of your pivot table report at one time by holding down the Ctrl key on your keyboard while selecting each section.

You can similarly choose all the data in the North region. Move the mouse pointer to the left of any of the cells in A5:A11 to select that region.

If you use pivot tables a lot, you might want to consider dragging three new buttons to the pivot table toolbar. Right-click any toolbar and choose Customize. The Customize dialog box shown in Figure 4.11 will activate. In the Categories list box, choose Data. In the Commands list box, scroll down to the three pivot table selection icons. These icons let you select all labels, all data, or both. Drag these to any visible toolbar.

Figure 4.11. If you frequently apply your own formatting to pivot tables, consider adding these three icons to the pivot table toolbar.