Blogs

Excel remains the ultimate sandbox for data analytics. Before you load your data into Python or SQL, you will almost always use Excel to inspect, clean, and run quick diagnostics on it.

To help you cut through the noise, here are the top 20 Excel functions every data analyst must master, categorized by exactly how you will use them on the job.

1. Lookup and Reference Functions (Finding the Data)

These functions are the backbone of data manipulation, allowing you to connect disjointed data tables using common identifiers.

• =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
  • What it does: The modern successor to VLOOKUP. It searches a range or an array and returns an item corresponding to the first match. It searches both left and right, defaults to an exact match, and doesn’t break when you insert columns.
• =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • What it does: Searches for a value in the first column of a table and returns a value in the same row from another column. While older, it is still crucial to know for legacy corporate spreadsheets.
• =INDEX(array, row_num, [column_num]) & =MATCH(lookup_value, lookup_array, [match_type])
  • What it does: Used together (INDEX(MATCH())), this dynamic duo forms a powerful lookup system that is faster and more flexible than VLOOKUP for handling massive datasets.

2. Logical and Conditional Functions (Filtering and Branching)

Data analysts use these to categorize data, flag specific anomalies, and perform conditional math.

• =IF(logical_test, value_if_true, value_if_false)
  • What it does: The fundamental logical building block. It returns one value if a condition is met, and another if it is false.
• =IFS(logical_test1, value_if_true1, …)
  • What it does: Eliminates the headache of writing complex “nested IF” statements by evaluating multiple conditions sequentially.
• =AND(logical1, [logical2], …) & =OR(logical1, [logical2], …)
  • What it does: Used inside an IF statement to check whether multiple criteria are met simultaneously (AND) or if at least one criterion is met (OR).
• =IFERROR(value, value_if_error)
  • What it does: Keeps your executive dashboards looking clean by catching and masking errors like #DIV/0! or #N/A with a custom message or a zero.

3. Aggregation and Statistical Functions (Summarizing the Data)

You cannot analyze data without summarizing it. These functions help you extract quick metrics from thousands of rows.

• =SUMIFS(sum_range, criteria_range1, criteria1, …)
  • What it does: Adds values in a range that meet multiple custom criteria (e.g., Sum total revenue where Region = “North” and Product = “Laptop”).
• =COUNTIFS(criteria_range1, criteria1, …)
  • What it does: Counts the number of cells across a dataset that meet multiple criteria.
• =AVERAGEIFS(average_range, criteria_range1, criteria1, …)
  • What it does: Calculates the arithmetic mean for cells that meet a specific set of requirements.
• =UNIQUE(array)
  • What it does: Dynamically spills a distinct list of values from a column, completely eliminating duplicates in one keystroke.
• =SORT(array, [sort_index], [sort_order])
  • What it does: Dynamically sorts a range of data by a specific column in ascending or descending order using a formula.

4. Text Functions (Data Cleaning)

Real-world data is notoriously messy. Names have random spaces, IDs are merged together, and formatting is inconsistent. These functions fix that.

• =TRIM(text)
  • What it does: Strips out all irregular leading, trailing, and duplicate spaces from your text, preventing broken lookup formulas.
• =CLEAN(text)
  • What it does: Removes all non-printable characters often hidden inside raw data exported from older database systems.
• =TEXTSPLIT(text, col_delimiter, [row_delimiter])
  • What it does: The dynamic formula version of the “Text to Columns” tool. It splits strings across cells using a specific delimiter like a comma or space.
• =CONCAT(text1, …) & =TEXTJOIN(delimiter, ignore_empty, text1, …)
  • What it does: Combines strings from multiple cells. TEXTJOIN is superior because it allows you to inject a standard separator (like a comma or hyphen) automatically between the elements.

5. Date and Time Functions (Trend Analysis)

Most business analysis relies heavily on time tracking (e.g., Month-over-Month growth, quarterly targets, or delivery turnarounds).

• =NETWORKDAYS(start_date, end_date, [holidays])
  • What it does: Returns the total number of exact working days between two dates, automatically excluding weekends and a custom list of holidays. Ideal for measuring operational efficiency.
• =EDATE(start_date, months)
  • What it does: Returns the exact serial date that is a specified number of months before or after a starting date—critical for forecasting monthly cohorts.
• =YEARFRAC(start_date, end_date, [basis])
  • What it does: Calculates the precise fraction of a year represented by the number of whole days between two dates. Analysts use this to calculate employee tenure or age groups rapidly.

Pro-Tip: While knowing these 20 functions will put you in the top 10% of standard spreadsheet users, always combine them with Pivot Tables and Power Query for advanced, automated data transformations.