--- title: "Excel Test for Data Analysts: Free Timed Practice Test | PracticeExcel" description: "Practice the Excel test for data analyst roles with 20 timed tasks. COUNTIFS, MEDIAN, RANK, UNIQUE, XLOOKUP and SUMPRODUCT, scored instantly." canonical_url: "https://practiceexcelonline.com/tests/excel-test-for-data-analysts" locale: en meta: "og:description": "Practice the Excel test for data analyst roles with 20 timed tasks. COUNTIFS, MEDIAN, RANK, UNIQUE, XLOOKUP and SUMPRODUCT, scored instantly." "og:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RXhjZWwgVGVzdCBmb3IgRGF0YSBBbmFseXN0czogRnJlZSBUaW1lZCBQcmFjdGljZSBUZXN0%2Cdescription_~UHJhY3RpY2UgdGhlIEV4Y2VsIHRlc3QgZm9yIGRhdGEgYW5hbHlzdCByb2xlcyB3aXRoIDIwIHRpbWVkIHRhc2tzLiBDT1VOVElGUywgTUVESUFOLCBSQU5LLCBVTklRVUUsIFhMT09LVVAgYW5kIFNVTVBST0RVQ1QsIHNjb3JlZCBpbnN0YW50bHku%2Ceyebrow_Excel%2Btests%2Bfor%2Bjob%2Bapplications%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJEIl0sInJvd3MiOlt7Im4iOjEsImNlbGxzIjpbIkVtcGxveWVlIiwiQW5udWFsIHNhbGFyeSIsIk1lZGlhbiJdfSx7Im4iOjIsImNlbGxzIjpbIkEiLCI0MTIwMDAiLCIiXX0seyJuIjozLCJjZWxscyI6WyJCIiwiNDU1MDAwIiwiIl19LHsibiI6NCwiY2VsbHMiOlsiQyIsIjM5ODAwMCIsIiJdfSx7Im4iOjUsImNlbGxzIjpbIkQiLCIxMjUwMDAwIiwiIl19LHsibiI6NiwiY2VsbHMiOlsiRSIsIjQ3MjAwMCIsIiJdfV0sInRhcmdldCI6WzEsMl19fQ%2Cp_Ii90ZXN0cy9leGNlbC10ZXN0LWZvci1kYXRhLWFuYWx5c3RzIg%2Cs_Mwd8y0B11ggy5Egb.png" "og:image:height": "600" "og:image:type": image/png "og:image:width": "1200" "og:title": "Excel Test for Data Analysts: Free Timed Practice Test | PracticeExcel" "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RXhjZWwgVGVzdCBmb3IgRGF0YSBBbmFseXN0czogRnJlZSBUaW1lZCBQcmFjdGljZSBUZXN0%2Cdescription_~UHJhY3RpY2UgdGhlIEV4Y2VsIHRlc3QgZm9yIGRhdGEgYW5hbHlzdCByb2xlcyB3aXRoIDIwIHRpbWVkIHRhc2tzLiBDT1VOVElGUywgTUVESUFOLCBSQU5LLCBVTklRVUUsIFhMT09LVVAgYW5kIFNVTVBST0RVQ1QsIHNjb3JlZCBpbnN0YW50bHku%2Ceyebrow_Excel%2Btests%2Bfor%2Bjob%2Bapplications%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJEIl0sInJvd3MiOlt7Im4iOjEsImNlbGxzIjpbIkVtcGxveWVlIiwiQW5udWFsIHNhbGFyeSIsIk1lZGlhbiJdfSx7Im4iOjIsImNlbGxzIjpbIkEiLCI0MTIwMDAiLCIiXX0seyJuIjozLCJjZWxscyI6WyJCIiwiNDU1MDAwIiwiIl19LHsibiI6NCwiY2VsbHMiOlsiQyIsIjM5ODAwMCIsIiJdfSx7Im4iOjUsImNlbGxzIjpbIkQiLCIxMjUwMDAwIiwiIl19LHsibiI6NiwiY2VsbHMiOlsiRSIsIjQ3MjAwMCIsIiJdfV0sInRhcmdldCI6WzEsMl19fQ%2Cp_Ii90ZXN0cy9leGNlbC10ZXN0LWZvci1kYXRhLWFuYWx5c3RzIg%2Cs_Mwd8y0B11ggy5Egb.png" --- > Index of every page on this site: [llms.txt](https://practiceexcelonline.com/llms.txt) # **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% = 30:0070 % **Question 1 of 20** One very high salary pulls the average up. In D2, find the median salary instead. D2fx | | A | B | C | D | E | F | | --- | --- | --- | --- | --- | --- | --- | | 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**](https://practiceexcelonline.com/pricing) [**Already premium? Sign in**](https://practiceexcelonline.com/login?next=/tests/excel-test-for-data-analysts) 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. 1. [**1****Median salary**](https://practiceexcelonline.com/functions/median) 2. [**2****Share of yes answers**](https://practiceexcelonline.com/practice/countif/countif-text) 3. [**3****Count orders between 100 and 500**](https://practiceexcelonline.com/practice/countifs/countifs-between-two-numbers) 4. [**4****Correlation between ad spend and sales**](https://practiceexcelonline.com/functions/correl) 5. [**5****Rank of one store by sales**](https://practiceexcelonline.com/practice/index-match/index-match) 6. [**6****Count tickets that mention a word**](https://practiceexcelonline.com/practice/countif/countif-text) 7. [**7****Conversion rate for one channel**](https://practiceexcelonline.com/practice/sumif/sumif-criteria-from-a-cell) 8. [**8****Share of revenue from the top three customers**](https://practiceexcelonline.com/practice/minmax/large-second-highest) 9. [**9****Average deal size for a region and product**](https://practiceexcelonline.com/functions/averageifs) 10. [**10****Count the different customers**](https://practiceexcelonline.com/functions/unique) 11. [**11****Count the months above average**](https://practiceexcelonline.com/practice/countif/countif-criteria-from-a-cell) 12. [**12****Count the orders in one month**](https://practiceexcelonline.com/practice/dates/month-from-date) 13. [**13****Find a SKU to the left with XLOOKUP**](https://practiceexcelonline.com/practice/xlookup/xlookup) 14. [**14****Latest order amount for a customer**](https://practiceexcelonline.com/practice/xlookup/xlookup-last-match) 15. [**15****Sales with three conditions**](https://practiceexcelonline.com/practice/sumifs/sumifs-two-conditions) 16. [**16****Name of the top seller**](https://practiceexcelonline.com/practice/index-match/index-match) 17. [**17****Revenue for one region from units and prices**](https://practiceexcelonline.com/functions/sumproduct) 18. [**18****Change on the previous month**](https://practiceexcelonline.com/practice/index-match/index-match) 19. [**19****Share of orders placed on the weekend**](https://practiceexcelonline.com/practice/networkdays-workday/weekday-day-of-week) 20. [**20****Median deal size for one region**](https://practiceexcelonline.com/functions/median) ## **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.
## **Read the guide** - [**How to clean data in Excel with formulas**](https://practiceexcelonline.com/learn/clean-data-in-excel) - [**Google Sheets vs Excel: what is the same, what is different, and how to switch**](https://practiceexcelonline.com/learn/google-sheets-vs-excel) ## **Other Excel tests** - [**Indeed Excel assessment practice test**](https://practiceexcelonline.com/tests/indeed-excel-assessment) - [**Excel test for job interviews**](https://practiceexcelonline.com/tests/excel-test-for-job-interview) - [**Kenexa Excel test practice**](https://practiceexcelonline.com/tests/kenexa-excel-test) - [**SHL Excel test practice**](https://practiceexcelonline.com/tests/shl-excel-test) - [**iMocha Excel test practice**](https://practiceexcelonline.com/tests/imocha-excel-test) - [**TestGorilla Excel test practice**](https://practiceexcelonline.com/tests/testgorilla-excel-test) - [**Excel test for finance jobs**](https://practiceexcelonline.com/tests/excel-test-for-finance) - [**Excel test for accounting and bookkeeping**](https://practiceexcelonline.com/tests/excel-test-for-accountants) - [**Excel test for budgeting and planning**](https://practiceexcelonline.com/tests/excel-test-for-budgeting) - [**Excel test for admin jobs**](https://practiceexcelonline.com/tests/excel-test-for-admin-jobs) ```json {"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#webpage","description":"Practice the Excel test for data analyst roles with 20 timed tasks. COUNTIFS, MEDIAN, RANK, UNIQUE, XLOOKUP and SUMPRODUCT, scored instantly.","name":"Excel Test for Data Analysts: Free Timed Practice Test","url":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/1"},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/2"},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/3"},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/4"},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/5"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/tests/excel-test-for-data-analysts"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel tests for job applications","item":"https://practiceexcelonline.com/tests","position":2},{"@type":"ListItem","name":"Excel Test for Data Analysts: Free Timed Practice Test","position":3}]},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"What Excel skills does a data analyst need?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"Do analyst Excel tests include pivot tables?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"How long is this practice test?","acceptedAnswer":{"@type":"Answer","text":"30 minutes for 20 questions. The free preview has the first 10 questions in 15 minutes."}},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/4","@type":"Question","inLanguage":"en-US","name":"What score should I aim for?","acceptedAnswer":{"@type":"Answer","text":"The passing score here is 70%, 14 of 20. Aim for 80% or more before the real test."}},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/question/5","@type":"Question","inLanguage":"en-US","name":"Do I get a certificate?","acceptedAnswer":{"@type":"Answer","text":"Yes. Pass the full test with a verified email and you get a certificate with a public link for your résumé or LinkedIn."}},{"@id":"https://practiceexcelonline.com/tests/excel-test-for-data-analysts#/schema/course/1","@type":"Course","description":"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.","educationalLevel":"Intermediate","inLanguage":"en-US","name":"Excel test for data analysts","teaches":"MEDIAN, COUNTIF, COUNTIFS, CORREL, INDEX-MATCH, SUMIF, MINMAX, AVERAGEIFS, UNIQUE, DATES-BASICS, XLOOKUP, SUMIFS, SUMPRODUCT, DATES-WORKDAYS","timeRequired":"PT30M","provider":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"}]} ``` ## Sitemap See the full [sitemap](https://practiceexcelonline.com/sitemap.md) for all pages.