Software

Make subtotal values stand out in Excel

When using Data | Subtotals to summarize data in your Excel worksheets, the more columns there are between the resulting subtotal labels and their values, the harder it is to read the data. Here's a step-by-step process that will make it easier to read the summarized data.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

When Excel's Data | Subtotals menu is used to calculate subtotals and grand totals in a list, Excel displays the resultant grand total and subtotal headings in bold; however, their associated values are not reformatted.

If there are a number of columns between the subtotal labels and their values, you may need to reformat the values manually to make the worksheet easier to read. Or, you can set conditional formatting that will automatically reformat the values for you.

For example, suppose you want to subtotal employee payroll worksheet data by employee and gross pay per month. You may prefer for the subtotals to appear bold and underlined, while the grand total is bold and double underlined.

Before running Data | Subtotals on the worksheet, follow these steps:

  1. Open the worksheet and select the raw data.
  2. Select Format | Conditional Formatting.
  3. Under Condition 1, select Formula Is.
  4. Press [TAB] and enter the following code:
    =$A1="Grand Total"
  5. Click the Format button.
  6. On the Font tab, select Double in the Underline drop-down menu and select Bold in the Font Style drop-down menu.
  7. Click OK and then click the Add button.
  8. Under Condition 2, select Formula Is.
  9. Press [TAB} and enter the following code:
    =Right($A1,5)="Total"
  10. Click the Format button.
  11. On the Font tab, select Single in the Underline drop-down menu and select Bold in the Font Style drop-down menu.
  12. Click OK twice to exit both dialog boxes.

When you run Data | Subtotals to obtain the gross pay subtotals and grand totals, both the labels and the total values for each employee and grand total will now be formatted.

Editor's Picks

Free Newsletters, In your Inbox