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 for Data Analysis

Transform raw data into insights with these powerful Excel functions. Learn SUMIFS, COUNTIFS, AVERAGEIF, and advanced analytical functions.

Turn Data Into Decisions

Data analysis is about answering questions. How much did we sell? What's trending up? Where are the outliers? Excel's analytical functions help you answer these questions quickly and accurately. This guide covers the functions that data professionals use to extract insights from raw data—no programming required. For a complete function reference, see our Excel Functions: Complete Guide.

Conditional Aggregation: The Foundation

SUMIFS — Sum with Multiple Conditions The most powerful aggregation function. Sum values that meet multiple criteria. \\\ =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) \\\ Example 1: Total sales for Region "West" in 2024: \\\ =SUMIFS(Sales, Region, "West", Year, 2024) \\\ Example 2: Sum orders over $1,000 from California: \\\ =SUMIFS(OrderValue, State, "CA", OrderValue, ">1000") \\\ Example 3: Sum sales between two dates: \\\ =SUMIFS(Sales, Date, ">="&StartDate, Date, "<="&EndDate) \\\ Pro tips: Use wildcards: "*Smith*" matches any text containing "Smith" Use cell references: ">"&A1 for dynamic criteria Criteria ranges must be the same size as sum range Learn more about SUMIFS → COUNTIFS — Count with Multiple Conditions Count records matching multiple criteria. \\\ =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) \\\ Example 1: Count high-priority open tickets: \\\ =COUNTIFS(Priority, "High", Status, "Open") \\\ Example 2: Count orders in a date range: \\\ =COUNTIFS(OrderDate, ">="&DATE(2024,1,1), OrderDate, "<="&DATE(2024,12,31)) \\\ Learn more about COUNTIFS → AVERAGEIFS — Average with Multiple Conditions Calculate averages for specific subsets. \\\ =AVERAGEIFS(average_range, criteria_range1, criteria1, ...) \\\ Example: Average order value for repeat customers in Q4: \\\ =AVERAGEIFS(OrderValue, CustomerType, "Repeat", Quarter, "Q4") \\\ MAXIFS / MINIFS — Conditional Max/Min Find the largest or smallest value meeting criteria. \\\ =MAXIFS(max_range, criteria_range1, criteria1, ...) =MINIFS(min_range, criteria_range1, criteria1, ...) \\\ Example: Highest sale in the West region: \\\ =MAXIFS(Sales, Region, "West") \\\

Statistical Analysis Functions

MEDIAN — The True Middle Unlike AVERAGE, MEDIAN isn't affected by outliers. \\\ =MEDIAN(A1:A100) \\\ When to use: Salary analysis (executives 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) \\\ Common percentiles: 25th (Q1) — First quartile 50th (Q2) — Median 75th (Q3) — Third quartile 90th — Top performers QUARTILE.INC — Quick Quartiles \\\ =QUARTILE.INC(array, quart) \\\ Quart values: 0 = Minimum 1 = 25th percentile 2 = Median 3 = 75th percentile 4 = Maximum STDEV.S — Measure Variability Standard deviation shows how spread out your data is. \\\ =STDEV.S(A1:A100) \\\ Interpretation: Low STDEV = consistent data High STDEV = variable data Use cases: Quality control Risk assessment Process consistency VAR.S — Variance Variance is standard deviation squared. Used in statistical calculations. \\\ =VAR.S(A1:A100) \\\ CORREL — Find Relationships Measure correlation between two variables (-1 to +1). \\\ =CORREL(array1, array2) \\\ Example: Correlation between temperature and ice cream sales: \\\ =CORREL(Temperature, IceCreamSales) \\\ Interpretation: +0.7 to +1.0 = Strong positive correlation +0.3 to +0.7 = Moderate positive -0.3 to +0.3 = Weak/no correlation -0.7 to -0.3 = Moderate negative -1.0 to -0.7 = Strong negative

Ranking and Comparison

RANK.EQ — Rank Values Assign ranks to values in a dataset. \\\ =RANK.EQ(number, ref, [order]) \\\ Example: Rank sales reps by performance: \\\ =RANK.EQ(B2, $B$2:$B$100, 0) \\\ Order: 0 = descending (highest = 1), 1 = ascending (lowest = 1) LARGE / SMALL — Nth Values Find the nth largest or smallest value. \\\ =LARGE(array, k) =SMALL(array, k) \\\ Example: Third highest sale: \\\ =LARGE(Sales, 3) \\\ FREQUENCY — Distribution Bins Count how many values fall into each bin. \\\ =FREQUENCY(data_array, bins_array) \\\ Example: Count orders by value range: Bins: 100, 500, 1000, 5000 \\\ =FREQUENCY(OrderValues, {100,500,1000,5000}) \\\ Returns: Count of orders 0-100, 101-500, 501-1000, 1001-5000, 5000+

Dynamic Array Functions (Excel 365)

UNIQUE — Extract Distinct Values \\\ =UNIQUE(array, [by_col], [exactly_once]) \\\ Example: List of unique customers: \\\ =UNIQUE(A:A) \\\ SORT — Dynamic Sorting \\\ =SORT(array, [sort_index], [sort_order], [by_col]) \\\ Example: Sort products by revenue descending: \\\ =SORT(A2:C100, 3, -1) \\\ SORTBY — Sort by Another Column \\\ =SORTBY(array, by_array1, [sort_order1], ...) \\\ Example: Sort customers by total purchases: \\\ =SORTBY(CustomerNames, TotalPurchases, -1) \\\ FILTER — Dynamic Filtering \\\ =FILTER(array, include, [if_empty]) \\\ Example: Filter orders over $1,000: \\\ =FILTER(Orders, OrderValue>1000, "No results") \\\ Multiple conditions: \\\ =FILTER(Orders, (Region="West")*(OrderValue>1000)) \\\ SEQUENCE — Generate Number Series \\\ =SEQUENCE(rows, [columns], [start], [step]) \\\ Example: Generate months 1-12: \\\ =SEQUENCE(12, 1, 1, 1) \\\

Text Analysis Functions

LEN — Count Characters \\\ =LEN(text) \\\ Useful for data validation and cleaning. SEARCH / FIND — Locate Text \\\ =SEARCH(find_text, within_text, [start_num]) \\\ SEARCH is case-insensitive; FIND is case-sensitive. TEXTJOIN — Combine with Delimiter \\\ =TEXTJOIN(delimiter, ignore_empty, text1, ...) \\\ Example: Combine names with commas: \\\ =TEXTJOIN(", ", TRUE, A1:A10) \\\

Data Cleaning Functions

TRIM — Remove Extra Spaces \\\ =TRIM(text) \\\ CLEAN — Remove Non-Printable Characters \\\ =CLEAN(text) \\\ SUBSTITUTE — Replace Text \\\ =SUBSTITUTE(text, old_text, new_text, [instance_num]) \\\ VALUE — Convert Text to Number \\\ =VALUE(text) \\\

Analysis Workflow Examples

Example 1: Sales Performance Dashboard \\\ ' Total Sales =SUM(Sales) ' Sales by Region =SUMIFS(Sales, Region, "West") ' Average Order Value =AVERAGE(OrderValue) ' Top 5 Products =SORT(UNIQUE(Products), 1, -1) ' Month-over-Month Growth =(ThisMonth-LastMonth)/LastMonth \\\ Example 2: Customer Segmentation \\\ ' Customer Count by Segment =COUNTIFS(Segment, "Enterprise") ' Average Revenue per Segment =AVERAGEIFS(Revenue, Segment, "Enterprise") ' High-Value Customer List =FILTER(Customers, Revenue>10000) ' Customer Ranking =RANK.EQ(B2, $B$2:$B$100, 0) \\\ Example 3: Trend Analysis \\\ ' Year-over-Year Comparison =SUMIFS(Sales, Year, 2024) - SUMIFS(Sales, Year, 2023) ' Growth Rate =(Current-Previous)/Previous ' Moving Average (3-month) =AVERAGE(OFFSET(A1, -2, 0, 3, 1)) ' Correlation with Marketing Spend =CORREL(Sales, MarketingSpend) \\\

Quick Reference: Analysis Functions

Task Function Conditional sum SUMIFS Conditional count COUNTIFS Conditional average AVERAGEIFS Find middle value MEDIAN Find percentile PERCENTILE.INC Measure spread STDEV.S Find correlation CORREL Rank values RANK.EQ Nth largest/smallest LARGE, SMALL Distribution FREQUENCY Unique values UNIQUE Sort data SORT, SORTBY Filter data FILTER

Next Steps

Continue building your analytical skills: Excel Functions: Complete Guide — Complete reference Excel Functions Every Analyst Should Know — Analyst toolkit Statistical Functions — Full statistical library Lookup & Reference Functions — Data retrieval Browse All Functions — Complete function library

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.