Add Other Text to the Subtotal Lines
September 29, 2022 - by Bill Jelen
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.
Strategy: If you never plan on showing the detail rows, start at the #3 view. Select B2 and do Home, Insert, Cells, Shift Cells Down, OK. When you display the #2 view, it will look perfect.
Alternate 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.
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.
This article is an excerpt from Power Excel With MrExcel
