MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


April 04, 2019 - by Bill Jelen

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


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.