10 Excel Formulas Everyone Should Know (With Real Examples)

Why These 10 Formulas Will Change How You Work

I have been using Excel professionally for over a decade, and here is something most people do not realize: you can handle about 90% of everyday spreadsheet tasks with just 10 formulas. I have seen colleagues spend hours manually copying data, counting entries by hand, or building workarounds for problems that a single formula could solve in seconds.

These are not obscure, advanced formulas. They are the practical workhorses that show up in virtually every spreadsheet scenario, from tracking expenses to managing inventory to analyzing sales data. I will explain each one with real examples that mirror actual work situations.

If you have ever thought “there must be a better way to do this in Excel,” you are about to find it.

1. IF: Making Decisions in Your Spreadsheet

The IF formula checks a condition and returns one value if it is true and a different value if it is false. It is the foundation of adding logic to your data.

Syntax: =IF(condition, value_if_true, value_if_false)

Real example: You have a list of student test scores in column B and want to assign Pass or Fail in column C. The passing score is 70.

Before: Manually typing “Pass” or “Fail” next to each of 200 student scores.

Formula: =IF(B2>=70, "Pass", "Fail")

After: Instant Pass/Fail designation for every student. Drag the formula down and 200 rows are done in seconds.

Practical variations:

• =IF(B2>100, "Over budget", "Within budget") for expense tracking
• =IF(A2="", "Missing", "Complete") to flag empty cells in data entry
• Nested IF for multiple conditions: =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "F")))

The nested IF approach works but gets messy with many conditions. For 4+ conditions, consider using IFS instead: =IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F").

2. VLOOKUP: Finding Data Across Tables

VLOOKUP searches for a value in the first column of a table and returns a corresponding value from another column. Think of it as looking up a name in a phone book and finding their number.

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

Real example: You have a product database in Sheet2 with product IDs in column A and prices in column C. On your order form in Sheet1, you want to automatically pull the price when you enter a product ID.

Before: Manually looking up each product price and typing it in. Error-prone and slow.

Formula: =VLOOKUP(A2, Sheet2!A:C, 3, FALSE)

This searches for the product ID in A2, looks in columns A through C of Sheet2, returns the value from the 3rd column (price), and FALSE means it needs an exact match.

After: Type a product ID and the price appears instantly. Change the ID and the price updates automatically.

Critical tip: Always use FALSE (exact match) as the last parameter unless you specifically need approximate matching. Leaving it out or using TRUE will give you wrong results more often than not.

Common pitfall: VLOOKUP only looks to the right. If your lookup value is in column B and you need a result from column A (to the left), VLOOKUP cannot do it. This is where INDEX/MATCH comes in, which we will cover later.

3. SUMIF: Adding Numbers With Conditions

SUMIF adds up numbers in a range, but only for rows that meet a specific condition. It is SUM with a filter.

Syntax: =SUMIF(range, criteria, sum_range)

Real example: You have a list of expenses with categories in column A and amounts in column B. You want to know the total spent on “Food.”

Before: Manually scanning the list, picking out every “Food” entry, and adding them up. Miss one and your total is wrong.

Formula: =SUMIF(A:A, "Food", B:B)

After: Instant total for any category. Change “Food” to “Transport” and get that total immediately.

Useful variations:

• =SUMIF(B:B, ">1000", B:B) sums all values greater than 1000
• =SUMIF(A:A, "*report*", B:B) sums amounts where the category contains “report” (wildcards)
• =SUMIFS(C:C, A:A, "Food", B:B, ">50") sums Food expenses over $50 using SUMIFS for multiple conditions

4. COUNTIF: Counting With Conditions

COUNTIF counts the number of cells that meet a condition. Simple but incredibly useful for data analysis.

Syntax: =COUNTIF(range, criteria)

Real example: You have a survey responses spreadsheet with answers in column D. You want to count how many respondents said “Yes.”

Before: Using Ctrl+F to find and manually counting, or sorting and counting by eye.

Formula: =COUNTIF(D:D, "Yes")

After: Instant count that updates as new responses come in.

Useful variations:

• =COUNTIF(A:A, ">100") counts values above 100
• =COUNTIF(A:A, "<>""") counts non-empty cells
• =COUNTIF(A:A, A2) counts duplicates of a specific value
• =COUNTIFS(A:A, "Sales", B:B, ">1000") counts with multiple conditions

Duplicate detection trick: In a new column, use =COUNTIF(A:A, A2). Any result greater than 1 means that value appears more than once. This is a quick way to find duplicates in your data.

5. INDEX/MATCH: VLOOKUP’s Superior Replacement

INDEX/MATCH combines two functions to look up values with more flexibility than VLOOKUP. It can look in any direction, does not break when columns are rearranged, and is faster on large datasets.

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

Real example: Same product lookup scenario, but now the product ID is in column C and you need the product name from column A (to the left). VLOOKUP cannot do this, but INDEX/MATCH can.

Formula: =INDEX(Sheet2!A:A, MATCH(D2, Sheet2!C:C, 0))

Breaking it down: MATCH finds the row number where D2 appears in column C. INDEX then returns the value from that same row in column A.

Before: Rearranging your data table so VLOOKUP works, or manually looking up values.

After: Flexible lookups that work regardless of column order.

Why you should learn INDEX/MATCH:

• It looks in any direction (left, right, different sheet)
• It does not break when you insert or delete columns
• It is faster than VLOOKUP on large datasets (100,000+ rows)
• Once you learn it, you will rarely go back to VLOOKUP

The 0 at the end of MATCH means exact match, similar to FALSE in VLOOKUP. Always use 0 unless you have a specific reason for approximate matching.

6. CONCATENATE (or the & Operator): Combining Text

Combining text from multiple cells into one cell is a common need. You can use the CONCATENATE function or the simpler & operator.

Syntax: =A2 & " " & B2 or =CONCATENATE(A2, " ", B2)

Real example: You have first names in column A and last names in column B. You need full names in column C.

Before: Typing each full name manually.

Formula: =A2 & " " & B2

After: Full names generated automatically.

More useful examples:

• Building email addresses: =LOWER(A2 & "." & B2 & "@company.com")
• Creating file paths: ="C:\Reports\" & A2 & "_" & TEXT(B2, "YYYY-MM") & ".pdf"
• Formatting data: =A2 & " (" & TEXT(B2, "$#,##0") & ")"

Modern alternative: If you are on Excel 365 or Google Sheets, TEXTJOIN is even better for combining multiple cells: =TEXTJOIN(", ", TRUE, A2:A10) joins all values with commas, skipping blank cells.

7. TEXT: Formatting Numbers and Dates as Text

The TEXT function converts a number or date into text with a specific format. Essential when you need dates or numbers to display a certain way inside text strings.

Syntax: =TEXT(value, format_code)

Real example: You have a date in cell A2 (2026-04-01) and want to display it as “April 1, 2026” in a sentence.

Formula: ="Report generated on " & TEXT(A2, "MMMM D, YYYY")

Result: “Report generated on April 1, 2026”

Common format codes:

• "$#,##0.00" formats 1234.5 as “$1,234.50”
• "0.0%" formats 0.856 as “85.6%”
• "YYYY-MM-DD" formats a date as “2026-04-01”
• "DDDD" returns the day name, like “Wednesday”
• "#,##0" formats 1234567 as “1,234,567”

TEXT is especially useful in dashboard cells where you combine explanatory text with dynamic numbers.

8. TRIM: Cleaning Up Messy Data

TRIM removes all extra spaces from text, leaving only single spaces between words. This sounds trivial until you realize that invisible extra spaces are one of the most common reasons formulas fail.

Syntax: =TRIM(text)

Real example: You imported data from another system and your VLOOKUP keeps returning #N/A even though the values look identical. The culprit is almost always invisible spaces.

Before: Hours of frustration trying to figure out why your formulas are not working.

Formula: =TRIM(A2)

After: Clean data that works with every formula.

Pro tip: When importing data from any external source, such as databases, web scraping, CSV exports, or copy-pasted data, always run TRIM on text columns before using them in lookups. It will save you debugging time consistently.

Combine TRIM with CLEAN to also remove non-printable characters: =TRIM(CLEAN(A2)). This handles data from the messiest sources.

9. IFERROR: Handling Errors Gracefully

IFERROR wraps around any formula and returns a custom value if that formula produces an error. Instead of showing ugly #N/A, #DIV/0!, or #VALUE! errors, you can show a meaningful message or a default value.

Syntax: =IFERROR(formula, value_if_error)

Real example: Your VLOOKUP returns #N/A when a product ID is not found. Instead of leaving error messages scattered through your spreadsheet, wrap it.

Before: =VLOOKUP(A2, Products!A:C, 3, FALSE) showing #N/A for missing products.

Formula: =IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Product not found")

After: Clean, readable output. Missing products show a helpful message instead of a cryptic error code.

Other common uses:

• =IFERROR(A2/B2, 0) prevents #DIV/0! errors in division
• =IFERROR(INDEX(MATCH(...)), "") returns a blank cell instead of an error for failed lookups

Caution: Do not overuse IFERROR to mask legitimate errors. If a formula should not be producing errors, investigating the root cause is better than hiding it behind IFERROR. Use it for expected scenarios like optional lookups, not as a blanket error suppressor.

10. Pivot Tables: Not a Formula, But Essential

I know this is not technically a formula, but no list of essential Excel skills is complete without mentioning pivot tables. A pivot table can summarize thousands of rows of data into a clear, interactive summary in about 30 seconds.

Real example: You have 10,000 rows of sales data with columns for Date, Salesperson, Region, Product, and Amount. Your boss asks “What are the total sales by region and product for Q1?”

Before: Hours of SUMIF formulas, manual filtering, and praying you did not miss anything.

After: Select your data > Insert > Pivot Table. Drag Region to Rows, Product to Columns, Amount to Values, and Date to Filters. Filter for Q1 dates. Done in 30 seconds.

How to create one:

1. Click any cell in your data
2. Go to Insert > Pivot Table
3. Choose where to place it (new worksheet is usually best)
4. Drag fields from the field list to Rows, Columns, Values, and Filters

Tips for better pivot tables:

• Make sure your data has clear headers in the first row
• Remove blank rows and columns from your source data
• Use “Refresh” (right-click > Refresh) after adding new data
• Double-click any number in the pivot table to see the underlying rows

Pivot tables feel intimidating the first time but become second nature after creating 3~4 of them. They are genuinely the most powerful data analysis tool available to non-programmers.

Putting It All Together

Here is a practical scenario that uses multiple formulas together. You are managing an event registration list.

Column A has names, B has email addresses, C has registration dates, D has payment status, and E has the amount paid.

Count total registrations: =COUNTA(A:A)-1 (subtract 1 for the header)

Count paid registrations: =COUNTIF(D:D, "Paid")

Total revenue: =SUMIF(D:D, "Paid", E:E)

Registration summary: =COUNTIF(D:D, "Paid") & " paid, " & COUNTIF(D:D, "Unpaid") & " unpaid"

Look up a specific person’s status: =IFERROR(INDEX(D:D, MATCH("John Smith", A:A, 0)), "Not found")

Format a report line: ="Total revenue as of " & TEXT(TODAY(), "MMMM D, YYYY") & ": " & TEXT(SUMIF(D:D, "Paid", E:E), "$#,##0")

Each formula is simple on its own. Combined, they turn a raw data list into an interactive dashboard.

Next Steps

Start with IF and SUMIF since those two alone will save you the most time. Then learn VLOOKUP for cross-referencing data, and gradually move to INDEX/MATCH as you get comfortable.

Practice with your own real data. Formulas stick much better when you are solving an actual problem rather than following a tutorial with fake data. The next time you catch yourself manually counting, sorting, or looking something up in a spreadsheet, stop and think about which formula could do it for you. That moment of pause is where real Excel proficiency begins.