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

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

April 08, 2020 - by

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

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