Excel Formulas I Use Every Day as a Data Analyst (And You Should Too!)

As a data analyst, I’ve found that Excel is still one of the most powerful tools for quick data manipulation—despite the rise of Python and SQL. While advanced tools have their place, knowing the right Excel formulas can save hours of work.

In this post, I’ll share the most common Excel formulas I rely on daily for cleaning, analyzing, and summarizing data—along with real-world examples.

1. VLOOKUP / XLOOKUP – The Data Matching Lifesaver

When to use: Merging datasets, finding related information.

• Example: You have customer IDs in one sheet and transaction details in another. Use VLOOKUP to pull in purchase history.excelCopy=VLOOKUP(A2, Transactions!A:B, 2, FALSE)
• Pro Tip: XLOOKUP (newer in Excel) is more flexible and avoids VLOOKUP‘s limitations.

2. SUMIFS / COUNTIFS – Conditional Aggregations Made Easy

When to use: Filtering and summarizing data based on criteria.

• Example: Calculate total sales for a specific product in Q1.excelCopy=SUMIFS(Sales_Amount, Product_Column, “Laptop”, Date_Column, “>=1/1/2024”, Date_Column, “<=3/31/2024”)
• Why it’s gold: Beats manual filtering and pivot tables for quick ad-hoc analysis.

3. IF + AND/OR – Logic for Data Cleaning

When to use: Flagging outliers, categorizing data.

• Example: Tag customers as “High Value” if they spent >$500 and made 3+ purchases.excelCopy=IF(AND(B2>500, C2>=3), “High Value”, “Standard”)
• Bonus: Nest with IFERROR to handle messy data gracefully.

4. INDEX-MATCH – A More Flexible VLOOKUP

When to use: When VLOOKUP fails (left-side lookups, dynamic columns).

• Example: Find an employee’s department without needing the lookup column to be first.excelCopy=INDEX(Departments, MATCH(Employee_Name, Employees, 0))
• Why I prefer it: Faster with large datasets and fewer errors.

5. TEXTJOIN / CONCAT – Combining Text Like a Pro

When to use: Merging names, addresses, or creating IDs.

• Example: Combine first and last names with a space:excelCopy=TEXTJOIN(” “, TRUE, A2, B2)
• Old-school alternative: =A2 & " " & B2, but TEXTJOIN handles blanks better.

6. UNIQUE / FILTER – Dynamic Arrays for Modern Excel

When to use: Extracting distinct values or filtering on the fly.

• Example: List all unique products sold:excelCopy=UNIQUE(Product_Column)
• Game-changer: No more manual removal of duplicates!

7. DATE Functions – Handling Time Data

When to use: Calculating durations, deadlines, or aging reports.

• Example: Days overdue from due date:excelCopy=TODAY() – Due_Date
• Bonus: EOMONTH() for month-end calculations.

8. Pivot Tables – The Ultimate Summarization Tool

Not a formula, but a must-know!

• Drag-and-drop to summarize 100k rows in seconds.
• Pro Move: Use GETPIVOTDATA to pull PivotTable results into formulas.

Final Thoughts

While tools like Python and SQL dominate advanced analytics, Excel remains unbeatable for quick, iterative analysis. Mastering these formulas will make you faster and more effective—whether you’re cleaning data, building reports, or validating results.

What’s your go-to Excel formula? Let me know in the comments!