Google Sheets interview questions

Basic questions

1. 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 is capped at 10 million cells.

2. 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.

   Try it in a live sheet

3. 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.

   Try it in a live sheet

4. 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.

   Try it in a live sheet

5. 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.

6. 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

1. 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.

   Try it in a live sheet

2. 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.

   Try it in a live sheet

3. 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").

   Try it in a live sheet

4. 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.

5. 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".

6. 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.

7. 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

1. 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.

   Try it in a live sheet

2. 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.

   Try it in a live sheet

3. 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.

4. 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.

5. 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

Reading answers helps; writing the formulas under a little time pressure helps more. The Google Sheets skills test takes about ten minutes, and the Sheets lessons cover every formula on this page with graded exercises.