Table Of Contents
Frequently Asked Questions (FAQs)
What is the best method to Write Formula in Excel?
Name the different methods to Write Formula in Excel.
Does the output of the formula change with a change in cell references?
What Is Write Basic Formulas In Excel?
Formulas are an integral part of Excel. As a new learner of Excel, we may know many built-in formulas but not the right application.
In this article, we will learn to Write Formula in Excel, such as SUM(), AVERAGE(), etc., as shown in the below image. We will also see how the dynamic change occurs in the results of the formulas when the referenced cell values change automatically.
For example, in cell E3, we will write a formula as =E1+E2. The output is 24, as shown below.
Key Takeaways
- Write Formula in Excel helps users understand the inbuilt formulas in Excel and use them accordingly to perform mathematical calculations.
- We must always use built-in formulas in Excel, with cell references or cell ranges, because cell values are constant, and modifications will not update the results.
- We can Write Excel formulas in 2 ways, first by directly entering an = equal sign,
- Then, enter the values with the necessary arithmetic operators in-between the values.
- Or, insert the inbuilt formulas in Excel with cell values, cell references, or cell ranges.
How To Write/Insert Formulas In Excel?
We can Write/Insert Formulas in Excel in 2 ways, namely,
- First, the formula always starts with an equal (=) sign, and use the arithmetic operators, or the mathematical operations accordingly.
- Second, use the inbuilt formulas in Excel.
Examples
We will consider some examples to Write Formula in Excel.
Example #1
Look at the below data in the Excel worksheet.
We have two numbers in cells A1 and A2, respectively. So, if we want to add these two numbers in cell A3, we must first open the equal sign in the A3 cell.
Enter the numbers as 525 + 800.
Press the “Enter” key to get the formula equation’s result, as shown below.
Output Observation: The formula bar shows the formula, not the result of the formula.
As you can see above, we have got the result as 1325 as the sum of the numbers, 525 and 800. In the formula bar, we can see how the formula has been applied = 525+800.
Now, we will change the A1 and A2 numbers to 50 and 80, respectively.
Result cell A3 shows the old formula result only.
Therefore, we will make dynamic cell reference formulas. So, now, instead of entering the cell values, we will give a cell reference in the formula as =A1+A2, as shown below.
Press 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 and A2 cells. Now, change the numbers of any of the cells. For example, in the A3 cell, it will automatically impact the result.
We have changed the A1 cell number from 50 to 100, and because the A3 cell has the reference of the cells A1 and A2, the A3 cell automatically changed.
Example #2
Now, we have other numbers for the adjacent columns.
We have values in 4 columns. We need to get the total of these numbers as we did for A1 & A3 cells.
We will make the formula dynamic. For example, copy cell A3, which has already applied the formula A1 + A2, and paste it in the next cell, B3.
Look at the result in cell B3. The formula bar says the formula as B1 + B2 instead of A1 + A2.
When we copied and pasted the A3 cell, the formula cell, we moved one column to the right. But in the same row, we have passed 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 and 2 remain the same.
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
For example, look at the below data in Excel.
We have numbers from the A1 to E5 range of cells. In the B7 cell, we need the total sum of these numbers. Adding individual cell references and entering each cell reference one by one consumes a lot of time, so putting an =S opens the SUM function in excel.
Select the range of cells from A1 to E5, and close the bracket.
Press the “Enter” key to get the total numbers from A1 to E5.
In just a fraction of a second, we got the total numbers. In the formula bar, we can see the formula as =SUM(A1:E5).
Example #2 - AVERAGE Function
Assume we have subject scores of students, and we have to find the average of each student. It is possible to find an average score in a fraction of a second. For example, look at the below data.
Open the 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.
Press the “Enter” key to get the average student “Amit.”
So, the student “Amit” average score is 79.
Now, drag formula cell H2 to the below cells to get the average of other students.
Important Things To Note
- The formula should always start with an equal sign. We can also start with a PLUS or MINUS sign but not recommended.
- While doing calculations, we need to remember BODMAS’s basic Maths rules.
