Excel Hacks Ebook [Electronic resources]

Raina Hawley, David Hawley

نسخه متنی -صفحه : 136/ 57

Hack 35 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 2-19.

Figure 2-19. Worksheet data before adding subtotals

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

In Figure 2-20, 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.

Figure 2-20. 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 Figure 2-19 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:

=RIGHT($A1,5)="Total"

Now click the Format button and then the Font tab, and select Bold as the Font Style. 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 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 2-21.

Figure 2-21. Worksheet data after subtotals have been formatted

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.

Starting with your raw data, select cell A1:B9, ensuring that A1 is the active cell. Now select Format Conditional Formatting.... Select Formula Is and then add the following formula:

=$A1="Grand Total"

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:

=RIGHT($A1,5)="Total"

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 2-22.

Figure 2-22. Worksheet data with more prominent grand total

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