Formulas & Functions

A financial analyst at Fidelity once told me: "I use maybe 15 formulas. They cover everything."

She managed a portfolio worth $2 billion. Her models predicted bond yields, analyzed risk exposure, and generated reports for institutional investors. When I asked what percentage of Excel's 500+ built-in functions she used regularly, she laughed. "Maybe 3%. SUM, IF, VLOOKUP, INDEX/MATCH — that is the whole toolkit. The rest is knowing when to use each one."

This module teaches you those essential formulas. Not all 500 — the 10-15 that handle 90% of real business work. By the end, you will be the person your team asks for help.

The building blocks: SUM, AVERAGE, COUNT, MIN, MAX

These five functions are the bread and butter. If spreadsheets were cooking, these would be salt, pepper, oil, garlic, and butter — you use them in almost everything.

The colon : in A1:A10 means "from A1 to A10" — it defines a range. Think of it like saying "pages 1 through 10."

The IF function — teaching Excel to make decisions

IF is the function that transforms a spreadsheet from a calculator into a decision-making engine. The structure is:

=IF(condition, value_if_true, value_if_false)

Think of it as asking Excel a yes-or-no question:

• "Is this sale over $1,000?" → =IF(B2>1000, "Big deal", "Standard")
• "Did the student pass?" → =IF(C2>=70, "Pass", "Fail")
• "Is inventory low?" → =IF(D2<10, "Reorder", "OK")

You can nest IFs for multiple conditions: =IF(C2>=90, "A", IF(C2>=80, "B", IF(C2>=70, "C", "F"))) — but nested IFs get messy fast. In Module 7, you will learn IFS, which handles this much more cleanly.

There Are No Dumb Questions

"Why do I need quotes around text in IF formulas?"

Excel needs to know the difference between text and cell references. "Pass" (with quotes) means the literal word "Pass." Pass (without quotes) would make Excel look for a named range called Pass — which probably does not exist. Numbers do not need quotes: =IF(A1>5, 100, 0) works fine.

"Can I use IF to compare text?"

Yes. =IF(A1="Yes", "Approved", "Pending") works perfectly. Text comparisons in Excel are case-insensitive by default — "yes", "YES", and "Yes" all match.

COUNTIF and SUMIF — counting and adding with conditions

These are the conditional versions of COUNT and SUM. Instead of counting everything, they count only what matches a criterion.

=COUNTIF(range, criteria) — count cells that match =SUMIF(range, criteria, sum_range) — add values where a condition is met

Real examples:

• How many orders came from California? → =COUNTIF(B:B, "CA")
• What is the total revenue from "Enterprise" customers? → =SUMIF(A:A, "Enterprise", C:C)
• How many invoices are overdue (past due date)? → =COUNTIF(D:D, "<"&TODAY())

VLOOKUP — the formula everyone asks about

VLOOKUP stands for "Vertical Lookup." It searches for a value in the first column of a table and returns a value from another column in the same row. Think of it like looking up someone's name in a phone book and reading across to find their number.

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

Broken down:

1. lookup_value — what are you searching for? (e.g., employee ID)
2. table_array — where is the lookup table? (e.g., A:D)
3. col_index_num — which column has the answer? (e.g., 3 for the third column)
4. range_lookup — exact match or approximate? (almost always FALSE for exact)

Example: You have an order with product ID "SKU-4521" and need to find its price from a product table.

=VLOOKUP("SKU-4521", Products!A:C, 3, FALSE)

This says: "Find SKU-4521 in column A of the Products sheet, then return the value from column 3 (Price)."

XLOOKUP — the modern replacement

If your version of Excel supports it (Microsoft 365, Excel 2021+), use XLOOKUP instead. It fixes every VLOOKUP annoyance.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Same example: =XLOOKUP("SKU-4521", Products!A:A, Products!C:C, "Not found")

Cleaner. No column index numbers. No TRUE/FALSE confusion. And if the product is not found, it shows "Not found" instead of an ugly #N/A error.

There Are No Dumb Questions

"Should I learn VLOOKUP or XLOOKUP?"

Learn both. VLOOKUP is in every version of Excel and Google Sheets — you will encounter it in every company's existing spreadsheets. XLOOKUP is the better tool but requires Excel 2021+ or Microsoft 365. When creating new formulas, use XLOOKUP if available. When reading others' work, you need to understand VLOOKUP.

"What does the FALSE at the end of VLOOKUP mean?"

FALSE means "exact match" — find exactly what I asked for. TRUE (or omitting the argument) means "approximate match," which uses binary search and requires sorted data. In business, you almost always want FALSE. The approximate match mode exists for tax brackets and grade ranges where you want "the closest value less than or equal to."

INDEX/MATCH — the power user's lookup

INDEX/MATCH is the combination that experienced Excel users swear by. It is more flexible than VLOOKUP and does not break when columns move.

INDEX returns a value from a specific row and column position: =INDEX(array, row_num) MATCH finds the position of a value in a range: =MATCH(lookup_value, lookup_array, 0)

Combined: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example: Find the price of "SKU-4521": =INDEX(Products!C:C, MATCH("SKU-4521", Products!A:A, 0))

This says: "Find the row position of SKU-4521 in column A, then return the value from that same row in column C."

Absolute vs relative references — the $ sign

When you copy a formula to another cell, Excel adjusts the cell references automatically. This is called a relative reference. Usually, that is exactly what you want.

But sometimes you need a reference to stay put. That is an absolute reference, created by adding $ signs.

• A1 — fully relative (both column and row change when copied)
• $A$1 — fully absolute (nothing changes when copied)
• $A1 — mixed (column stays, row changes)
• A$1 — mixed (row stays, column changes)

Real scenario: You have a tax rate in cell E1 (8%). You want every row to multiply its subtotal by that tax rate. If you write =B2*E1 and copy it down, E1 becomes E2, E3, E4 — wrong cells. Write =B2*$E$1 instead, and E1 stays locked no matter where you copy the formula.

There Are No Dumb Questions

"How do I remember when to use dollar signs?"

Ask yourself: "When I copy this formula down or across, should this reference move with me?" If yes, no dollar sign. If no, add dollar signs. Press F4 (Windows) or Cmd+T (Mac) while editing a reference to cycle through all four options: A1 → $A$1 → A$1 → $A1 → A1.

Key takeaways

• SUM, AVERAGE, COUNT, MIN, MAX are the five foundational functions — learn them first, use them daily
• IF turns Excel into a decision engine — master the three-part structure: condition, true result, false result
• COUNTIF/SUMIF add conditions to counting and summing — essential for slicing data by category, region, or time period
• VLOOKUP searches the leftmost column of a table and returns a value from another column — use FALSE for exact match
• XLOOKUP replaces VLOOKUP with cleaner syntax, any-direction searching, and built-in error handling (Excel 365/2021+)
• INDEX/MATCH is the most flexible lookup combination — preferred by power users for complex models
• Absolute references ($) lock cell references when copying formulas — critical for tax rates, conversion factors, and shared constants