Microsoft Excel is a software spreadsheet designed and deployed to manage tabular data, most of which is numbers. Time and again, you might want to add up the numbers to find totals. So, to make things easy, Excel has this inbuilt feature called Autosum in excel.
This article on Autosum in Excel will help you learn the fundamentals of the autosum feature and how to implement it in real-time.
What Is Autosum in Excel?
Autosum in Excel is a built-in summation feature employed to automatically add up the numbers in a few clicks to save time.
Now, in the next section, let us try to practically implement the Autosum feature in excel.
Shortcut for Sum in Excel
Alt+=
The autosum shortcut in excel feature makes adding up several items quick and simple. The Alt+= keyboard shortcut makes using the AutoSum command much quicker. It may be found on the Home or Formulas tabs. Simply hold down the Alt key while pressing the equals sign on your keyboard to activate this shortcut.
How to Implement Autosum in Excel?
Autosum in excel is a simple operation that can be carried out in a few clicks, as shown below.
Consider a sample store data categorized into five different zones and four different quarters, as shown below.
Now let us imagine a use case where we are supposed to calculate the sum of all sales from all the zones in Q1. Also, let us consider another use case, where we will try to calculate the sum of all sales from one zone throughout the year.
We are considering two use cases as we are trying to implement the Autosum feature in excel row-wise and column-wise.
First Use Case.
We will try to calculate the sum of all sales from Q1 from all four zones. Looking at the sample data, we are supposed to have our result in cell "C7".
- Navigate to Cell "C7".
- Select the" Home" tab on the toolbar
- Navigate to the editing group
- Click on the Sigma symbol as shown below
- The Sigma symbol represents summation.
- To avoid any ambiguities, you can always click on the dropdown and ensure the operation selected is "Summation," as shown below.
- Now, after we click on the Sigma symbol, excel will automatically highlight the added cells and implement the summation operation as shown below.
- Now press "Enter," The result is stored in Cell "C7".
- Drag the same cell to all the remaining cells (D7, E7, and F7) to get the results of Q2, Q3, and Q4, respectively.
Second Use Case
Follow the same method as before. Here we needed the sum of all sales throughout the year from individual zones.
- To calculate the yearly sales of East Zone, select cell G2
- Now click on the "Sigma" symbol.
- Excel will automatically highlight the cells from C2 to F2 and the Sum function.
- Press Enter and the result will be saved in G2.
- Drag the same cell through G3, G4, G5, and G6 to get the results of the West, South, North, and Central zones
- The result is shown as follows.
So with that, we have come to an end of this article on autosum in excel.
How to use AutoSum With Other Functions?
Let’s see how to do autosum in excel. Allow Excel to handle your arithmetic whenever you add a column or row of numbers. To total the numbers, choose a cell next to them, choose AutoSum from the Home menu, and then press Enter. Excel automatically enters a formula (using the SUM function) to sum the values when you click AutoSum.
Where is autosum in excel? There are two locations for AutoSum: Home > Formulas and AutoSummaries > AutoSummary.
Instead of repeatedly inputting a formula, you may duplicate it to additional cells after you've created it. For instance, the formula in cell C7 automatically adapts to the new position and computes the values in cells C3–C6 if you transfer the formula from cell B7 to cell C7.
Additionally, you may apply AutoSum to many cells at once. For instance, you may choose cell B7 and cell C7, choose AutoSum, and total both columns simultaneously.
How to AutoSum Only Visible (Filtered) Cells in Excel?
Now, that you know what is autosum in excel, know some tricky parts! You may automatically enter the Subtotal calculation using Excel's AutoSum function. Simply arrange your data in a table (Ctrl + T) or apply the desired filter by selecting the Filter option. Click the AutoSum button on the ribbon after choosing the cell directly beneath the column you wish to total. The column's visible cells will be added using the SUBTOTAL formula, which will be entered.
Excel AutoSum Tips and Tricks
Trick 1:
Simply pick the cell and press Alt whenever you want to total something (horizontally or vertically). The sum function will infer the range you want to total. Enter if the range is accurate. This is the alternative to the excel autosum formula.
Trick 2:
Suppose the equation used is =SUM. (‘J*’!A1) Note a couple of items
- There are two apostrophes after the letter "J," thus it is not a double quotation.
- You can use any other letter instead of "J*." After J, an asterisk denotes anything.
- Excel displays =SUM as the formula is immediately updated when you press enter.
- (Jan!A1,June:July!A1)
- Use the formula =SUM('J*' in the identical case to obtain the sum of a range rather than a single cell.
- A1:A10)
- If you enter this formula in the Sheet with the name Jan, January will be excluded, and only June and July will be considered. Put this formula in a separate sheet for it to function correctly.
Trick 3:
Use the SUBTOTAL Function instead of the SUM function, which is =SUBTOTAL(9, Range to total), if you want the Total to change as the rows are filtered. When applying the filter, the number 9 represents just adding the visible rows.
Excel AutoSum Not Working
Numbers expressed as text are the most frequent cause of AutoSum failing in Excel. Those values may first appear to be regular integers, but Excel views them as text strings and does not factor them into computations.
The default left alignment of the numbers, and the little green triangles in the top-left corner of the cells are the easiest ways to tell if a number has been formatted as text. Select all the troublesome cells, click the cautionary icon, and then click Convert to Number to correct such text numbers.
Numerous scenarios, such as importing data from an external source or putting numerical values in double quotes in your Excel calculations, might result in numbers being represented as text. If the latter, Excel will presume that you wish to print a text string on purpose, and neither green triangles nor the warning sign will display in cells.
Conclusion
"Hyperlinks in Excel" can be your next stop. Hyperlinks can ease up the task of navigating in excel. Whether you want to navigate to a webpage, dashboard, existing document in the local system, or write an email to a friend. All the activities mentioned can be done with a single click using Microsoft Excel.
Are you curious to learn more about Microsoft Excel, receive virtual training, and get certified in the domain of Business intelligence and analytics?
Feel free to go through the Business Analytics certification course offered by Simplilearn. The course offered by simplilearn os is career-oriented and helps you learn according to the current IT standards. Simplilearn's training will help you with the fundamental concepts of data analytics and statistics, enabling you to devise insights from data to present your findings using executive-level dashboards and help you develop data-driven decision-making.
Got any queries for us? Please leave your doubts in the comments section of this "Excel Data Cleaning" tutorial, and our team of experts will be happy to resolve all your questions.
