Top 100 Excel & Google Sheets Interview Questions and Answers — The Complete Guide for Aspiring Business Analysts
- IOTA ACADEMY
- Oct 16
- 11 min read
Introduction
Microsoft Excel and Google Sheets are two of the most essential tools for any Business Analyst. From data cleaning and organization to complex analysis and visualization, these tools form the foundation of analytical decision-making in every modern organization. Recruiters hiring for Business Analyst roles — especially at premium packages like 12 LPA — expect candidates to be proficient not only in using basic formulas but also in applying advanced functions, pivots, lookups, and automation to solve real business problems efficiently.
This comprehensive guide — “Top 100 Excel & Google Sheets Interview Questions and Answers” — is designed to help aspiring analysts build strong conceptual and practical command over these tools. The questions are structured progressively, covering everything from the fundamentals of spreadsheets and formulas to advanced features like Power Query, Pivot Tables, Data Validation, Dynamic Arrays, and Google Sheets’ cloud-specific functions such as QUERY and IMPORTRANGE.
Each question comes with a clear, easy-to-understand answer and concise explanation so you can grasp both how and why a function or technique is used. Whether you are a fresher entering the analytics field or an upskilling professional, this guide will give you the clarity, confidence, and competitive edge to excel in Business Analyst interviews.
Basics & Fundamentals
What is a spreadsheet? What are Excel / Google Sheets used for?
Answer:
A spreadsheet is a grid of rows and columns used to store, organize, compute, and analyze data. Excel (Microsoft) and Google Sheets are tools used for data entry, calculations, charting, pivoting, and analysis.
What’s the difference between Excel and Google Sheets?
Answer:
Excel (desktop) is powerful, supports large datasets, advanced features (Power Query, VBA).
Google Sheets (cloud) is collaborative (real-time multiuser).
Some newer Excel features (like dynamic arrays) were introduced recently; Google Sheets has its own features (e.g. ARRAYFORMULA, IMPORTRANGE, add-ons).
What is a cell, row, column, worksheet vs workbook?
Answer:
Cell: one box (intersection of row & column)
Row / Column: horizontal / vertical line of cells
Worksheet (sheet): single tab within file
Workbook: entire file (Excel .xlsx or Google Sheets)
How do you enter formulas? What is formula syntax?
Answer:
Precede formula with =. E.g.: =A1 + B2, =SUM(C1:C10). Function name, arguments in parentheses, separated by commas (or semicolons depending locale).
What is a range?
Answer:
A group of contiguous cells. E.g. A1:B10, C3:C20. You can apply formulas, formatting, etc., to ranges.
What are absolute vs relative references?
Answer:
Relative (e.g. A1): changes when formula is copied
Absolute (e.g. $A$1): remains fixed
You can also use mixed references (e.g. $A1, A$1).
What is a function?
Answer:
A predefined operation you can call with arguments. Examples: SUM, AVERAGE, VLOOKUP, IF.
What is auto-fill / fill handle?
Answer:
Drag a cell’s corner to auto-fill adjacent cells (copy formula, continue series, etc.).
What is conditional formatting?
Answer:
A feature to format cells (color, font) based on conditions (e.g. highlight values > 1000). Helps visually flag data.
How do you sort & filter data in Excel / Sheets?
Answer:
Use Sort (ascending/descending by column) and Filter (show/hide rows based on criteria). Google Sheets has “Filter views” for custom filters per user.
What is data validation?
Answer:
A restriction on what input is allowed (e.g. dropdown list, number range). Prevents invalid entries.
How to remove duplicates?
Answer:
Excel: “Remove Duplicates” in Data tab.
Sheets: Data → Data cleanup → Remove duplicates.
What is text-to-columns?
Answer:
Splits cell content by delimiter or fixed width into multiple columns (e.g. split “Name, Age” into two columns).
What is concatenate / join text?
Answer:
Combine strings:
Excel: =CONCATENATE(A1, " ", B1) or new =CONCAT / =TEXTJOIN
Sheets: =A1 & " " & B1 or =TEXTJOIN(" ", TRUE, A1, B1)
What is the difference between COUNT, COUNTA, COUNTBLANK?
Answer:
COUNT: counts numeric values only
COUNTA: counts non-empty (numbers, text)
COUNTBLANK: counts empty cells
What’s the difference between SUM and SUMIF / SUMIFS?
Answer:
SUM: adds all values in range
SUMIF: adds values in range that meet one condition
SUMIFS: adds values meeting multiple conditions
What is AVERAGEIF / AVERAGEIFS?
Answer:
AVERAGEIF(range, criteria, [average_range]) calculates average for values meeting condition. AVERAGEIFS handles multiple conditions.
What is MIN / MAX?
Answer:
Return the minimum / maximum value in a range.
What is the IF function?
Answer:
=IF(condition, value_if_true, value_if_false)
E.g. =IF(A1 > 100, "High", "Low").
What is nested IF?
Answer:
Using an IF inside another, e.g. =IF(A1 > 100, "High", IF(A1 > 50, "Medium", "Low")). But newer functions (like IFS) are preferable for many conditions.
Intermediate / Useful Functions
What is VLOOKUP?
Answer:
Vertical lookup: VLOOKUP(lookup_value, table_array, col_index, [range_lookup]). Searches first column, returns value from specified column.
Caveats: It has limitations (requires lookup column to be leftmost, approximate match issues). Use INDEX + MATCH or XLOOKUP (Excel newer versions) or LOOKUP in Sheets.
What is HLOOKUP?
Answer:
Horizontal lookup: searches in top row and returns value from a specified row in that table.
What is INDEX and MATCH used together?
Answer:
More flexible lookup combo: MATCH finds position; INDEX gets value at that position. E.g. =INDEX(B2:B10, MATCH("Alice", A2:A10, 0)).
What is XLOOKUP (Excel newer versions)?
Answer:
More powerful lookup function: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Replaces many VLOOKUP/HLOOKUP issues. (Excel 365 / 2019+).
What is MATCH?
Answer:
MATCH(lookup_value, lookup_array, [match_type]) returns position (index) of lookup_value in lookup_array.
What is OFFSET function?
Answer:
Returns a range offset from a given reference: =OFFSET(start_cell, rows, cols, [height], [width]). Useful but volatile (recalculates often).
What is TRANSPOSE?
Answer:
Converts vertical ranges to horizontal and vice versa. In Sheets: =TRANSPOSE(A1:A5).
What is UNIQUE (Google Sheets / Excel dynamic arrays)?
Answer:
Returns unique values from range. In Sheets: =UNIQUE(range). Excel’s dynamic arrays support UNIQUE() in newer versions (Office 365).
What is FILTER (Google Sheets / Excel dynamic arrays)?
Answer:
Filters values based on condition. E.g. =FILTER(A1:A10, B1:B10 > 100). Excel dynamic array version exists in newer Excel.
What is SORT function?
Answer:
Sorts range: =SORT(range, sort_index, is_ascending). Available in Sheets and newer Excel.
What is ARRAYFORMULA (Sheets)?
Answer:
Allows applying a formula over an array automatically. E.g. =ARRAYFORMULA(A2:A10 * B2:B10).
What is SPLIT (Sheets)?
Answer:
Splits text by delimiter: =SPLIT(A1, ",").
What is IMPORTRANGE (Sheets)?
Answer:
Imports range from another Google Sheets document: =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10").
What is QUERY function (Sheets)?
Answer:
Issues SQL-like query on a sheet: =QUERY(data_range, "SELECT A, B WHERE C > 100", 1). Very powerful for filtering, aggregations.
What is GOOGLEFINANCE (Sheets)?
Answer:
Fetches stock / financial data, e.g. =GOOGLEFINANCE("GOOG", "price").
What is SUBTOTAL?
Answer:
Performs aggregate functions (sum, average, count, etc.) but only on visible rows (excludes hidden rows). Syntax: =SUBTOTAL(function_num, range).
What is AGGREGATE (Excel)?
Answer:
An advanced version of SUBTOTAL allowing more functions and options (ignore hidden, errors, nested).
What is RANK / RANK.EQ / RANK.AVG?
Answer:
Ranks a number within a dataset. RANK.EQ gives the same rank to ties; RANK.AVG gives average rank for ties.
What is PERCENTRANK / PERCENTRANK.INC / PERCENTRANK.EXC?
Answer:
Gives relative standing of a value in range as percentile.
What is ROUND, ROUNDUP, ROUNDDOWN?
Answer:
Round a number to specified decimal places. ROUNDUP always rounds up, ROUNDDOWN always down.
What is TEXT function?
Answer:
Converts numbers to formatted text: =TEXT(A1, "yyyy-mm-dd"), =TEXT(B1, "#,##0.00").
What is VALUE function?
Answer:
Converts text that looks like number back to numeric type.
What is LEFT, RIGHT, MID?
Answer:
Extract substring: LEFT(text, num_chars), RIGHT(...), MID(text, start, length).
What is LEN, TRIM, CLEAN?
Answer:
LEN: length of text
TRIM: removes extra spaces
CLEAN: removes non-printable characters
What is FIND vs SEARCH?
Answer:
Both find substring; FIND is case-sensitive, SEARCH is not.
What is CONCAT / TEXTJOIN?
Answer:
CONCAT joins two or more items; TEXTJOIN joins with delimiter and can ignore empty cells. E.g. =TEXTJOIN(", ", TRUE, A1:A5).
What is EDATE / EOMONTH / TODAY / NOW?
Answer:
TODAY() returns current date
NOW() returns date + time
EOMONTH(start_date, months) returns end of month
EDATE(start_date, months) returns date shifted by months
What is DATEDIF?
Answer:
Calculates difference between two dates in years, months, days. (Not documented in Excel UI but works).
What is NETWORKDAYS / NETWORKDAYS.INTL?
Answer:
Returns count of working days between dates (excluding weekends, holidays). INTL allows custom weekend definitions.
What is WORKDAY / WORKDAY.INTL?
Answer:
Returns a date after adding a number of working days. INTL allows custom weekends.
Advanced / Analytical & Dashboarding
What is Pivot Table / Pivot in Sheets?
Answer:
A tool to quickly summarize data by grouping and aggregating (sum, count) along different dimensions. In Sheets: Data → Pivot table.
What is slicer / filter in pivot?
Answer:
A user interface element (filter) to dynamically filter pivot table data.
What is calculated field (in pivot)?
Answer:
A custom formula inside pivot to compute new metrics (e.g. profit margin) using pivot fields.
What is conditional formatting in pivot tables?
Answer:
Same concept: apply formatting based on values in pivot cells (e.g. highlight high sales).
What is “drill-down” in pivot table?
Answer:
The ability to click on aggregated value and see underlying detailed records.
How do you handle large data sets (tens of thousands of rows) in Excel / Sheets?
Answer:
Tips:
Use efficient formulas (avoid volatile ones)
Use filtering / pivot rather than writing many formulas
Use Excel’s Data Model / Power Pivot (in Excel)
Split across sheets or use external data sources
In Sheets, avoid leaving many formulas on long ranges
What is Power Query / Get & Transform (Excel)?
Answer:
A data extraction, transformation, and loading tool inside Excel (since Excel 2016+). Helps clean, shape, merge external data, and refresh.
What is data model in Excel / Data Model / Power Pivot?
Answer:
The internal model to support relational tables, relationships, and DAX-like measures. Allows you to go beyond flat tables.
What is dynamic chart / dynamic range for chart?
Answer:
Chart automatically updates when data expands. Achieved using named ranges or tables (Ctrl+T) or dynamic named formulas (OFFSET / INDEX).
What is named range / named table?
Answer:
You name a cell or range (e.g. “SalesData”) and refer to it in formulas or charts, making readability better.
What is error handling functions like IFERROR, ISERROR?
Answer:
IFERROR(expr, value_if_error) returns fallback if expr errors
ISERROR(expr) returns TRUE/FALSE if error
What is goal seek / solver?
Answer:
Goal Seek: find input that yields desired output (one variable)
Solver: more advanced, multiple variables & constraints optimization
What is scenario manager / data tables?
Answer:
Tools to model multiple hypothetical scenarios. Data Tables allow sensitivity analysis (one- or two-variable). Scenario Manager stores named scenarios.
What is macro / VBA in Excel?
Answer:
A macro is an automated sequence of actions. VBA (Visual Basic for Applications) is Excel’s scripting language for macros.
What is Google Apps Script (for Sheets)?
Answer:
JavaScript-based scripting environment for extending Sheets (automations, custom functions, triggers).
What is an add-on / plugin?
Answer:
Additional tools you can install to extend Excel or Sheets functionality (e.g. analytics, connectors).
How do you protect sheet / workbook / lock cells?
Answer:
Use “Protect Sheet / Protect Workbook” in Excel. You can lock specific cells (unlock others) before protection. In Sheets: Data → Protect sheets and ranges.
What is conditional formatting with formulas?
Answer:
Use a custom formula (e.g. =$B2 > 1000) to format cells. Powerful for cross-column conditions.
What is sparklines?
Answer:
Tiny inline charts inside a cell. In Excel: Insert → Sparklines. In Sheets: =SPARKLINE(range).
What is chart types and when to use which?
Answer:
Line: trend over time
Bar / Column: compare categories
Pie / Donut: part-to-whole
Scatter: relationships
Combo: mix (e.g. bars + line)
Area: cumulative trends
Histogram: distribution
How to make dashboard in Excel / Sheets?
Answer:
Combine visuals, slicers / filters, pivot tables, layout, interactive elements. Use good design (white space, titles, consistency).
What are best practices for dashboard layout?
Answer:
Arrange top KPIs at top
Group related visuals
Use consistent colors / styles
Avoid clutter
Use filter controls
Ensure readability (labels, legends)
How do you refresh data / link external data?
Answer:
Excel: connect to external sources (CSV, DB, web) via Power Query; refresh.
Sheets: use IMPORTRANGE, GOOGLEFINANCE, Sheets API, add-ons.
What is version history / revision history?
Answer:
Sheets tracks changes over time; you can revert. In Excel (OneDrive or SharePoint), version history is available.
What is collaborative editing / sharing?
Answer:
In Sheets, multiple users can edit simultaneously. Excel Online or via SharePoint / OneDrive also supports co-authoring.
Problem-solving / Scenario / Trick Questions
How would you flag top 10% performers in a list?
Answer:
Use PERCENTRANK, RANK, or sort and mark first 10%. E.g. =IF(RANK(A2, A$2:A$100) <= (0.1 * COUNT(A$2:A$100)), "Top10%", "").
How to count unique distinct values in a range?
Answer:
Sheets: =COUNTA(UNIQUE(range))
Excel newer: =COUNTA(UNIQUE(range)) (dynamic arrays)
Older Excel: use SUM(1/COUNTIF(range, range)) with array formula
How to find duplicates and highlight them?
Answer:
Use conditional formatting rule: e.g. =COUNTIF($A$2:$A$100, A2) > 1.
How to compare two columns and highlight differences?
Answer:
Use conditional formatting: e.g. in B2, formula =A2 <> B2 to format mismatches.
How to do a two-way lookup (row & column)?
Answer:
Use INDEX + MATCH twice: =INDEX(data, MATCH(row_key, row_headers,0), MATCH(col_key, col_headers,0)).
How to do dynamic dropdown list?
Answer:
Use data validation with range that can expand (named range or table). In Sheets, use INDIRECT + OFFSET or FILTER.
How to sum top N values?
Answer:
E.g. =SUM(LARGE(range, {1,2,3})) sums top 3. Or SUMPRODUCT + RANK.
How to do month-to-date or year-to-date sum?
Answer:
Use SUMIFS with date conditions:
=SUMIFS(amount_range, date_range, ">=" & start_of_period, date_range, "<=" & TODAY()).
How to create moving average / rolling sum?
Answer:
Use AVERAGE or SUM with offset: e.g. =AVERAGE(B2:B4) sliding window, or dynamic: =AVERAGE(OFFSET(...)).
How to find nth largest / smallest value?
Answer:
Use LARGE(range, n) or SMALL(range, n).
How to rank with tie-breaker (secondary sort)?
Answer:
Combine tie-breaker in RANK or use RANK + COUNTIFS to refine order.
How to convert text to date / number?
Answer:
Use DATEVALUE, VALUE, or parse with LEFT/MID etc. If Excel doesn’t auto-detect, use Text to Columns.
How to calculate age from birthdate?
Answer:
=DATEDIF(birthdate, TODAY(), "Y") (years). Or more precise with months/days using DATEDIF.
How to dynamically highlight a row when selecting a cell?
Answer:
Use conditional formatting with =ROW() = $A$X or similar referencing an input cell, or using CELL("address").
How to protect workbook but allow editing specific cells?
Answer:
Unlock those cells (Format Cells → Protection → uncheck Locked), then protect the sheet. Only unlocked cells are editable.
How to get unique combinations from two columns?
Answer:
Use UNIQUE( (col1 & "|" & col2) ) and then split. Or in Excel, use array formula with UNIQUE and FILTER.
How to do multi-criteria lookup (lookup where A = x AND B = y)?
Answer:
Use INDEX + MATCH with combined key: e.g. MATCH(1, (A:A = x) * (B:B = y), 0). Or use FILTER in Sheets / Excel dynamic array.
How to count blank / non-blank cells with conditions?
Answer:
Use COUNTIFS or SUMPRODUCT: e.g. =COUNTIFS(A2:A100, "", B2:B100, "some condition").
How to check if a value exists in a range?
Answer:
=IF(COUNTIF(range, value) > 0, "Exists", "No").
How to combine multiple conditions in IF (AND / OR)?
Answer:
=IF(AND(condition1, condition2), value1, value2) or IF(OR(...)).
How to note differences in two sheets / workbooks?
Answer:
Use formulas in one workbook referencing the other (e.g. =IF(A1 <> '[OtherBook.xlsx]Sheet1'!A1, "Diff", "")) or use “Inquire” add-in (Excel) or compare via Apps Script (Sheets).
How to automatically update a chart when new rows are added?
Answer:
Use named dynamic range or Excel Table (structured reference) so the chart range expands automatically.
How to import data from web / external source?
Answer:
Excel: Data → From Web / Power Query → Web / API / CSV.
Sheets: IMPORTHTML, IMPORTXML, IMPORTDATA, IMPORTRANGE.
How to perform “What-If” analysis?
Answer:
Use data tables, scenario manager, goal seek / solver, or manual parameter change with dynamic formulas.
How to use “regex” or pattern matching in Sheets / Excel?
Answer:
In Google Sheets, functions like REGEXEXTRACT, REGEXREPLACE, REGEXMATCH are available. Excel (Office 365) includes TEXTSPLIT etc., but no built-in regex; you may use VBA or newer functions (e.g., LET, FILTER, XMATCH) or add-ins.
Tips & Notes for Interview Preparation
Practice using real datasets in Excel / Sheets to build fluency in formula writing.
Focus more on understanding logic and when to use each function rather than memorizing syntax.
Be ready for live “Excel test” rounds (you may be given a sheet and asked to produce a pivot, formula, etc.).
Know which version of Excel your target company uses (Office 365, Excel 2019, etc.), as newer versions add dynamic arrays and new functions (XLOOKUP, UNIQUE, FILTER).
In Google Sheets, practice collaboration features, version history, and cloud connectivity (IMPORTRANGE, QUERY).
In interview, always explain your steps and reasoning (this shows clarity).
Comments