You might be surprised to know that major use of Excel spreadsheet is only done for summation of large data ranging across all levels of Organizations, Business and Personal Lists.
Adding up of cells can be an important part of data analysis where in your cell contains some sales figure or expenditure or credit/debit bills etc. So, let us start learning summation process of columns in the simplest way.
Assume you have a data record of Students Marksheet as shown below in image
Column A shows the Subject Names and Columns B,C,D,E,F,G shows students marks respectively.
Now to sum up each student marks we will add up the numbers present in respective Columns of student. We will use the below formula in Cell B11 for adding of numbers in Column B
Similarly, for adding the numbers in Column C we will use below formula in Cell C11
Refer below image showing formula usage in Cell B11
Subsequently similar formula can be written for other columns summation where in Column letter will change and Cell range would be from 4:10
Excel takes care of any modification in the cell ranges defined, in this case if there will be any change in students’ marks then the formula would instantly reflect the new total. So, you do not need to type in the formula again and again.
So wasn’t it an easier method to add up the numbers in a column?
Alternative to above method of writing Cell ranges to add up you can also click and define them by below method.
Select the Cell B11 put an equal to symbol and now click on cell C4 then press the + symbol now again click on Cell C5 then + symbol till you reach the end of range.
But you might be wondering what if I have multiple records do I need to type in the formula again and again? Well the answer is No. Excel itself is a smart software having many in built features. You only need to write down the formula once let’s say for Cell B11 only and then hold the bottom right corner of the cell and drag towards Cells – C11, D11, E11, F11, G11.
Refer image showing applied formula in Cell C11
This will copy the summation formula you typed to these cells and auto arrange the reference cells. Which means that Summation of cell C11 will add numbers from the range C4:C10 only irrespective of the fact that the formula was copied from B11 cell defining range of B4:B10.
Isn’t it a smart feature!
Now let’s go to next level – What if I have thousands or Lac’s of records for addition, do I need to type in the addition formula for first column comprising of thousand entries?
Well the Answer is again NO. For handling, large volumes of data addition excel has a built-in function called as SUM. This function can be called by selecting the cell and put equal to symbol followed by SUM with brackets for defining ranges of data to be added.
Now let us use this formula in our previous case. We will select cell B11 and put equal to symbol along with function SUM defining range from B4:B10
On pressing enter Excel will add up the numbers in column B. Similarly, we can use this function for all other Cell ranges to add up the student’s marks. Alternatively, you can also drag the formula to all relevant cells for auto summation.
Another interesting feature of addition function in Excel is that you can define any range while writing the formula or using the SUM function. Refer below image for example.
Cells ranges have been defined from each of the columns and the Excel adds up each cell value as desired.
Excel gives another smart feature of Auto Sum for those who find typing formula cumbersome or difficult.
To use the Auto Sum feature, select the cell you wish to calculate the sum in and then click on Auto Sum button present under Home Toolbar (highlighted). Function of SUM will be automatically applied to the given cell for entire table range.
If you want to select specific cells in a column for addition, then select those cells and then press Auto Sum button.
If you want to apply the Auto Sum function to complete numeric data, let’s say in our case we need to calculate the Total Marks of all students at once then select Cell ranges from B4 to G10 and press the Auto Sum button.
All columns will display the total marks below the selected range cells i.e. in Cell Range B10:G10
Hope you understand this feature now and do not forget to try out yourself for sample cases as described in the post.
Please comment for any queries you have or any valuable suggestions.