Lesson 1 of 8 · 8 min · Formulas
Formula basics, cell references and IF logic
What you will learn
- Write formulas and understand relative, absolute and mixed references
- Use Excel Tables so formulas stay readable
- Make decisions with
IF,AND,OR,IFSandSWITCH - Handle errors with
IFERROR - Avoid the most common formula mistakes
In simple words
A formula is a small instruction you type in a cell, always starting with an equals sign. Instead of typing a number, you tell Excel how to work it out from other cells, so when those cells change, the answer updates by itself. An IF formula is a yes-or-no decision: "if the marks are 40 or more, show Pass, otherwise show Fail".
Words to know
- Formula: an instruction starting with
=that Excel calculates. - Cell reference: the address of a cell, such as
B2. - Function: a built-in formula such as
SUMorIF. - Absolute reference: a reference with
$signs that does not move when copied.
Formulas and references
Type in a cell:
=B2*C2
Excel multiplies the values in B2 and C2. Copy the formula down and it becomes =B3*C3, =B4*C4, and so on. That is a relative reference: it shifts with the cell it is copied to.
Sometimes you need a reference to stay still, such as a GST rate in one cell:
=B2*$F$1
The $ locks the column and row. Press F4 after clicking a reference to cycle through the types:
| Reference | Meaning |
|---|---|
B2 |
relative: moves in both directions |
$B$2 |
absolute: never moves |
$B2 |
column locked, row moves |
B$2 |
row locked, column moves |
Mixed references are what make a multiplication table or a lookup grid work with one formula.
Order of operations is the usual one: brackets, then ^, then * and /, then + and -. Use brackets to be safe: =(B2+C2)/2.
Ranges and basic functions
A range is a block of cells, such as B2:B100. Common functions:
=SUM(B2:B100) =AVERAGE(B2:B100) =MIN(B2:B100) =MAX(B2:B100)
=COUNT(B2:B100) counts numbers =COUNTA(B2:B100) counts non-empty cells
=ROUND(B2,0) rounds to whole number
Excel Tables
Select your data and press Ctrl+T to make a Table. Benefits:
- It grows automatically when you add rows, so formulas and pivots include new data.
- Formulas use column names, called structured references:
=[@Quantity]*[@Price]instead of=B2*C2, and=SUM(Sales[Amount])for a whole column. - Headers stay visible, and filters and banding are built in.
Name the table on the Table Design tab (for example Sales). Working from Tables rather than loose ranges is the first habit that separates careful analysts from beginners.
IF: making decisions
=IF(logical_test, value_if_true, value_if_false)
=IF(B2>=40, "Pass", "Fail")
Tests use =, <> (not equal), <, >, <=, >=. Text in formulas goes in double quotes.
Combine tests with AND (all must be true) and OR (at least one):
=IF(AND(B2>=40, C2>=40), "Pass", "Fail")
=IF(OR(D2="Hyderabad", D2="Pune"), "South-West", "Other")
Nest an IF inside another for several outcomes:
=IF(B2>=75, "Distinction", IF(B2>=40, "Pass", "Fail"))
Nesting past two or three levels is hard to read. Use IFS (Excel 2019 and Microsoft 365), which checks conditions in order:
=IFS(B2>=75, "Distinction", B2>=40, "Pass", TRUE, "Fail")
The final TRUE acts as "everything else". SWITCH matches one value against several exact options:
=SWITCH(D2, "H", "Hyderabad", "P", "Pune", "Other")
IFERROR and error values
Common errors and what they mean:
| Error | Usually means |
|---|---|
#DIV/0! |
dividing by zero or an empty cell |
#N/A |
a lookup found nothing |
#NAME? |
a misspelt function or name |
#VALUE! |
the wrong type of value (text where a number is needed) |
#REF! |
a referenced cell was deleted |
#SPILL! |
a dynamic array result has no room to spill |
Wrap a formula to show something friendly instead:
=IFERROR(B2/C2, 0)
=IFERROR(B2/C2, "n/a")
Use it carefully: IFERROR hides every error, including real mistakes. Prefer a targeted test where you can, such as =IF(C2=0, 0, B2/C2), or IFNA for lookups, which hides only #N/A.
Checking your work
- Show Formulas (Ctrl+`) displays every formula instead of results.
- Trace Precedents on the Formulas tab draws arrows to the cells a formula uses.
- Press F2 to edit a formula and see coloured boxes around its inputs.
- Evaluate a long formula piece by piece with Evaluate Formula.
- Keep inputs (rates, thresholds) in labelled cells, not typed into formulas ("hard-coded").
Common mistakes
- Typing numbers into formulas (
=B2*0.18) so nobody knows where 0.18 came from. Put the rate in a cell and use$. - Forgetting to lock a reference and getting wrong results lower down.
- Numbers stored as text, which makes
SUMignore them (look for the green triangle or left alignment). - Overusing
IFERROR, hiding real problems. - Very deep nested
IFs; useIFSor a lookup table instead.
Try it yourself
- Build a mark sheet: marks in B, pass or fail in C with
IF, and a grade withIFS. - Put a GST rate in one cell, compute GST and total for a list of prices using
$. - Convert the list into a Table and rewrite the formulas with structured references.
- Divide revenue by orders, and show 0 instead of
#DIV/0!when orders are blank.
Check yourself
What is the difference between B2, $B$2 and B$2?
B2 moves in both directions when copied, $B$2 never moves, and B$2 locks the row but lets the column move.
How do you test two conditions that must both be true?
=IF(AND(test1, test2), value_if_true, value_if_false).
Why use a Table instead of a plain range?
It expands automatically with new rows, lets formulas use column names, and keeps pivots and formulas up to date.
Why can IFERROR be risky?
It hides all errors, including genuine mistakes in your formula, so results can be wrong without any warning.
Interview questions
How would you apply a tax rate to a column of prices?
Put the rate in one cell, then use an absolute reference in the first formula, =B2*$F$1, and copy it down.
How do you give grades such as A, B and C from marks?
Use IFS with conditions from highest to lowest, or an approximate-match lookup against a small grade table, which is easier to change.
A column of numbers will not add up. What do you check?
Whether the numbers are stored as text (left-aligned, green triangle), stray spaces, or hidden or filtered rows; fix with VALUE, Text to Columns or multiply by 1.
Summary
- Formulas start with
=; references are relative, absolute ($) or mixed. - Excel Tables (Ctrl+T) grow with data and give readable structured references.
IF,AND,OR,IFSandSWITCHmake decisions; keep nesting shallow.IFERRORandIFNAtidy errors, but use them sparingly.
Going deeper
- Overview of formulas in Excel: the official starting point.
- IF function: syntax and examples.
- IFS function: multiple conditions.
- Create and format Excel Tables: Tables and structured references.
See how much of this you can explain
A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.