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.
Table of contents
- 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.
We will consider some examples to Write Formula in Excel.
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 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. 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.
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 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..
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.
Frequently Asked Questions
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 Formula in Excel in 2 ways, namely,
Method #1 – Write 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 #2 – Enter 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,
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.
This article must help understand Write Formula in Excel with its formulas and examples. You can download the template here to use it instantly.
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: –
- 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.
- Marksheet 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.
- Excel Infographics
- Excel Hacks