What should I practice after reading this tutorial?

Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.

Which Excel version do I need for the formulas in this article?

Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.

Tutorials - 2026-01-21

Excel Functions Every Analyst Should Know

Level up your data analysis skills with these must-know Excel functions. From lookups to statistical functions, become a more effective analyst.

The Analyst's Excel Toolkit

Data analysts live in Excel. Whether you're crunching sales numbers, building reports, or preparing data for visualization, the right functions make the difference between hours of manual work and minutes of automated analysis. This guide covers the functions that separate junior analysts from senior ones—the tools that let you work smarter, not harder. For a complete function reference, see our Excel Functions: Complete Guide.

Data Retrieval Functions

XLOOKUP — The Modern Lookup If you learn one lookup function, make it XLOOKUP. It replaces VLOOKUP, HLOOKUP, and INDEX/MATCH in most scenarios. \\\ =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) \\\ Why analysts love it: Searches left or right (VLOOKUP only goes right) Built-in error handling Can return multiple columns Supports wildcards and approximate matches Example: Find a customer's region from a lookup table: \\\ =XLOOKUP(A2, Customers!A:A, Customers!D:D, "Unknown") \\\ Learn more about XLOOKUP → INDEX + MATCH — The Classic Combo Before XLOOKUP, this was the analyst's go-to. Still essential for older Excel versions. \\\ =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) \\\ Example: \\\ =INDEX(C:C, MATCH(A2, A:A, 0)) \\\ When to use over XLOOKUP: Working with Excel 2019 or earlier Need to return values from multiple columns dynamically Complex multi-criteria lookups Learn more about INDEX → Learn more about MATCH →

Conditional Aggregation Functions

SUMIFS — Multi-Criteria Totals The workhorse of financial analysis. Sum values based on multiple conditions. \\\ =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) \\\ Example: Total sales for Product A in Q1: \\\ =SUMIFS(Sales, Product, "A", Quarter, "Q1") \\\ Pro tips: Use wildcards: "Product*" matches "Product A", "Product B", etc. Use operators: ">1000", "<="&B1 Combine with date criteria for time-based analysis Learn more about SUMIFS → COUNTIFS — Multi-Criteria Counting Count records matching multiple conditions. \\\ =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) \\\ Example: Count high-value orders from California: \\\ =COUNTIFS(State, "CA", OrderValue, ">500") \\\ Learn more about COUNTIFS → AVERAGEIFS — Conditional Averages Calculate averages for specific subsets of data. \\\ =AVERAGEIFS(average_range, criteria_range1, criteria1, ...) \\\ Example: Average order value for repeat customers: \\\ =AVERAGEIFS(OrderValue, CustomerType, "Repeat") \\\

Statistical Functions

MEDIAN — The True Middle Unlike AVERAGE, MEDIAN isn't skewed by outliers. Essential for salary analysis, pricing, and any data with extreme values. \\\ =MEDIAN(A1:A100) \\\ When to use MEDIAN over AVERAGE: Income/salary data (high earners skew averages) Home prices (luxury homes distort means) Response times (outliers are common) PERCENTILE.INC — Distribution Analysis Find the value at a specific percentile. \\\ =PERCENTILE.INC(array, k) \\\ Example: Find the 90th percentile of sales: \\\ =PERCENTILE.INC(Sales, 0.9) \\\ Use cases: Identify top performers (90th percentile) Set thresholds for alerts Understand data distribution STDEV.S — Measure Variability Standard deviation tells you how spread out your data is. \\\ =STDEV.S(A1:A100) \\\ Interpretation: Low STDEV = data points are close to the mean High STDEV = data is spread out Use cases: Quality control (are measurements consistent?) Risk analysis (how volatile are returns?) Process improvement (reducing variability) CORREL — Find Relationships Measure the correlation between two variables (-1 to +1). \\\ =CORREL(array1, array2) \\\ Example: Correlation between ad spend and sales: \\\ =CORREL(AdSpend, Revenue) \\\ Interpretation: +1 = perfect positive correlation 0 = no correlation -1 = perfect negative correlation

Text Functions for Data Cleaning

TRIM — Remove Extra Spaces Imported data often has hidden spaces. TRIM removes them. \\\ =TRIM(A1) \\\ CLEAN — Remove Non-Printable Characters Removes characters that can break formulas and analysis. \\\ =CLEAN(A1) \\\ TEXT — Format Numbers as Text Convert numbers to formatted strings for reports. \\\ =TEXT(A1, "0.00%") =TEXT(A1, "$#,##0") =TEXT(A1, "YYYY-MM-DD") \\\ SUBSTITUTE — Find and Replace Replace specific text within a string. \\\ =SUBSTITUTE(A1, "old", "new") \\\ Example: Standardize state abbreviations: \\\ =SUBSTITUTE(A1, "California", "CA") \\\

Date Functions for Time Analysis

EOMONTH — End of Month Returns the last day of a month, X months from a date. \\\ =EOMONTH(A1, 0) ' End of current month =EOMONTH(A1, -1) ' End of previous month =EOMONTH(A1, 3) ' End of month 3 months from now \\\ Use cases: Monthly reporting periods Due date calculations Aging analysis NETWORKDAYS — Working Days Count business days between two dates (excludes weekends). \\\ =NETWORKDAYS(start_date, end_date, [holidays]) \\\ Example: Working days until deadline: \\\ =NETWORKDAYS(TODAY(), B1, Holidays) \\\ DATEDIF — Date Differences Calculate the difference between dates in years, months, or days. \\\ =DATEDIF(start_date, end_date, unit) \\\ Units: "Y" = complete years "M" = complete months "D" = days Example: Customer tenure in years: \\\ =DATEDIF(JoinDate, TODAY(), "Y") \\\

Dynamic Array Functions (Excel 365)

UNIQUE — Extract Distinct Values \\\ =UNIQUE(A:A) \\\ Returns a list of unique values—no more Remove Duplicates! SORT — Dynamic Sorting \\\ =SORT(A:B, 2, -1) \\\ Sorts data by column 2 in descending order. FILTER — Dynamic Filtering \\\ =FILTER(Data, Criteria) \\\ Example: Filter orders over $1000: \\\ =FILTER(A:D, D:D>1000) \\\ SORTBY — Sort by Another Column \\\ =SORTBY(array, by_array, [sort_order]) \\\ Example: Sort products by revenue (descending): \\\ =SORTBY(Products, Revenue, -1) \\\

Error Handling

IFERROR — Catch All Errors \\\ =IFERROR(formula, value_if_error) \\\ Example: \\\ =IFERROR(A1/B1, 0) \\\ IFNA — Catch Only #N/A More specific than IFERROR—only catches #N/A errors. \\\ =IFNA(XLOOKUP(A1, Data, Values), "Not Found") \\\

Analyst Workflow: Combining Functions

Scenario 1: Sales Performance Report \\\ ' Total sales by region =SUMIFS(Sales, Region, "West", Year, 2024) ' Average deal size =AVERAGEIFS(DealSize, Region, "West", Year, 2024) ' Number of deals =COUNTIFS(Region, "West", Year, 2024) ' Top performer =INDEX(SalesRep, MATCH(MAX(Sales), Sales, 0)) \\\ Scenario 2: Customer Analysis \\\ ' Customer lifetime value =SUMIFS(Revenue, CustomerID, A2) ' Days since last purchase =TODAY() - MAX(IF(CustomerID=A2, PurchaseDate)) ' Purchase frequency =COUNTIFS(CustomerID, A2) / DATEDIF(MIN(IF(CustomerID=A2, PurchaseDate)), TODAY(), "M") \\\

Quick Reference Card

Task Function Multi-criteria sum SUMIFS Multi-criteria count COUNTIFS Multi-criteria average AVERAGEIFS Flexible lookup XLOOKUP or INDEX/MATCH Find middle value MEDIAN Measure spread STDEV.S Find correlation CORREL Clean text TRIM, CLEAN Date differences DATEDIF, NETWORKDAYS Handle errors IFERROR, IFNA Dynamic lists UNIQUE, SORT, FILTER

Next Steps

Continue building your analyst toolkit: Excel Functions: Complete Guide — Comprehensive reference Excel Functions for Data Analysis — Deep dive into analytical functions Excel Functions for Business & Finance — Financial modeling Statistical Functions — Full statistical function library Lookup & Reference Functions — Master data retrieval

Apply this tutorial in your workbook

Copy one example from this article into a blank sheet. Change the sample ranges to match your column letters. Press F2 and Enter after edits so Excel recalculates. Compare your result to the expected output in the article. Related hubs: Excel functions directory · Formula guides · Function comparisons

Frequently asked questions

What should I practice after reading this tutorial? Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.
Which Excel version do I need for the formulas in this article? Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.