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.
- Add customer name to the subtotal rows.
Strategy: To add the customer name to the subtotal lines, you follow these steps:
- Collapse the report by clicking the small 2 Group & Outline button above and to the left of cell A1.
- 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.
- Type Ctrl+; to select the visible cells only. (Ctrl and Semicolon)
- 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.
- 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 YouTube for Learn Excel 712 for the details.