The 12 Excel Functions Every Office Worker Should Know

Excel ships with more than 450 functions, and you will use maybe a dozen of them in a typical office job. After years of cleaning up other people’s spreadsheets, I can tell you the gap between “struggles with Excel” and “the person everyone asks for help” comes down to knowing these twelve cold. Each one below gets a one-line job description, a real scenario you will actually face, and a formula you can adapt today.

The Quick Reference

Function	What it does in one line
SUM	Adds up a range of numbers
IF	Returns one value if a condition is true, another if false
VLOOKUP / XLOOKUP	Finds a value in one table and pulls back related data
COUNTIF	Counts cells that meet a condition
SUMIF	Adds only the cells that meet a condition
INDEX	Returns the value at a given position in a range
TEXT	Formats a number or date as text, your way
CONCAT	Joins text from multiple cells into one
IFERROR	Replaces ugly error values with something useful
TODAY	Returns the current date, always up to date
ROUND	Rounds a number to the digits you specify
FILTER	Extracts every row that matches your criteria

The Foundation: Math and Logic

1. SUM

Job description: Adds up a range of numbers.

Office scenario: You manage a department budget and need the total of 200 expense lines. Highlighting the column and squinting at the status bar is not a deliverable.

=SUM(B2:B201)

Pro tip: press Alt + = and Excel writes the SUM formula for you, guessing the range above or to the left. Verify the guess — AutoSum stops at the first blank cell, which is the most common way totals end up wrong.

2. IF

Job description: Returns one value when a condition is true and a different value when it is false.

Office scenario: Sales reps earn a bonus only when they hit $50,000. You need a column that says “Bonus” or “No Bonus” without you eyeballing each row.

=IF(B2>=50000, "Bonus", "No Bonus")

The IF function is the gateway to every conditional formula in Excel. Resist the urge to nest more than two or three IFs — past that point, IFS or a lookup table is far easier to debug.

3. ROUND

Job description: Rounds a number to a specified number of digits.

Office scenario: Your commission calculation produces $1,247.38333… and finance rejects any report where the pennies do not add up. Formatting a cell to show two decimals only hides the extra digits — the underlying value still carries them, so totals drift.

=ROUND(B2*0.035, 2)

ROUND changes the actual stored value, which is what you want for anything involving money. Use ROUNDUP or ROUNDDOWN when the direction matters, like billing increments.

The Lookups: Where Real Power Starts

4. VLOOKUP / XLOOKUP

Job description: Finds a value in one table and returns related information from another column.

Office scenario: You have an export of 800 order IDs and need each customer’s region from a separate master list. Doing this by hand is an afternoon; a lookup is ten seconds.

Classic VLOOKUP:

=VLOOKUP(A2, Customers!A:D, 4, FALSE)

Always use FALSE (exact match) unless you genuinely want approximate matching — forgetting this is the number one source of silently wrong lookup results.

If you are on Microsoft 365 or Excel 2021+, use XLOOKUP instead:

=XLOOKUP(A2, Customers!A:A, Customers!D:D, "Not found")

XLOOKUP can look left, defaults to exact match, and has a built-in not-found message. There is no reason to learn VLOOKUP’s quirks if your version supports it — but you should still be able to read VLOOKUP, because it is in every legacy workbook you will ever inherit.

5. INDEX

Job description: Returns the value at a given row (and column) position within a range.

Office scenario: You build a monthly summary where a dropdown picks the month and the sheet pulls that month’s figures. Combined with MATCH, INDEX retrieves data from any direction — left, right, above, below.

=INDEX(B2:B13, 4)

That returns the fourth value in the range — April’s number if B2 starts at January. The famous combo is INDEX MATCH:

=INDEX(Sales!C:C, MATCH(A2, Sales!A:A, 0))

It is more flexible than VLOOKUP and does not break when someone inserts a column into the source table. The INDEX function on its own is also the cleanest way to grab “the Nth item” from any list.

The Counters and Conditional Adders

6. COUNTIF

Job description: Counts how many cells in a range meet a condition.

Office scenario: Your team tracker has a Status column and your manager asks, every Monday, how many tickets are still “Open.” Stop filtering and reading the row count off the status bar.

=COUNTIF(C2:C500, "Open")

COUNTIF takes wildcards too: =COUNTIF(A:A, "*urgent*") counts every cell containing the word “urgent” anywhere in the text. For multiple conditions, graduate to COUNTIFS — same idea, plural ranges.

7. SUMIF

Job description: Adds only the cells that meet a condition.

Office scenario: One long export of expenses, one Category column, and you need total travel spend for the quarterly review.

=SUMIF(B2:B500, "Travel", D2:D500)

Read it as: look in B, find “Travel,” sum the matching rows from D. SUMIF plus a short list of categories gives you a summary table that updates itself every time new data is pasted in — which is most of what people use pivot tables for, with none of the refresh step. Need two or more conditions (Travel and Q2)? That is SUMIFS, where the sum range moves to the first argument.

The Text and Date Workhorses

8. TEXT

Job description: Converts a number or date into text in exactly the format you specify.

Office scenario: You are building an email mail-merge column and =A2 shows the invoice date as 46184 or the amount as 1234.5. TEXT makes values presentable inside sentences.

=TEXT(A2, "mmm d, yyyy")      → "Jun 11, 2026"
=TEXT(B2, "$#,##0.00")        → "$1,234.50"

The second argument uses the same format codes as Excel’s custom number formatting, so anything you can do in Format Cells, you can do in a TEXT formula — and the same codes power Excel’s whole family of text functions.

9. CONCAT

Job description: Joins text from multiple cells or strings into one cell.

Office scenario: HR sends you first names in column A and last names in column B; the badge system wants “Last, First” in one field.

=CONCAT(B2, ", ", A2)

CONCAT replaced the older CONCATENATE and can accept whole ranges. If you need a delimiter between every piece — say, joining five address fields with commas — TEXTJOIN does it in one shot and can skip blanks:

=TEXTJOIN(", ", TRUE, A2:E2)

Combine CONCAT with TEXT from above and you can generate full sentences: =CONCAT("Invoice due ", TEXT(C2, "mmm d"), " for ", TEXT(D2, "$#,##0")).

10. TODAY

Job description: Returns the current date and updates automatically every time the workbook recalculates.

Office scenario: An accounts-receivable aging sheet. You need to know how many days each invoice has been outstanding — and you need it correct tomorrow without touching anything.

=TODAY()-B2

If B2 holds the invoice date, that returns days outstanding (format the result as a number, not a date). Wrap it in IF and you have an automatic flag: =IF(TODAY()-B2>30, "OVERDUE", ""). One warning about TODAY: because it recalculates, never use it where you need a frozen timestamp — for that, type Ctrl + ; to insert today’s date as a static value.

The Polish: Error Handling and Dynamic Output

11. IFERROR

Job description: Returns your formula’s result normally, but substitutes a value you choose if the formula errors.

Office scenario: Your lookup sheet works perfectly until someone adds a new product code that is not in the master list yet, and suddenly the report your VP sees is full of #N/A.

=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Check code")

IFERROR is the difference between a sheet that looks professional and one that looks broken. Two rules: wrap it around formulas only after they are debugged (it hides real mistakes too), and prefer an informative message or 0 over an empty string when other formulas depend on the cell.

12. FILTER

Job description: Returns every row from a range that matches your criteria, as a live spilling result.

Office scenario: From a 2,000-row sales log, you need a tab that always shows just the West region’s open deals — and updates the moment the source changes. Manual copy-paste filtering goes stale the same day.

=FILTER(A2:E2000, (C2:C2000="West")*(D2:D2000="Open"), "No matches")

Multiply conditions together for AND, add them for OR. The FILTER function (Microsoft 365 / Excel 2021+) spills results into neighboring cells automatically, which means one formula replaces an entire manual report. It pairs beautifully with SORT and UNIQUE once you are comfortable with it.

How to Actually Learn These

Do not memorize syntax from a list — pick one real spreadsheet you touch weekly and force one new function into it each week. Two habits that accelerate everything: learn absolute references (the $A$1 style, toggled with F4) so your formulas survive being copied, and build every lookup against clean source data before blaming the function. Twelve weeks from now, you are the person other people ask.

Frequently Asked Questions

Should I learn VLOOKUP or XLOOKUP first?

Learn XLOOKUP if your Excel version has it (Microsoft 365 or Excel 2021 and later) — it is simpler and safer. But learn to read VLOOKUP regardless, because nearly every workbook built before 2020 uses it, and you will inherit those files.

Why does my SUMIF or COUNTIF return 0 when I can see matching values?

Nine times out of ten the “numbers” in your range are actually text — usually from a system export. Look for a green triangle in the cell corner or values aligned left. Convert them with Data → Text to Columns, or multiply the column by 1 in a helper column.

What’s the difference between ROUND and just formatting a cell to two decimals?

Formatting changes only what you see; the cell still stores the full value, so downstream totals can be off by a penny or more. ROUND changes the stored value itself. For anything financial, use ROUND in the formula.

FILTER returns a #SPILL! error — what does that mean?

FILTER needs empty cells below and beside it to spill its results into. Something — often a stray value or a merged cell — is blocking that area. Clear the blocking cells and the formula resolves itself.