MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Format or Copy the Subtotal Rows


April 10, 2019 - by Bill Jelen

Excel Format the Subtotal Rows. Photo Credit: Nicholas Punter at Unsplash.com

It is a little odd that Subtotals only bolds the customer column and not anything else in the subtotal row. Follow these steps to format the subtotal rows:

  1. Collapse the data to the #2 view.
  2. Select all data from the first subtotal to the grand totals.
  3. Press Alt+; or select Home, Find & Select, Go To Special, Visible Cells Only).

    The Go To Special dialog offers a choice for Visible Cells Only. The shortcut to select visible cells without opening the dialog is Alt SemiColon.

  1. Click OK. Format the subtotal rows by applying bold and a fill color.

    Now, when you go back to the #3 view, the subtotal rows will be easy to spot.

    The Subtotal rows are now in a contrasting color from the other rows.

Copy the Subtotal Rows

Once you’ve collapsed the data down to the #2 view, you might want to copy the subtotals to a new worksheet. If so, select all the data. Press Alt+; to select only the visible cells. Press Ctrl+C to copy. Switch to a new workbook and press Ctrl+V to paste. The pasted subtotal formulas are converted to values.

Thanks to Patricia McCarthy for suggesting to select visible cells. Thanks to Derek Fraley for his suggestion from row 6.

Title Photo: Nicholas Punter 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.