5 Excel Formulas Everyone Should Know (and the Mistakes to Avoid)
New to Excel?
If you’ve ever opened Excel, entered a few numbers, and then stared blankly at the toolbar wondering what to do next - you’re not alone.
Most of us learn Excel by trial and error. But understanding just a few core formulas can make everything click into place. These are the five you’ll use most often - whether you’re managing budgets, tracking data, or creating reports.
Each one is easy to learn, powerful in practice, and (if you’re not careful) surprisingly easy to get wrong.
1. SUM – Add it up automatically
What it does:
Adds up a list of numbers so you don’t have to do it manually.
Example:
=SUM(B2:B10)
You can total sales figures, expenses, or hours worked in seconds.
Why it matters:
SUM is the simplest but most-used formula in Excel. Once you understand how ranges work (B2:B10 means “everything from B2 to B10”), you can apply the same pattern everywhere.
Mistake to avoid:
Typing =B2+B3+B4... instead of using a range. It’s slower, harder to update, and easy to break if you add rows.
2. AVERAGE – Find your typical value
What it does:
Adds up a range of numbers and divides by how many there are.
Example:
=AVERAGE(C2:C9)
Perfect for finding the average sale value, exam score, or customer rating.
Why it matters:
It smooths out highs and lows so you can see the overall trend.
Mistake to avoid:
Including blank cells or a “Total” row in your range — Excel will count them as zeros and pull your average down.
3. IF – Add logic to your sheet
What it does:
Checks whether something is true or false and shows a result based on that.
Example:
=IF(D2>=100, "Target Met", "Target Missed")
Why it matters:
This is where Excel starts thinking for you. Use it to automatically mark sales targets, attendance, or project status — no manual checking needed.
Mistake to avoid:
Forgetting quotation marks around words (“Target Met”). Without them, Excel thinks they’re formulas and returns an error.
4. COUNTIF – Count based on a rule
What it does:
Counts how many cells meet a specific condition — for example, how many times a name, word, or number appears.
Example:
=COUNTIF(A2:A20, "Complete")
Why it matters:
It saves you scanning down a long list. You can instantly count how many people completed training, how many products are “In Stock,” or how many scores are above 70.
Mistake to avoid:
Forgetting that text criteria (like “Complete”) need quotation marks.
5. XLOOKUP – The modern way to find things
What it does:
Searches for something in one list and brings back matching information from another list.
Example:
=XLOOKUP(A2, F2:F20, G2:G20, "Not Found")
Why it matters:
Think of it like a smarter “find and match” tool. If you’ve got a product code in one sheet and need to pull the price from another, XLOOKUP does it instantly.
Why not VLOOKUP?
VLOOKUP used to be the go-to formula for this, but it’s limited - it only searches left-to-right and breaks easily if your columns move.
XLOOKUP is newer, faster, works in any direction, and handles missing data more gracefully.
Mistake to avoid:
Leaving out the final argument ("Not Found") - that’s what stops your sheet showing ugly #N/A errors when something doesn’t match.
Common Mistakes to Avoid Altogether
- Forgetting $ to lock cells (absolute references) when copying formulas.
- Using “+” to build formulas instead of ranges.
- Leaving blank rows inside your data.
- Copying someone else’s spreadsheet without understanding the logic.
The best way to fix these habits is to learn why Excel behaves the way it does - once you do, it all starts to make sense.
Try This Mini Practice
Create a small table with a few rows of data - anything you like (sales, hours, marks).
Then try:
- Add up a column using SUM.
- Find the average using AVERAGE.
- Use IF to flag values above a target.
- Count how many meet the target with COUNTIF.
- Build a simple lookup table and use XLOOKUP to return related info.
It’s five skills that underpin almost everything you’ll do in Excel.
Next Steps
Once these formulas click, Excel stops being intimidating - and starts being useful.
Whether you’re new to spreadsheets or filling gaps in your knowledge, our Excel training courses will help you build real confidence - from everyday essentials to advanced techniques.
With 12 courses covering beginner to advanced levels, our Microsoft Excel training builds skills step by step - from everyday formulas and charts to advanced data analysis, pivot tables, and automation. Each session is hands-on, practical, and built for real workplace application.
- Excel Module 1 – Key Skills and Formula Creation
- Excel Module 2 – Data Analysis and Pivot Tables
- Excel Module 3 – Reporting and Automating
- Excel Module 4 – Advanced Formulas and Functions
- Excel – Mastering Formulas and Functions
- Excel – Pivot Table Specialist
- Excel – Power Query and Power Pivot
- Excel – Charting Specialist
- Excel – Advanced Statistical Analysis
- Excel – Working with Macros
- VBA in Excel – Part 1
- VBA in Excel – Part 2
