Data analysts who know these ten formulas can handle the vast majority of data manipulation tasks in Excel without resorting to VBA, pivot tables for simple operations, or manual work. Each is worth mastering in depth, not just knowing by name.
1. XLOOKUP
The modern successor to VLOOKUP. Finds a value in one range and returns the corresponding value from another range. Unlike VLOOKUP, it works in any direction — you can look left, right, up, or down. It accepts a default value to return when the lookup fails instead of producing an error. Available in Microsoft 365 and Excel 2021+. If your Excel version is older, use INDEX/MATCH.
2. INDEX/MATCH
A two-function combination that does what XLOOKUP does in older versions. MATCH returns the position (row number) of a value within a range. INDEX returns the value at a specified position in a range. Combined, they do any lookup without the left-to-right constraint of VLOOKUP. Many analysts still prefer INDEX/MATCH for its flexibility even when XLOOKUP is available.
3. SUMIFS
Sums values in a column where multiple conditions are met. Example: sum all revenue where region is "North" AND product is "Enterprise". This is the workhorse for conditional aggregation in Excel. SUMIF handles one condition; SUMIFS handles as many as you need.
4. COUNTIFS
Counts rows where multiple conditions are met. The counting equivalent of SUMIFS. Essential for building summary tables, QA checks (how many rows have status "Error"?), and validation reports.
Sohovi lets you set up validation rules for any column and instantly see which rows fall outside them — no code or SQL required.
5. UNIQUE
Returns the distinct values from a range, removing duplicates. Available in Microsoft 365. Turns a column of potentially repeated values into a unique list — without pivot tables or manual deduplication. Use it to build dynamic dropdowns or distinct value summaries.
6. FILTER
Returns a filtered subset of a range based on one or more conditions. Available in Microsoft 365. Replaces the tedious workflow of autofilter → copy → paste with a formula that updates dynamically as the source data changes. Example: return all rows where status is "Pending".
7. IFERROR
Wraps any formula and returns a custom value if the formula produces an error. The most common use is IFERROR(VLOOKUP(...), "") — replace the #N/A error from a failed lookup with a blank cell. Every analyst needs this to produce clean output from lookups that don't always find a match.
8. TEXT
Converts a number or date to a text string in a specified format. Critical for combining dates with text, formatting numbers for display, and ensuring that dates in different locale formats appear consistently. TEXT(A1,"YYYY-MM-DD") produces an ISO date string regardless of the system's regional settings.
9. DATEDIF
Calculates the difference between two dates in years, months, or days. Despite being undocumented (it's a holdover from Lotus 1-2-3), it works in all Excel versions. Essential for age calculations (DATEDIF(DOB, TODAY(), "Y") returns age in years) and tenure calculations.
10. SUMPRODUCT
Multiplies corresponding elements in multiple arrays and sums the results. Predates SUMIFS and works in all Excel versions. Also useful for weighted averages: SUMPRODUCT(scores, weights) / SUM(weights). Its ability to evaluate array logic makes it a versatile tool for complex conditional calculations.
If any of these formulas appears in a spreadsheet you've inherited and you're unsure what it's doing with its specific arguments, paste it into Sohovi's free Excel Formula Explainer for a plain-English breakdown.
Sohovi gives you a full quality report on any spreadsheet in seconds — upload your file and see exactly what needs fixing.
Keep Reading
Explain Any Excel Formula in Plain English →