Excel Hacks Ebook [Electronic resources] نسخه متنی

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.