Excel skills test / Data analysis
Excel test for data analysts
Ten tasks an analyst gets asked before lunch, in a live spreadsheet: a median, shares and growth rates, counts and averages with conditions, a ranking, distinct customers, the top three, the best seller and a correlation. It's free and there's nothing to sign up for.
What it tests
An analyst is rarely asked for a formula. The question is "how many", "which one", "is it growing" or "are these two related", and the formula is up to you. This test is written the same way. It covers summarising data with conditions, comparing and ranking, working with distinct values, and the first steps of statistics.
The 10 tasks
- Averages (beginner): One very large order pulls the average up. What is the median order value? Work it out in
D2. - Percentages (beginner): Show each channel's share of total sales in
C2:C5. One formula inC2, copied down. - Percentages (intermediate): Work out the growth on the month before for February to June in
C3:C7: the change as a share of the earlier month. One formula inC3, copied down. - Conditional counts (intermediate): How many orders from the North region were worth more than 100? Count them in
F2. - Ranking (intermediate): Rank the reps by sales in
C2:C6, with 1 for the highest. One formula inC2, copied down. - Conditional averages (intermediate): What is the average order value for the Online channel? Work it out in
F2. - Dynamic arrays (advanced): Some customers ordered more than once. How many different customers are there? Count them in
D2with a formula that still works when rows are changed. - Ranking (advanced): What do the three largest deals add up to? Work it out in
D2, so the answer follows the data when the amounts change. - Lookups (advanced): Which product has the highest sales? Show its name in
E2with a formula, so it changes if another product takes the lead. - Statistics (advanced): Do sales rise with advertising? Work out the correlation between ad spend and sales in
E2.
Your result
- Job-ready (9–10): You would be comfortable with these tasks in an interview or on the first day.
- Strong (7–8): Most of it is there. The tasks you missed are worth an evening each.
- Getting there (4–6): You know the basics of the job. The lessons below fill the gaps.
- Starting out (0–3): A fair first try. Start with the lessons below and come back.
Any formula that gets the right answer counts. Missed a task? The results link to the lesson that teaches it. We keep each attempt's answers and score, without any personal details, to see which tasks people find hard.
Common questions
What Excel skills does a data analyst need?
COUNTIFS, SUMIFS and AVERAGEIFS for summaries, INDEX and MATCH or XLOOKUP for lookups, RANK and LARGE for ordering, UNIQUE, SORT and FILTER for dynamic lists, and basic statistics such as MEDIAN and CORREL. PivotTables do much of the same work without formulas, and have their own track in the course.
Do I need to know which function to use?
The questions never name one. Any formula that gives the right answer and follows the data is accepted, so COUNTIFS, SUMPRODUCT or FILTER can all solve the same task.