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

Excel 2020: Format or Copy the Subtotal Rows

April 09, 2020 - by

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

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