How to Use Autosum in Microsoft Excel

Autosum in Microsoft Excel

AutoSum is a special tool in Excel that allows you to quickly calculate the SUM of numbers within a worksheet. You can access the AutoSum button from the Home – Editing group or Formulas – Function Library group. By clicking the little arrow on the right of the AutoSum button, you can access many other types of functions, such as AVERAGE, MAX, MIN, and COUNT. Simply select one of these functions to apply an appropriate formula to your data.

How An AutoSum Works

Whilst a cell on your Excel worksheet is active, click on the AutoSum button to trigger Excel to analyze the data around the active cell. The system will use the analysis to propose a SUM formula. Check if the range proposed by Excel is accurate, and then press Enter to accept and apply the formula. But if Excel has guessed your range incorrectly, you can manually select the range to sum up and press Enter. You can also press Esc to cancel and start over again by highlighting the range using your mouse.

Top AutoSum Tricks

Here are the top auto sum tricks you can work around with on your Excel worksheet and make summing quick and simple regardless of the number of rows or columns you’re working with:

1: Summing Down Columns and Across Tables

To enter a similar SUM formula into a range of cells, simply select the entire range and then click the AutoSum button. To sum down a column or across a table, select the range of numbers and any additional column or row. Next, click the AutoSum button, and the system will insert the formulas that automatically add the rows and columns.

Unless you apply an alternative number format to the cell that holds the SUM formula, AutoSum typically applies the same number format as the first cell in the range. However, you can still create a SUM formula that utilizes only certain values in a column. To do that, select the cells to be summed up and click the AutoSum button. Microsoft Excel will then automatically insert the SUM formula to the first empty cell under the selected range. However, you need to ensure the range selected is continuous.

2: Auto Sum with Function Keys

Whenever you want to sum numbers horizontally or vertically, just select the cell and press “Alt =.” The auto sum function will be applied to the worksheet, and guess the range you wish to total. If the range highlighted is correct, press “ENTER.” If it’s not, you can highlight the range manually and press “ENTER.”

To auto sum multiple cells, select cells that you want to total and use the shortcut “Alt =,” and the sum automatically appears.

3: Total of Filtered Rows

If you want your total to update when you filter rows, use the SUBTOTAL function as opposed to the SUM function.

For example, you can press “=SUBTOTAL(9, Range_to_total)”

The number 9 means that only the visible rows will be summed when the filter is applied.

4: Summing Visible Rows

When working with manually hidden rows, you can use this formula “=SUBTOTAL(109, Range_to_total).”

The number 109 means that only the visible rows will be summed, particularly when the rows are hidden manually and not with a filter.

5: Getting Grand Total of Sub Totals

If you want to take a Grand Total of several subtotals, you can use the subtotal formula in all applicable ranges. Note that you’ll use subtotals to get both Emp Level sum Grand Total, which helps to avoid totaling the cells twice.

6: Getting Running Totals

To get running totals, just freeze (lock) the first portion of the Sum range on your worksheet and then drag the formula all the way down.

7: Auto sum Around a Range

Start by selecting the entire range with a single row and a single column extra.

Use the shortcut “Alt =” to instantly get the totals across the range.

8: Ignoring Errors in a Sum Range

If you want to ignore error values, you can simply use the Aggregate function. In Excel, number 9 means summing while number 6 means ignoring error values.

9: Using AutoSum Button to Insert other Functions

Here are common functions you can execute using the AutoSum Button:

• AVERAGE – This function returns the average (arithmetic mean) of a range of numbers.
• COUNT NUMBERS– This function counts the total number of cells with numbers.
• MAX – This function returns the largest number.
• MIN – This function returns the smallest value.

To get the desired results from these functions, simply click on the blank cell where you want to populate the result, click the AutoSum drop-down arrow, and finally choose your desired function from the list.

10: Organizing Data to Auto Sum

You can always organize tables in your Excel sheet by simply pressing the Ctrl+T shortcut. After organizing the data, press the AutoSum Button instead of the SUM function to insert the SUBTOTAL function automatically. However, only visible cells are added to the SUBTOTAL function, while cells hidden using the filter method are ignored.

Now, if you want to sum numbers in several rows and columns at once, select the cells where you want to input the AutoSum formula and click the AutoSum button on the Excel ribbon. For instance, if you want to get the sum of values in cells A8, B8, and C8, just select A8, B8, and C8, then click AutoSum. You’ll notice that the values in each of the three columns are summed individually.

Need Help With Microsoft Solutions?

So, how many tricks did you know already? Feel free to share your most helpful AutoSum tricks and other unique variations. At K² Technologies, we provide Microsoft solutions and Microsoft consulting services for organizations throughout the United States. Our CEO, Dan King, is one of the top thought leaders in Microsoft Office 365 and multiple other Microsoft Cloud technologies. If you’re a small or midsize organization seeking Microsoft solutions to spur business growth, K² Technologies is your ultimate technology partner. Contact us today to learn more about Excel and other Microsoft solutions.