Shading subtotals in a pivot table makes large reports easier to scan by visually separating each group’s totals from the detail rows. This guide shows you how to apply a fill color to subtotal rows (and columns) in Microsoft Excel, using built-in formatting and a few conditional formatting tricks. You’ll need a pivot table with at least one subtotal level already showing (by default, Excel shows subtotals for each field in the Rows area).
What You’ll Need
- A pivot table in Microsoft Excel (2016 or later; steps are similar in older versions).
- The pivot table must have subtotals visible. If they are hidden, right-click the field in the pivot table, choose Field Settings, and under Subtotals & Filters select Automatic or Custom.
- Basic familiarity with the ribbon and the Format Cells dialog.
Step-by-Step
- Select the subtotal rows manually (quick method). Click on the first subtotal row label (e.g., the row that says “Total” or the field name). Hold Ctrl and click each additional subtotal row label to select multiple. Then go to Home → Fill Color and pick a color. This works but is tedious if you have many groups.
- Use the “Format Only Cells That Contain” rule (better for dynamic data). Select the entire range of the pivot table (click any cell inside the pivot, then press Ctrl+A twice if needed). Go to Home → Conditional Formatting → New Rule.
- Choose Use a formula to determine which cells to format.
- Enter a formula that identifies subtotal rows. For example, if your subtotal rows have the word “Total” in the first column (like “Region Total”), use:
=$A1="*Total*"(but this will match any cell containing “Total”). A more precise method: use=$A1=LEFT($A1,LEN($A1)-6)if your subtotal label is like “Region Total” (i.e., the field name + space + “Total”). Simpler: if your subtotal rows are always bold (Excel default), you can base the formula on font bold:=GET.CELL(20,INDIRECT("rc",FALSE))but that requires a defined name. For most users, the easiest is to use the built-in Subtotal option in conditional formatting (see next step). - Use the built-in “Subtotal” rule (Excel 2010 and later). Select the pivot table range, go to Home → Conditional Formatting → New Rule. Choose Format only cells that contain. In the drop-downs, select Cell Value → equal to and type
="*Total*"(with quotes). Then click Format, choose a fill color, and OK. This will highlight any cell containing “Total” – which works if your subtotals are labeled with that word. It will also highlight any other “Total” in the pivot (like Grand Total), which you may want to exclude. - To exclude the Grand Total row, add another rule: select the Grand Total row (usually the last row) and apply a different fill or no fill. Or refine the formula:
=AND($A1="*Total*",ROW() - For column subtotals (if you have subtotals in columns), use the same conditional formatting but apply to the columns. For example, if your subtotal columns are labeled “Total” in the header row, use a formula like
=B$1="*Total*". - Alternative: Use the PivotTable Styles. Excel’s built-in pivot table styles have options for banded rows and subtotal shading. Click anywhere in the pivot table, go to Design tab (under PivotTable Tools). Check Banded Rows or choose a style that has a distinct subtotal color (e.g., “Pivot Style Medium 2”). This is the fastest but least customizable – you cannot choose your own color per subtotal level.
- For a more robust solution, use a helper column. Add a column to your source data that flags subtotal rows (e.g., a formula that checks if the row is a subtotal). Then add that column to the pivot as a filter or use it in a conditional formatting formula. This is overkill for most users.
- Apply the formatting to the entire pivot range. If you used conditional formatting, ensure the “Applies to” range covers the whole pivot table (including future rows when you refresh). By default, Excel expands the range when you add data, but you can manually set it to a large range like
=$A$1:$Z$1000. - Test by refreshing. Right-click the pivot table and choose Refresh. If you used manual selection, you’ll need to reapply after each refresh. Conditional formatting persists.
Common Mistakes
- Selecting only the subtotal label cell, not the entire row. If you select just the label (e.g., “Region Total”), only that cell gets shaded. You need to select the whole row (from the label to the last data column) to shade the entire subtotal row.
- Using a conditional formatting rule that also highlights detail rows. For example, if any detail row contains the word “Total” in a text field, it will be wrongly shaded. Check your data for such cases.
- Forgetting that the Grand Total row is also a subtotal. If you shade all rows containing “Total”, the Grand Total gets shaded too. Decide if that’s acceptable or exclude it manually.
- Applying formatting to the pivot table’s data range only, not the labels. If you select only the data cells (e.g., B5:C10), the subtotal row labels won’t be shaded. Select the entire pivot area including the row labels.
- Using manual fill on a pivot table that gets refreshed. Any manual fill you apply to cells will be lost when the pivot table is refreshed or when the layout changes. Use conditional formatting instead.
Tips
- Use a light color for subtotals (e.g., light gray or pale blue) so that the data remains readable.
- Combine with bold font to make subtotals stand out even more. In conditional formatting, set both fill and font bold.
- For multiple levels of subtotals (e.g., Region then City), you can use different shades by creating separate conditional formatting rules for each level. Identify each level by the label pattern – e.g., “Region Total” vs. “City Total”. Use a formula that checks the text after the space:
=RIGHT($A1,5)="Total"and then use a different rule for each level based on the field name. - If you often need this, save the pivot table as a template or record a macro to apply the formatting automatically.
- Consider using Excel’s “Format as Table” on the pivot’s source data – not directly helpful for subtotals, but it can make the source data easier to manage.
Troubleshooting
- Subtotals are not visible. Right-click the field in the pivot table, choose Field Settings, and under Subtotals & Filters, select Automatic or Custom. Also check that the field is not set to “None”.
- Conditional formatting does not work after refreshing. This usually happens if the rule’s “Applies to” range is too small. Go to Conditional Formatting → Manage Rules and edit the range to cover the entire pivot table area (e.g.,
=$A$1:$Z$1000). - The rule highlights the wrong rows. Double-check your formula. If you used
="*Total*"with “equal to”, it may not work because the asterisks are not wildcards in that context. Instead, use the “contains” option: in the New Rule dialog, select Cell Value → contains → typeTotal(without asterisks). - Subtotal rows are not contiguous. If your pivot has multiple subtotal levels, they may be nested. Conditional formatting will still work as long as the formula correctly identifies each row.
- Manual shading disappears when you change the pivot layout. That’s expected. Use conditional formatting for a permanent solution.
- Formatting only applies to the first column. Make sure your “Applies to” range covers all columns you want shaded. If you only selected column A, only that column gets shaded.
Frequently Asked Questions
- Can I shade subtotals in Google Sheets pivot tables? Yes, but the steps differ. In Google Sheets, you can use conditional formatting with a custom formula like
=REGEXMATCH($A1,"Total")and apply it to the pivot table range. Manual shading also works but resets on refresh. - Will the shading update when I refresh the pivot table? Only if you use conditional formatting. Manual fill will not persist after a refresh or layout change.
- How do I shade only the subtotal row, not the Grand Total? Use a conditional formatting rule that specifically targets rows that contain the field name plus “Total” (e.g., “Region Total”) but not “Grand Total”. You can use a formula like
=AND(ISNUMBER(SEARCH("Total",$A1)),$A1<>"Grand Total"). - Can I use different colors for different subtotal levels? Yes. Create separate conditional formatting rules for each level, each with its own formula that identifies that level (e.g.,
=RIGHT($A1,11)="Region Total"and=RIGHT($A1,9)="City Total"). - Is there a way to automatically bold subtotals as well? Yes, in the same conditional formatting rule, click Format and set the font to bold. This works for both manual and conditional formatting.
- Why does my conditional formatting rule not work on the subtotal rows? Check that the “Applies to” range includes the row labels. Also ensure that the formula is relative to the top-left cell of the range. For example, if your range starts at A2, use
=A2(not=A1) in the formula.
Related
- Can You Survive on 5 Hours of Sleep? Facts
- Why Did Zeus Leave T1? Full Story
- Zeus Network Baddies? Myth & Modern Meaning
How pages are sourced and corrected: Sources and editorial policy.