Write Formula In Excel

Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

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.

Write-Formula-in-Excel

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,

  1. First, the formula always starts with an equal (=) sign, and use the arithmetic operators, or the mathematical operations accordingly.
  2. 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.

Write Formula in Excel Example 1

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.

Example 1.1

Enter the numbers as 525 + 800.

Write Formula in Excel Example 1.2

Press the “Enter” key to get the formula equation’s result, as shown below.

Example 1.3

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.

Write Formula in Excel Example 1.4

Result cell A3 shows the old formula result only.

Therefore, we will 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. So, now, instead of entering the cell values, we will give a cell reference in the formula as =A1+A2, as shown below.

 Example 1.5

Press the “Enter” key to get the result of this formula.

Write Formula in Excel Example 1.6

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.

 Example 1.7

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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #2

Now, we have other numbers for the adjacent columns.

Write Formula in Excel Example 2

We have values in 4 columns. We need to get the total of these numbers as we did for A1 & A3 cells.

Example 2.1

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.

Write Formula in Excel Example 2.2

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.

 Example 2.3

How To Use Built-In Excel Functions?

Example #1 – SUM Function

For example, look at the below data in Excel.

Write Formula in Excel Example 3

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 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.

 Example 3.1

Select the range of cells from A1 to E5, and close the bracket.

Write Formula in Excel Example 3.2

Press the “Enter” key to get the total numbers from A1 to E5.

Example 3.3

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.

Write Formula in Excel Example 4

Open the AVERAGE function in the H2 cell first.

Example 4.1

Select the range of cells reference as B2 to G2 because for the student “Amit,” all the subject scores are in this range only.

Write Formula in Excel Example 4.2

Press the “Enter” key to get the average student “Amit.”

Example 4.3

So, the student “Amit” average score is 79.

Now, drag formula cell H2 to the below cells to get the average of other students.

Write Formula in Excel Example 4.4

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.

Frequently Asked Questions

1. What is the best method to Write Formula in Excel?

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.

2. Name the different methods to Write Formula in Excel.

We can Write Formula in Excel in 2 ways, namely,
Method #1Write the formula manually
a). First, type an equal (=) sign in an empty cell.
b). Enter the cell value or cell reference.
c). Enter the arithmetic operator, such as +, *, -, /,  or the mathematical operations according to the required calculation.
d). Then, enter the next value, the operator, the other value, and so on.
e). The final formula will be =number1+number2+number3+so on…, or =number1*number2*number3*so on…,
f). Finally, press “Enter” to get the calculated result.

Method #2Enter the inbuilt formula
a). Type = equal sign in an empty cell.
b). Enter the formula name as =SUM(, =AVERAGE(, or any other formula as per the calculation.
c). Then enter the arguments as cell values, cell references, or a cell range.
d). Finally, close the brackets, and press “Enter”.
The formulas will look as follows,

Sum Formula
Average Formula

3. Does the output of the formula change with a change in cell references?

Yes, when we give the cell reference or cell range in a formula instead of the cell values, then the results change automatically. Because the formula with cell references is dynamic.

Download Template

This article must help understand Write Formula in Excel with its formulas and examples. You can download the template here to use it instantly.

You can download this Write Formula Excel Template here – Write Formula Excel Template

This article is a guide to Write Formula in excel. Here we learn to write formulas, use built-in functions, SUM(), AVERAGE(), examples & downloadable template. You may learn more about Excel from the following articles: –