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

  1. Averages (beginner): One very large order pulls the average up. What is the median order value? Work it out in D2.
  2. Percentages (beginner): Show each channel's share of total sales in C2:C5. One formula in C2, copied down.
  3. 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 in C3, copied down.
  4. Conditional counts (intermediate): How many orders from the North region were worth more than 100? Count them in F2.
  5. Ranking (intermediate): Rank the reps by sales in C2:C6, with 1 for the highest. One formula in C2, copied down.
  6. Conditional averages (intermediate): What is the average order value for the Online channel? Work it out in F2.
  7. Dynamic arrays (advanced): Some customers ordered more than once. How many different customers are there? Count them in D2 with a formula that still works when rows are changed.
  8. 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.
  9. Lookups (advanced): Which product has the highest sales? Show its name in E2 with a formula, so it changes if another product takes the lead.
  10. Statistics (advanced): Do sales rise with advertising? Work out the correlation between ad spend and sales in E2.

Your result

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.

Other tests