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

By David Weedmark

Learn how to quickly create subtotals and add them together in Excel 2013 so that when you change any values the subtotals and totals readjust automatically.

Things You'll Need

  • Excel Book
  • Microsoft Excel

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.

Step 1

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.

Step 2

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.

Tip

To quickly verify that a sum is correct, add up only the last digits. If the last digits are 9,3 and 8, then the last digit of the sum has to be 0. If it's not 0, you know that the sum is not calculated correctly.

Step 3

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.

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.

Step 4

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.

Tip

Totals should always be below the subtotals or to the right. Totals never appear above or to the left of a subtotal.