MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: An Easier Way to Fill in a Text Field on Subtotal Rows


April 08, 2019 - by Bill Jelen

An Easier Way to Fill in a Text Field on Subtotal Rows. Photo credit: Denise Chan at Unsplash.com.

Kimberly in Oklahoma City and Sarah in Omaha combined to provide a faster solution to getting the sales rep to appear on the Subtotal rows. Provided you only need the data in the #2 Summary View, this works amazingly well:

  1. Click the #3 group and outline button to see all rows.
  2. Select the first sales rep in A2.

  1. Press Ctrl++ and press Enter. In other words, while holding down Ctrl, press the plus sign. This opens the Insert Cells dialog with "Shift Cells Down" selected. Pressing Enter is like pressing OK. This moves all of the sales reps down one row and leaves an ugly gap in A2 and the first row of every other customer.

    This is a quick and dirty hack. If you intention is to never show the detail rows and to only show the subtotal rows, you can shift all cells in the sales rep column down by 1. The first detail row for each customer will not have a sales rep, but the total row will correctly show the sales rep.

    But when you go back to the #2 view, the gaps disappear and the report is correct.

    The gaps in the Sales Rep column do not appear when you use the #2 Group and Outline button.

Title Photo: Denise Chan at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.