Home › Guides

Excel Test for Data Analyst Roles

Data analyst Excel tests go a step past the basics. Expect conditional math, lookups, data cleaning and pivot tables, often on a messy dataset.

Skills to have down

• Conditional aggregation: SUMIFS, COUNTIFS, AVERAGEIFS
• Lookups: XLOOKUP or INDEX/MATCH, plus handling #N/A with IFERROR
• Cleaning: TRIM, PROPER, TEXT, splitting text
• Pivot tables: summarize sales by region and month
• Dynamic arrays: FILTER, UNIQUE, SORT (newer Excel versions)

Practice: a typical analyst question

Tips

Read the whole question first, and check whether the answer should be a number, a percentage or text. Use absolute references when you'll copy formulas. If you're stuck, a working SUMPRODUCT beats an empty cell.