Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to highlight all subtotal rows at once in Excel?

In Excel, we often add subtotals for a large worksheet to count or analyze the data, and the subtotal rows are formatted with bold font by default. But, sometimes, we need the subtotal rows to be stand out the crowd, so that we can view them quickly and intuitively. In this case, we can highlight all the subtotal rows with a desired formatting.

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


Select the entire row if cell contains specific value in Excel:

Click Kutools > Select > Select Specific Cells. The Kutools for Excel's Select Specific Cells utility helps you easily select entire row if cell contains specific value in Excel. See below screenshot:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Highlight all subtotal rows with Go To Special function


As we all known, when we add subtotals for the data, the outline symbols will be displayed at the left of the worksheet as following screenshot shown:

doc-highlight-subtotals1

Now, we can fold the outline and just leave the Total and Grand Total rows. And then apply formatting for these visible rows only. Please do with following steps:

1. Click outline level 2 to display the subtotals and Grand total rows only. See screenshot:

doc-highlight-subtotals1

2. And then select these displayed subtotal rows, and click Home > Find & Select > Go To Special.

doc-highlight-subtotals1

3. In the Go To Special dialog box, check Visible cells only option, see screenshot:

doc-highlight-subtotals1

4. Then click OK button to close the dialog, and only the subtotal rows have been selected.

doc-highlight-subtotals1

5. And then you can format them with a background color as you like by clicking Home > Fill Color.

doc-highlight-subtotals1

6. At last, click outline level 3 in the left pane to expand the detail information, and the subtotal rows have been highlighted from the crowded data. See screenshot:

doc-highlight-subtotals1


Highlight all subtotal rows with Conditional Formatting

Conditional Formatting is a powerful feature in Excel, we can also use it to highlight all subtotal rows immediately.

1. Select your data range or whole worksheet which contains subtotal rows.

2. Click Home > Conditional Formatting > New Rule, see screenshot:

doc-highlight-subtotals1

3. In the New Formatting Rule dialog box, click Use a formula to determine which cell to format, and enter this formula =ISNUMBER(FIND("Total",$A1)) into the Format values where this formula is true text box (A1 indicates the column that your subtotals are located, if the subtotals are in column C, you would use C1 instead of A1 in the formula ). See screenshot:

doc-highlight-subtotals1

4. Then click Format button, in the popped out Format Cells dialog, choose one color you like to format the subtotal rows under Fill tab.

doc-highlight-subtotals1

5. And then click OK > OK to close the dialogs, and the entire rows which contain subtotal values have been highlighted if you selected the whole worksheet.

doc-highlight-subtotals1


Highlight all subtotal rows with VBA code

With the following VBA code, we can easily highlight the entire rows containing the subtotal and grand total values.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in 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. Then press F5 key to run this code, and a prompt box will pop out to remind you select your data range.

doc-highlight-subtotals1

4. And then click OK, the subtotal rows are colored by yellow and the grand total row is filled with blue.

doc-highlight-subtotals1

Note: You can format the color you like by changing the ColorIndex in the above code.


Highlight all subtotal rows with Kutools for Excel

This section will recommend you the Select Specific Cells utility of Kutools for Excel. With this utility, you can easily select all rows which contain word "total" in specified column, and then specify a fill color to highlight them.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

1. Select the column contains word "total" you will highlight rows based on. Then click Kutools > Select > Select Specific Cells. See screenshot:

2. In the Select Specific Cells dialog box, please do as follows.

(1) select Entire row option in the Selection type section.

(2) in the Specific type section, select Contains in the drop-down list, enter Total into the box.

(3) Click the OK button

3. Now all subtotal rows are selected immediately. Please specify a background color in the Font group under Home tab in Excel to highlight them.

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.


Easily select and highlight all subtotal rows with Kutools for Excel

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!


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?

  • Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

    • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
    • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
    • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
    • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
    • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
    • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
    • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
    • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
    • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
    kte tab 201905

    Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

    • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
    • Open and create multiple documents in new tabs of the same window, rather than in new windows.
    • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
    officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    GrahamCB · 4 years ago
    Your answer isn't making bold the number that is underpinned by the SUBTOTAL formula
  • To post as a guest, your comment is unpublished.
    GrahamCB · 4 years ago
    Only the text has been made bold - what about the number underpinned by SUBTOTAL formulae? That's what would make the data totals stand out.