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

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

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.

Advertisement

Step 1: Create a Subtotal Row

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.

Advertisement

Video of the Day

Image Credit: Screenshot courtesy of Microsoft.

Step 2: Sum and Verify the First Subtotal

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.

Advertisement

Image Credit: Screenshot courtesy of Microsoft.

Step 3: Copy the Sum Formula to all Subtotals

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.

Advertisement

Advertisement

Image 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.

Advertisement

Image Credit: Screenshot courtesy of Microsoft.

Step 4: Total the Subtotals

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.

Advertisement

Image Credit: Screenshot courtesy of Microsoft.

Video of the Day

Advertisement

Advertisement