How to highlight all subtotal rows at once in Excel?

When working with large datasets in Excel, it is common to insert subtotals to summarize and better analyze key segments of the data. By default, Excel bolds subtotal rows to help differentiate them from regular rows. However, in many situations—especially when dealing with extensive tables—this default formatting is not always sufficient. Visually accentuating subtotal rows with color or other formatting can make these critical data points stand out, allowing for much quicker examination, easier presentation, and enhanced reporting accuracy. In the following sections, you’ll find several practical methods to highlight all subtotal rows at once so they are instantly recognizable at a glance.

Highlight all subtotal rows with Go To Special function
Highlight all subtotal rows with Conditional Formatting
Highlight all subtotal rows with VBA code
Easily select and highlight all subtotal rows with Kutools for Excel
Highlight all subtotal rows via Excel Filter

Highlight all subtotal rows with Go To Special function

When you create subtotals in Excel, outline symbols appear on the left side of your worksheet, as shown below. These allow you to collapse and expand details, making it easier to manage and analyze data by letting you display only summary information when needed.

To quickly highlight only the subtotal rows, you can use the outline levels and Excel’s Go To Special feature. This combination is effective for instances where subtotals have been automatically inserted and you wish to visually emphasize summary rows while ignoring detailed data rows in between. Here’s how you can do it step by step:

1. On the left side of your worksheet, click the outline level2. This will collapse your data so only subtotal and grand total rows are displayed. This is especially useful for focusing attention on summary points while temporarily hiding unnecessary details.

2. Select all the visible subtotal rows by clicking and dragging across the row numbers. Next, go to Home > Find & Select > Go To Special. The Go To Special feature provides advanced selection tools to quickly isolate certain types of cells, such as visible (unhidden) cells, which is useful when working with collapsed outlines.

3. In the Go To Special dialog box, select Visible cells only. This step ensures only the rows you can see—including your subtotal rows—are selected, leaving out any hidden underlying detail rows.

4. Click the OK button to confirm. Now, only the subtotal rows are selected, making them easy to format all together without altering hidden data rows.

5. With all subtotal rows selected, you can now apply formatting. Go to Home > Fill Color and pick your preferred highlighting color. This makes subtotals stand out clearly amidst surrounding data, especially after expanding the worksheet again.

6. When you wish to return the worksheet to its original, expanded view, click outline level3 on the left. This brings back all hidden detail rows, but the subtotal rows remain highlighted, making them easy to distinguish throughout the dataset.

Highlight all subtotal rows with Conditional Formatting

Conditional Formatting offers a flexible and instant way to highlight subtotal rows based on their text content or other logic—ideal when subtotals are marked by keywords such as "Total". It is particularly useful if you frequently add or remove data, as the formatting updates automatically.

1. Select the range of cells covering your data table, or select the entire worksheet if you want to ensure no subtotal row is missed (for large datasets, using the full worksheet is recommended, but can slow down performance on very big files).

2. Click Home > Conditional Formatting > New Rule. This opens the formatting rule editor, allowing for complex and dynamic highlighting rules.

3. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format. Enter the following formula in the field:=ISNUMBER(FIND("Total",$A1))
(Note: replace A1 with the first cell reference of the column where your subtotal row labels appear, for example: if subtotals appear in column C, use C1 instead).

This formula checks if the word Total exists within each cell of the specified column, capturing both "Subtotal" and "Grand Total".

4. Click Format to choose your preferred fill color under the Fill tab. Formatting options can also include bolding, changing font color, or adding borders for extra emphasis.

5. Click OK to close the Format Cells window, then click OK again to apply the conditional formatting rule. Now, all subtotal rows that match your criteria will be highlighted instantly.

Highlight all subtotal rows with VBA code

VBA (Visual Basic for Applications) is a useful solution for automating the highlighting of subtotal rows—especially when dealing with large or complex spreadsheets, or when subtotals do not follow a fixed pattern. This method is especially advantageous if you need a repeatable action across different sheets or if you want precise color customizations.

1. Press ALT + F11 to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, then paste the following code into the Module window.

VBA code: highlight all subtotal rows in Excel

Sub FormatTotalRows()
'Update 20140318
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    If Right(Rng.Value, 5) = "Total" Then
        Rng.EntireRow.Interior.ColorIndex = 6
    End If
    If Right(Rng.Value, 11) = "Grand Total" Then
        Rng.EntireRow.Interior.ColorIndex = 8
    End If
Next
End Sub

3. Press F5 to run the code. A dialog box will prompt you to select your data range for the macro to process. This extra step helps avoid accidental modifications to unrelated areas of your worksheet.

4. After choosing your range and clicking OK, the macro will automatically highlight subtotal rows in yellow and the grand total row in blue.

Highlight all subtotal rows with Kutools for Excel

If your data frequently needs subtotal row identification or you want a much faster approach with an intuitive interface, Kutools for Excel offers the Select Specific Cells utility. This tool streamlines the process, helping you select and highlight all rows containing a specific keyword (such as "Total") in just a few clicks—especially beneficial for users who prefer working without formulas or code.

1. Select the column that includes the word "total" in the subtotal or grand total rows you wish to highlight. Click Kutools > Select > Select Specific Cells.

2. In the Select Specific Cells dialog box:

• Choose Entire row under the Selection type section.
• In the Specific type area, select Contains from the drop-down, then enter Total in the text field.
• Click OK to confirm.

3. All matching subtotal rows are instantly selected. Go to the Home tab, click Fill Color in the Font group, and pick the desired highlight color.

This method is user-friendly and efficient, making it suitable for both regular and occasional Excel users. It is particularly helpful when subtotal or total labels follow a consistent pattern.

Highlight all subtotal rows via Excel Filter

Another practical method is to use Excel's built-in filter feature to quickly isolate and then highlight subtotal rows—especially useful when subtotal rows contain a consistent keyword, such as "Total" or "Subtotal", and you want a straightforward, formula-free approach.

1. First, make sure your data includes a column where subtotal rows are clearly labeled (e.g., "Total" or "Subtotal"). Select your data and click Data > Filter to enable filter drop-down arrows.

2. Click the filter arrow in the column where your subtotal or total labels appear. In the resulting filter menu, uncheck Select All, then check only the values containing "Total" or "Subtotal" (you can type "Total" in the search box to filter the list).

3. Only subtotal and grand total rows will be visible now. Select these rows, go to Home > Fill Color, and apply your desired highlight. Finally, clear the filter to return to the full dataset; highlighted rows will stand out from all others.

Demo: Easily select and highlight all subtotal rows with Kutools for Excel

Related articles:

• How to highlight all named ranges in Excel?
• How to highlight all error cells in Excel?
• How to highlight cells with external links in Excel?