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 equal (=) sign.
Example #1
For example, look at the below data in excel worksheet.
I have two numbers in A1 & A2 cell respectively, if we want to add these two numbers in cell A3 we need to first open the equal sign in A3 cell.
Enter the numbers as 525 + 800.
Hit enter key to get the result of the entered formula equation.
Formula bar shows formula not 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 the dynamic cell reference formulas.
Instead of entering the two cell numbers give a cell reference only to the formula.
Hit 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 cell and in A3 cell it will automatically impact the result.
I have changed the A1 cell number from 50 to 100 and because A3 cell has the reference of the cells A1 & A2 result 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 cell.
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 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 excel.
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 an 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 enter key to get the average of 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 downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
