Excel Subtotal Rows in Color: How to Highlight Them

To color subtotal rows in Excel, use conditional formatting with a rule that recognizes each subtotal row, then choose a fill color. For rows labeled “Subtotal,” a formula rule can check the label; for rows created with Excel’s Subtotal command, a rule can check for a SUBTOTAL formula. The color is formatting, not something the SUBTOTAL function adds by itself.

What makes a subtotal row different?

Excel has two related features that people often call “subtotals.” The SUBTOTAL function calculates a result from a range, such as a sum or average. Separately, the Subtotal command on the Data tab can insert subtotal rows and create an outline so you can collapse or expand groups. Either way, a cell’s calculated result does not automatically determine its fill color. Color comes from ordinary cell formatting or a conditional-formatting rule.

The distinction matters because the best coloring method depends on how the rows are identified. A report might use labels such as “East Subtotal,” generated SUBTOTAL formulas, or a distinctive layout. Conditional formatting is usually the most reliable option for a report that changes, because it reapplies the color when rows or values change. A manual fill is simpler for a fixed report.

How Excel’s Subtotal command works

On Windows in Microsoft 365, the command is Data > Outline > Subtotal. It is designed for a sorted list of records: choose the column that defines each group, the function to use, and the column or columns to calculate. Excel inserts a summary row at each change in the grouping field and adds outline controls that let you show detail or summaries. For example, sorting sales by Region and choosing Sum for the Amount column can produce a sum row for each region.

The command does not offer a fill-color choice in its Subtotal dialog. You can format the inserted rows afterward, or set conditional formatting so the color follows the rows. The command is intended for a normal range, not an Excel Table. If the data is a Table, use Table Design > Convert to Range first if you want to use the Subtotal command; converting changes the Table into a regular range. Consider whether that loss of Table behavior is appropriate for your workbook.

Sorting first is important: the command creates a subtotal whenever the selected “At each change in” field changes as Excel reads down the list. If identical group names are separated by other groups, you can get multiple subtotal rows for the same name. Keep a copy of the original data or use a separate report sheet if you need to preserve the source layout.

Color rows with a subtotal label

If the label column consistently contains the word “Subtotal,” conditional formatting is straightforward. Suppose the report has headings in row 1, labels in column A, and data through column F. Select the rows you want the rule to cover, for example A2:F500, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the rule below, choose Format, select a Fill color, and confirm.

With labels in A2:A500 and the conditional-formatting range set to A2:F500, this formula highlights every selected row whose column A label contains “Subtotal”:

=ISNUMBER(SEARCH("Subtotal",$A2))

The dollar sign fixes the label column while leaving the row number relative. That is why the rule tests A2 for the first selected row, A3 for the next, and so on, while applying the resulting format across columns A through F. SEARCH is not case-sensitive, so “subtotal,” “East Subtotal,” and “East subtotal” all match. If labels have a consistent exact value, use an exact comparison instead, such as =$A2="Subtotal".

Choose a fill that contrasts with the text and does not overwhelm the detail rows. A pale tint is often easier to read than a saturated background, especially if the report may be printed. To color only the label cell, select A2:A500 instead of the full row range before creating the rule.

Color rows generated by SUBTOTAL formulas

When the row labels are inconsistent, you can test a cell in the subtotal-result column instead. Suppose each subtotal result is in column B and the first data row is row 2. Select the complete report range, such as A2:F500, and create a formula-based conditional-formatting rule. This rule checks whether B in each row contains a formula whose text includes SUBTOTAL:

=IFERROR(ISNUMBER(SEARCH("SUBTOTAL(",FORMULATEXT($B2))),FALSE)

FORMULATEXT returns the formula as text; SEARCH looks for the function name, and IFERROR returns FALSE for a row where B is a constant or otherwise has no formula text to inspect. This is useful when Excel’s Subtotal command has inserted formulas in B. It also matches a manually written SUBTOTAL formula, which is usually acceptable when the goal is to color subtotal calculations.

For example, if the subtotal row’s B cell contains =SUBTOTAL(9,B3:B8), the rule finds “SUBTOTAL(” and formats that row. In this example, the 9 argument requests a sum, so if B3:B8 contain 12, 18, 20, 25, 30, and 35, the result is 140. Check that the chosen column really contains the subtotal formulas on every subtotal row. If the formula is in column F instead, change $B2 to $F2 in the rule.

This method can also catch other formulas containing SUBTOTAL, not only rows inserted by the Data tab command. If regular detail rows have SUBTOTAL formulas too, the rule will color them as well. In that case, use a distinct label, a dedicated helper column, or a more specific layout test to distinguish summary rows.

Format a fixed report manually

For a one-time report, select the subtotal cells or whole subtotal rows and use Home > Fill Color to apply a fill. You can also use bold text, a top border, or a different font color to make the summary stand out without relying on color alone. If the subtotal rows are separated by detail rows, hold Ctrl while selecting individual row ranges on Windows. On Mac, use Command while selecting nonadjacent ranges.

Manual formatting is quick, but it does not identify rows by their meaning. If you insert a new group, paste new data, or refresh a report, the new subtotal row may not inherit the intended color. Use conditional formatting for a recurring report or one that changes frequently. For a stable, print-ready snapshot, manual formatting may be all you need.

Choose a clear style for grouped summaries

Color should help readers scan the hierarchy, not substitute for clear labels or correct calculations. A practical scheme is to give group subtotals a light fill, use bold type for the label and result, and reserve a stronger border or a second shade for the grand total. Avoid using several similar fills without a legend; readers may assume the colors represent different categories or values.

For nested groups, consider whether each level needs a distinct treatment. A report grouped by Region and then Product might use a subtle fill for product subtotals and a stronger fill for region subtotals. If one color is used for every subtotal, labels such as “Product subtotal” and “Region total” can still make the hierarchy understandable. Check contrast on screen and in grayscale printing, and do not rely on color alone to communicate an important distinction.

Common problems and fixes

The rule colors the wrong row: The row number in the formula must correspond to the first row of the selected “Applies to” range. If the range begins at row 3, start the formula with row 3 rather than row 2.
Only one cell changes color: The formula may be fine, but the “Applies to” range may cover only the result column. Edit the rule under Home > Conditional Formatting > Manage Rules and extend the range across the full rows.
Subtotal rows are missed: Check the exact label text or the column containing the formula. A rule checking column B cannot identify a subtotal if the subtotal formula is actually in F. For text matching, make sure the word used in the rule occurs in the label.
Detail rows are highlighted too: A detail cell may also contain a SUBTOTAL formula, or the label may contain the searched word. Use a stricter label condition or add a helper indicator that is true only for summary rows.
The color appears inconsistent: Another conditional-formatting rule may have a competing fill, or direct formatting may make the sheet harder to interpret. Review rule order and the “Applies to” ranges in Manage Rules, and remove obsolete rules when the report structure changes.
The Subtotal command is unavailable: The selected data may be an Excel Table. Convert it to a range using Table Design > Convert to Range, or use another summary approach that works with Tables.

Keep the formatting dependable

Before distributing a report, test the rule on more than one group and add a new example subtotal row if the report will be updated. Confirm that the rule covers future rows, not just the rows currently on the sheet. Conditional-formatting ranges do not always expand the way a user expects after data is pasted below them, so check the range in Manage Rules after changing the report structure.

Also keep the underlying numbers and labels intact. Do not type a colored subtotal value over a formula simply to make it look like a summary. If a value is wrong, inspect the source range and the subtotal function; if its appearance is wrong, change the formatting rule. Keeping calculation and presentation separate makes later edits easier to audit.

Frequently Asked Questions

Does Excel automatically color subtotal rows?
No. The SUBTOTAL function calculates a result, and the Data tab’s Subtotal command can insert summary rows and outline controls, but color is a separate formatting choice. Apply a fill manually or create a conditional-formatting rule.

Can I color only the subtotal result cell?
Yes. Select just the result column as the conditional-formatting range, or manually apply Fill Color to those cells. If you use a formula rule, make its reference point to the column that holds the subtotal formulas.

Will conditional formatting work if I collapse the outline?
Yes. The rule remains attached to its worksheet range when outline levels are collapsed. Whether a row is currently hidden does not remove its conditional formatting; expand the outline to inspect the row itself.

Can I use the Data Subtotal command inside an Excel Table?
Not directly. The command is intended for a normal range. Convert the Table to a range with Table Design > Convert to Range if that is suitable, or keep the Table and build summaries using a separate method.

How do I remove the subtotal-row color?
For a conditional fill, use Home > Conditional Formatting > Manage Rules to delete or edit the rule. For a manually applied fill, select the cells and use Home > Fill Color > No Fill. Check for other rules if the color remains.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.