![how to create multiple subtotals in excel 2016 how to create multiple subtotals in excel 2016](https://cdn.ablebits.com/_img-blog/subtotal/sort-source-data.png)
Don’t continue with the next steps if you don’t see the thin white lines. Again, look for the thin white lines appearing between each block of gray in the selection area to confirm that you have successfully selected only the visible cells. Press Alt+ to select only the visible rows. Select the range of blank descriptions in column B. To shift the subtotal formulas to the right, you need to select the blank cells to the left of each subtotal. Select the Grand Total cell and apply a Double Accounting Underline to that cell. To check the formatting, click the #3 Group and Outline button and check that each of the subtotals is in bold text and have accounting underlines. Open the Underline dropdown and choose Single Accounting. (Either right-click the cell and select Format Cells and then navigate to the Font tab, or click the dialog launcher in the Font group of the Home tab of the ribbon to open the Format Cells dialog directly on the Font tab).
![how to create multiple subtotals in excel 2016 how to create multiple subtotals in excel 2016](https://www.contextures.com/images/pivot/pivotsubtotal03.png)
![how to create multiple subtotals in excel 2016 how to create multiple subtotals in excel 2016](https://cdn.extendoffice.com/images/stories/doc-excel/table-add-subtotals-total-row/doc-table-add-total-row-1.png)
To apply a Single Accounting Underline, open the Format Cells dialog and go to the Font tab. The other cells in the selection are gray, but thin white lines surround each cell (or group of cells, in the case of the last subtotal and the grand total).Īpply bold formatting to the subtotals using Ctrl+B. The active cell is white, and the remaining cells in the selection are a single block of gray fill.Īfter Visible Cells Only is selected, the active cell has a thin border. Before, there was a single border around the selection. Look for a change in the selection rectangle after you’ve applied Visible Cells Only (or pressed the shortcut). If you continue the next steps without selecting the visible cells, all of the detailed rows will be formatted along with the subtotal rows.
![how to create multiple subtotals in excel 2016 how to create multiple subtotals in excel 2016](https://www.myexcelonline.com/wp-content/uploads/2021/06/Create-multiple-subtotals-01.jpg)
It’s important to select Visible Cells Only correctly. Alternately, simply press Alt+ as the shortcut. The long way to find this command is Home, Find & Select, Go To Special, Visible Cells Only, OK. Since your goal is to format only the subtotal rows, you need to use the Visible Cells Only command. Select the numbers in column C.Īt this point, any formatting you add will be applied to both the numbers that you can see and the numbers in the hidden rows. The Subtotal command will bold the words “A1001 Total” in column A, but no special formatting gets applied to the numbers in column C.Ĭlick the #2 Group and Outline button to see only the subtotal and grand total rows. The #2 button will collapse the data and show only the subtotals and grand total. New Group and Outline buttons also will appear to the left of column A. But if you have extra columns in the data, you might need to adjust the settings to choose Sum and the correct columns.Ĭlick OK in the Subtotal dialog, and Excel will insert new subtotal rows in the data. In the Subtotal dialog box, the default settings assume you want to use the Sum function to add a subtotal to the right-most column (in this case, column C) at each change in the first column. Make sure to sort the data by Account Number so that all of the rows for each account appear in adjacent rows.įrom the Data tab, select Subtotals. Say that your data has Account Number (such as A1001, A1002, etc.) in column A, Description in column B, and Amount in column C.