Jayen Thakker 2025
Data Analytics Mentor
Data Analytics
Top 10 Excel Functions
for Data Analysis
Top 10 Excel Functions for Data Analysis Swipe next
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
VLOOKUP (Vertical Lookup)
Purpose
Retrieves values from a column in a table based on a
lookup value.
Syntax
= VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
Example Usage
If you have a dataset of sales and need to find the price of a
specific product, use VLOOKUP to search for the product name
and return the price from another column.
Limitations
It only works with data where the lookup column is the first
column
Cannot search to the left.
Top 10 Excel Functions for Data Analysis 02
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
HLOOKUP (Horizontal Lookup)
Purpose
Retrieves values from a row in a table based on a lookup
value.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num,
[range_lookup])
Example Usage
If you have a dataset where products are listed across
columns and prices in rows, HLOOKUP can retrieve the
price for a specific product.
Limitations
Less commonly used than VLOOKUP
The table must be arranged horizontally.
Top 10 Excel Functions for Data Analysis 03
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
INDEX-MATCH (Alternative to
VLOOKUP)
Purpose
A more flexible alternative to VLOOKUP that allows
searching in any direction.
Syntax
=INDEX(column_to_return, MATCH(lookup_value,
lookup_column, 0))
Example Usage
If you have sales data and want to find the price of a product
where the product name is in column C and the price is in
column A, INDEX-MATCH allows searching in any order.
Advantages Over VLOOKUP
Works faster with large datasets
Can search left or right
More dynamic.
Top 10 Excel Functions for Data Analysis 04
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
XLOOKUP (Modern Alternative to
VLOOKUP & HLOOKUP)
Purpose
Searches for a value in a column and returns a
corresponding value from another column.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array,
[if_not_found], [match_mode], [search_mode])
Example Usage
If you want to find the price of "Product A" in a dataset,
XLOOKUP can return it without the limitations of VLOOKUP.
Advantages
Works in both directions
Can return multiple values
Handles missing values gracefully.
Top 10 Excel Functions for Data Analysis 05
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
SUMIFS (Conditional Summation)
Purpose
Adds values based on multiple criteria.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2], …)
Example Usage
To calculate the total sales of "Product A" in "Region 1,"
SUMIFS allows summing only the values that meet these
conditions.
Use Case in Data Analysis
Sales analysis based on product and region
Aggregating data based on multiple filters.
Top 10 Excel Functions for Data Analysis 06
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
COUNTIFS (Conditional Counting)
Purpose
Counts the number of cells that meet multiple
conditions.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2,
criteria2], …)
Example Usage
Count how many times "Product A" was sold in "Region 1".
Use Case in Data Analysis
Finding how many transactions meet specific conditions
Identifying trends based on criteria.
Top 10 Excel Functions for Data Analysis 07
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
IFERROR (Handling Errors Gracefully)
Purpose
Returns a specified value if a formula results in an error.
Syntax
=IFERROR(expression, value_if_error)
Example Usage
If VLOOKUP cannot find a value, IFERROR(VLOOKUP(A2,
B2:D100, 2, FALSE), "Not Found") will return "Not Found"
instead of an error.
Use Case in Data Analysis
Preventing errors from breaking dashboards or reports
Providing user-friendly messages in case of missing data.
Top 10 Excel Functions for Data Analysis 08
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
TEXT (Formatting Data)
Purpose
Formats numbers and dates as text.
Syntax
=TEXT(value, format_text)
Example Usage
Convert a date to "MMM-YYYY" format: =TEXT(A2, "MMM-
YYYY"
Format numbers with commas: =TEXT(A2, "#,###")
Use Case in Data Analysis
Preparing data for reporting
Ensuring consistent formats for analysis.
Top 10 Excel Functions for Data Analysis 09
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
LEFT, RIGHT, MID, LEN (Text Extraction)
Purpose
Extracts specific parts of text from a string.
Syntax
LEFT(text, num_chars): Extracts characters from the start
RIGHT(text, num_chars): Extracts characters from the end
MID(text, start_num, num_chars): Extracts characters
from the middle
LEN(text): Counts the number of characters.
Example Usage
Extract the first 3 letters of a product code: =LEFT(A2,3
Extract the last 4 digits of an ID: =RIGHT(A2,4
Find the length of a text entry: =LEN(A2)
Use Case in Data Analysis
Cleaning and preparing data for analysis
Extracting key information from text-based data.
Top 10 Excel Functions for Data Analysis 10
Jayen Thakker Swipe to
Data Analytics Mentor continue reading
CONCATENATE / TEXTJOIN
(Combining Text)
Purpose
Merges text from multiple cells.
Syntax
=CONCATENATE(text1, text2, …) (Older version
=TEXTJOIN(delimiter, ignore_empty, text1, text2, …)
(Newer version)
Example Usage
Merge first name and last name: =CONCATENATE(A2, " ",
B2
Combine multiple columns with a separator: =TEXTJOIN(",
", TRUE, A2:C2)
Use Case in Data Analysis
Preparing data for reports
Merging categories or classifications into a single field.
Top 10 Excel Functions for Data Analysis 11
Jayen Thakker 2025
Data Analytics Mentor
Dr. Jayen Thakker MetricMinds.in
Which function do you
use most often?
Share your progress or
challenges in the comments.
Follow us for more.
Top 10 Excel Functions for Data Analysis 12
