How Do I Use Subtotals and Totals in an Excel Spreadsheet?

While calculating totals in Excel 2013 is usually just a matter of clicking the AutoSum button, calculating subtotals and totals is just a bit more complex, especially for beginners. Begin by calculating the subtotals first -- then you can plug in the Total using the SUM formula directed to each Subtotal cell.

Video of the Day

Open the Excel worksheet containing your data. Insert a row below each group of data that will be presented as a subtotal. In a cell on the same row, type "Subtotal" to mark the column. You may want to add a different background color to the row or use bold text to make the subtitle more prominent.

Four subtotal rows have been added to calculate quarterly results.
credit: Screenshot courtesy of Microsoft.

Click the first Subtotal cell. Click the Formulas tab, then click AutoSum. If the numbers you're adding are directly above the subtotal cell, Excel automatically adds them for you. Note that the selected cells are highlighted with a dotted line. If the cells are in a different column, as may be the case with an invoice, delete the cell numbers between the brackets and then Ctrl-click each value. Press Enter. Examine the number to ensure it's accurate.

This subtotal adds cells B3, B4 and B5.
credit: Screenshot courtesy of Microsoft.

Make every other Subtotal cell a Sum value. To do this quickly, use copy and paste. Right-click the Subtotal that you already calculated and press Ctrl-C. Right click the other subtotal cells and press Ctrl-V. When you copy and paste sums, Excel readjusts the formula to select the appropriate cells for the new position.

Copy and paste the Sum formula to the other subtotal fields.
credit: Screenshot courtesy of Microsoft.

Note that Excel pastes the formatting as well as the formula when you use Ctrl-V. If you right-click the destination cell, you can copy only the formula by selecting the Fx icon under Paste options. In the example here, the Profit column uses decimal values, while the Sales column does not. Using Fx when pasting into the Profit column ensures that not a penny of profit is missing.

Select Fx to paste only a formula.
credit: Screenshot courtesy of Microsoft.

Type "Total" in an empty cell in a row below your last subtotal, and then click the cell where you want the Total value to appear. Since the subtotals aren't all directly above the Total cell, the AutoSum won't work very well. It's faster to simply type the formula. Type "=SUM()" (without the quotes) in the cell where you want the total to display. Place the cursor between the brackets and Ctrl-click each Subtotal cell. Press Enter and then examine the total to ensure it's accurate.

Ctrl-clicking the four subtotals gives us the total annual sales.
credit: Screenshot courtesy of Microsoft.
Show Comments