Yes. In Excel for Microsoft 365 on Windows, the most reliable way to highlight PivotTable subtotal rows is to create or apply a PivotTable style and format its subtotal elements; for a quick one-off change, select the subtotal cells and apply a fill or font format. To keep manual formatting after a refresh, open PivotTable Options and enable Preserve cell formatting on update.
What a PivotTable subtotal row represents
A subtotal summarizes the values belonging to one group in a PivotTable. For example, if Region and Product are in the Rows area and Sales is in Values, Excel can show a subtotal for each region as well as detail rows for its products. A grand total is different: it summarizes the entire PivotTable rather than one group. These rows can look similar, but they serve different purposes and may need separate formatting.
Subtotals appear when the row or column fields are arranged in groups and subtotals are enabled. A PivotTable with only one row field may have a subtotal for that field, depending on its settings. A flat-looking list is not necessarily missing subtotals: the report layout, field order, and subtotal settings all affect where they appear. Changing the layout from Compact to Outline or Tabular changes how labels are displayed, but does not itself mean every group has a subtotal.
Formatting is useful when readers need to distinguish a group summary from its detail rows. A restrained fill, bold text, or top border can make the hierarchy easier to scan without confusing a subtotal with the grand total.
Apply a built-in PivotTable style
Start by clicking anywhere inside the PivotTable so its contextual ribbon tabs appear. On Windows, select PivotTable Design, then choose a style from the PivotTable Styles gallery. The gallery styles can provide banding, borders, and emphasis for different parts of the report. Some built-in styles make subtotal rows stand out more clearly than others, so preview a few before choosing one.
The Design tab also has PivotTable Style Options, including settings such as Row Headers, Column Headers, Banded Rows, and Banded Columns. These options control which parts of the selected style are used. They do not act as a direct on/off switch for subtotal rows. If a built-in style does not give the subtotal rows the emphasis you need, make a custom style instead.
To apply a style, use this sequence:
- Click a cell in the PivotTable.
- Open PivotTable Design and expand the PivotTable Styles gallery with its More button.
- Choose a style and check whether its subtotal and grand total rows are visually distinct.
- Use the PivotTable Style Options on the Design tab to adjust available header and banding features.
A style is usually a better starting point than coloring cells one by one because it is attached to the PivotTable. When the report changes shape after filtering or refreshing, the style can be applied to the PivotTable’s current layout rather than relying on the original worksheet row numbers.
Create a custom style for subtotal rows
For precise control, create a custom PivotTable style. Click the PivotTable, open PivotTable Design, expand the PivotTable Styles gallery, and choose New PivotTable Style. In the dialog, give the style a name and select the subtotal element you want to change. Excel offers subtotal elements by row or column position and nesting level, such as First Row Subtotal or First Column Subtotal. The relevant element depends on whether the fields are in the Rows or Columns area and how many levels are nested.
Select the element, choose Format, and set the font, border, or fill. For a typical report with Region and Product in Rows, you would usually start with a row subtotal element, then check the result in the PivotTable. If the wrong level is emphasized, return to the style dialog and adjust the next subtotal level. The labels refer to hierarchy levels, not to the text in a particular worksheet row.
After defining the style, select it in the PivotTable Styles gallery. A custom style lets you give region subtotals a pale fill and bold font, for instance, while using a stronger border or different fill for the grand total. Keep contrast and text readability in mind, especially if the worksheet may be printed in grayscale or viewed by people using accessibility settings.
Styles can be adjusted later: select the PivotTable, open the styles gallery, right-click the custom style, and choose Modify. If the style is not available in another workbook, it may need to be recreated or copied with the workbook content; do not assume every workbook has the same custom style definitions.
Format existing subtotal cells directly
For a quick adjustment, select the subtotal cells across the PivotTable and use Home > Fill Color, Font Color, Bold, or the Borders menu. You can also right-click a subtotal cell and use the formatting controls available in the context menu. Be careful to format the subtotal row’s cells, not just its label: a fill applied only to the label cell will leave the values looking like ordinary detail.
Worksheet row numbers are not a dependable way to target subtotal rows. Filtering, expanding or collapsing groups, changing fields, and refreshing the source can move the rows. Avoid selecting a fixed range such as rows 12 through 12 and assuming it will remain the subtotal row. A PivotTable style is generally more robust when the table’s size or structure changes.
To help retain direct formatting, click inside the PivotTable and open PivotTable Analyze > Options. In the PivotTable Options dialog, select the Layout & Format tab and check Preserve cell formatting on update. The exact dialog layout can vary slightly by Excel build. This setting helps preserve cell formatting when the PivotTable updates, but it does not make a manually colored worksheet row a rule that recognizes every future subtotal. If the layout changes substantially, review the result and consider using a custom style instead.
Control whether subtotals appear
Highlighting only helps if the PivotTable actually displays subtotal rows. Select the PivotTable and use PivotTable Design > Subtotals to choose Show All Subtotals at Top of Group, Show All Subtotals at Bottom of Group, or Do Not Show Subtotals. The top or bottom choice changes their position, not their meaning. A custom style can make them easier to see in either position.
You can also control subtotals for an individual field. Right-click a row or column field label, choose Field Settings, and use the Subtotals & Filters tab to select Automatic, Custom, or None, as applicable. Automatic uses Excel’s default subtotal behavior for that field; Custom lets you select a supported summary function. If the field is set to None, changing the PivotTable style will not create a subtotal that is absent.
Subtotals are based on the values summarized in the PivotTable, such as Sum of Sales or Count of Orders. A subtotal’s calculation depends on the field’s summary settings and the source data. Highlighting is purely visual: it does not change the calculation, the source records, or the meaning of the displayed number.
Choosing a formatting method
The right method depends on whether the report will be reused and how much control you need. A built-in style is quick, a custom style offers more control over hierarchy levels, and direct formatting is convenient for a small change. Conditional formatting can be useful for value-based rules, but it is not automatically the simplest way to identify subtotal rows.
- Use a built-in style when you need fast, consistent formatting and one of the gallery choices is clear enough.
- Use a custom PivotTable style when different subtotal levels or the grand total need distinct formatting that should follow the PivotTable layout.
- Use direct formatting for a temporary or small adjustment, and enable Preserve cell formatting on update if the formatting should survive routine refreshes.
- Use conditional formatting when the condition is about a value, such as highlighting unusually high totals. Check the rule’s application scope so it covers the intended PivotTable values after refresh.
Conditional formatting should not be confused with subtotal detection. A color scale can highlight high and low numbers whether they appear in detail or subtotal rows. If the requirement is specifically “format each subtotal row,” a subtotal element in a custom PivotTable style is usually a more direct fit. PivotTable-aware conditional formatting offers options to apply a rule to values in a field, but those options concern the rule’s range and scope; they do not turn every value-based rule into a subtotal-only rule.
Common problems and fixes
The fill disappears after refresh. Direct cell formatting can be affected when Excel rebuilds the PivotTable. Enable Preserve cell formatting on update, then refresh and inspect the result. For a report that changes size often, use a PivotTable style rather than relying only on manual cell formatting.
The wrong subtotal level is highlighted. A PivotTable with nested row fields can have multiple subtotal levels. In a custom style, try the other row subtotal elements and verify the result with a small sample. Also confirm which fields are in Rows and which are in Columns; a column subtotal element will not format row subtotal bands.
No subtotal row appears. Check PivotTable Design > Subtotals and the individual field’s Field Settings. Also confirm that the report has fields arranged in a grouping that can produce the subtotal you expect. A style changes appearance; it does not turn subtotals on.
The grand total changes color too. Grand totals have their own style elements. Modify the subtotal element rather than the Grand Total Row element, or adjust both separately if you want a coordinated design.
Formatting looks different after changing layout. Outline, Compact, and Tabular layouts show field labels differently, and moving fields changes the hierarchy. Recheck which subtotal level the style targets after changing the field arrangement. Refreshing and filtering can also change which rows are visible, so assess the formatting on the actual report view.
Excel for Mac and Excel for the web
The instructions above target Excel for Microsoft 365 on Windows. Excel for Mac also has contextual PivotTable tabs and style galleries, though the ribbon arrangement and dialog presentation can differ by release. Look for the PivotTable Design tab, style gallery, and PivotTable Options rather than relying on a Windows-only screen layout. Windows keyboard shortcuts may not transfer exactly to a Mac; the steps here use ribbon commands instead of shortcut-dependent selection.
Excel for the web supports working with PivotTables, but available design and formatting controls can differ from the desktop app and may depend on the current release. If you cannot find New PivotTable Style or the needed subtotal formatting control in the browser, open the workbook in the desktop Excel app. Verify the final appearance in the version that will be used to maintain the report.
Frequently Asked Questions
Can I highlight only the subtotal rows in an Excel PivotTable?
Yes. Create a custom PivotTable style and format the appropriate row or column subtotal element. For a one-time change, select the subtotal cells and apply formatting directly.
How do I keep PivotTable formatting after refresh?
Open PivotTable Analyze > Options, go to Layout & Format, and select Preserve cell formatting on update. This helps retain manual formatting, while a custom PivotTable style is generally better for formatting that should follow changing PivotTable structure.
Why is Excel not showing subtotal rows?
Check PivotTable Design > Subtotals and the relevant field’s Field Settings. The field may be set to None, or the PivotTable’s field arrangement may not produce the grouping you expect.
Can I use conditional formatting to highlight subtotals?
Conditional formatting can format PivotTable values, and its scope can be tied to a value field. But a general value rule may also affect detail values. For formatting subtotal levels specifically, use the subtotal elements in a custom PivotTable style.
Are subtotal rows the same as the grand total?
No. A subtotal summarizes one group, such as one region, while the grand total summarizes the full PivotTable. Excel styles have separate elements for subtotals and grand totals, so they can be formatted differently.
Related
- Excel Name Manager Spill Range: Create and Use One
- Excel Edit Defined Name Range: Steps and Common Fixes
- Excel Trendlines: How to Add, Format, and Choose One
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram: How Many Bins Should You Use for Data?There is no single correct number of bins for an Excel histogram: let Excel choose a starting point,…
- Free Excel Dashboard: Templates, Costs and How to Build OneYes—you can create an Excel dashboard for free using Excel for the web or a free template, provided…
- How to Flip a Bar Graph in Excel: Reverse Its OrderTo flip the order of bars in an Excel chart, right-click the category axis, choose Format Axis, and…
- Excel Gantt Chart Schedule: Create and Track ProjectsAn Excel Gantt chart schedule is a task list paired with a date-based timeline, where colored cells show…
- Excel Pivot Chart: Count Unique ValuesYes. An Excel PivotChart can display a count of unique values, but its PivotTable must use the Data…
- Excel Line Chart by Date: Make Dates Plot CorrectlyTo make an Excel line chart by date, put real Excel dates in one column and the values…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.