AutoSum inserts a SUM formula for you. Select the cell directly below a column of numbers (or directly to the right of a row) and press Alt + =, then Enter — Excel works out the range on its own. The button sits on the Home tab at the right-hand end, and again on the Formulas tab.
Its dropdown also offers Average, Count Numbers, Max and Min, so the same shortcut covers more than totals. Two things worth knowing.
To total a whole table in one go, select the data plus the empty row below it and the empty column to its right, then press Alt + = — Excel fills in every total at once.
And AutoSum guesses the range by looking upwards until it hits a blank cell or a non-number, so a gap in the middle of your data means a short total; always check the highlighted range before pressing Enter. On filtered data it inserts SUBTOTAL rather than SUM, so the total follows the filter.
To use auto sum in Excel, you need to select the cell below or on the right of the values that you want to sum. Once you do that, you can use the keyboard shortcut or use the option from the Home tab on the ribbon. AutoSum is an option to quickly calculate the sum of the values that you have in a range.
AutoSum for a Column
You can use the following steps:
- First, select the cell just below the last cell with a value.
- After that, go to the Home tab, and click on the AutoSum drop-down (in the Editing group).
- Now, select “Sum” from the dropdown.
- Once you select it, it will insert the SUM function in the selected cell by referring to the range above the cell.
- In the end, hit Enter to get the result.
This gives you the same SUM function that you insert manually in a cell.
You can also click the AutoSum button itself (the Σ sign) instead of the drop-down arrow; it inserts SUM straight away. The same button is on the Formulas tab as well.
AutoSum for a Row
If you want to get the total of a row, you need to select the cell at the right of the values (next to the last value).
Now use the same steps that we have used for the column.
In the end, hit Enter to get the sum.
Using the Keyboard Shortcut to AutoSum
You can also use a keyboard shortcut for AutoSum. All you need to do is select the cell and then press the keyboard shortcut Alt + = on Windows, or Command + Shift + T on a Mac.
Use AutoSum for an Entire Table
You can also use AutoSum to add a total row and column for a table in Excel. Select the data along with the empty row below it and the empty column at its right.
And then use the keyboard shortcut Alt + =, or use the AutoSum option from the Home tab. It will get the total for all the columns and rows.
Other Functions in the AutoSum Drop-Down
Apart from Sum, the AutoSum drop-down has Average, Count Numbers, Max and Min. They work in the same way: select the cell below or at the right of the values and pick the function. For example, Average inserts a formula like the one below.
=AVERAGE(B2:B10)
And “More Functions” opens the Insert Function dialog box, where you can pick any other function.
Check the Range AutoSum Picks
AutoSum guesses the range by looking for numbers above (or at the left of) the selected cell, and it stops at the first blank cell or text. So, if your column has an empty cell in the middle, AutoSum only takes the numbers below that blank. Before you press Enter, check the range in the formula, and if it’s wrong, select the right range with your mouse.
When your data is filtered, AutoSum inserts the SUBTOTAL function instead of SUM, so the total only includes the visible rows:
=SUBTOTAL(9,B2:B10)
Frequently Asked Questions
Why does AutoSum return 0?
Usually because the numbers are stored as text, and SUM ignores text. Look for numbers aligned to the left or a green triangle in the corner of the cells, and convert them to numbers (select them, click the warning icon and pick Convert to Number).
Can I use AutoSum on several columns at once?
Yes. Select the empty cells below all the columns and press Alt + =. Excel adds a separate SUM formula to each cell.
How do I see a quick total without a formula?
Select the cells and look at the status bar at the bottom of the Excel window. It shows the Sum, Average and Count of the selection without adding anything to the sheet.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.