Are you tired of creating and applying formulas to your Excel spreadsheet to get subtotals, totals, sum values, etc.? Use AutoSum in Excel instead!

In today’s tech-savvy world, mastering essential spreadsheet skills is crucial for boosting productivity and efficiency. One such skill is utilizing the powerful AutoSum feature in Microsoft Excel.

Whether you’re a beginner or a seasoned Excel user, this handy tool can save you time and effort when working with numbers. With AutoSum, complex calculations become a breeze as it automatically generates sum formulas for selected ranges.

In this tutorial, I’ll dive into the world of AutoSum, providing step-by-step instructions and insightful tips to help you unlock its full potential. In the end, you can easily streamline your data analysis process. Let’s get started!

What Is AutoSum in Excel?

AutoSum is the built-in command to perform additions for large datasets. When you’re unable to scroll up/ down or left/ right in a huge spreadsheet, you can rely on AutoSum for accurate subtotals and totals. You simply highlight the range for which you need the total value and hit the AutoSum button on Excel. Excel instantly gives you the sum value you’ve been looking for.

Where Is AutoSum in Excel?

At the time of writing, there are three different methods to access the AutoSum feature in Excel. Find all the locations below:

1. You can go to the Editing commands block within the Home tab on the Excel ribbon to find AutoSum.

2. Also, the same AutoSum button is available inside the Function Library commands block on the Formulas tab.

3. Lastly, you can access AutoSum from the Totals tab of the Quick Analysis tool in Excel.

Why Should You Use AutoSum in Excel?

• AutoSum saves time by automatically generating sum formulas for selected ranges of cells.
• It reduces the chances of human error that can occur while manually including cells in a SUM function.
• You can use the same button for other common calculations like Average, Count, Min, Max, etc.
• No need to scroll through thousands of cells to include SUM ranges.
• It’s user-friendly and doesn’t require advanced knowledge of Excel functions to calculate subtotals or totals.
• You can use AutoSum in Excel in both single columns and rows, as well as multiple selections.

How to AutoSum in Excel

Find below several common ways to use the AutoSum tool in Excel:

Use AutoSum to Sum a Column

1. Select the column data from the first to the last value.
2. Click the AutoSum button on the Home tab.
3. Excel will automatically generate the sum of the values in the next empty cell of the last selected value.

If there’s any number in the column header without any text, like the year 2025 or model number 1005, etc., Excel will include that in the summation if you highlight headers. To avoid this, include any text in the headers that contain only numbers or type the accent symbol (`) before the number as the header.

Use AutoSum to Sum a Row

When you need a row-wise subtotal or total value, just select the row from the first to last data. Ensure there’s a blank cell to the right of the farthest highlighted data for summation. Click the AutoSum button and you get your sum value.

Get Subtotals and Totals in a Multitier Table Using AutoSum

AutoSum is particularly useful when you got a multi-tiered table or dataset. You need to create several subtotals and totals in this dataset to visualize the data analysis appropriately. Consider the example below:

In the multitiered dataset above, you’d need to use AutoSum four times and manual sum to create content-wise subtotals, country-wise subtotals, and grand totals. Instead, you can press the Ctrl key and then use the AutoSum button to automatically get subtotals and grand totals. Here’s how:

1. Highlight B4:F7 and press the Ctrl key.
2. Now, go on and highlight B10:F13.
3. Click the AutoSum button to generate continent-wise subtotals of chip sales across manufacturers.

4. Now, highlight B4:G16 cell ranges and hit the AutoSum button on the Home tab.
5. Excel will automatically populate the country-wise totals in column G and the grand totals in row 16.

In the above method, first, you train Excel to identify a pattern by performing one AutoSum to calculate the continent-wise subtotal. Then, you can select the whole dataset and click AutoSum again to get the rest of the subtotals and grand totals. This time Excel predicts what you’re looking for.

The method is highly compatible with advanced Excel desktop apps like Excel for Microsoft 365, Excel 2021, Excel 2019, etc. However, it won’t work on any Excel for the web versions. Furthermore, the trick may not work successfully on dated Excel desktop apps like Excel 2007, Excel 2010, etc.

Use the Quick Analysis Tool for AutoSum in Excel

Another way to apply AutoSum while calculating subtotals or totals in Excel is by using the Quick Analysis tool. Here’s how:

1. Select the dataset for which you’d like to get subtotals or totals column-wise or row-wise.
2. Click the Quick Analysis icon that shows up at the bottom right corner of the highlighted cell ranges. Alternatively, you can press Ctrl + Q keys together to get the Quick Analysis toolbar.
3. In the toolbar, click the Totals tab.
4. Inside the Totals tab, you get two sets of commands for rows (highlighted in yellow) and columns (highlighted in blue).

5. Click Sum for columns to generate summataion column-wise.
6. You can also hit the Sum for rows to get row-wise totals.

Shortcut for AutoSum in Excel

If you’re in hurry and need to find a total in Excel quickly, you can apply the AutoSum function using a hotkey as well. Find below the shortcut key combination and the steps to use it:

1. Highlight the cells for which you need a summation.
2. Hit Alt + = together to get the sum value in the next empty cell.

How to Use Other Functions in AutoSum in Excel

The AutoSum feature on Excel is nothing but a dynamic shortcut key. You can use other quick functions than SUM on your worksheet using thsi feature. By default, the Excel desktop and web app will show the following additional functions in AutoSum when you click the drop-down arrow attached to it:

• Average
• Count Numbers
• Max
• Min

So, if you’d like to calculate the average of the selected cell range quickly, press the following keys in the order of the appearance:

Alt > H > U > A

Excel will generate the average of the selected range in the next empty cell of the same column. Replace A with C for Count Numbers, M for Max, and I for Min.

If you need more functions, click AutoSum > drop down arrow > More Functions or Alt + H + U + F. The Insert Function dialog box will pop up. Here, you can choose more functions, input arguments, and hit OK to apply the formula.

The additional function in the More Functions menu will only work if you select an emapty cell on the worksheet. Then, add the necessary arguments and data in the Insert Function dialog box.

How to Use AutoSum Using Excel VBA

So far, you learned the manual methods to use the AutoSum feature in Excel using either a user interface button or a shortcut key.

What if you’re automating the data analytics process in your Excel worksheet using Excel VBA and need to automate the summation process too. In this scenario, you can use an Excel VBA script. Find below the code and steps to use it:

1. Bring up the Excel VBA Editor by pressing the Alt + F11 keys together.
2. On the VBA Editor toolbar, click Insert and choose Module in the context menu that follows.
3. Inside the new Module, copy and paste the following VBA script:

Sub UseAutoSum()
    Dim LastRow As Long
    Dim rng As Range
    Dim nextCell As Range
    
    'Specify the range for AutoSum
    Set rng = Range("B2:B6") 'Change the range as per your data
    
    'Find the last non-empty cell in the range
    LastRow = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'Select the empty cell below the last non-empty cell
    Set nextCell = rng.Cells(LastRow + 1)
    
    'Apply AutoSum formula
    nextCell.FormulaR1C1 = "=SUM(R" & rng.Row & "C:R" & LastRow & "C)"
End Sub

4. Click the Save button, choose Yes on the warning message, and close the VBA Editor.

5. Now, hit Alt + F8 together to open the Macro dialog box.
6. Choose the UseAutoSum macro and hit the Run button.
7. You should find the sum value just below the cell ranges entered into the VBA code.

You need to modify the data reference cell ranage which is "B2:B6" to get the sum of your own dataset in the worksheet.

Since the above script only works column-wise, you can use the following code to use AutoSum in row-wise:

Sub UseAutoSum()
    Dim rng As Range
    Dim sumCell As Range
    
    'Specify the range for AutoSum
    Set rng = Range("A2:F2") 'Change the range as per your data
    
    'Select the cell for the sum
    Set sumCell = Range("G2")
    
    'Apply AutoSum formula
    sumCell.Formula = "=SUM(" & rng.Address & ")"
End Sub

Also, don’t forget to modify the input cell range "A2:F2" and destination cell reference "G2" according to your own dataset.

Use AutoSum Using Excel Automate or Office Scripts

Office Scripts feature is only available on Excel for Microsoft 365 and Excel for the Web app. Also, you need Microsoft 365 Business Standard or better subscription to use this feature. If you’ve got the Automate tab in your Excel desktop or web app, follow these steps to automate the usage of AutoSum:

1. Click the Automate tab on the target worksheet.
2. Select New Script command in the Scripting Tools menu.
3. Copy and paste the following Office Scripts code inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range B7:F7 on selectedSheet
	selectedSheet.getRange("B7:F7").setFormulasLocal([["=SUM(B3:B6)","=SUM(C3:C6)","=SUM(D3:D6)","=SUM(E3:E6)","=SUM(F3:F6)"]]);
	// Set range G3:G6 on selectedSheet
	selectedSheet.getRange("G3:G6").setFormulasLocal([["=SUM(B3:F3)"],["=SUM(B4:F4)"],["=SUM(B5:F5)"],["=SUM(B6:F6)"]]);
}

3. Modify the cell references according to your own data as outlined below:
   • B7:F7: is the destination for sum values vertically (column-wise)
   • B3:B6, C3:C6, etc., are the cell ranges for column-wise input data
   • G3:G6: is the destination for sum values horizontally (row-wise)
   • B3:F3, B4:F4, etc., are cell ranges for row-wise input data
4. Click the Save script button when modification is done.
5. Hit the Run button to let Excel automatically AutoSum the input data horizontally and vertically.

Shortcomings of AutoSum in Excel

• AutoSum will stop if it encounters any blank cells or cells with text values within the selected area in your worksheet.
• AutoSum will generate zero value or error when you try to sum text values. You must convert all the cell values to the Number format before working with AutoSum.
• If your dataset has headers and headers contain numbers like years, model numbers, stock-keeping units (SKU) numbers, etc., AutoSum will include that in the calculation.
• You can’t edit the cell ranges in an AutoSum.
• AutoSum depends on the accurate selection of cell ranges. When the cells have inconsistencies, AutoSum may not work or generate incorrect values.
• When you filter a dataset or table, AutoSum no longer works. It uses the SUBTOTAL function in place of the SUM function.

Conclusions

Now you should know how to use the AutoSum feature in Excel.

If you’re working on subtotaling and totaling, you can definitely use this powerful tool of Excel to save a lot of time entering formulas manually.

When working with longer columns and rows, use AutoSum along with shortcuts, like Ctrl + Down Arrow for columns and Ctrl + Right Arrow for rows to generate totals or subtotals instantly.

Do you use the AutoSum feature in Excel? Let me know in the comments!