Excel and Google Sheets Quiz: Formulas, Lookups and Pivot Tables
Test the spreadsheet skills every data analyst needs: absolute and relative references, IF and COUNTIFS, VLOOKUP, INDEX-MATCH and XLOOKUP, text and date functions, data cleaning, pivot tables, charts and Google Sheets functions like QUERY and FILTER.

Excel & Google Sheets Quiz
60 questions · 8 topics · timer · instant explanations · printable mark sheet.
- Questions60 in the bank, 10–30 per attempt
- Levels20 easy · 24 medium · 16 hard
- TimeAbout 5–20 minutes
- PriceFree, no login needed
- Made byDr. Aammar Tufail, Codanics
- Updated18 November 2026
In short: the Excel & Google Sheets Quiz is a free online multiple-choice quiz by Codanics with 60 questions on cell references; core functions and logic; lookup functions; text and date functions; data cleaning tools; sorting, filtering and pivots; charts; and google Sheets features. You choose the number of questions and the difficulty, answer against a timer, see an explanation after every answer and finish with a printable mark sheet.
What the Excel & Google Sheets Quiz covers
Spreadsheets are still the first tool most analysts open. Interviews and job tests often start with a lookup, a COUNTIFS or a pivot table, so getting these right quickly is a real advantage.
This quiz covers Microsoft Excel and Google Sheets side by side. Formula questions ask you to predict results, just like a practical test.
- Cell references 7 questions
- Core functions and logic 8 questions
- Lookup functions 9 questions
- Text and date functions 8 questions
- Data cleaning tools 6 questions
- Sorting, filtering and pivots 7 questions
- Charts 6 questions
- Google Sheets features 9 questions
Who it is for: Students, office professionals and aspiring data analysts who use Excel or Google Sheets for reports, data cleaning and dashboards.
How the quiz works
- Type your name if you want it printed on the mark sheet (optional; it never leaves your browser).
- Choose 10, 20 or 30 questions, a difficulty (Easy, Mixed or Hard) and the timer: Standard, Relaxed or none.
- Answer each question before the timer runs out. Press A–D or 1–4 on a keyboard, or tap an option on your phone.
- Read the explanation after every answer, then press Next.
- At the end, get your score, grade, topic-wise breakdown and a full answer review. Print it or save it as a PDF.
- Retake the quiz for a fresh random set, or practise only the topics you found hard.
Free video lessons from Codanics
Score lower than you hoped? These free lessons from the Codanics YouTube channel cover the same topics, explained step by step.
Free on the Codanics YouTube channel, taught in Urdu/Hindi.
Sample questions with answers
Here are 8 questions from the bank, with answers and explanations. The quiz above draws from all 60.
1. What do the dollar signs in the reference $A$1 do when the formula is copied to other cells?
- They lock both column and row
- They format the cell as currency
- They fix only the column letter
- They make the formula recalculate more often
Answer: A. They lock both column and row
A $ before the column letter locks the column and a $ before the row number locks the row. $A$1 is an absolute reference, so it always points to A1 wherever you copy it.
2. Cells A1:A5 contain 10, the text "abc", an empty cell, 5 and 0. What does =COUNT(A1:A5) return?
- 3
- 4
- 5
- 2
Answer: A. 3
COUNT counts only cells that contain numbers: 10, 5 and 0. COUNTA would return 4 because it counts every non-empty cell, including text.
3. In =VLOOKUP(E2, A2:D50, 3, FALSE), what does FALSE tell VLOOKUP to do?
- Find the closest smaller value
- Find an exact match only
- Search from the bottom up
- Ignore upper and lower case
Answer: B. Find an exact match only
FALSE (or 0) as the last argument requests an exact match and returns #N/A if none is found. TRUE requests an approximate match on sorted data.
4. What does =MID("PK-2024-ISB", 4, 4) return?
- -202
- 024-
- 2024
- ISB
Answer: C. 2024
MID starts at character 4 (P=1, K=2, -=3, 2=4) and takes 4 characters, giving "2024".
5. Which feature splits a column of values such as "Ali,Khan,Lahore" into three separate columns?
- Remove Duplicates
- Data Validation
- Text to Columns
- Flash Fill
Answer: C. Text to Columns
Text to Columns (Split text to columns in Google Sheets) splits each cell using a delimiter such as a comma, or a fixed width.
6. What is a pivot table mainly used for?
- Checking the spelling in text columns
- Creating dropdown lists inside cells
- Summarising data by groups, e.g. sales per region
- Locking a sheet with a password
Answer: C. Summarising data by groups, e.g. sales per region
A pivot table groups and aggregates data (sum, count, average and more) by the fields you place in rows and columns, without writing formulas.
7. You want to show the distribution of 500 students' marks, grouped into ranges such as 40-50 and 50-60. Which chart fits?
- Line chart
- Histogram
- Pie chart
- Radar chart
Answer: B. Histogram
A histogram groups a numeric variable into bins and shows how many values fall in each, revealing shape, spread and skew.
8. You share a Google Sheet with a teacher who should add comments but not change any cells. Which access level should you give?
- Commenter
- Editor
- Viewer
- Owner
Answer: A. Commenter
Commenters can read and add comments but cannot edit cells. Viewers can only read, and Editors can change the content.
Free guides and blogs to study
Free tools for your studies and research
Codanics also has 160+ free online tools for students and researchers: statistics calculators, meta-analysis, PDF and writing tools. They run in your browser, with no login.
Frequently asked questions
Is the Excel & Google Sheets Quiz free?
Yes. The Excel & Google Sheets Quiz is completely free, with no sign-up, no login and no limit on attempts.
How many questions are in the Excel & Google Sheets Quiz?
The question bank has 60 multiple-choice questions (20 easy, 24 medium and 16 hard) across 8 topics. Each attempt draws 10, 20 or 30 of them at random, so every attempt is different.
How does the timer work?
Each question has its own countdown: about 25 seconds for easy, 40 for medium and 55 for hard questions, plus 15 seconds when there is code to read. Choose Relaxed to double the time or switch the timer off to study at your own pace. Unanswered questions count as skipped.
Can I get a certificate or mark sheet?
Yes. When you finish, you get a mark sheet with your name, score, grade, time, topic-wise performance and every answer explained. Use Print or save as PDF to keep it. It is a self-assessment result, not an official certificate.
Are the answers and explanations correct?
Every question was written and reviewed for one clearly correct answer, and code questions were checked by running the code. If you think an answer is wrong, tell us on the Codanics WhatsApp channel or in the YouTube comments and we will fix it.
Does the quiz cover Excel or Google Sheets?
Both. Most functions work the same in Excel and Google Sheets. Where they differ, the question names the app, for example QUERY and IMPORTRANGE in Google Sheets or XLOOKUP in Excel 365.
Is this quiz useful for a data analyst job test?
Yes. Lookups, conditional counting and summing, cleaning functions and pivot tables are the most common topics in spreadsheet assessments for analyst roles.
Is my data stored anywhere?
No. The quiz runs entirely in your browser. Your name, answers and score are not uploaded. Your best score and settings are remembered only in this browser so you can track your progress.
More free quizzes
See all Codanics quizzes → · Courses · Free tools · Blog



