Introduction to Basic Formulas
Unlock the true power of Excel by learning to create formulas. Understand formula syntax, master arithmetic operators, learn operator precedence, and start performing calculations that update automatically when your data changes. This is where spreadsheets transform from simple tables into powerful calculation tools.
In This Lesson
- 1What Are Formulas?
- 2Anatomy of a Formula
- 3Creating Your First Formula
- 4Arithmetic Operators
- 5Comparison Operators
- 6Operator Precedence (Order of Operations)
- 7Using Parentheses
- 8Cell References in Formulas
- 9Common Formula Errors
- 10Editing and Copying Formulas
- 11Practical Formula Examples
- 12Practice Exercise
What Are Formulas?
A formula is an instruction that tells Excel to perform a calculation. Formulas are what transform Excel from a simple data storage tool into a powerful calculation engine. Without formulas, Excel would just be a fancy way to organize information in rows and columns. With formulas, it becomes a tool that can analyze data, automate calculations, and provide instant insights.
Think of formulas as mathematical expressions that you write in a cell. Instead of typing a number directly, you tell Excel how to calculate that number. The beauty of formulas is that they are dynamic — when the source data changes, the formula result automatically updates without any additional work from you.
Why Formulas Matter
Consider this real-world scenario: You are tracking monthly expenses in a spreadsheet. Without formulas, every time you add a new expense, you would need to manually recalculate your total using a calculator and type in the new sum. With formulas, you write the calculation once, and Excel updates the total automatically every single time you add, change, or remove an expense.
- Automatic recalculation: Change any input value and all dependent formulas update instantly
- Accuracy: Eliminate human calculation errors — Excel performs math perfectly every time
- Efficiency: Calculate thousands of values in seconds instead of hours of manual work
- Consistency: Apply the same calculation logic across your entire dataset
- Analysis: Perform complex analyses that would be impractical to do manually
Formulas vs. Functions
You will often hear the terms "formula" and "function" used in Excel. A formula is any expression that begins with an equals sign (=) and performs a calculation. A function is a predefined formula built into Excel, like SUM or AVERAGE. Functions are used inside formulas. We will cover functions in detail in Lesson 8.
Anatomy of a Formula
Every Excel formula follows a specific structure. Understanding this structure is essential for creating formulas correctly and troubleshooting them when they do not work as expected.
The Essential Rule: Start with Equals Sign
Every formula in Excel must begin with an equals sign (=). This is how Excel knows that what follows is a calculation instruction rather than text or a number. If you type 5+3 without the equals sign, Excel treats it as text and displays "5+3" literally. If you type =5+3, Excel performs the calculation and displays "8".
Formula Components
A formula can contain several different elements:
| Component | Description | Example |
|---|---|---|
| Equals Sign (=) | Required starting character that tells Excel this is a formula | = |
| Cell References | Addresses that point to other cells containing values | A1, B5, C10 |
| Constants | Fixed numeric or text values typed directly into the formula | 100, 0.08, "Yes" |
| Operators | Symbols that specify the type of calculation | +, -, *, / |
| Functions | Predefined formulas that perform specific calculations | SUM(), AVERAGE() |
| Parentheses | Control the order of operations in complex formulas | (A1+B1)*C1 |
Where Formulas Appear
When you enter a formula in a cell, two things happen:
- The cell displays the result — You see the calculated value (like 150), not the formula itself
- The Formula Bar shows the formula — When you select the cell, the actual formula (like =A1+B1) appears in the Formula Bar above the worksheet
Show All Formulas
To see all formulas in your worksheet at once (instead of their results), press Ctrl + ` (the backtick key, usually above Tab). Press it again to return to normal view. This is extremely helpful for reviewing and troubleshooting formulas.
Creating Your First Formula
Let us walk through creating a simple formula step by step. This hands-on approach will help you understand exactly how formulas work in practice.
Example: Adding Two Numbers
Suppose you have the number 50 in cell A1 and the number 30 in cell B1, and you want to add them together in cell C1.
- Enter your data first: Type 50 in cell A1 and press Tab. Type 30 in cell B1 and press Tab.
- Click on cell C1 — This is where your formula result will appear.
- Type the equals sign: Press the = key. Excel now knows you are entering a formula.
- Click on cell A1 (or type A1). Notice "A1" appears in your formula after the equals sign.
- Type the plus sign: Press the + key.
- Click on cell B1 (or type B1). Your formula now reads =A1+B1.
- Press Enter to confirm the formula.
- See the result: Cell C1 now displays 80 (the sum of 50 + 30).
Your First Formula Result
Cell C1 shows the result (80). The Formula Bar would show =A1+B1
Testing the Dynamic Nature
Now comes the magic. Go back to cell A1 and change 50 to 75. Press Enter. Watch cell C1 — it automatically updates to show 105 (75 + 30). You did not have to touch the formula at all. This is the power of formulas: they recalculate automatically whenever their source data changes.
Click vs. Type Cell References
You can either click on cells to add them to your formula or type the cell addresses directly. Clicking is often faster and reduces errors, especially when referencing cells that are far from your current location. Both methods produce identical results.
Arithmetic Operators
Arithmetic operators perform basic mathematical calculations. These are the building blocks of most Excel formulas. Each operator has a specific symbol that you type between the values you want to calculate.
Addition
Adds values together
=A1+B1
Subtraction
Subtracts one value from another
=A1-B1
Multiplication
Multiplies values together
=A1*B1
Division
Divides one value by another
=A1/B1
Exponent
Raises a number to a power
=A1^2
Percent
Converts to percentage (divides by 100)
=A1*10%
Detailed Operator Examples
| Formula | If A1=100, B1=25 | Result | Explanation |
|---|---|---|---|
=A1+B1 |
100 + 25 | 125 | Simple addition |
=A1-B1 |
100 - 25 | 75 | Simple subtraction |
=A1*B1 |
100 × 25 | 2500 | Multiplication (use asterisk, not x) |
=A1/B1 |
100 ÷ 25 | 4 | Division (use forward slash) |
=A1^2 |
100² | 10000 | 100 raised to the power of 2 (squared) |
=A1*20% |
100 × 0.20 | 20 | 20% of 100 (percent converts to decimal) |
Multiplication Symbol
In Excel, you must use the asterisk (*) for multiplication. You cannot use the letter "x" or the dot (·) like you might in handwritten math. Similarly, division uses the forward slash (/), not the division symbol (÷).
Comparison Operators
Comparison operators (also called logical operators) compare two values and return TRUE or FALSE. While you might not use these in basic calculations, they become essential when you learn conditional functions like IF in later lessons.
| Operator | Meaning | Formula Example | Result (if A1=10, B1=5) |
|---|---|---|---|
= |
Equal to | =A1=B1 |
FALSE (10 does not equal 5) |
<> |
Not equal to | =A1<>B1 |
TRUE (10 is not equal to 5) |
> |
Greater than | =A1>B1 |
TRUE (10 is greater than 5) |
< |
Less than | =A1 |
FALSE (10 is not less than 5) |
>= |
Greater than or equal to | =A1>=10 |
TRUE (10 is equal to 10) |
<= |
Less than or equal to | =B1<=5 |
TRUE (5 is equal to 5) |
When to Use Comparison Operators
Comparison operators are most useful inside functions like IF, COUNTIF, and SUMIF. For example, =IF(A1>100,"High","Low") displays "High" if A1 is greater than 100, otherwise "Low". You will learn more about these in advanced lessons.
Operator Precedence (Order of Operations)
When a formula contains multiple operators, Excel does not simply calculate from left to right. Instead, it follows a specific order called operator precedence — the same order of operations you learned in math class (sometimes called PEMDAS or BODMAS).
The Order of Operations
Excel evaluates operators in this sequence:
| Priority | Operator | Description |
|---|---|---|
| 1st (Highest) | ( ) |
Parentheses — evaluated first |
| 2nd | ^ |
Exponentiation (powers) |
| 3rd | * / |
Multiplication and Division (left to right) |
| 4th | + - |
Addition and Subtraction (left to right) |
| 5th (Lowest) | = <> < > <= >= |
Comparison operators |
Why This Matters
Consider this formula: =10+5*2
If Excel calculated left to right, the result would be 30 (10+5=15, then 15*2=30). But because multiplication has higher precedence than addition, Excel actually calculates 5*2 first, then adds 10. The correct result is 20.
How Excel Calculates =10+5*2
Common Mistake
Forgetting operator precedence is one of the most common formula errors. Always think about which operations should happen first, and use parentheses when in doubt. It is better to have "unnecessary" parentheses that make your intent clear than to get wrong results.
Using Parentheses to Control Order
Parentheses are your tool for controlling calculation order. Anything inside parentheses is calculated first, regardless of the normal precedence rules. You can nest parentheses (put parentheses inside parentheses) for complex formulas.
Parentheses in Action
| Formula | Calculation Order | Result |
|---|---|---|
=10+5*2 |
5*2=10, then 10+10 | 20 |
=(10+5)*2 |
10+5=15, then 15*2 | 30 |
=100/10-5 |
100/10=10, then 10-5 | 5 |
=100/(10-5) |
10-5=5, then 100/5 | 20 |
=2^3+1 |
2^3=8, then 8+1 | 9 |
=2^(3+1) |
3+1=4, then 2^4 | 16 |
Real-World Example: Calculating Average
Suppose you want to calculate the average of three test scores in cells A1, B1, and C1. The formula for average is: sum of all values divided by the count of values.
The parentheses ensure that all three scores are added together before dividing by 3. Without parentheses, =A1+B1+C1/3 would only divide C1 by 3, then add A1 and B1 — giving a completely wrong result!
Pro Tip: Use Parentheses for Clarity
Even when parentheses are not mathematically necessary, adding them can make complex formulas easier to read and understand. For example, =(A1*B1)+(C1*D1) is clearer than =A1*B1+C1*D1, even though both give the same result.
Cell References in Formulas
One of the most powerful aspects of Excel formulas is the ability to reference other cells. Instead of typing numbers directly into your formula, you reference cells that contain those numbers. This means your formula automatically uses whatever value is currently in that cell.
Why Use Cell References?
Consider calculating sales tax. You could write =100*0.08 to calculate 8% tax on $100. But what if you have 500 products? What if the tax rate changes? Using cell references solves both problems:
- Reusability: The same formula works for different input values
- Easy updates: Change the tax rate once, all calculations update automatically
- Transparency: Anyone can see where your values come from
- Copy-ability: Copy formulas to other cells and they adjust automatically
Constants vs. Cell References
Comparing Approaches
The "bad formula" uses constants (fixed numbers typed directly). It works but requires manual changes if prices or tax rates change. The "good formula" uses cell references, making it flexible and maintainable.
Best Practice
As a general rule, avoid typing numbers directly into formulas. Instead, put those numbers in cells and reference them. This makes your spreadsheet more flexible and easier to update. The exception is truly universal constants like the number of months in a year (12) or days in a week (7).
Common Formula Errors
When Excel cannot calculate a formula correctly, it displays an error message starting with a hash symbol (#). Understanding these errors helps you troubleshoot and fix problems quickly.
Error Messages Explained
#DIV/0! — Division by Zero
Cause: You are trying to divide by zero or by an empty cell.
Fix: Check your divisor. Make sure the cell you are dividing by contains a non-zero number.
#VALUE! — Wrong Value Type
Cause: A formula references a cell containing text when it expects a number.
Fix: Check that all cells in your calculation contain appropriate values. Look for hidden spaces or text in "number" cells.
#REF! — Invalid Reference
Cause: A formula refers to a cell that no longer exists (usually because rows or columns were deleted).
Fix: Update the formula to reference valid cells, or press Ctrl+Z to undo the deletion.
#NAME? — Unrecognized Name
Cause: Excel does not recognize something in your formula — usually a misspelled function name or missing quotes around text.
Fix: Check spelling of function names. Make sure text values are enclosed in quotation marks.
#NUM! — Invalid Number
Cause: A formula produces a number that is too large or too small for Excel, or an invalid numeric operation occurred.
Fix: Check for impossible calculations like the square root of a negative number.
#NULL! — Incorrect Range
Cause: You used a space between cell references when you should have used a colon or comma.
Fix: Use a colon (:) for ranges (A1:A10) and commas for separate references (A1,B1,C1).
Error Indicator
When a cell contains an error, a small green triangle appears in the top-left corner. Click the cell and then click the yellow warning icon that appears to see error-checking options and get hints about what went wrong.
Editing and Copying Formulas
As your spreadsheets grow, you will frequently need to modify existing formulas and copy them to other cells. Excel provides several ways to do this efficiently.
Editing an Existing Formula
There are three ways to edit a formula:
- Double-click the cell containing the formula. This enters Edit Mode directly in the cell, with color-coded cell references.
- Press F2 while the cell is selected. Same as double-clicking — enters Edit Mode.
- Click in the Formula Bar. Edit the formula in the bar above the worksheet.
When editing, you will notice that cell references in your formula become color-coded, and corresponding colored borders appear around the referenced cells. This visual aid helps you see exactly which cells your formula is using.
Copying Formulas
One of Excel's most powerful features is the ability to copy formulas. When you copy a formula to other cells, Excel automatically adjusts the cell references. This is called relative referencing (covered in detail in Lesson 9).
Method 1: Copy and Paste
- Select the cell with the formula
- Press Ctrl + C to copy
- Select the destination cell(s)
- Press Ctrl + V to paste
Method 2: Fill Handle (AutoFill)
- Select the cell with the formula
- Position your mouse on the small square at the bottom-right corner (Fill Handle)
- Click and drag down or across to fill adjacent cells
- Release — the formula copies and adjusts automatically
Quick Fill Shortcut
To quickly copy a formula down an entire column: Select the cell with the formula and all cells below where you want it copied. Then press Ctrl + D (fill Down). For filling right, select cells and press Ctrl + R.
Practical Formula Examples
Let us look at some real-world scenarios where you would use basic formulas. These examples demonstrate how formulas solve everyday calculation problems.
Example 1: Simple Budget Calculator
Monthly Budget
Example 2: Sales Tax Calculator
| Cell | Content | Formula |
|---|---|---|
| A1 | Item Price: $250 | 250 |
| A2 | Tax Rate: 8.5% | 0.085 (or 8.5%) |
| A3 | Tax Amount | =A1*A2 → Result: $21.25 |
| A4 | Total Price | =A1+A3 → Result: $271.25 |
Example 3: Percentage Calculations
| Scenario | Formula | Example (A1=200) |
|---|---|---|
| Calculate 25% of a number | =A1*25% or =A1*0.25 |
50 |
| Increase by 15% | =A1*(1+15%) or =A1*1.15 |
230 |
| Decrease by 10% | =A1*(1-10%) or =A1*0.90 |
180 |
| What percent is B1 of A1? | =B1/A1 (format as %) |
If B1=50: 25% |
Practice Exercise: Formula Fundamentals
Put your formula knowledge into action with this comprehensive practice exercise. Complete each task to reinforce what you have learned.
Your Formula Challenge
- Create a new workbook and save it as "Formula_Practice"
- Basic Math: In A1, type 150. In B1, type 75. In C1, create a formula to add them (=A1+B1)
- Subtraction: In C2, create a formula to subtract B1 from A1
- Multiplication: In C3, create a formula to multiply A1 by B1
- Division: In C4, create a formula to divide A1 by B1
- Test dynamic updates: Change A1 to 200 and verify all formulas in column C update automatically
- Operator precedence: In D1, enter =10+5*3 (should equal 25, not 45)
- Parentheses: In D2, enter =(10+5)*3 (should equal 45)
- Percentage: In A5, type 500. In B5, create a formula to calculate 20% of A5
- Complex formula: Create a formula that calculates: (A1 + B1) * 2 - 50
- Create an error: In E1, type =A1/0 to see the #DIV/0! error
- Fix the error: Change E1 to =A1/B1 to fix the division by zero
- Copy a formula: Select C1 and use the fill handle to copy it down to C5
- Save your work with Ctrl + S
Excellent Progress!
You have learned the fundamentals of Excel formulas! You can now create calculations using arithmetic operators, understand operator precedence, use parentheses to control calculation order, and troubleshoot common errors. Next, you will learn powerful built-in functions that make complex calculations simple.
Key Takeaways from Lesson 7
- Every formula must begin with an equals sign (=) — this tells Excel to calculate
- Formulas can contain cell references, constants, operators, functions, and parentheses
- Use cell references instead of typing numbers directly for flexible, updatable formulas
- Arithmetic operators: + (add), - (subtract), * (multiply), / (divide), ^ (power), % (percent)
- Comparison operators (=, <>, >, <, >=, <=) return TRUE or FALSE
- Operator precedence: Parentheses first, then exponents, then multiply/divide, then add/subtract
- Use parentheses to override default precedence and make your calculation order clear
- Common errors include #DIV/0! (division by zero), #VALUE! (wrong data type), #REF! (deleted reference)
- Press F2 or double-click to edit an existing formula and see color-coded references
- Copy formulas with Ctrl+C/Ctrl+V or by dragging the Fill Handle — references adjust automatically
- Press Ctrl + ` (backtick) to toggle between showing formulas and showing results