Select Pivot Table Parts For Formatting

December 16, 2022 - by Bill Jelen

Problem: I want to manually format a pivot table. Can I select all the row subtotals? For example, select the region totals in rows 8, 12, and 16.

Row 8 is the Central region Total. Row 12 is the East Total. Row 16 is the West Total — Figure 870. Select row subtotals.

Strategy: A clever mouse trick will allow you to select similar rows in a pivot table. Follow these steps:

1. Select one cell in the pivot table. On the Design tab, choose Report Layout, Show in Tabular Form.
2. Hover the mouse over cell A8. This is the Central region total. Slowly move the mouse toward the left edge of the cell. Eventually, the cell pointer changes to a black arrow that points to the right. When this cell pointer appears, click the mouse. Excel will now select all the subtotal rows.

Hover the mouse over the Central Total in A8. Slowly move towards the left edge until the mouse cursor turns into a arrow pointing to the right. Click when you see that arrow appear and you will select all Region Total Rows. — Figure 871. One click select all subtotal rows.

3. Using the formatting icons on the Home tab of the ribbon, assign a color to the subtotal rows.

Additional Details: Click in the left side of cell B5, and you will select all the ABC records throughout the pivot table. Below, different colors are applied to ABC, DEF, and XYZ using this method.

In a similar fashion, hover over the left fifth of the ABC cell and you will see an arrow. Click and select all ABC rows. — Figure 872. Format all cells for one product.

If you have multiple column fields, you can select various columns by hovering near the top of the label for a column.

Gotcha: This feature can be turned off. To ensure that it’s not turned off, enable the Enable Selection setting under the Select dropdown on the Analyze tab.

This article is an excerpt from Power Excel With MrExcel

Title photo by Pawel Czerwinski on Unsplash