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.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
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:
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:
2. And then select these displayed subtotal rows, and click Home > Find & Select > Go To Special.
3. In the Go To Special dialog box, check Visible cells only option, see screenshot:
4. Then click OK button to close the dialog, and only the subtotal rows have been selected.
5. And then you can format them with a background color as you like by clicking Home > Fill Color.
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:
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:
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:
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.
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.
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.
4. And then click OK, the subtotal rows are colored by yellow and the grand total row is filled with blue.
Note: You can format the color you like by changing the ColorIndex in the above code.
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.
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.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 years agoYour answer isn't making bold the number that is underpinned by the SUBTOTAL formula
To post as a guest, your comment is unpublished.· 4 years agoOnly the text has been made bold - what about the number underpinned by SUBTOTAL formulae? That's what would make the data totals stand out.