Top 10 Excel Functions for Data Analysis
1. VLOOKUP (Vertical Lookup)
● Purpose: Fetch data from another table based on a unique identifier.
● Example:
=VLOOKUP(101, A2:D10, 3, FALSE)
Finds the value associated with ID 101 in column 3 of the range
A2:D10.
● Use Case: Merging datasets or finding product details from a catalog.
2. HLOOKUP (Horizontal Lookup)
● Purpose: Fetch data from rows instead of columns (similar to
VLOOKUP).
● Example:
=HLOOKUP("Product", A1:Z5, 2, FALSE)
Finds the product name in the first row and returns the value from the
second row.
● Use Case: Analyzing wide datasets.
3. INDEX
● Purpose: Returns a value from a specific row and column in a range.
● Example:
=INDEX(A1:C10, 5, 2)
Returns the value from the 5th row and 2nd column of A1:C10.
● Use Case: Advanced lookups and dynamic table navigation.
4. MATCH
● Purpose: Finds the position of a value in a range.
● Example:
=MATCH(500, A1:A100, 0)
Returns the position of the value 500 in column A.
● Use Case: Combining with INDEX for flexible lookups.
5. CONCATENATE / TEXTJOIN
● Purpose: Combines multiple text strings into one.
● Example:
=CONCATENATE(A2, " ", B2)
Combines first and last names from cells A2 and B2.
=TEXTJOIN(", ", TRUE, A1:A5)
Combines all non-blank cells in A1:A5, separated by commas.
● Use Case: Creating unique identifiers or formatted text fields.
6. IF
● Purpose: Performs logical tests and returns different values based on
the result.
● Example:
=IF(A2>100, "High", "Low")
Checks if a value in A2 is greater than 100 and returns "High" or "Low".
● Use Case: Conditional analysis and categorization.
7. SUMIF / COUNTIF
● Purpose: Perform conditional summing or counting.
● Example:
=SUMIF(A1:A10, ">100", B1:B10)
Sums values in B1:B10 where A1:A10 is greater than 100.
=COUNTIF(A1:A10, "Apple")
Counts the number of times "Apple" appears in A1:A10.
● Use Case: Aggregating data based on conditions.
8. PIVOT TABLES (with GETPIVOTDATA)
● Purpose: Summarizes data dynamically and retrieves specific values.
● Example:
Use GETPIVOTDATA("Sales", PivotTableName, "Region",
"North") to extract the total sales for the "North" region.
● Use Case: Quickly summarizing and analyzing large datasets.
9. TEXT Functions (LEFT, RIGHT, MID, LEN, TRIM)
● Purpose: Extract or clean text data.
● Examples:
○ =LEFT(A1, 5) extracts the first 5 characters.
○ =TRIM(A1) removes extra spaces from text.
○ =LEN(A1) calculates the length of the text in A1.
● Use Case: Cleaning and preparing messy text data.
10. POWER QUERY (Not a formula, but essential!)
● Purpose: Automates data extraction, transformation, and loading (ETL).
● How to Use:
○ Go to the Data tab → Get Data.
○ Clean and transform data using the Power Query Editor.
● Use Case: Handling repetitive data cleaning tasks efficiently.
Bonus Tips
● XLOOKUP: A modern alternative to VLOOKUP and HLOOKUP with
more flexibility.
● ARRAY FORMULAS: Use dynamic arrays for advanced calculations.
● CHARTS: Combine functions with visualizations for enhanced
storytelling.
✅ Follow @newtonschoolofficia for more such information on Data Science.
