Whether you're tracking your monthly budget, analyzing sales data, or just trying to make sense of a spreadsheet someone else sent you, Excel formulas are your best friend. They can save you hours of manual work and help you uncover insights buried in your data. Here’s a breakdown of the top 10 Excel formulas everyone should know — with real-life examples to get you started.
1. SUM() – Add It All Up
Formula: =SUM(A1:A10)
Use it when: You need a quick total of a range of numbers.
Example: Total sales from January to October.
2. AVERAGE() – Find the Mean
Formula: =AVERAGE(B1:B10)
Use it when: You want to know the average score, expense, or value.
Example: Average test score across all students.
3. IF() – Decision Maker
Formula: =IF(C2>70, "Pass", "Fail")
Use it when: You need to apply logic based on conditions.
Example: Pass/fail result based on exam score.
4. VLOOKUP() / XLOOKUP() – Search Like a Pro
Formula (VLOOKUP): =VLOOKUP("Apples", A2:B10, 2, FALSE)
Formula (XLOOKUP): =XLOOKUP("Apples", A2:A10, B2:B10)
Use it when: You need to pull information from another table.
Example: Find the price of an item based on its name.
5. CONCAT() / TEXTJOIN() – Combine Text
Formula (CONCAT): =CONCAT(A1, " ", B1)
Formula (TEXTJOIN): =TEXTJOIN(", ", TRUE, A1:A5)
Use it when: You want to join names, addresses, or any text fields.
Example: Full name from first and last name columns.
6. COUNT() / COUNTA() / COUNTIF() – Count What Matters
COUNT(): Counts numbers
COUNTA(): Counts non-blank cells
COUNTIF(): Counts cells that meet a condition
Example: =COUNTIF(A2:A10, ">100") – Count values over 100.
7. LEN() – Length of a Text String
Formula: =LEN(A1)
Use it when: You want to check character limits or text lengths.
Example: Character count for a tweet or Instagram bio.
8. TRIM() – Clean Your Data
Formula: =TRIM(A1)
Use it when: You have extra spaces in your data that are messing things up.
Example: Clean up messy imports from other software.
9. NOW() / TODAY() – Current Date and Time
Formula (NOW): =NOW()
Formula (TODAY): =TODAY()
Use it when: You need to timestamp or calculate durations.
Example: Days remaining until a deadline: =DUE_DATE - TODAY()
10. INDEX() + MATCH() – A Better VLOOKUP
Formula: =INDEX(B2:B10, MATCH("Apples", A2:A10, 0))
Use it when: You need a more flexible lookup that doesn’t break if columns move.
Example: Search for employee details based on ID.
Bonus Tip: Combine Formulas for Power Moves 💪
Try nesting formulas like this:
=IF(AVERAGE(B2:B10)>75, "Above Average", "Keep Trying")