SELECT 100 COLUMNS IN SUBTOTALS

Gotcha: This method works only if all the customers are totaled. A manager who doesn’t know how to use subtotals might be the kind of manager who doesn’t total the customers with only one detail line. Below, line 9 will cause the total to not work.

Figure 658 If someone is manually adding totals, he might not add a redundant total for row 9.

Figure 657 Sum and divide by 2.

ADD OTHER TEXT TO THE SUBTOTAL LINES

Problem: My data set has account number in column A and a customer name in column B. When I subto- tal by account and collapse using the 2 Group & Outline button, I see only the Account numbers. While I have memorized that B4504 is Bell Canada, my manager cannot seem to remember this, so I need to add the customer name to the subtotal lines.

Figure 659 Add customer name to the subtotal rows.

Strategy: To add the customer name to the subtotal lines, you follow these steps:

1. Collapse the report by clicking the small 2 Group & Outline button above and to the left of cell A1. 2. Select all the blank cells in column B by using the mouse to drag from B6 down to the cell above the

Grand Total row. In doing so, you will select all the cells in the range B6:B136. 3. Type Ctrl+; to select the visible cells only. (Ctrl and Semicolon)

4. Note the row number of your first subtotal row. In this example, the first subtotal is row 6, and you will write a formula to copy the total from row 5. Change the cell reference in the following formula to point to the row above your first subtotal row: =“Total ”&B5. To enter a similar formula in every selected cell, press Ctrl+Enter.

Figure 660 Add a customer name to each subtotal row.

Gotcha: Step 3 to select the visible cells only is important. If you fail to do this, you will overwrite all cus- tomers from row 6 to the bottom with Total Total Total.

If you see this, you need to immediately press Ctrl+Z to undo.

Gotcha: This trick gets the last customer name. If you need to get the first customer name from the group, you are going to have to use a clever trick and a three-line macro. Search YouTube for Learn Excel 712 for the details.

SUBTOTALS BY PRODUCT WITHIN REGION

Problem: I want to add subtotals by two fields, such as Product and Region.

Strategy: Adding subtotals by two fields seems easy, but there is a trick to it. You need to add subtotals to the least detailed field first. Here’s how it works:

1. Sort by product within region. Select a cell in the Product column. Click the AZ icon on the Data tab. Select a cell in Region. Click AZ.

2. Select Data, Subtotal and add a subtotal by Region. 3 3. Select Data, Subtotal again. Change Region to Product. Be sure to uncheck the Replace Current

Subtotals box.

Figure 661 Uncheck Replace Current Subtotals.

Results: You now have two sets of subtotals. There are now four Group & Outline buttons to the left of cell A1.