Excel: 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.

1. 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.

   

2. 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.