AI Generated Illustration for What Are the Most Commonly Used Excel Formulas and Their Functions?

What Are the Most Commonly Used Excel Formulas and Their Functions?

Most people treat Excel like a very expensive calculator. They type numbers in, they SUM them, they feel good about themselves. Then someone asks for a report and they spend four hours copy-pasting things they could have done in four minutes with the right formula. What are the most commonly used Excel formulas and their functions? It's a fair question — and honestly, the answer is shorter than most Excel training courses would like you to believe. You don't need 400 formulas. You need about ten. Maybe fifteen if you're feeling ambitious.

Amanda Thomas July 6, 2026 8 min read 0 views

The big four formulas everyone needs first

Start here. These four formulas appear in virtually every spreadsheet that does anything useful. **SUM** adds up a range of numbers. =SUM(A1:A10) adds everything from A1 to A10. Dead simple. Wildly useful. You already know this one, but it's worth saying out loud because SUM with a large range beats typing =A1+A2+A3... by about a mile and a half. **AVERAGE** does exactly what it sounds like. =AVERAGE(A1:A10) gives you the mean of that range. Rule of thumb: use AVERAGE for quick sense-checks, but know it's sensitive to outliers. One rogue value can drag your average somewhere misleading. (Statistics problems, brought to you by spreadsheets since 1985.) **IF** is where Excel gets interesting. It makes decisions. =IF(B2>500,"Over budget","Fine") checks if B2 exceeds 500 and returns one of two responses. The structure is always: =IF(condition, value if true, value if false). Nest multiple IFs together and you can build something surprisingly close to logic — though I'd recommend stopping at two or three nested IFs before it becomes unreadable by any human being, including yourself six weeks later. **IFERROR** is the formula that stops your spreadsheet looking like it's having a breakdown. Wrap any formula in =IFERROR(your formula, "Not found") and instead of seeing #N/A or #VALUE! errors everywhere, you get a clean fallback message. It's basically Excel's way of saying "don't panic" — and it makes your work look professional without much effort.

Lookup formulas: finding needles in spreadsheet haystacks

This is where most people either level up or stay stuck forever. **VLOOKUP** searches for a value in the leftmost column of a table and returns data from a column to the right. The syntax is =VLOOKUP(lookup value, table range, column number, FALSE). That FALSE at the end means exact match — almost always what you want. VLOOKUP is the workhorse of thousands of finance departments, and it's been doing its job reliably since before most of us had broadband. Its main weakness: it only looks right. If the column you want is to the left of your lookup column, VLOOKUP shrugs and tells you nothing useful. **INDEX MATCH** solves that. It's actually two formulas working together. INDEX returns a value from a specific position in a range. MATCH finds what position a value sits at. Combined, =INDEX(C1:C100,MATCH(F1,A1:A100,0)) finds whatever's in column C that corresponds to your search value in column A — and it doesn't care which direction it's looking. It's more flexible than VLOOKUP and doesn't break when someone inserts a column. (Which someone always does.) **XLOOKUP** is the modern version that does everything better. =XLOOKUP(lookup value, lookup range, return range) — cleaner syntax, works in any direction, handles errors gracefully. If your Excel supports it, XLOOKUP is the one to use. Think of VLOOKUP as the reliable older uncle, and XLOOKUP as the younger sibling who learned from his mistakes.

Counting and adding with conditions attached

Plain SUM and COUNT are great. But real data always has conditions attached. **COUNTIF** counts cells that meet a criterion. =COUNTIF(A1:A50,"Approved") counts every cell in that range containing the word Approved. You can also use comparisons: =COUNTIF(B1:B50,">100") counts everything over 100. Simple, fast, essential for any kind of data summary. **COUNTIFS** (with an S) lets you apply multiple conditions at once. You need sales from London AND over £1,000? COUNTIFS handles it. Same logic, multiple criteria pairs. **SUMIF** adds numbers that meet a condition. =SUMIF(A1:A50,"London",B1:B50) adds all the values in column B where the corresponding cell in column A says London. Nine times out of ten, this is exactly what someone means when they say "I need to add up just these specific rows." **SUMIFS** does the same with multiple conditions. Once you understand COUNTIFS and SUMIFS, you'll reckon you've been wasting years doing this manually. Because you probably have been. No judgement.

The underrated formulas most explainers skip

Most Excel tutorials stop at VLOOKUP and call it a day. Here are the ones that quietly save hours of annoying work. **TRIM** removes extra spaces from text. Anyone who's ever imported data from another system knows that mysterious extra space is the enemy of every lookup formula ever written. =TRIM(A1) cleans it up. Run it before any lookup if your data came from somewhere else. Seriously. TRIM before VLOOKUP is practically a rule of life. **LEN** tells you the length of a text string. =LEN(A1) returns the number of characters. Sounds niche, but it's brilliant for data validation — if a product code should always be exactly eight characters and something returns nine, LEN flags it immediately. **TEXT** converts numbers and dates into formatted text. =TEXT(A1,"dd/mm/yyyy") turns a date serial number into something a human can read. =TEXT(B1,"£#,##0.00") formats a number as currency in text form. Useful whenever you're building sentences or reports that mix numbers with words. **CONCATENATE** (or just the & operator) joins text from different cells. ="Hello, "&A1&"!" combines fixed text with cell content. Handy for building names, codes, or any string that pulls from multiple columns. The ampersand method is quicker to type and does the same job — though CONCATENATE does have a certain old-school charm about it.

When not to bother with fancy formulas at all

Here's my one strong opinion, and I'll back it up: if your dataset is under about 50 rows and you're doing a one-off task, a formula is often overkill. Excel's built-in sort, filter, and subtotal tools handle a huge amount of what people reach for formulas to do. I've watched people spend 20 minutes writing a SUMIFS formula for something they could have solved in 30 seconds with a filter and a status bar total at the bottom of the screen. The status bar in Excel (the grey bar at the bottom) automatically shows SUM, COUNT, and AVERAGE for any selected range. No formula required. Most people don't know it's there because no one ever mentions it — it's the Ringo Starr of Excel features. Underrated, quietly doing important work, mostly overlooked. The rule of thumb here: use formulas when the calculation needs to update automatically, repeat across many rows, or live permanently in the spreadsheet. If you just need a quick answer once, don't over-engineer it. Formula for the right job. Filter for the quick job. Also worth saying: VLOOKUP on a table with 200,000 rows will slow your spreadsheet to a crawl. At that scale, consider Power Query, which handles large data transformations faster and more cleanly than stacking formulas ever will. Knowing when to step outside the formula world is as valuable as knowing the formulas themselves.

The honest summary

The most commonly used Excel formulas aren't complicated. SUM, IF, VLOOKUP, XLOOKUP, COUNTIF, SUMIF, INDEX MATCH, IFERROR, TRIM, and LEN cover the overwhelming majority of what real-world spreadsheet work looks like. Learn what each one does, practise them on actual data, and you'll go from "I know Excel" to "I actually know Excel" faster than you'd think. And if someone asks you to find a value in a column to the left of your lookup column, now you know exactly what to do. You've got the LOOKUP of a winner.

Frequently Asked Questions

SUM is the most commonly used Excel formula by a wide margin. It adds up a range of numbers instantly. After that, IF, VLOOKUP, and COUNTIF show up constantly in real-world spreadsheets. If you only learn three formulas, make it SUM, IF, and VLOOKUP — you'll handle roughly 80% of what most workplaces throw at you.
The IF formula checks whether a condition is true or false, then returns one value for true and another for false. For example, =IF(A1>100,"Over budget","Fine") checks if a number exceeds 100 and labels it accordingly. It's the closest Excel gets to making a decision for you — which, honestly, is more than most meetings achieve.
VLOOKUP searches for a value in the first column of a table and returns something from a column to the right. Use it when you need to pull matching data from a separate list — like finding a price from a product code. It's not perfect, but it's been solving that problem since Excel was young and had more hair.
Yes, XLOOKUP is better than VLOOKUP in almost every way. It searches in any direction, handles missing values more cleanly, and doesn't break when you insert columns. If your version of Excel supports it (Microsoft 365 and Excel 2021 onwards), use XLOOKUP and don't look back. VLOOKUP had a good run though — respect the veteran.
COUNTIF counts the number of cells in a range that meet a specific condition. For example, =COUNTIF(A1:A100,"Yes") counts every cell containing the word Yes. It's brilliant for quick tallies — how many orders are over £500, how many entries say Pending, that kind of thing. Simple formula, surprisingly powerful in practice.
COUNT only counts cells that contain numbers. COUNTA counts any cell that isn't empty — numbers, text, dates, the lot. If your data is a mix of text and numbers and you want a headcount, COUNTA is your formula. COUNT will just quietly ignore anything that isn't a number and give you a suspiciously low result.
CONCATENATE joins text from multiple cells into one. So =CONCATENATE(A1," ",B1) might turn "John" and "Smith" into "John Smith". These days Excel prefers the ampersand shorthand or the CONCAT function, but CONCATENATE still works fine. It's basically the duct tape of Excel — not glamorous, but it holds things together when you need it.
Use INDEX MATCH when you need to search a column that isn't the first one in your table, or when you want to look left instead of right. VLOOKUP can only look rightward, which is a genuine limitation. INDEX MATCH is more flexible, doesn't break when columns move, and once you get your head around it, you'll wonder why you waited.

Related Topics