MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: Fill in a Text Field on the Subtotal Rows

April 06, 2020 - by

Excel Fill in a Text Field on the Subtotal Rows. Photo Credit: Jan Antonin Kolar at Unsplash.com

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.

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

    With the data in the #2 view, all of the Sales Rep fields are empty on the Customer Subtotals. Select all of the blank cells. In the Go To Special dialog, choose Blanks.
  2. 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. Type =A48. Instead of pressing Enter, press Ctrl+Enter to enter a similar formula in all of the subtotal rows. In each case, it brings the sales rep from the previous row down.

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

The Sales Rep information in the Subtotal rows now has the value from the previous row, filling in the data as needed.

Title Photo: Jan Antonin Kolar at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.