Excel Totals, Subtotals, and Grand Totals Explained

The ability to add dynamic subtotals and totals into an Excel worksheet is a massive time saver. It’s particularly useful for finance and accounting use cases but fundamental to much of the analysis performed in spreadsheets. Fortunately, Excel provides several different methods to create them.

Learning the different methods to calculate subtotals and grand totals in Excel can be a big time saver. We explain the different methods.

We’ll explain the different ways to create totals and subtotals from use of the SUM formula, SUBTOTAL formula, how to quickly view totals without a formula, and keyboard shortcuts to help you build out your Excel spreadsheets more efficiently.

Let’s jump in!

Excel Sum Vs Subtotal

Understanding the difference between ADDITION, SUM and SUBTOTAL is crucial for using Excel efficiently. The most common starting point for people new to Excel is using the addition sign to manually add rows together. However, what happens when a new row is added and you forget to update the formula?

SUM will add all numbers in a range regardless which is better, but then you have to add all of the subtotals created with a SUM to get to get a grand total.

The SUBTOTAL function goes a step further and allows people to dynamically add values together with subtotals and grand totals being calculated for all values in the same column. We’ll cover each one of these methods in Excel so you can work quicker.

Calculating Excel Subtotals with Addition

One of the most common ways to create Excel Subtotals is by using the + symbol to add each individual cell in a row. While this method is effective, it is not as dynamic as SUM or SUBTOTAL and can lead to errors when new rows are added into a data set and you forget to update the cell reference in each formula.

Using standard addition formulas to add individual cells together for a subtotal

This leads us to our next option, the SUM formula.

Calculating Excel Subtotals with SUM Formulas

The SUM formula can be used to add all of the values in a defined range. You can insert rows in the middle of the dataset and the formula will automatically adjust the range. This helps you avoid errors when data above the SUM range is updated and eliminates the need to update manual formulas.

Using the SUM formula in Excel to calculate a subtotal for a range of cells

The issue with this method is when you want to calculate a grand total from a total. The example below highlights the most common method. A SUM formula to calculate subtotals, and a + operator to add the two subtotals together for a total.

Using an addition formula to add two sum formulas together for a grand total

This method is very useful in our example, but not as useful if you have 20 different groups of subtotals where categories tend to change or move around. The use of addition combined with SUM formulas is only recommended for small data sets that are not likely to significantly change over time.

Calculating Excel Subtotals and Grand Totals with the SUBTOTAL Function

The Subtotal function in Excel offers a dynamic way to calculate Subtotals and Grand Totals. It’s similar to the SUM function in that it can add a range of cells together, but it is smart enough to avoid other subtotals in the same range, unlike a SUM formula.

This example shows a SUBTOTAL formula being used to add a range of cells together.

Using the Excel SUBTOTAL formula to add all values in a range.

To use the SUBTOTAL function, you first define the aggregation method. Then you define the cell range that you want to apply the aggregation method to.

SUBTOTAL differs from SUM in that you can tell it to COUNT, AVERAGE, or SUM along with a number of other functionalities.

  • 1 – Average
  • 2 – COUNT
  • 3 – COUNTA
  • 4 – MAX
  • 5 – MIN
  • 6 – PRODUCT
  • 7 – STDEV.S
  • 8 – STDEV.P
  • 9 – SUM

There are more, but 9 is going to be the one that most people use often, it tells the SUBTOTAL function to add all of the numbers in range like a SUM formula.

The following is an example of SUBTOTAL formulas being used to calculate Subtotals along with a Grand Total that’s in the same column.

Using a SUBTOTAL formula to add all values, ignoring other SUBTOTALS to calculate a Grand Total

The screenshot shows a SUBTOTAL being used on an entire column. This is highly valuable because you can insert additional rows of data in the middle of a category and each subtotal will calculate appropriately, and the Grand Total will also calculate appropriately.

Excel SUM vs SUBTOTAL for Grand Totals

Use the SUBTOTAL formula to calculate Grand Totals in Excel with the syntax =SUBTOTAL(9,<Cell Range>). 9 defines the SUM aggregation method, and the cell range are values to add. SUBTOTAL excludes other SUBTOTAL formulas giving users a true Grand Total, where a SUM would duplicate values.

The example below shows SUBTOTAL formulas being used for each section’s subtotal.

Example to compare the calculation differences between SUM and SUBTOTAL when calculating Grand Totals in Excel with other subtotals in the cell range

The grand total calculation at the bottom of the spreadsheet show how a SUBTOTAL and SUM calculate values in the same column. A SUM duplicates the true Grand Total value because it adds the section subtotals along with the individual values while SUBTOTAL provides a true Grand Total.

To learn more about the differences between SUM and SUBTOTAL, check out our comprehensive guide.

Filtering With SUBTOTALS in Excel

The Excel SUBTOTAL formula can be used when filtering datasets to only show the Subtotals and Grand Totals of the filtered data, where a standard SUM formula would provide the total of all rows even though they are not visible which can lead to inaccurate analysis.

The following example shows SUBTOTAL formulas used for Subtotals and Grand Totals as it automatically re-calculates based on the filter context applied to the worksheet.

A filter being applied to categories that have SUBTOTALS and a Grand Total with the SUBTOTAL function dynamically calculating for visible cells only

Other than for quick calculations, SUBTOTALS are our go to method for calculating Subtotals and Grand Totals due to how dynamic they are. Data has a way of changing over time and SUBTOTALS help you avoid errors and manual updates to your workbooks.

Automatically Add Totals to Excel Tables

To create grand totals in Excel Tables automatically enable the Total Row function. by going to the Excel Ribbon, Table Design, Table Style Options, and select Total Row. (CTRL+SHIFT+T) and a grand total row will be added to the bottom of an Excel Table.

Adding a Total Row automatically to an Excel Table

This method only works with Excel Tables, for data that is not defined in a table you will need to manually add, sum, or subtotal the values.

Viewing Quick Totals in Excel without Formulas

A way to save time in Excel is to instead of adding a SUM or SUBTOTAL formula, you can select a range of values and a total will be displayed at the bottom of the Excel application window along with an average and count of rows.

This method won’t work for presenting data but is a big time saver for performing quick analysis on values.

How to see totals of selected cells without using a formula using the quick view bar in Excel.

Give it a try next time you’re working on a spreadsheet or reconciliation and need a quick check to see if values tie out. You can also use CTRL + Left-Click to multi-select individual cells and see their total at the bottom of the Excel window.

Subtotal and Total Formatting in Excel

Subtotals and Grand Totals can be formatted in Excel using the Format Options under the Font section of the Excel Home Tab or by right clicking on a cell and applying quick formatting. Subtotals are often designated with a single line while Grand Totals are double underlined.

The format options available in Excel to format Subtotals and Grand Totals.

These same options can be accessed even faster by right clicking on a cell for the formatting quick options menu.

Grand Totals being calculated in Excel and being formatted using the quick format options menu

To quickly apply formatting to many different subtotals or grand totals, use the Format Painter to quickly copy and paste cell formatting from one cell to multiple cells.

Excel Keyboard Shortcuts for SUM

Leveraging keyboard shortcuts can significantly speed up your workflow in Excel. For instance, Alt + = quickly inserts a SUM formula to add the values above the selected cell. The keyboard shortcut for the Excel formula is so coinvent it prevents us from always using a SUBTOTAL.

Conclusion

Knowing the different ways to calculate subtotals and grand totals in Excel can be a big time saver. Excel provides a number of different methods, each with their own nuances and ideal use cases. Our go to method for larger workbooks is the SUBTOTAL formula, but often find ourselves using a SUM for quick analysis using the ALT + = keyboard shortcut.

When we’re performing quick analysis, we may even skip the SUM and SUBTOTALS all together and select the column or row of cells we’re interested in and view the quick total at the bottom of the Excel window.

Scroll to Top