Microsoft Excel comes with a lot of features. In this post, we will explain what is is AutoSum and how to use it? With this post’s help, you will learn to automatically sum columns or rows with the Sum shortcut, a sum only visible cells, sum selected range vertically and horizontally in one go, and the common reason for Excel AutoSum not working.
Since the AutoSum is vast, hence we will see it topic-wise. We will cover the following in this tutorial:-
- Where is AutoSum in Excel?
- How to do AutoSum in Excel?
- Excel Sum shortcut.
- How to use AutoSum with other functions?
- How to AutoSum only visible cells?
- Excel AutoSum tips and tricks.
Where is AutoSum in Excel?
For easiness, the AutoSum button is available in 2 locations on the Microsoft Excel ribbon.
Step 1. Home > Editing > AutoSum.
How to do AutoSum in Excel?
If you have to do the sum of a single range of cells, columns, or rows; or several adjacent columns or rows, here Excel AutoSum feature comes in handy, to sum up automatically.
To use the AutoSum feature of Excel, follow these 5 easy steps:-
Step 1. Select a blank cell next to numbers where you want to store the sum’s values.
Step 2. To sum a column, select the blank cell immediately below the column’s last value. Click on the AutoSum button from the Home or Formulas tab. Sum formula will automatically be entered in the selected blank cell, and the cell’s values will be added, which will be highlighted.
Step 3. Now, press the Enter key from the keyboard to get the sum of the selected columns.
Note: By default, it will select all columns upward if there is no blank cell is available in between.
Step 4. In similar ways, to sum a row, select the blank cell to the right of the row’s last number. Click on the AutoSum button from the Home or Formulas tab. Sum formula will automatically be entered in the selected blank cell, and the cell’s values will be added, which will be highlighted.
Step 5. Now, press the Enter key from the keyboard to get the sum of the selected rows.
Note: By default, it will select all rows to the left side of the selected cell if there is no blank cell is available in between.
Bonus Tips: If there is a blank cell and Microsoft Excel does not select the desired rows or columns by default, after pressing the AutoSum button, you can select the desired cell to sum it.
Excel Sum shortcut
How to use AutoSum with other functions?
Apart from adding cells, Excel’s AutoSum button can be used to insert other functions:-
AVERAGE – You can use this function to return the average (arithmetic mean) of numbers.
COUNT NUMBERS– You can use this function to count cells with numbers.
MAX – You can use this function to get the largest number.
MIN – You can use this function to get the smallest value.
To get the desired results from the above function, as usual, click on the blank cell where you want to get the desired result, click the AutoSum drop-down arrow, and choose the desired function from the list.
How to AutoSum only visible cells?
Till now, you have learned to AutoSum the values of rows or columns. But, what will you do if you want to sum only visible cells from the filtered list?
You can always organize the tables in an Excel sheet by pressing the Ctrl+T shortcut. Once you organize the data and press AutoSum, instead of the SUM function, it will insert the SUBTOTAL function by default. Only visible cells will be added in the SUBTOTAL function, and those cells hidden by using the filter method will be ignored.
Excel AutoSum tips and tricks
If you want to sum values in several columns or rows at once, select all the cells where you want to insert the Sum formula, click the AutoSum button on the ribbon, or press the shortcut keys Alt + =.
For example, if you want to get the sum of columns in cells A9, B9, and C9, select A9, B9, and C9, then click AutoSum. The values in each of the 3 columns will be summed individually.