Excel test for data analysts

Data analysts, business analysts and reporting specialists are usually tested on more than totals: they have to summarize, compare and rank data in one formula. This practice test has 20 analysis tasks, from medians, ranks and correlation to distinct counts, conversion rates, the latest order and month-on-month change, in a live grid with a 30-minute clock.

20 questions · 30 minutes · passing score 70%

Question 1 of 20

One very high salary pulls the average up. In D2, find the median salary instead.

	A	B	D
1	Employee	Annual salary	Median
2	A	412000
3	B	455000
4	C	398000
5	D	1250000
6	E	472000
7	F	436000
8	G	501000
9	H	420000
10

No tips during the test. I'm back with help when the clock stops.

The free preview is the first 10 questions in 15 minutes. It isn't saved.

Unlock the full test from $19/month Already premium? Sign in

Premium gets you the full 20-question test, your score history and a certificate when you pass.

What analyst roles test

An Excel test for an analyst role checks that you can answer a question about a table without building helper columns first. Expect conditional counts and averages with COUNTIFS and AVERAGEIFS, MEDIAN, RANK and CORREL, distinct counts with UNIQUE, ratios from two SUMIF formulas, and lookups with XLOOKUP, including the last match.

The hardest questions work on whole ranges at once: a median for one group with MEDIAN and IF, counts by month or weekday with SUMPRODUCT, and a revenue figure built from units and prices without a revenue column. These are the formulas that save an analyst from a pivot table for every question.

How this practice test works

You have 30 minutes for 20 questions. Each question has its own small sheet, written for this test only: orders, web traffic by channel, store sales or survey answers. Click the answer cell, type your formula and press Enter, as in Excel.

Nothing is marked until you hand in. Every answer has to be a formula that still gives the right result when the data changes, so a typed number does not count. Afterwards you see your score, every question you missed and where to practice it.

How to prepare

Take the free preview of the first 10 questions to see where you stand. Then practice SUMPRODUCT with a condition in parentheses and INDEX with MATCH, which the harder half of the test builds on. Take the full test against the clock once you are confident with both.

What the test covers

Each question practices one of these skills. Follow a link to practice it before you start.

Questions

What Excel skills does a data analyst need?

Conditional counts, sums and averages, MEDIAN and RANK, distinct counts with UNIQUE, lookups with XLOOKUP or INDEX and MATCH, and array formulas with SUMPRODUCT. This practice test covers all of them.

Do analyst Excel tests include pivot tables?

Some do. The questions here ask for the same answers a pivot table gives, a total, count or median per group, in a single formula, which is what most timed tests check.

How long is this practice test?

30 minutes for 20 questions. The free preview has the first 10 questions in 15 minutes.

What score should I aim for?

The passing score here is 70%, 14 of 20. Aim for 80% or more before the real test.

Do I get a certificate?

Yes. Pass the full test with a verified email and you get a certificate with a public link for your résumé or LinkedIn.