Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 29 ยท Projects, Practice and Interviews

Interview Questions

Prepare for Google Sheets interviews: common questions with clear answers on formulas, lookups, QUERY, pivots, errors and Excel differences.

Interviewers for analyst, operations, finance and support roles rarely ask for trivia. They want to see that you can pick the right tool, explain why a formula broke and avoid mistakes that cost money. This lesson collects the questions that come up most often, with short answers you can say out loud, the pitfalls behind them, and the differences from Excel that people ask about.

Google Sheets cannot run on this page, so formulas are read-only. Two TryIt blocks are stand-ins, small programs that demonstrate the ideas behind two classic questions: why approximate lookups fail on unsorted data, and why floating-point math surprises people.

What interviewers look for

Right tool Pivot, QUERY or formula?
Safe formulas Absolute refs, exact match.
Debugging Read the error, find the cause.
Clear words Explain it to a non-expert.
1 / 2

Basics

What is the difference between a cell, a range and a sheet? A cell is one box such as B3. A range is a block of cells such as B3:D10. A sheet (tab) is one grid of cells; a spreadsheet file holds one or more sheets.

How does Google Sheets decide whether an entry is a number, text or date? It parses what you type. 12 and 12.5 become numbers, 2026-03-01 usually becomes a date, and '123 (leading apostrophe) forces text. Numbers align right and text left by default, which is a quick visual check. A number stored as text will not add up, so check alignment when a SUM looks wrong (Entering and Editing Data).

What is the formula bar shortcut for editing a cell? Press F2 or double-click the cell.

References and formulas

Explain relative, absolute and mixed references. A1 shifts when copied. $A$1 never shifts. $A1 locks the column only, A$1 locks the row only. Use F4 while editing to cycle through them. A typical bug is copying a tax-rate formula down without locking the rate cell (Relative, Absolute and Mixed References).

What is the order of operations? Parentheses, exponents, multiplication and division, then addition and subtraction. =2+3*4 is 14, =(2+3)*4 is 20. Percent signs and the unary minus are also part of the rules, so add parentheses when in doubt.

What do #DIV/0!, #N/A, #REF!, #VALUE! and #NAME? mean?

Error Typical cause
#DIV/0! Dividing by zero or an empty cell
#N/A A lookup found nothing
#REF! A referenced cell or sheet was deleted
#VALUE! Wrong type, such as text in math
#NAME? Misspelled function or unknown name

See Understanding Formula Errors. A good answer adds that IFERROR hides errors and so should not be wrapped around everything: it can mask real problems.

Lookups

What is wrong with VLOOKUP and how do you fix it? It can only return columns to the right of the key, uses a column number that breaks when you insert columns, and defaults to approximate match. Fixes: pass FALSE for exact match, or use XLOOKUP or INDEX/MATCH (VLOOKUP, HLOOKUP and Their Limits, XLOOKUP, INDEX and MATCH).

Python Stand-in: approximate match on unsorted data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

How do you do a lookup on two criteria? Combine conditions: =XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), C2:C100) inside ARRAYFORMULA, or build a helper key column. For sums use SUMIFS.

Data tools

When do you use a pivot table instead of formulas? Use a pivot table for fast exploratory summaries: drag fields, change the grouping, filter. Use formulas (SUMIFS, QUERY) when the result must sit in a precise place, feed other formulas, or be built from a dropdown (Pivot Tables).

Explain QUERY in one sentence. It runs a SQL-like statement (select, where, group by, order by, label) over a range and returns the result as a table; the third argument tells it how many header rows there are (The QUERY Function).

What is the difference between a filter and a filter view? A normal filter changes what everyone sees. A filter view is saved per person and does not disturb other editors, which matters in shared files (Sorting and Filtering Data).

How do you stop bad input? Data validation: a dropdown list, a number range, a checkbox or a custom formula, with reject-input turned on (Data Validation and Dropdowns).

What does ARRAYFORMULA do? It lets a formula work on whole ranges, so one cell fills a column. Newer functions like MAP, BYROW and LAMBDA extend that idea (Array Formulas and ARRAYFORMULA).

Excel and Sheets differences

Be conservative: say what you know, and say that details vary by version.

  • Collaboration: Sheets is browser-first with live multi-user editing and version history; Excel has similar online features but a strong desktop version.
  • Functions unique to Sheets: QUERY, IMPORTRANGE, IMPORTHTML, GOOGLEFINANCE, SPARKLINE options and ARRAYFORMULA as an explicit wrapper (Importing Live Data).
  • Automation: Sheets uses Apps Script (JavaScript); Excel uses VBA and Office Scripts (Automating with Apps Script).
  • Scale: Sheets has a cell limit per spreadsheet, so very large data is better kept in a database or BigQuery.
  • Function names and arguments mostly match, but some differ, such as how arrays spill, so test imported files.

Number surprises

Why does =0.1+0.2=0.3 sometimes return something odd? Computers store many decimals in binary, so tiny errors can appear. Round money with ROUND before comparing.

JS Stand-in: floating-point comparison and rounding

Press Run (or Ctrl + Enter) to see the console output here.

No output.

How to answer well

State the goal, choose the simplest tool, mention one pitfall, and give a one-line formula. If you are asked a live question, talk while you build: interviewers value your reasoning more than a memorized syntax. Practice with Practice Exercises with Solutions before the interview.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A SUM returns 0 although the column shows numbers. What is the most likely cause?

    Numbers stored as text (often left-aligned) are ignored by SUM; convert them to real numbers.
  2. 2. Which reference keeps the column fixed but lets the row change when copied down?

    $A1 locks column A and allows the row to move.
  3. 3. Why is IFERROR around an entire formula risky?

    IFERROR returns the fallback for any error, so genuine problems silently look like normal results.
  4. 4. What is a filter view useful for?

    Filter views are saved separately and do not alter the shared sheet.

Summary

  • Explain references, order of operations and common errors with one example each.
  • Prefer exact-match lookups; know XLOOKUP and INDEX/MATCH as the VLOOKUP replacements.
  • Choose a pivot table for exploration and QUERY or SUMIFS for fixed results.
  • Mention validation, filter views and protected ranges when discussing shared files.
  • Be honest about Excel differences and round before comparing decimals.

Next: Google Sheets Cheat Sheet