# 18 Google Sheets interview questions, with answers

> These are the Google Sheets questions that come up in interviews for analyst, operations, marketing and admin roles, with the answer an interviewer is listening for. The formula questions come with a graded task: write the formula in a live sheet and it is checked on the spot, then re-checked on changed data.

Source: https://spreadsheetpractice.com/google-sheets/interview-questions · Updated October 29, 2026

## Basic questions

### What are the main differences between Google Sheets and Excel?

Sheets runs in the browser, saves every change automatically and lets several people edit the same file at once, with comments and a full version history. Most everyday functions work the same way, but Sheets has its own: `QUERY`, `IMPORTRANGE`, `ARRAYFORMULA`, `SPLIT` and the `REGEX` functions. A few shared functions take different arguments, such as `FILTER`, which takes each condition as a separate argument. Excel is usually the better fit for very large files and heavy modeling, since a Sheets file tops out at 20 million cells.

### What is the difference between a relative and an absolute reference?

A relative reference like `B2` shifts when you copy the formula: one row down, it becomes `B3`. An absolute reference like `$F$1` stays fixed, which is what you want for a single rate or target that every row uses. Mixed references lock only one part: `$B2` locks the column, `B$2` locks the row. In Sheets, pressing F4 while the cursor is on a reference cycles through the four versions.

A graded task for this question ("Commission at one rate") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### How do you count the rows that meet more than one condition?

Use `COUNTIFS`, which takes pairs of a range and a condition and counts the rows where every condition is true: `=COUNTIFS(B2:B11,"High",C2:C11,"Open")`. For a single condition, `COUNTIF` is enough. A good answer also mentions putting the conditions in cells, like `=COUNTIFS(B2:B11,E2,C2:C11,F2)`, so the report changes when someone picks a different priority or status.

A graded task for this question ("Open tickets by priority") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### What does IFERROR do, and when would you use IFNA instead?

`IFERROR(value, value_if_error)` returns the value, or a fallback when the formula errors for any reason. `IFNA` only catches `#N/A`, the "not found" error from lookups. Around a lookup, `IFNA` is the safer choice: a missing code becomes "Not found", but a broken range or a typo still shows an error you can fix instead of quietly turning blank.

A graded task for this question ("Prices with a fallback") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### How would you share a sheet so a colleague can comment but not change anything?

Click Share, add their email and set their role to Commenter. The roles are Viewer, Commenter and Editor. Under General access you choose whether only the people added can open the file (Restricted) or anyone with the link. For a file that goes outside the company, a good answer adds that you would share with named people rather than "anyone with the link", and that editors can be stopped from resharing in the sharing settings.

### Someone overwrote a column yesterday. How do you get it back?

Open File > Version history > See version history. Sheets saves versions automatically, shows who changed what, and lets you restore an earlier version or copy the old values out of it. You can also name an important version, such as "Before Q3 import", so it is easy to find later. For a single cell, right-clicking it and choosing Show edit history shows its past values.

## Intermediate questions

### What is the difference between VLOOKUP and XLOOKUP?

`VLOOKUP` searches the first column of a table and returns a column you count by number, and it defaults to an approximate match unless you pass `FALSE`. `XLOOKUP` takes a search range and a return range separately, so it can return a column to the left, defaults to an exact match, and has a built-in value for when nothing is found. Google Sheets added XLOOKUP in 2022, so both work in current Sheets files.

A graded task for this question ("Look up to the left") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### How do you total sales for one region and one product?

Use `SUMIFS`. The range to add comes first, then each criteria range and its condition: `=SUMIFS(C2:C10,A2:A10,"West",B2:B10,"Chair")`. It adds only the rows where every condition is true. Interviewers like to hear that the argument order differs from `SUMIF`, where the range to add comes last, and that the conditions belong in cells so the summary can be reused.

A graded task for this question ("Region and product total") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### How does FILTER work in Google Sheets, and how is it different from Excel?

`FILTER` returns the rows of a range that meet your conditions, and the results spill into the cells below. In Sheets, each condition is its own argument and all of them must be true: `=FILTER(A2:A9,B2:B9="West",C2:C9>500)`. In Excel, the third argument is the value to show when nothing matches. In Sheets, no match returns `#N/A`, so you wrap it: `=IFNA(FILTER(...),"None")`. For OR logic, add the tests inside one condition: `(B2:B9="West")+(B2:B9="East")`.

A graded task for this question ("Big orders from the West") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### How do you get a list of the unique values in a column, and count them?

`=UNIQUE(A2:A)` returns each value once, in the order it first appears, and `=SORT(UNIQUE(A2:A))` sorts that list. To count distinct values directly, Sheets has `COUNTUNIQUE`, which skips blanks: `=COUNTUNIQUE(A2:A)`. A strong answer mentions cleaning the data first, because "Acme" and "Acme " with a trailing space count as two different values.

### What is data validation, and how would you use it?

Data validation limits what can be typed into a cell. In Data > Data validation you add a rule, such as a dropdown built from a list of values or from a range, a checkbox, a number within limits, or a valid date. You then choose whether invalid entries show a warning or are rejected. It is the main way to keep shared sheets consistent, for example so a Status column only ever holds "Open", "In progress" or "Done".

### How would you make overdue tasks turn red?

Select the task rows, open Format > Conditional formatting and choose "Custom formula is" from the "Format cells if" list. Enter a formula that is true for overdue rows, such as `=AND($C2<TODAY(),$D2<>"Done")`, where C holds the due date and D the status, and pick a red fill. The dollar sign before the column letter keeps every cell in the row looking at columns C and D, so the whole row turns red, not just one cell.

A graded task for this question ("Overdue tasks turn red") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### How do you stop people from editing part of a shared sheet?

Use Data > Protect sheets and ranges. You can protect a range or a whole tab, then either restrict who can edit it or only show a warning when someone tries. A common setup is to protect the formula columns and the lookup tabs while leaving the input columns open, so collaborators can enter data without breaking the calculations. Owners and the people you list can still edit.

## Advanced questions

### What does ARRAYFORMULA do, and why would you use it?

`ARRAYFORMULA` lets one formula work on whole ranges and return a result for every row, so you don't fill a formula down. `=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))` calculates every row, including rows added later, and leaves empty rows blank. It is useful on sheets fed by forms or imports, where new rows arrive without anyone copying formulas down. Pressing Ctrl+Shift+Enter while editing a formula wraps it in ARRAYFORMULA.

A graded task for this question ("One formula for every row") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### Explain the QUERY function. How would you total sales by region?

`QUERY(data, query, headers)` runs a SQL-like statement over a range, with clauses such as `select`, `where`, `group by`, `order by`, `limit` and `label`. Columns are named by their letters: `=QUERY(A1:C9,"select A, sum(C) group by A order by sum(C) desc",1)` totals column C for each region in column A, largest first. The last argument says how many header rows the data has. Text in a query goes in single quotes, as in `where B = 'West'`, and the comparison is case-sensitive.

A graded task for this question ("Totals by region") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### What is IMPORTRANGE, and what has to happen the first time you use it?

`IMPORTRANGE` pulls a range from another spreadsheet: `=IMPORTRANGE("spreadsheet URL","Sales!A1:D200")`. The first time a sheet imports from a particular file, the cell shows `#REF!` with an "Allow access" button, and someone with access to both files has to click it. After that the data updates on its own. A good answer adds that you would usually wrap it in `QUERY` to bring in only the rows and columns you need.

### When would you use a pivot table, and when would you use QUERY instead?

A pivot table (Insert > Pivot table) is the fastest way to summarize and explore: drag fields into rows, columns and values, and change the layout with a few clicks, which suits people who don't write formulas. `QUERY` is a formula, so it is easier to reuse, to filter with conditions from other cells, and to feed into charts or other formulas. Many analysts explore with a pivot table, then build the final report with QUERY or SUMIFS so it is easy to audit.

A graded task for this question ("Hours by project") is on [the interview questions page](https://spreadsheetpractice.com/google-sheets/interview-questions).

### How would you split "Last, First" names into two columns?

For a one-off, Data > Split text to columns does it in place. As a formula that keeps working when new names arrive, use `SPLIT`: `=SPLIT(A2,", ",FALSE)` splits at the comma-and-space and spreads the parts across two cells. The third argument, `FALSE`, tells SPLIT to treat ", " as one delimiter rather than splitting at every comma and every space. Run `TRIM` first if the data has stray spaces.

Practice before the interview: the [Google Sheets skills test](https://spreadsheetpractice.com/google-sheets/skills-test) takes about ten minutes.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
