Categories
Excel

Format Subtotal Rows Using Conditional Formatting

Wouldn’t it be great if you could identify the subtotals in your worksheets so that you can find them easily? With the technique in this section, you can.

When you are working with a spreadsheet that has subtotals you created by selecting Data » Subtotals (see, Insert Subtotals For a Range of Data), the subtotals can be very hard to identify, making the spreadsheet hard to read. This is true especially if you applied subtotals to a table of data with many columns.

Typically, the resulting subtotals appear on the right, while their associated headings are often in the first column. As the subtotal values are not in boldface, it can be hard to visually align them with their row headings. You can make these subtotals much easier to read by applying bold formatting to the subtotal values and by highlighting the data row.

To test the problem, set up some data similar to that shown in the figure.

Worksheet data before adding subtotals

Now add the subtotals by selecting Data » Subtotals, accepting the defaults in the Subtotals dialog, and clicking OK.

In the figure, the subtotal headings have been boldfaced but their associated results have not. As this table has only two columns, it is not that hard to read and pick out the subtotal amounts.

Worksheet data after subtotals have been applied

The more columns a table has, however, the harder it is to visually pick out the subtotals. You can solve this problem by using Excel’s conditional formatting. Using the table in from figure as an example, try this before adding your Subtotals:

  1. Select cell A1:B13, ensuring that A1 is the active cell.
  2. Go to the Home tab, click Conditional Formatting drop-down, and choose New Rule..., it will opens the New Formatting Rule dialog box.
  3. Select Use a formula to determine which cells to format from the Select a rule type box, and then add the following formula:
=RIGHT($A1,5)="Total"
  1. Now click the Format button.
  2. And then the Font tab, and select Bold as the Font Style.
  3. Next, click the Fill tab and select a color from the Background Color section.
  4. Click OK, then OK again.

The important part of the formula is the use of an absolute reference of the column $A and a relative reference of the row 1. As you start the selection from cell A1, Excel will automatically change the formula for each cell.

For example, cells A2 and B2 will have the conditional format formula =RIGHT($A2,5)="Total“, and cells A3 and B3 will have the conditional format formula =RIGHT($A3,5)="Total“.

Add the subtotals, and they will look like those in the figure.

Worksheet data after subtotals have been formatted

One last thing to remember is that if you remove the subtotals, the formatting will no longer apply.

The only possible pitfall with this method is that the Grand Total appears in the same style as the Subtotals. It would be nice to see the Grand Total formatted in another way so that it stands out from the Subtotals and is identified more easily. You can do this using the same example.

Starting with your raw data, select cell A1:B13, ensuring that A1 is the active cell. Create a second conditional formatting and in step 3, add the following formula:

=$A1="Grand Total"

Click the Format button and then the Font tab, and select Bold as the Font Style. Click the Fill tab and select a different background color. Click OK, and then click OK again.

Next, select Data » Subtotals, accept the defaults, and click OK. Your worksheet data should now look like the figure.

Worksheet data with a more prominent grand total

You can use any format you want to make your subtotals easier to read.

Subtotal

  1. Insert Subtotals For a Range of Data
  2. Format Subtotal Rows Using Conditional Formatting
  3. Make the Subtotal Function Dynamic