Write Basic Formulas in Excel

Formulas are an integral part of Excel. As a new learner of Excel, one does not understand the importance of formulas in Excel. However, all the new learners know plenty of built-in formulas but do not know how to apply them.

In this article, we will show you how to write formulas in Excel and dynamically change them when the referenced cell values are changed automatically.

How to Write/Insert Formulas in Excel?

To write a formula in Excel, you first need 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

Look at the below data in the Excel worksheet.

We have two numbers in A1 and A2 cells, 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.

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

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 numbers in four more columns. We need to get the total of these numbers as we did for A1 and A3 cells.

It is where the real power of cell-references formulas is crucial in making the formula dynamic. For example, copy cell A3, which has already applied the formula A1 + A2, and paste it to 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

We have plenty of built-in functions in Excel according to the requirements and situations we can use. 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 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.

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

In just a fraction of seconds, we got the total numbers. In the formula bar, we can see the formula as =SUM(A1: E5).

Example #2 – AVERAGE Function

Assume you have students” every subject score, and you need to find the student’s average score. 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.

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.

Things to Remember about Inserting Formula in Excel

  • 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.We must always use built-in functions in Excel.

This article is a guide to Write Formula in Excel. Here, we discuss how to insert formulas and built-in functions in Excel with examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Evaluate Formula in ExcelEvaluate Formula In ExcelThe Evaluate formula in Excel is used to analyze and understand any fundamental Excel formula such as SUM, COUNT, COUNTA, and AVERAGE. For this, you may use the F9 key to break down the formula and evaluate it step by step, or you may use the “Evaluate” tool.read moreMarksheet in ExcelMarksheet In ExcelMarksheets in Excel help keep records, entries, and tracking the performance of students, candidates, and even employees and workers, thereby, reducing the task of regularizing hundreds of people to a more simple and easy way within the desired format.read moreExcel InfographicsExcel Hacks