Written by Allen Wyatt (last updated August 21, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
When you add subtotals to a worksheet, Excel automatically formats the subtotals using a bold font. You, however, may want to have some different type of formatting for the subtotals, such as shading them in yellow or a different color.
If you use subtotals sparingly, and only want to apply a different format for one or two worksheets, you can follow these general steps:
Figure 1. The Go To dialog box.
Figure 2. The Go To Special dialog box.
If you will be repeatedly adding and removing subtotals to the same data table, you may be interested in using conditional formatting to apply the desired subtotal formatting. Follow these steps:
Figure 3. The New Formatting Rule dialog box.
Figure 4. The Format Cells dialog box.
When following the above steps, make sure that you replace A1 (steps 7 and 14) with the column in which your subtotals are added. Thus, if your subtotals are in column G, you would use G1 instead of A1.
If you need to format subtotals on quite a few worksheets, then you may want to create a macro that will do the formatting for you. The following macro examines all the cells in a selected range, and then applies cell coloring, as appropriate.
Sub FormatTotalRows() Dim rCell as Range For Each rCell In Selection If Right(rCell.Value, 5) = "Total" Then Rows(rCell.Row).Interior.ColorIndex = 36 End If If Right(rCell.Value, 11) = "Grand Total" Then Rows(rCell.Row).Interior.ColorIndex = 44 End If Next End Sub
The macro colors the subtotal rows yellow and the grand total row a darker shade of yellow. (The exact colors on your system may vary depending on the theme you have loaded.) The macro, although simple in nature, is not as efficient as it could be since every cell in the selected range is inspected. Nevertheless, on a 10 column 5000 row worksheet this macro runs in under 5 seconds.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8110) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Formatting Subtotal Rows.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
When you enter information into a row on a worksheet, Excel automatically adjusts the height of the row based on what you ...
Discover MoreYou may have a need to increase the height of the rows in your worksheet to "spread out" the data when it is printed. ...
Discover MoreWhen you format a cell so that the information within it can wrap to multiple lines, you may be surprised if Excel ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-03-09 17:39:29
Ron
If you are taking the Conditional Formatting approach, instead of using FIND, you might want to use SEARCH instead. That way it won't be case-sensitive. With =ISNUMBER(FIND("Grand Total",$A1)), it will only format cells containing "Grand Total" spelled with initial capitals. However, if you use =ISNUMBER(SEARCH("Grand Total",$A1)), it will format any cell with "Grand Total" or "GRAND TOTAL" or "GRAND Total", ... or even "GrAnD ToTaL". You should also note that both FIND and SEARCH equate to "contains". So you can FIND/SEARCH for just "Total" an it will format any cells with "Subtotal" or "Grand Total" or "Total for ...", etc.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments