AutoSum in Excel (Complete Guideline)

In this article, you will learn all things you need to know about AutoSum in Excel.

AutoSum means the process of automatically writing formulas. Instead of typing complex formulas, you can simply select a range and let AutoSum do the work.

It eliminates the need for manual calculation. It is particularly useful when dealing with large datasets or long columns/rows of numbers.

Excel has a special AutoSum feature that inserts the SUM  formula immediately. We will discuss different use cases of AutoSum features in Excel.

Download Practice Workbook

Apply AutoSum Feature in Excel

To apply the AutoSum feature:

• Go to the Home tab > click AutoSum feature under the Editing group.
• This will insert the SUM formula immediately.

• Press ENTER to show the sum.

Keyboard Shortcut for AutoSum Feature

To apply the AutoSum feature with the keyboard shortcut, just press CTRL+=.

This will act the same as applying AutoSum from Excel ribbon.

Use AutoSum Feature for Other Excel Functions

Users can use the Autosum feature for some other Excel functions.

Navigate to the Home tab from the ribbon and Click the drop-down of the AutoSum icon. Excel will show you a list of functions with this button. Choose the function that you need for your calculation.

AutoSum Filtered Cells in Excel

AutoSum inserts the SUBTOTAL function for filtered cells.

• Click the dropdown arrow at the header of the columns that contain the filter feature.

• Choose the option on which you want to apply the filter  > click OK.

• After filtering cells, apply the AutoSum feature. You will see that Excel has inserted the subtotal function. So, it will find the sum of only the filtered cells.

AutoSum for Multiple Cells in Excel

1. Apply AutoSum Feature Vertically

AutoSum feature is also available on the Formulas tab. To apply the AutoSum feature vertically for multiple cells of columns:

• Select the range of cells > go to the Formulas tab > click AutoSum feature.

• This will calculate the sum of the respective columns in these multiple cells at once.

2. Apply AutoSum Feature Horizontally

To apply the AutoSum feature horizontally for multiple cells of rows:

• Select the range of cells right beside the rows and apply the AutoSum feature.

• Like the previous method, this will calculate the sum for rows immediately.

Excel AutoSum Not Working

The main reason behind Excel autosum not working is because of the formatting of numbers. If the numbers are stirred as text, the sum won’t take the numbers as an argument. In that case, the cells containing numbers will show a green triangle at the top left corner.

The way to solve this problem is to click on that triangle and select Convert to Number for the dropdown options. After fixing it, the AutoSum feature will work spontaneously.

Frequently Asked Questions

1. What are the different types of AutoSum?

AutoSum offers several variations to perform different types of calculations based on your data like: SUM, AVG. MAX, MIN, COUNT etc.

2. Why is AutoSum using subtotal?

If you have a filtered range of numbers and you use AutoSum, Excel will insert the SUBTOTAL function with the function number 9 (SUM) as the first argument. This ensures that the sum is calculated only for the visible (unfiltered) cells in the range, excluding any hidden rows

3. Why does AutoSum show 0?

The reasons behind AutoSum returning 0 are: empty cells, formatting issues, incorrect range selection, error values, hidden or filtered data etc.

Things to Remember

• AutoSum simplifies calculations in Excel by automatically generating formulas for basic operations.
• The default function in the AutoSum feature is the SUM formula.
• AUtoSUm inserts the SUBTOTAL function for a range of filtered cells.
• AutoSum does not work for cells that contain values stored as text.

Conclusion

Throughout the article, we have tried to discuss various aspects of AutoSum in Excel. This feature is greatly helpful for inserting formulas immediately and speeding up calculations in Excel.

AutoSum saves time and reduces the chances of errors. It is a valuable feature for data analysis, collaboration, and learning, as it promotes consistency, accuracy, and ease of use.

AutoSum in Excel: Knowledge Hub

<< Go Back to How to Sum in Excel |How to Calculate in Excel | Learn Excel