20 Excel Functions Every
Analyst Should Master
Level up your data analysis skills 📊
Swipe through for formulas + examples →
Ekta Joshi
1 VLOOKUP
Find data in tables
Formula: =VLOOKUP(lookup_value, table_array, col_index,
FALSE)
Example: =VLOOKUP(A2, Products!A:D, 3, FALSE)
Use case: Find product prices from a master list
2 XLOOKUP
The modern VLOOKUP
Formula: =XLOOKUP(lookup_value, lookup_array,
return_array)
Example: =XLOOKUP(E2, A:A, C:C)
Use case: Search in any direction, returns exact matches
3 INDEX + MATCH
More flexible lookup
Formula: =INDEX(return_range, MATCH(lookup_value,
lookup_range, 0))
Example: =INDEX(C:C, MATCH(E2, A:A, 0))
Use case: Lookup left, right, or create dynamic references
4 SUMIFS
Conditional summing
Formula: =SUMIFS(sum_range, criteria_range1, criteria1, ...)
Example: =SUMIFS(D:D, A:A, "Electronics", B:B, ">1000")
Use case: Sum sales for specific category above $1000
5 COUNTIFS
Count with conditions
Formula: =COUNTIFS(criteria_range1, criteria1,
criteria_range2, criteria2)
Example: =COUNTIFS(A:A, "USA", B:B, ">=50000")
Use case: Count employees in USA earning ≥$50k
6 AVERAGEIFS
Conditional averaging
Formula: =AVERAGEIFS(average_range, criteria_range1,
criteria1, ...)
Example: =AVERAGEIFS(C:C, A:A, "Q1", B:B, "West")
Use case: Average Q1 sales in West region
7 IF
Logical decisions
Formula: =IF(logical_test, value_if_true, value_if_false)
Example: =IF(B2>1000, "High", "Low")
Use case: Categorize values based on conditions
8 IFS
Multiple conditions
Formula: =IFS(condition1, value1, condition2, value2, ...)
Example: =IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE,
"F")
Use case: Assign letter grades based on scores
9 SUMPRODUCT
Array calculations
Formula: =SUMPRODUCT(array1, array2, ...)
Example: =SUMPRODUCT(B2:B100, C2:C100)
Use case: Calculate total revenue (quantity × price)
10 CONCATENATE / TEXTJOIN
Combine text
Formula: =TEXTJOIN(delimiter, ignore_empty, text1, ...)
Example: =TEXTJOIN(", ", TRUE, A2:A5)
Use case: Combine first name, last name, and title
11 LEFT / RIGHT / MID
Extract text
Formula: =LEFT(text, num_chars) or =MID(text, start,
num_chars)
Example: =LEFT(A2, 3) extracts first 3 characters
Use case: Extract area codes from phone numbers
12 TRIM / CLEAN
Clean data
Formula: =TRIM(text) removes extra spaces =CLEAN(text)
removes non-printable characters
Example: =TRIM(A2)
Use case: Clean up messy imported data
13 DATE / TODAY / EOMONTH
Date functions
Formula: =DATE(year, month, day) / =EOMONTH(start_date,
months)
Example: =EOMONTH(A2, 0) returns last day of month
Use case: Calculate month-end dates for reporting
14 IFERROR
Handle errors gracefully
Formula: =IFERROR(value, value_if_error)
Example: =IFERROR(VLOOKUP(A2, Data!A:B, 2, 0), "Not
Found")
Use case: Display custom message instead of #N/A error
15 UNIQUE
Extract unique values
Formula: =UNIQUE(array)
Example: =UNIQUE(A2:A100)
Use case: Get list of unique customers from transaction
data
16 FILTER
Dynamic data extraction
Formula: =FILTER(array, include, [if_empty])
Example: =FILTER(A2:C100, B2:B100>1000)
Use case: Show only high-value transactions
17 SORT / SORTBY
Organize data dynamically
Formula: =SORT(array, [sort_index], [sort_order])
Example: =SORT(A2:C100, 3, -1)
Use case: Sort sales data by revenue (descending)
18 PIVOT TABLE
Not a formula, but essential!
Steps:
1. Select data range
2. Insert → PivotTable
3. Drag fields to rows/columns/values
Use case: Summarize sales by region and product category
19 CHOOSE
Return value from list
Formula: =CHOOSE(index_num, value1, value2, ...)
Example: =CHOOSE(A2, "Q1", "Q2", "Q3", "Q4")
Use case: Convert month numbers (1-12) to quarters or
categories
20 POWER QUERY
Transform data at scale
Access: Data → Get Data → From Table/Range
Key features:
Remove duplicates
Merge tables
Split columns
Change data types
Use case: Clean and combine data from multiple sources
📘 Master these functions to elevate your
data expertise.
💡Pro tip: Practice with real datasets
📌Save this post for reference
♻️Share with your network
Ekta Joshi
📈 Follow for more Excel, Power BI & Data Analytics content.