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

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

Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.