In Microsoft Excel, you may need to manage larger data sets and add data in data cells. And doing this manually can take a long time, especially if you’re working with huge data sheets. So here’s an Excel tool called AutoSum that can make your work a lot easier and more manageable.
Here’s a quick breakdown of how you can use the autosum in Excel shortcut to simplify your tasks and save time.
What is AutoSum in Excel?
Autosum is a built-in function in Excel that allows us to add numbers in data cells with a single click. In Excel, AutoSum creates a formula to sum numbers on your data sheet.
Where is AutoSum Located?
AutoSum is located in two places in Excel.
In Home Tab
In Formulas Tab
AutoSum Shortcut in Excel
If you prefer to operate with a keyboard and shortcuts rather than a mouse, this function has a keyboard shortcut to make it simpler.
Here’s the AutoSum Excel shortcut:
Alt + =
To activate AutoSum on your selected cells, simply press the Alt button followed by the = button while holding the Alt button.
How to Use AutoSum in Excel?
If you want to add numbers or cells, rows or columns, you can use the AutoSum function, which will automatically create a sum formula for you.
Imagine you have a table of monthly sales for different products in an Excel sheet, and you want to sum up sales of months and products separately.
1. Summing Up a Row (Total Sales for Each Product)
- Click on cell E2 where you want the total for the laptop row.
- Go to the Home tab and click AutoSum (Σ) in the toolbar. You can also use the shortcut key here.
- Excel will automatically select B2:D2 (January to March sales).
- Press Enter to confirm.
- Drag the fill handle (bottom right corner of E2) down to copy the formula for other products.
2. Summing Up a Column (Total Sales for Each Month)
- Click on cell B5 (to find the total for January).
- Click AutoSum (Σ) again.
- Excel will select B2:B4 (all product sales for January).
- Press Enter and repeat for February and March (C5 and D5).
3. Grand Total (Sum of All Sales)
- Click on cell E5.
- Click AutoSum (Σ) again.
- Excel will select E2:E4 (total sales of each product).
- Press Enter to get the total.
Can We Use AutoSum With Other Functions?
Yes, you can use the AutoSum function with other functions such as AVERAGE, COUNT, MAX, and MIN. Instead of entering formulas, the AutoSum button allows you to instantly apply these functions.
First, select a cell where you want to display the result, then click the drop-down option and choose one of the available functions:
- SUM: Adds all selected values.
- AVERAGE: Calculates the arithmetic mean.
- COUNT: Counts how many numbers are in the selected range.
- MAX: Finds the largest value.
- MIN: Finds the smallest value.
Can We AutoSum Only Visible or Filtered Cells in Excel?
Normally, when you use AutoSum, it adds up all the numbers in a column or row, including the hidden ones.
But if you filter your data (hide some rows using filters), AutoSum will only add up the visible numbers, not the hidden ones.
If your data is in an Excel table (press Ctrl + T to make it a table), AutoSum automatically uses the SUBTOTAL function instead of SUM.
SUBTOTAL ignores hidden rows and only adds up the numbers you can see.
Example:
Let’s assume that you have a sales report with Product Names, Regions, and Sales Amounts. You want to sum only the visible sales after filtering a specific region.
First, select your data and then press Ctrl + T to convert it into a table. Now you have to filter your data. First, click on the Region column and then select North.” Now, only rows with “North” in the Region column will be visible.
Now, to AutoSum only visible cells, follow these steps:
- Click on the first empty cell below the “Sales ($)” column (e.g., C10).
- Go to the Home tab.
- Click on the AutoSum button (∑).
- Press Enter, and Excel will automatically enter the formula in the selected cell.
If you change the filter to another region, the total will update automatically.
Can We Use AutoSum On More Than One Cell?
Yes, you can use the AutoSum function on more than one cell by selecting all of the rows or columns where you want to perform the calculation.
As you can see in the image, all of the columns are summed individually at the same time.
AutoSum On Multiple Columns
If you want to sum multiple columns at the same time, simply pick all of them and use the AutoSum button. The AutoSum function automatically sums all columns and returns results.
AutoSum On Multiple Rows
If you want to sum multiple rows at the same time, simply select all of them and an empty column in which it will give results and use the AutoSum button. The AutoSum function automatically sums all rows and returns results in the empty column E.
AutoSum On Multiple Rows And Columns
To sum up multiple columns and rows, simply select them with an empty column at the right and an empty row below the columns.
Excel AutoSum Not Working! Why?
If you are working with Excel, you may encounter the problem of the AutoSum function not working. The most common reason for this is that numbers are formatted as text and appear like numbers, but Excel considers them text strings. As Excel considers them as text, these values are not included in calculations.
So are text strings rather than numbers? The answer is that these numbers represented as text can be recognized by their default left alignment and small green triangles in the top-left corner of the cells.
To convert these text strings, you can simply click on the warning signals for each number one by one, and they will be converted into numbers (by clicking “convert to number”).
If you ever copy data from any external sources or if you place your numbers in double quotes like “123,” you could encounter this issue.
If you do not see any warning signs or green triangles on the cells where you are having trouble, you might check that either of the numbers is quoted in “. After removing the double quotations, you can solve your problem.
Wrap-Up: Autosum in Excel Shortcut
Now that you know how to use AutoSum in Excel, you can save time and effort when working with large data sets. Whether you need to sum columns, rows, multiple ranges, or even filtered data, AutoSum makes it easy with just a click or a simple shortcut (Alt + =).
You also learned that AutoSum isn’t just for adding numbers; you can use it with AVERAGE, COUNT, MAX, and MIN to get even more insights from your data. And if AutoSum isn’t working, checking for text-formatted numbers can help you fix the issue quickly.
