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

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

Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.