Excel Formulas

📘 All Essential Excel Formulas (Explained in Plain English)

Microsoft Excel isn’t just another spreadsheet—it’s honestly a lifesaver once you get the hang of it. It can do math, analyze your data, help with text, dates, and even make your reports way more dynamic.

If you’re new or just want a refresher, here’s a full list of everyday Excel formulas. I’ve grouped them into types and explained them like how I’d explain it to a friend. 😊

🔢 Basic Math Formulas

These are your go-to formulas when you wanna do simple stuff like adding or averaging numbers.

✅ SUM() – Add Stuff Up

This one’s basic but super useful.

=SUM(A1:A5)

It just adds everything from A1 to A5. Nothing fancy.

✅ AVERAGE() – What’s the Average?

Use this when you need to get the average (mean) of numbers.

=AVERAGE(B1:B10)

It adds all the values and divides them by the number of cells. Simple as that.

✅ MIN() / MAX() – Smallest & Biggest Numbers

=MIN(A1:A10)  
=MAX(A1:A10)

MIN gives the smallest, MAX gives the biggest number in the range.

✅ ROUND() – Round Numbers Neatly

=ROUND(3.14159, 2)

This will round Pi to 2 decimal places → 3.14. You can round up or down using ROUNDUP or ROUNDDOWN too.

📅 Date and Time Formulas

Time flies, and so does Excel if you know how to handle dates and times.

✅ TODAY() – What Day is it?

=TODAY()

Just gives today’s date. It updates every day.

✅ NOW() – What Time is it?

=NOW()

Returns the current date and time. Pretty cool.

✅ DATEDIF() – How Many Days Between?

=DATEDIF(A1, B1, "d")

Shows how many days are between two dates. Kinda hidden but super helpful.

🧠 Logical Formulas

Great for creating conditions like “Pass/Fail” or “Yes/No” type of stuff.

✅ IF() – Classic Yes or No

=IF(A1>60, "Pass", "Fail")

If A1 is more than 60, it shows “Pass”. Otherwise, you guessed it—”Fail”.

✅ IFERROR() – Hide Those Nasty Errors

=IFERROR(A1/B1, "Check")

If there’s an error (like dividing by 0), it just says “Check” instead of blowing up.

✅ AND() / OR() – Combo Conditions

=AND(A1>10, B1<20)  
=OR(A1>10, B1<20)

AND needs both conditions to be true. OR is chill—only one needs to be true.

🔍 Lookup & Reference Formulas

These ones help you search for stuff in a table or list.

---

✅ VLOOKUP() – Old School Search

=VLOOKUP(101, A2:C10, 2, FALSE)

Looks for 101 in column A, and returns the matching value from column B.

✅ XLOOKUP() – The New & Better Search

=XLOOKUP("Apple", A2:A10, B2:B10)

Searches for “Apple” in A2:A10 and gives you what’s in B2:B10 from the same row.

✅ INDEX() + MATCH() – Power Combo

=INDEX(B2:B10, MATCH("John", A2:A10, 0))

Finds “John” in A column and gives you the value next to it in column B.

📝 Text Formulas

You’ll love these if you work with names, addresses, or messy text data.

✅ CONCAT() / TEXTJOIN() – Combine Words

=CONCAT(A1, B1)  
=TEXTJOIN(" ", TRUE, A1, B1, C1)

Both join stuff together. TEXTJOIN is smarter—it lets you add a space or comma between items.

✅ LEFT(), RIGHT(), MID() – Pick Parts of Text

=LEFT(A1, 3)     ' First 3 letters  
=RIGHT(A1, 4)    ' Last 4 letters  
=MID(A1, 2, 5)   ' From 2nd character, get 5 letters

✅ LEN() – Count Characters

=LEN(A1)

Shows how many characters are in a cell (yes, even spaces count).

✅ TRIM() – Clean Up Extra Spaces

=TRIM(A1)

Gets rid of weird spaces before or after text. I use this a lot when copying from websites.

📊 Counting Formulas

Helpful when you want to know how many things are there, or how many match a rule.

✅ COUNT() – Count Numbers

=COUNT(A1:A10)

Only counts cells with numbers.

✅ COUNTA() – Count All Filled Cells

=COUNTA(A1:A10)

Counts anything that isn’t blank—even if it’s just a letter.

✅ COUNTBLANK() – Count Empty Spaces

=COUNTBLANK(A1:A10)

✅ COUNTIF() / COUNTIFS() – Count With Conditions

=COUNTIF(A1:A10, ">50")  
=COUNTIFS(A1:A10, ">50", B1:B10, "<100")

Really handy when filtering specific values.

🎲 Random Number Formulas

Need fake data? These will help.

✅ RAND() – Random Decimal

=RAND()

Gives a number like 0.234234. Changes every time you refresh.

✅ RANDBETWEEN() – Random Whole Numbers

=RANDBETWEEN(1, 100)

Boom—random number between 1 and 100. Great for test data.

🔄 Data Cleanup & Validation

These help when you’re cleaning messy data.

✅ ISBLANK() / ISNUMBER() / ISTEXT()

=ISBLANK(A1)  
=ISNUMBER(B1)  
=ISTEXT(C1)

Tells you if the cell is blank, a number, or text. Helps with validations.

✅ SUBSTITUTE() – Swap Out Words

=SUBSTITUTE(A1, "old", "new")

Replaces all instances of “old” with “new”. Not case-sensitive tho.

⚡ Dynamic Array Formulas (For New Excel Users)

These are available in Excel 365 or Excel 2021+ only.

✅ UNIQUE() – Show Only Unique Stuff

=UNIQUE(A1:A10)

✅ SORT() – Sort a List

=SORT(A1:A10)

✅ FILTER() – Filter by a Condition

=FILTER(A1:B10, B1:B10="Yes")

✅ TRANSPOSE() – Flip Rows and Columns

=TRANSPOSE(A1:C3)

🧾 Final Thoughts

Honestly, Excel can be confusing at first, but these formulas are your best friends. You don’t need to learn them all at once. Just start with the basics like SUM(), IF(), and VLOOKUP().