March 25, 2011, 10:31 p.m.
posted by donnie
Boldface Excel Subtotals
Wouldn't it be great if you could identify the subtotals in your worksheets so that you can find them easily? With the hacks in this section, you can.
When you are working with a spreadsheet that has subtotals you created by selecting Data Subtotals, 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.
To test the problem, set up some data similar to that shown in Figure.
In 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.
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 Figure as an example, try this before adding your Subtotals. Select cell A1:B9, ensuring that A1 is the active cell. Select Format Conditional Formatting..., select Formula Is, and then add the following formula:
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 started 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 Figure.
One last thing to remember is that if you remove the subtotals, the boldfaced font will no longer apply.
Hacking the Hack
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 stand outs from the Subtotals and is identified more easily. You can do this using the same example.
Click the Format button and then the Font tab, and select Bold as the Font Style. Click OK, and then click Add to add a second format condition. Select Formula Is and add the following formula:
Click the Format button and then the Font tab. On this tab, select Bold Italic as the Font Style. Select Single from Underline, click OK, and then click OK again.
Next, select Data Subtotals, accept the defaults, and click OK. Your worksheet data should now look like Figure.