How to Use Formulas in Excel

Formulas are the reason Excel is more than just a grid for typing numbers — they’re what turn a static table into a living, calculating system. When I first started using Excel, formulas felt intimidating, full of strange symbols and function names. Over time I realized they follow a small set of consistent rules, and once those click, you can build almost anything. In this guide, I’ll walk through everything I wish someone had explained to me when I was starting out.

What a Formula Actually Is

A formula is an instruction that tells Excel to calculate something, rather than just display a typed-in value. Every formula starts with an equals sign (=). Without it, Excel just treats whatever you type as plain text.

For example:

• 5+5 typed into a cell shows literally as “5+5” (text).
• =5+5 calculates and shows “10.”

Basic Formula Structure

A formula generally combines:

• Operators: + (add), - (subtract), * (multiply), / (divide), ^ (exponent)
• Cell references: like A2, B5, or ranges like A2:A10
• Functions: pre-built formulas like SUM, AVERAGE, IF
• Constants: fixed numbers or text you type directly

Example: =A2*B2+10 multiplies A2 by B2, then adds 10 to the result.

Step-by-Step: Writing Your First Formula

1. Click the cell where you want the result to appear.
2. Type =.
3. Click a cell (or type its reference), like A2.
4. Type an operator, like +.
5. Click another cell, like B2.
6. Press Enter.

Excel shows the result immediately, and if you click back on that cell, the formula bar at the top shows the actual formula, not just the answer.

Relative vs. Absolute References

This is one of the most important concepts I had to properly understand before formulas started making sense.

• Relative reference (e.g., A2): changes automatically when you copy the formula to another cell. If =A2*2 is in cell B2 and you copy it to B3, it becomes =A3*2.
• Absolute reference (e.g., $A$2): stays fixed no matter where you copy the formula. Add a $ before the column letter and/or row number to lock it.
• Mixed reference (e.g., $A2 or A$2): locks only the column or only the row, letting the other part adjust.

I use absolute references constantly when multiplying a whole column by one fixed value, like a tax rate or exchange rate sitting in a single cell.

Commonly Used Functions

Beyond basic math operators, Excel has hundreds of built-in functions. Here are the ones I reach for most often:

• =SUM(A2:A10) – adds up a range of numbers.
• =AVERAGE(A2:A10) – calculates the mean.
• =COUNT(A2:A10) – counts how many cells contain numbers.
• =COUNTA(A2:A10) – counts non-empty cells (numbers or text).
• =MAX(A2:A10) / =MIN(A2:A10) – finds the largest/smallest value.
• =IF(A2>50, "Pass", "Fail") – returns one value if a condition is true, another if false.
• =VLOOKUP(A2, D:E, 2, FALSE) – looks up a value in the first column of a range and returns a corresponding value from another column.
• =XLOOKUP(A2, D:D, E:E) – the newer, more flexible replacement for VLOOKUP.
• =CONCATENATE(A2, " ", B2) or =A2&" "&B2 – joins text from multiple cells.
• =TODAY() – returns today’s date, updating automatically.

Step-by-Step: Using the IF Function

The IF function is one of the most powerful and commonly used formulas, so let me walk through it specifically:

=IF(logical_test, value_if_true, value_if_false)

Example: =IF(B2>=50, "Pass", "Fail") checks if B2 is 50 or more. If true, it shows “Pass”; if false, it shows “Fail.”

I can also nest multiple IF statements for more conditions:

=IF(B2>=90, "A", IF(B2>=75, "B", IF(B2>=50, "C", "F")))

This checks each condition in order and assigns a grade accordingly.

Step-by-Step: Using VLOOKUP

VLOOKUP searches for a value in the leftmost column of a range and returns a value from a specified column in the same row.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example: =VLOOKUP(A2, D2:F20, 3, FALSE) looks for the value in A2 within column D, then returns the value from the third column of that range (F), on the matching row. I always set the last argument to FALSE for an exact match — leaving it blank or TRUE can silently return incorrect approximate matches.

Copying Formulas Across Rows or Columns

Instead of retyping a formula in every row, I write it once and then:

1. Click the cell with the formula.
2. Hover over the small square at the bottom-right corner (the fill handle) until the cursor becomes a thin black cross.
3. Double-click it (auto-fills down to match adjacent data), or click and drag down/across manually.

Using Functions Across Multiple Sheets

Formulas can reference cells on other sheets using the sheet name followed by an exclamation mark:

=Sheet2!A2 + Sheet3!A2

This adds a value from Sheet2 to a value from Sheet3, right on your current sheet.

Practical Example

Let’s say I have a student marksheet:

• Column A: Student Name
• Column B: Marks (out of 100)
• Column C: Grade
• Column D: Remarks

My formulas:

• C2: =IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=50,"C","F")))
• D2: =IF(B2>=50,"Passed","Failed")

Dragging both down instantly grades every student and flags pass/fail status without manual review.

Common Mistakes I See

• Forgetting the equals sign, so the formula is treated as plain text.
• Mismatched parentheses, especially in nested IF statements — Excel will flag this, but it can be confusing to fix in a long formula.
• Using relative references where absolute ones were needed, causing formulas to break or shift incorrectly when copied.
• Referencing the wrong range in VLOOKUP, especially forgetting that VLOOKUP only searches the leftmost column of the selected range.
• Leaving VLOOKUP’s last argument blank, which defaults to an approximate match and can return silently wrong results.
• Circular references — accidentally having a formula refer back to its own cell, which Excel will warn you about.

Troubleshooting Tips

• #REF! means a formula is referencing a cell that’s been deleted — check for recently removed rows/columns.
• #VALUE! usually means you’re trying to do math on text — check that referenced cells actually contain numbers.
• #N/A in a lookup formula means no match was found — double-check spelling, spacing, or data type mismatches (text vs. number).
• #DIV/0! means you’re dividing by zero or an empty cell — wrap the formula in IFERROR() to handle it gracefully.
• Use Formulas > Evaluate Formula to step through a complex formula piece by piece and see exactly where it’s going wrong.

Real-World Use Cases

• Students calculating grades and pass/fail status automatically.
• Businesses calculating profit margins, taxes, and commissions.
• HR teams looking up employee details across large datasets with VLOOKUP/XLOOKUP.
• Finance teams building dynamic budgets that recalculate as new data comes in.
• Project managers flagging overdue tasks using IF formulas compared against today’s date.

Best Practices I Follow

• Use named ranges (Formulas > Define Name) for values you reference often, like a tax rate — it makes formulas like =B2*TaxRate far more readable than =B2*$E$1.
• Break complex formulas into smaller helper columns when they get too nested to read easily.
• Always test formulas with a few known values first, before applying them across an entire dataset.
• Use IFERROR to prevent ugly error codes from appearing in shared reports.
• Prefer XLOOKUP over VLOOKUP where available — it’s more flexible, doesn’t require the lookup column to be leftmost, and handles missing values more gracefully.

Formulas are what make Excel genuinely powerful, and the learning curve is really just about getting comfortable with references, functions, and a bit of logical thinking. Once IF, SUM, and VLOOKUP start feeling natural, you’ll find you can build almost any calculation you need without ever touching a calculator again.