Write Basic Formulas in Excel
Formulas are an integral part of excel, and as a new learner of excel, one doesn’t understand the importance of formulas in excel. However, all the new learners know there are plenty of built-in formulas, but don’t know how to apply them.
In this article, we will show you how to write formulas in excel and how to dynamically change them when the referenced cell values are changed automatically.
How to Write/Insert Formulas in Excel?
To write a formula in excel, the first basic thing you need to do is to enter the equal sign in the cell where we need to enter the formula. So, the formula always starts with an equal (=) sign.
Example #1
For example, look at the below data in excel worksheet.
I have two numbers in A1 & A2 cells, respectively. If we want to add these two numbers in cell A3, we need to first open the equal sign in the A3 cell.
Enter the numbers as 525 + 800.
Hit the enter key to get the result of the entered formula equation.
The formula bar shows formula, not the result of the formula.
As you can see above, we have got the result as 1325 as the summation of the numbers 525 & 800. In the formula bar, we can see how the formula has been applied = 525+800.
Now I will change the numbers of the cells A1 & A2 to 50 and 80, respectively.
Result cell A3 shows the old formula result only. This is where we need to make dynamic cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more formulas.
Instead of entering the two cell numbers, give a cell reference only to the formula.
Hit the enter key to get the result of this formula.
In the formula bar, we can see the formula as A1 + A2, not the numbers of the A1 & A2 cells. Now change the numbers of any of the cells, and in A3 cell, it will automatically impact the result.
I have changed the A1 cell number from 50 to 100, and because the A3 cell has the reference of the cells A1 & A2, resulting in the A3 cell automatically changed.
Example #2
Now I have further numbers to the adjacent columns.
I have numbers in four more columns, and we need to get the total of these numbers as we did for A1 & A3 cells.
This is where the real power of cell referenced formulas are crucial in making the formula dynamic. Copy the cell A3, which is already applied the formula as A1 + A2, and paste to the next cell, i.e., B3.
Wow!!! Look at the result in the cell B3, and the formula bar says the formula as B1 + B2 instead of A1 + A2.
When we copy and paste the A3 cell, which is the formula cell, and we moved one column to the right, but in the same row, we have pasted the formula. Because we moved one column to the right in the same row-column reference or header value, “A” has been changed to “B,” but row numbers 1 & 2 remain the same.
Like the copy and paste the formula to other cells to get the total in all the cells.
How to use Built-in Excel Functions?
Example #1 – SUM Function
We have plenty of built-in functions in excel according to the requirement and situation we can use them. For example, look at the below data in excel.
We have numbers from A1 to E5 range of cells. In the B7 cell, we need the total sum of these numbers. Adding individual cell reference and entering each cell reference individually consumes a lot of time, so by putting equal sign open SUM function in excelSUM Function In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more.
Select the range of cells from A1 to E5 and close the bracket.
Hit enters the key to get the total of the numbers from A1 to E5.
Wow!!! In just a fraction of seconds, we got the total of the numbers, and in the formula bar, we can see the formula as =SUM(A1: E5)
Example #2 – AVERAGE Function
Assume you have students each subject score and you need to find the average score of the student, it is possible to find an average score in a fraction of seconds. For example, look at the below data.
Open average function in the H2 cell first.
Select the range of cells reference as B2 to G2 because for the student “Amit,” all the subject scores are in this range only.
Hit the enter key to get the average student “Amit.”
So the student “Amit” average score is 79.
Now drag formula cell H2 to below cells as well to get the average of other students.
Things to Remember about Inserting Formula in Excel
- The formula should always start with an equal sign. You can also start with PLUS or MINUS sign as well but not recommended.
- While doing calculations, you need to keep in mind the basic math rule of BODMAS.
- Always use built-in functions in excel.
Recommended Articles
This has been a guide to write a formula in excel. Here we discuss how to insert formula and built-in functions in excel with examples and a downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
54.4K subscribers