Excel remains the number one tool in data analysis — and for good reason. It's accessible, powerful, and used across every industry. But most people only scratch the surface of what Excel can do.
These 10 functions are the ones professional data analysts use every single day. Master them and you'll handle most analysis tasks faster and more accurately.
1. VLOOKUP / XLOOKUP
What it does: Searches for a value in one table and returns a corresponding value from another column.
=VLOOKUP(lookup_value, table_array, col_index, FALSE)
Real-world use: You have a list of employee IDs and need to pull their department names from a master list.
Pro tip: If you're using Excel 365 or later, switch to XLOOKUP — it's more flexible, searches in any direction, and handles errors more gracefully.
=XLOOKUP(A2, MasterList!A:A, MasterList!C:C, "Not Found")
2. IF / IFS
What it does: Returns different values based on a condition.
=IF(B2 >= 80, "Pass", "Fail")
For multiple conditions, use IFS (Excel 365+):
=IFS(B2 >= 90, "A", B2 >= 80, "B", B2 >= 70, "C", TRUE, "F")
Real-world use: Categorizing performance scores, flagging overdue items, or creating status labels.
3. COUNTIF / COUNTIFS
What it does: Counts cells that meet one or more criteria.
=COUNTIF(C:C, "Lagos")
=COUNTIFS(C:C, "Lagos", D:D, ">50000")
Real-world use: How many customers are from Lagos? How many high-value transactions happened last month?
4. SUMIF / SUMIFS
What it does: Adds up values that meet specific criteria.
=SUMIF(B:B, "Marketing", D:D)
=SUMIFS(D:D, B:B, "Marketing", C:C, ">=2026-01-01")
Real-world use: Total sales by region, total expenses by department, revenue by product category.
5. INDEX + MATCH
What it does: A more powerful alternative to VLOOKUP that can look up values in any direction.
=INDEX(C:C, MATCH(A2, B:B, 0))
Why it's better than VLOOKUP:
- Can search left (VLOOKUP can only search right)
- Doesn't break when you insert or delete columns
- Works with large datasets more efficiently
6. TEXT
What it does: Formats numbers, dates, and times as text in a specific format.
=TEXT(A2, "YYYY-MM-DD")
=TEXT(B2, "#,##0.00")
=TEXT(C2, "dddd, MMMM d, yyyy")
Real-world use: Standardizing date formats across datasets, creating readable labels for reports.
7. TRIM + CLEAN
What it does: Removes extra spaces and non-printable characters from text.
=TRIM(CLEAN(A2))
Real-world use: Cleaning imported data that has invisible characters or inconsistent spacing. This is a data cleaning essential — if you're not using TRIM, you're probably getting wrong VLOOKUP results and not knowing why.
8. CONCATENATE / TEXTJOIN
What it does: Combines text from multiple cells.
=TEXTJOIN(", ", TRUE, A2, B2, C2)
Real-world use: Creating full names from first/last name columns, building address strings, generating unique IDs.
Note: TEXTJOIN (Excel 365+) is far superior to the older CONCATENATE function — it handles delimiters and empty cells automatically.
9. IFERROR
What it does: Returns a custom value when a formula produces an error.
=IFERROR(VLOOKUP(A2, Data!A:D, 3, FALSE), "Not Found")
Real-world use: Preventing ugly #N/A, #VALUE!, or #DIV/0! errors in your reports and dashboards. Makes your work look professional.
10. Pivot Tables (Bonus — Not a Function, but Essential)
While not technically a function, pivot tables are the single most important data analysis feature in Excel. They let you:
- Summarize thousands of rows into meaningful totals
- Group data by category, date, or any dimension
- Filter to focus on specific segments
- Compare metrics across different time periods
How to Create a Pivot Table
- Select your data range
- Go to Insert then PivotTable
- Drag fields into Rows, Columns, Values, and Filters
- Right-click any value to change the summary (sum, count, average, etc.)
This is where analysis happens. If you learn nothing else from this list, learn pivot tables.
Quick Reference Table
| Function | Use Case | Difficulty |
|---|---|---|
| VLOOKUP/XLOOKUP | Look up related data | Beginner |
| IF/IFS | Conditional logic | Beginner |
| COUNTIF/COUNTIFS | Count by criteria | Beginner |
| SUMIF/SUMIFS | Sum by criteria | Beginner |
| INDEX+MATCH | Flexible lookups | Intermediate |
| TEXT | Format values | Beginner |
| TRIM+CLEAN | Clean text data | Beginner |
| TEXTJOIN | Combine text | Beginner |
| IFERROR | Handle errors | Beginner |
| Pivot Tables | Summarize and explore | Intermediate |
Practice Makes Perfect
Reading about these functions is one thing — using them on real data is what builds the skill. Here's what we recommend:
- Download a practice dataset from Kaggle or our Resources page
- Try to answer 5 business questions using only these functions
- Time yourself — speed comes with repetition
Want structured practice with feedback? Our Data Analysis with AI Training includes hands-on Excel projects where you'll use all of these functions on real-world datasets, with instructor guidance.
Found this helpful? Share it with a colleague who's learning Excel, or subscribe to our newsletter for weekly tips like this.
