Creating Formulas
By the Syncfusion DevTutorials team · Last updated
Write formulas with arithmetic operators, follow the order of operations, and add, subtract, multiply, and divide with cell addresses.
Formulas are what make Excel more than just a table of data. A formula is an instruction that tells Excel how to perform a calculation, such as adding values, multiplying a quantity by a price, or finding a difference between two numbers. Whenever the data changes, Excel automatically recalculates the result. In this lesson, you'll learn how formulas are structured, how arithmetic operators work, and the order Excel uses to evaluate calculations.
Open the Excel sandbox and follow along.
Formula Syntax
Every formula follows the same basic structure:
- Begin with an equals sign (
=). - Enter values, cell references, or both.
- Use operators such as
+,-,*, or/to perform calculations.
For example: =B2*C2
This formula multiplies the value in B2 by the value in C2.
To create a formula:
- Select the cell where you want the result to appear.
- Type
=. - Enter the formula or select cells by clicking them.
- Press Enter.
The cell displays the calculation result, while the Formula Bar displays the formula itself.
For example:
- Type
=. - Click B2.
- Type
*. - Click C2.
- Press Enter.
Excel creates: =B2*C2
Always use cell references instead of typing values directly into formulas. For example, =B2*C2 updates automatically if the values in B2 or C2 change, while =4*25 always returns the same result.
Arithmetic Operators
Excel uses arithmetic operators to perform calculations.
| Operator | Purpose | Example | Result (B2 = 10, C2 = 4) |
|---|---|---|---|
+ | Addition | =B2+C2 | 14 |
- | Subtraction | =B2-C2 | 6 |
* | Multiplication | =B2*C2 | 40 |
/ | Division | =B2/C2 | 2.5 |
^ | Power | =C2^2 | 16 |
% | Percentage | =B2*20% | 2 |
Excel uses:
*for multiplication/for division
Do not use × or ÷ in formulas.
Formula Order
When a formula contains multiple operations, Excel follows a specific calculation order known as the order of operations.
Excel calculates in the following sequence:
- Parentheses
( ) - Powers
^ - Multiplication and Division
*/ - Addition and Subtraction
+-
For example: =2+3*4
Result: 14
Excel calculates:
3 × 4 = 12
2 + 12 = 14
Now consider: = (2+3)*4
Result: 20
Excel calculates:
2 + 3 = 5
5 × 4 = 20
When a formula contains multiple calculations, use parentheses to make the calculation order clear. Example: = (B2 + C2) * D2. Parentheses make formulas easier to understand and help reduce calculation mistakes.
Addition
Use the plus sign (+) to add values.
Example: = B2 + B3 + B4
This adds the values in B2, B3, and B4.
For longer lists, using a function such as SUM is usually more efficient: = SUM(B2:B20)
You'll learn about functions in a later lesson.
Subtraction
Use the minus sign (-) to subtract values.
Example: = B1 - B2
This subtracts the value in B2 from the value in B1.
Subtraction can produce negative results if the second value is larger than the first.
Multiplication
Use the asterisk (*) to multiply values.
Example: = B2 * C2
A common use is multiplying a quantity by a price.
Division
Use the forward slash (/) to divide values.
Example: = B1 / B2
This divides the value in B1 by the value in B2.
If a formula attempts to divide by zero or a blank cell, Excel displays the error: #DIV/0!. This means the value being used as the divisor is missing or zero. Enter a valid number in the referenced cell to resolve the error.
Common mistakes
| Mistake | Result | Solution |
|---|---|---|
Omitting the = sign | Excel treats the entry as text | Add = at the beginning |
Using x instead of * | The formula does not calculate correctly | Use * for multiplication |
| Typing values directly into formulas | Results do not update automatically | Use cell references |
Expecting =2+3*4 to return 20 | Result is 14 | Use =(2+3)*4 |
| Dividing by a blank or zero value | #DIV/0! appears | Enter a valid divisor |
Exercise
Click Reset in the sandbox header and enter the following data:
- A1:
Budget, B1:500 - A2:
Spent, B2:320 - A3:
Guests, B3:8
Complete the following tasks and then click Check.
What to Remember
- Every formula begins with an equals sign (
=). - Use cell references instead of typing values directly into formulas.
Arithmetic operators:
+Addition-Subtraction*Multiplication/Division^Power%Percentage
- Excel follows the order: Parentheses → Powers → Multiplication/Division → Addition/Subtraction.
- Use parentheses whenever you want to control calculation order.
#DIV/0!indicates division by zero or a blank cell.- The Formula Bar displays the formula, while the worksheet cell displays the result.
In the next lesson, you'll learn how cell references behave when formulas are copied and how to lock references when needed.