Essential Spreadsheet Formulas Every Professional Should Know

Spreadsheets are the world's most-used programming environment. Millions of professionals make critical decisions based on formulas they half-remember from a tutorial three years ago. Mastering a core set of formulas transforms spreadsheets from glorified tables into powerful data analysis tools.

The anatomy of a formula

Every formula starts with = and can reference:

• Cell references: A1, B2:D10 (relative) or $A$1, $B$2:$D$10 (absolute)
• Named ranges: =SUM(Revenue) instead of =SUM(B2:B100)
• Functions: SUM, VLOOKUP, IF, TEXT, etc.
• Constants: numbers, text in quotes, TRUE/FALSE

Relative vs. absolute references: When you copy a formula, relative references shift. Absolute references (with $) stay fixed.

=B2*C2       → When copied down, becomes =B3*C3, =B4*C4...
=B2*$C$1     → C1 stays fixed (e.g., a tax rate), B2 shifts

Lookup formulas

VLOOKUP

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

Find a value in the first column of a table and return a value from another column:

=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)

• A2 — the value to search for
• $E$2:$G$100 — the table (locked with $)
• 2 — return the 2nd column of the table
• FALSE — exact match (always use FALSE for lookups)

VLOOKUP's limitations: It can only look left-to-right, and inserting a column shifts the index number.

INDEX / MATCH (the better alternative)

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

More flexible than VLOOKUP — can look in any direction:

=INDEX($G$2:$G$100, MATCH(A2, $E$2:$E$100, 0))

Returns the value from column G where column E matches A2. Column insertion never breaks it.

XLOOKUP (modern Excel / Google Sheets)

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

The modern replacement for both:

=XLOOKUP(A2, $E$2:$E$100, $G$2:$G$100, "Not found")

Cleaner syntax, handles not-found cases gracefully, works in any direction.

Conditional formulas

IF

=IF(condition, value_if_true, value_if_false)
=IF(B2>1000, "High", "Low")
=IF(C2="", "Missing", C2)

Nested IF (use IFS instead)

=IF(B2>=90,"A", IF(B2>=80,"B", IF(B2>=70,"C","F")))

-- Cleaner with IFS:
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")

AND / OR in conditions

=IF(AND(B2>100, C2="Active"), "Priority", "Normal")
=IF(OR(D2="VIP", E2>10000), "Discount", "")

Aggregation formulas

SUMIF / SUMIFS

Sum values that meet a condition:

=SUMIF(range, criteria, sum_range)
=SUMIF(B:B, "Electronics", C:C)   -- sum C where B = "Electronics"

-- Multiple conditions:
=SUMIFS(C:C, B:B, "Electronics", D:D, ">2025-01-01")

COUNTIF / COUNTIFS

=COUNTIF(A:A, "Active")           -- count rows where A = "Active"
=COUNTIF(B:B, ">1000")            -- count rows where B > 1000
=COUNTIFS(A:A, "Active", C:C, "US")  -- both conditions

AVERAGEIF

=AVERAGEIF(B:B, "Electronics", C:C)  -- average C where B = "Electronics"

Text formulas

Combining text

=CONCATENATE(A2, " ", B2)          -- old syntax
=A2 & " " & B2                     -- modern syntax (preferred)
=TEXTJOIN(", ", TRUE, A2:A10)      -- join with delimiter, skip empty

Extracting text

=LEFT(A2, 3)                       -- first 3 characters
=RIGHT(A2, 4)                      -- last 4 characters
=MID(A2, 3, 5)                     -- 5 characters starting at position 3
=LEN(A2)                           -- total character count
=FIND("@", A2)                     -- position of @ in string

Cleaning text

=TRIM(A2)                          -- remove leading/trailing spaces
=UPPER(A2) / =LOWER(A2)           -- change case
=PROPER(A2)                        -- Title Case
=SUBSTITUTE(A2, " ", "_")         -- replace spaces with underscores
=CLEAN(A2)                         -- remove non-printable characters

Date and time formulas

=TODAY()                           -- current date
=NOW()                             -- current date and time
=YEAR(A2) / =MONTH(A2) / =DAY(A2) -- extract parts
=DATEDIF(A2, B2, "D")             -- days between two dates
=DATEDIF(A2, B2, "M")             -- complete months between dates
=DATEDIF(A2, B2, "Y")             -- complete years (useful for age)
=NETWORKDAYS(A2, B2)              -- working days between dates (excludes weekends)
=EDATE(A2, 3)                     -- date 3 months from A2
=EOMONTH(A2, 0)                   -- last day of the month in A2

Working with data: practical patterns

Remove duplicates with UNIQUE (modern)

=UNIQUE(A2:A100)                   -- list of unique values

Dynamic arrays with FILTER

=FILTER(A2:C100, B2:B100="Active")   -- rows where B = "Active"
=FILTER(A2:C100, (B2:B100="Active")*(C2:C100>1000))  -- both conditions

Sort dynamically with SORT

=SORT(A2:C100, 2, -1)              -- sort by column 2, descending

Converting and exporting data

When your spreadsheet work is done and you need to move data to another system:

• Export to CSV for universal compatibility with our Excel to CSV converter
• Merge multiple CSV files with CSV Merger
• Convert to JSON for API consumption with JSON to Excel (works both ways)

Common mistakes to avoid

1. Hardcoded values in formulas — Use cell references instead. =B2*0.08 becomes a mystery later; =B2*$D$1 where D1 is labeled "Tax Rate" is self-documenting.
2. Not locking references when copying — Forgetting $ causes formulas to reference wrong cells.
3. Using VLOOKUP with TRUE (approximate match) — This requires sorted data and produces surprising results when data isn't sorted.
4. Circular references — A formula that references itself (or a cell that references it). Excel will warn you.
5. Mixing data types — Storing numbers as text (left-aligned in a cell) breaks SUM and sort.

Spreadsheet mastery is a superpower. The formulas above handle 90% of real-world data tasks. Practice them on actual datasets and you'll reach for complex formulas effortlessly.