Data Analyst Excel Test: What They Ask and How to Prep

A data analyst Excel test gives you a raw table and asks you to turn it into answers: how many, how much, which one, and by group. The core skills are COUNTIFS and SUMIFS, XLOOKUP or VLOOKUP, pivot tables, UNIQUE and basic data cleaning. This guide is for job seekers applying to analyst roles. It shows the kind of tasks you can expect, solves sample questions on one small table, and ends with a one-week prep plan.

What is a data analyst Excel test?#

A data analyst Excel test is a timed task where you work with a data set and answer questions about it. Some employers use a multiple-choice quiz. Others give you a workbook and check the numbers you produce. Many do both.

The format varies by employer, but the skills overlap a lot. You need to read a table quickly, pick the right tool, and check your result. If you want the full list of skills an analyst job expects, read the Excel skills every data analyst needs. Business analyst roles test a very similar set, covered in Excel skills for business analysts.

What skills does a data analyst Excel test cover?#

Most questions fall into six groups. This table shows each group, the tools that solve it, and a typical question.

Harder tests add charts, percent change, date functions and nested IF logic. The advanced Excel interview questions cover that next level.

Sample data analyst Excel test questions with answers#

The best way to prepare is to solve real tasks. All the examples below use this order table in A1:D8:

Question 1: How many East orders are worth 500 or more?#

Use COUNTIFS, which counts rows that meet every condition you give it:

=COUNTIFS(B2:B8,"East",D2:D8,">=500")

The result is 2 (orders 1003 and 1005). Order 1001 is East but only 450, so it doesn't count. The COUNTIFS function guide lists every criteria form.

Question 2: What is the total for West?#

Use SUMIFS. The sum range comes first, then each range and condition pair:

=SUMIFS(D2:D8,B2:B8,"West")

The result is 1,330 (820 + 510).

Question 3: Which rep handled order 1004?#

Use XLOOKUP: what to find, where to look, what to bring back.

=XLOOKUP(1004,A2:A8,C2:C8)

The result is Chen. XLOOKUP needs Excel 2021 or Microsoft 365. In older Excel, write =VLOOKUP(1004,A2:D8,3,FALSE) for the same answer. The XLOOKUP function guide explains the optional arguments.

Question 4: Which regions appear in the data?#

Use UNIQUE, which returns each value once:

=UNIQUE(B2:B8)

It spills East, West and North into three cells. Wrap it in COUNTA, as in =COUNTA(UNIQUE(B2:B8)), to get the count: 3. UNIQUE also needs Excel 2021 or Microsoft 365. See the UNIQUE function guide for sorting and filtering the list.

How pivot tables appear in a data analyst Excel test#

Pivot tables answer "total by group" questions in seconds, so many tests include at least one. Select the table, choose Insert > PivotTable, then drag Region to Rows and Amount to Values. On the data above you get East 2,040, West 1,330 and North 1,020, with a grand total of 4,390.

Tests often go one step further. They ask for a filter, a second grouping (Region and Rep), or values shown as a percentage of the total. Learn to change Sum to Count or Average in Value Field Settings, and to refresh the pivot table after the data changes. If a question says "use a formula", SUMIFS gives the same totals as the pivot table, which is a good way to check your work.

Data cleaning tasks you should expect#

Many test files are messy on purpose. Before you count anything, look for these problems:

• Extra spaces. "East " with a trailing space won't match "East". Clean a copy of the column with =TRIM(B2).
• Numbers stored as text. They sit on the left of the cell and SUM ignores them. Convert them with VALUE or Text to Columns.
• Duplicates. Use Data > Remove Duplicates on a copy, or UNIQUE to list distinct values.
• Combined fields. "Ana Lopez - East" in one cell may need splitting with Text to Columns.

Data entry and accuracy tests check the same care with typing and checking. See what a data entry Excel test looks like if your role includes that.

How to prepare for a data analyst Excel test in one week#

A short, focused plan beats long reading sessions. Spend 30 to 60 minutes a day typing formulas on real data.

1. Day 1: Sorting, filtering, and absolute references with $.
2. Day 2: COUNTIFS and SUMIFS with text, number and date conditions.
3. Day 3: XLOOKUP and VLOOKUP, including the #N/A error and how to fix it.
4. Day 4: Pivot tables: build, group, filter, change the summary type, refresh.
5. Day 5: Cleaning: TRIM, VALUE, Remove Duplicates, UNIQUE, Text to Columns.
6. Day 6: A full timed practice test, then fix every question you got wrong.
7. Day 7: A second timed test, focusing on speed and checking.

Try the data analyst Excel mock test on day 6. If your role is closer to finance, the financial analyst Excel test guide adds the money-focused tasks. Some employers use a vendor platform; this overview of the TestGorilla Excel test format shows how one of them works.

Tips for test day#

Small habits save marks under time pressure:

• Read the whole question first. "Count" and "sum" look alike when you are rushing.
• Check one answer by hand. Filter the table and look at the status bar total to confirm a SUMIFS.
• Lock ranges before filling. A missing $ is the most common reason a filled-down formula goes wrong.
• Skip and return. If one question blocks you, move on and come back at the end.

Practise it#

Analyst tests reward speed, and speed comes from typing the formulas yourself. Practise COUNTIFS and SUMIFS in a live spreadsheet, then practise XLOOKUP on a lookup table, and finish with the Pivot tables 2 lesson to group and filter a summary. Each one checks your answer instantly. Try the COUNTIFS exercise on this page first.