Fill Text on Subtotals

August 02, 2017 - by Bill Jelen

If you need additional text on a subtotal row

Say that each customer in a data set is assigned to a single sales rep. It would be great if you could bring the sales rep name down to the subtotal row. Here are the steps:

1. Collapse the data to the #2 view.

2. Select all of the sales rep cells, from the first subtotal row to the last customer subtotal row. Don’t include the Grand Total row. At this point, you have both the visible and hidden rows selected. You need just the blank rows or just the visible rows.

3. ---

   ---

   At the right side of the Home tab, open the Find & Select dropdown. Choose Go To Special. In the Go To Special dialog, choose Blanks. Click OK.

   

4. At this point, you’ve selected only the blank sales rep cells on the Subtotal rows. In my case, the active cell is A49. You need a formula here to point one cell up. So, as in the figure below, type =A48. Instead of pressing Enter, press Ctrl + Enter. This will enter a similar formula in all of the subtotal rows. In each case, it will bring the sales rep from the previous row down.

   

The results: The subtotal rows show the sales rep name in addition to the numeric totals.

Watch Video

Title Photo: Pezibear / pixabay