This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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 subtotal 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 663 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 664 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 customers 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 for Learn Excel 712 for the details.