What Are Excel Formulas and How Do They Work?

Most people use Excel to store data. But the real power kicks in the moment you start using Excel formulas — the built-in instructions that tell Excel to calculate, compare, search, and transform your data automatically. Whether you’re calculating a team’s total sales or grading a class of 15 students, formulas do the heavy lifting for you.

The good news? Most formulas covered here work in Excel 2016 and beyond. A few newer ones, like XLOOKUP and IFS, require Excel 2019 or Microsoft 365. Where that matters, it’s called out clearly.

This post walks you through what Excel formulas are, the five main categories, how they work under the hood, and how to handle the errors that trip up almost everyone.

Quick Takeaways:

• Every Excel formula starts with an = sign — without it, Excel treats your entry as plain text.
• There are five main formula categories: Math, Text, Logical, Lookup, and Date/Time.
• Simple formulas like =SUM(E6:E20) can replace minutes of manual adding in seconds.
• You can nest formulas inside each other for more complex, real-world calculations.
• All seven Excel error types (#DIV/0!, #VALUE!, #REF!, etc.) have straightforward fixes.

What Is an Excel Formula?

A formula is an instruction you type into a cell that tells Excel to compute a result. It always starts with an equals sign (=), followed by a function name and its arguments.

Take a look at this real example from a sales dataset tracking 15 employees across three months:

Employee	Units Sold	Unit Price (GHS)	Discount
Kwame Asante	120	250	5%
Ato Mensah	230	310	10%
Grace Acheampong	210	420	8%

To add up all units sold across all 15 employees, you’d write:

=SUM(E6:E20)

Excel scans cells E6 through E20, adds every number it finds, and returns 1,693 total units. That’s it. No calculator, no manual adding.

The Anatomy of a Formula

Every formula follows the same pattern:

= FunctionName(argument1, argument2, …)

The function name tells Excel what to do (SUM, IF, VLOOKUP). The arguments inside the brackets are the inputs — cell references, numbers, or text — that the function needs to work.

The 5 Main Categories of Excel Formulas

1. Math and Arithmetic Formulas

These are the ones most people learn first. They handle numbers: adding, averaging, rounding, and more.

From the sales dataset, here’s what each core formula does with real values:

Formula	Example	Result
=SUM(E6:E20)	Total units sold	1,693
=AVERAGE(F6:F20)	Average unit price	GHS 270.67
=MAX(E6:E20)	Highest units sold	230 (Ato Mensah)
=MIN(E6:E20)	Lowest units sold	0
=ROUND(3.14159, 2)	Round pi to 2 decimals	3.14

I’ve found ROUND gets overlooked a lot — but it’s essential whenever you’re working with currency and can’t have fractions of a pesewa showing up in a report.

2. Text Formulas

Text formulas manipulate strings — names, IDs, labels. They’re incredibly useful for cleaning up messy data.

Using the name “Kwame Asante” as the input:

• =LEFT(“Kwame Asante”, 5) → Kwame
• =RIGHT(“Kwame Asante”, 6) → Asante
• =LEN(“Kwame Asante”) → 12 (including the space)
• =UPPER(“kwame asante”) → KWAME ASANTE
• =TRIM(” Kwame Asante “) → Kwame Asante

TRIM is a quiet hero. Imported data almost always has stray spaces that break lookups — TRIM strips them cleanly.

3. Logical Formulas

Logical formulas make decisions. The most important one is IF.

Here’s how it looks applied to a student grade book with 15 students:

=IF(H6>=70, “Pass”, “Fail”)

If a student’s average is 70 or above, the cell shows “Pass.” Below 70, it shows “Fail.” Yaw Frimpong, who averaged 96.25 across Maths, English, Science, and Social Studies, would pass comfortably. Kwesi Amponsah, averaging 41.25, would not.

You can stack conditions using IFS (Excel 2019+):

=IFS(H6>=80,”A”, H6>=70,”B”, H6>=60,”C”, H6>=50,”D”, TRUE,”F”)

This assigns a letter grade in one formula instead of nesting five IF statements inside each other.

4. Lookup Formulas

Lookup formulas search a table and return matching data. VLOOKUP is the classic choice, available since Excel 2007.

Using the employee score table with 15 employees, searching for employee E009 (Yaw Frimpong):

=VLOOKUP(“E009”, A6:J20, 4, FALSE)

This returns his score: 95. The 4 tells Excel to return the value from the 4th column (Score), and FALSE means find an exact match only.

XLOOKUP (Excel 365 / 2019+) is more flexible and frankly easier to write:

=XLOOKUP(“Abena Mensah”, B6:B20, D6:D20)

No column number required. You just point directly at the return range. Abena Mensah’s score? 92, Grade A, 15% bonus. XLOOKUP also handles left-to-right and right-to-left lookups — something VLOOKUP simply can’t do.

5. Date and Time Formulas

These extract or calculate dates automatically. Useful for project tracking, payroll, or any time-sensitive reporting.

A few essentials:

Formula	What It Returns
=TODAY()	Today’s date, updated each time you open the file
=YEAR(45366)	Extracts the year from a date serial number
=NETWORKDAYS(A2, B2)	Working days between two dates, excluding weekends
=EDATE(A2, 3)	Adds 3 months to a date

NETWORKDAYS alone is worth learning if you manage deadlines or payroll cycles.

The 7 Formula Errors — and How to Fix Them

Every Excel user hits errors. Here are all seven, what causes them, and the fastest fix:

Error	Cause	Fix
#DIV/0!	Dividing by zero or an empty cell	=IF(D2=0, 0, C2/D2)
#VALUE!	Wrong data type (text where a number is expected)	Use VALUE() to convert
#REF!	A referenced cell or column was deleted	Rebuild the formula; use named ranges
#NAME?	Mistyped function name (e.g., SUMM instead of SUM)	Check spelling; use autocomplete
#N/A	Lookup value not found in the table	Wrap with IFERROR or IFNA
#NULL!	A space used instead of a comma or colon between ranges	Replace the space with , or :
#NUM!	Invalid number (e.g., square root of a negative)	Use ABS() before SQRT

The single best habit I can recommend: wrap any formula that touches unpredictable data in IFERROR:

=IFERROR(VLOOKUP(“E099”, A6:J20, 4, FALSE), “Not found”)

Employee E099 doesn’t exist in the table — without IFERROR, you’d get #N/A. With it, you get a clean “Not found” message instead.

Start Small, Build Up

You don’t need to master all five formula categories at once. Start with SUM and AVERAGE on real data you care about. Once those feel natural, add IF for simple decisions. Then VLOOKUP or XLOOKUP when you need to pull data from another table.

Formulas compound — once you know three or four of them, you can combine them into something genuinely powerful. The jump from “I know SUM” to “I can build a working grade book or sales dashboard” is smaller than it looks.For deeper practice, Microsoft’s official formula reference is the most comprehensive resource available.