Excel Interview Questions for Data Analyst 2026 — Top 40 Q&A with Answers

What is the difference between absolute and relative cell references in Excel?

A relative reference (e.g. A1) adjusts automatically when you copy a formula to another cell — the row and column change relative to the new position. An absolute reference (e.g. $A$1) stays fixed no matter where you copy the formula. Mixed references ($A1 or A$1) lock either the column or the row. In practice: use absolute references when pointing to a fixed lookup table or a rate value that should not change as you fill down.

What is VLOOKUP and when do you use it?

VLOOKUP (Vertical Lookup) searches for a value in the leftmost column of a range and returns a value from a specified column in the same row. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Use it when you need to combine data from two tables using a common key — for example, joining customer names from a customer table onto a sales transaction table using CustomerID. The key limitation: the lookup column must be the leftmost column of the range.

What does the IF function do? Give an example.

IF evaluates a condition and returns one value if true and another if false. Syntax: =IF(condition, value_if_true, value_if_false). In data analysis, IF is used to categorise data, flag anomalies, or calculate conditional values.

What is the difference between COUNT, COUNTA, and COUNTBLANK?

COUNT counts only cells that contain numbers. COUNTA counts all non-empty cells regardless of data type — numbers, text, dates, and logical values. COUNTBLANK counts empty cells. In data quality work, COUNTA - COUNT tells you how many non-numeric values exist in a column that should be all numbers — a quick way to detect data entry errors.

How do you remove duplicate rows in Excel?

Go to the Data tab → Remove Duplicates. You can choose which columns to check — if you only want to remove rows where all columns are identical, select all columns. If you want to remove rows that match on specific key columns (e.g. same CustomerID), select only those columns. Important: Excel removes duplicates in place without a preview of what will be deleted — always work on a copy of your data first.

Explain how a Pivot Table works and what it is used for.

A Pivot Table automatically groups, summarises, and aggregates data from a flat table into a more meaningful summary. You drag fields into four areas: Rows (what you want to group by), Columns (optional secondary grouping), Values (the metric to aggregate — sum, count, average), and Filters (to narrow down the data). Pivot Tables are the primary tool for exploratory data analysis in Excel — a good analyst can answer most "what is the total X by Y?" questions in under 30 seconds using a Pivot Table.

What is the difference between a Pivot Table VALUE field set to Sum versus Count?

Sum adds all numeric values in the group — useful for totalling revenue, units sold, or costs. Count counts the number of rows in the group regardless of the value — useful for counting transactions, customers, or orders. A common mistake is having text in a numeric column — Excel will default to Count instead of Sum and the analyst does not notice. Always check the Value Field Settings to confirm you are getting the aggregation you expect.

How do you calculate the % of row total or % of column total in a Pivot Table?

Right-click any value in the Values area → "Show Values As" → choose "% of Row Total" or "% of Column Total". This is useful for understanding proportion — for example, showing what percentage of each region's revenue comes from each product category. You can also choose "% of Grand Total" to show each cell as a percentage of the overall total.

What is a Calculated Field in a Pivot Table?

A Calculated Field lets you add a formula-based column to a Pivot Table without adding data to the source. For example, if your Pivot Table shows Revenue and Cost, you can add a Calculated Field for Profit Margin = Revenue - Cost, or Margin % = (Revenue - Cost) / Revenue. Go to PivotTable Analyze → Fields, Items & Sets → Calculated Field.

How do you refresh a Pivot Table when the source data changes?

Right-click anywhere in the Pivot Table → Refresh. To refresh all Pivot Tables in the workbook at once: PivotTable Analyze → Refresh → Refresh All. If you have added new rows to the source data, you may also need to update the data source range: PivotTable Analyze → Change Data Source.

What is INDEX-MATCH and why is it preferred over VLOOKUP?

INDEX-MATCH is a combination of two functions that together perform a lookup more flexibly than VLOOKUP. INDEX returns a value from a range at a given position. MATCH finds the position of a value in a range. Together: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). Advantages over VLOOKUP: (1) The lookup column does not need to be leftmost — you can look right-to-left. (2) You reference columns by name rather than number, so the formula does not break if columns are inserted. (3) Faster performance on large datasets.

What is XLOOKUP and how does it improve on VLOOKUP?

XLOOKUP is available in Excel 365 and Excel 2021+. It replaces both VLOOKUP and INDEX-MATCH in most cases. Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [not_found], [match_mode], [search_mode]). Key improvements: can look in any direction, returns the exact column you reference rather than a number, has a built-in error message when not found, can return multiple columns at once, and supports approximate match modes including binary search.

How do SUMIF and SUMIFS work? What is the difference?

SUMIF adds values in a range that meet one condition. Syntax: =SUMIF(criteria_range, criteria, sum_range). SUMIFS adds values that meet multiple conditions (all must be true). Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). Note: in SUMIFS, the sum_range comes first — opposite to SUMIF.

What does IFERROR do and when do you use it?

IFERROR catches formula errors and returns a specified value instead. Common errors in Excel: #N/A (lookup not found), #DIV/0! (dividing by zero), #REF! (broken reference), #VALUE! (wrong data type). Wrap any formula that might produce an error: =IFERROR(your_formula, value_if_error). Use "Not Found" for lookups, 0 for calculations that should be zero when data is missing, or "" (empty string) when you want to hide errors in a report. Avoid using IFERROR to hide all errors without understanding why they occur — that can mask real data problems.

What is the TEXT function used for?

TEXT converts a number or date into a text string with a specific format. Useful when you need to combine a date or number into a text string using &, or when you need to display values in a specific format for reporting.

What is Power Query in Excel and why is it important for data analysts?

Power Query is Excel's built-in ETL (Extract, Transform, Load) tool. It allows you to connect to data sources (CSV files, databases, web pages, SharePoint, other Excel files), clean and transform the data through a point-and-click interface, and load the result into your workbook as a table or into the Data Model. The critical advantage: every transformation step is recorded and can be refreshed with one click when the source data updates. This makes Power Query far superior to manual data cleaning for recurring reports.

What transformations can you do in Power Query?

Power Query can: remove and reorder columns, filter rows based on conditions, split columns by delimiter or character count, merge (join) queries together, append (union) multiple queries, unpivot columns (convert wide data to long format), group and aggregate data, replace values, change data types, create custom columns using M language, remove duplicates, fill down empty cells, and extract date parts (year, month, quarter). For a data analyst, the most frequently used are: column removal, filtering, merging two queries, unpivoting, and changing data types.

What does "Unpivot Columns" do in Power Query?

Unpivot transforms a wide table (where months or categories are spread across columns) into a long table (where each row represents one data point with a category column and value column). Wide data is common in raw business reports; long data is required for Pivot Tables, Power BI, and most analysis tools. Example: a table with columns Jan, Feb, Mar, Apr (each containing revenue) becomes a table with columns Month and Revenue — one row per month per customer.

You receive a sales report with 50,000 rows and 15 columns. How do you start your analysis?

Step 1: Check data quality first — look at row counts, check for blanks with COUNTBLANK or COUNTA, look at each column's data type, check for obvious outliers in numeric columns with MIN/MAX. Step 2: Understand the data — what does each column mean? What is the date range? What is the granularity (one row = one transaction? one row = one customer?). Step 3: Use a Pivot Table to get high-level summaries — total revenue by region, by product, by month. Step 4: Ask the specific business question you are trying to answer. Good analysts do not just calculate everything possible — they start with a question and use the data to answer it.

How would you merge two Excel files with customer data — one with demographics, one with transactions — into a single analysis file?

If both files have a common CustomerID column: bring both files into Excel using Power Query (Data → Get Data → From File). In Power Query, merge the two queries using Merge Queries → Left Join on CustomerID. This is equivalent to a SQL LEFT JOIN. The result is a single combined table that you can load into Excel and analyse. The advantage of using Power Query over a VLOOKUP for this task: the merge is repeatable and refreshable — if either source file is updated, click Refresh and the combined table updates automatically.

You are building a monthly sales report that takes 3 hours to produce manually. How would you automate it?

First, understand the current manual steps: which files are the sources, what cleaning is done, what calculations are made, what the final format is. Then use Power Query to automate the data extraction and cleaning steps — connect to the source files, set up the transformations, and save the query. Use named ranges, structured tables, and Pivot Tables for the analysis layer — these refresh with the data. Use formulas rather than hard-coded values wherever possible. Finally, document the process with a simple refresh guide. A report that took 3 hours manually should take 15 minutes with this approach — 10 minutes for new source files and 5 minutes for quality checking.

What is conditional formatting and how have you used it in reporting?

Conditional formatting automatically changes a cell's colour, font, or border based on its value or a formula condition. Common uses in data analyst work: colour scale to show high/low/medium values at a glance (e.g. green for high revenue, red for low), data bars to show relative size within a column without a chart, highlight cells above/below a threshold (e.g. flag revenue below ₹10,000 in red), and icon sets for status indicators (up/down arrows, traffic lights). The key reporting use: conditional formatting makes it possible for stakeholders to spot the important data in a large table without reading every cell.

How do you protect an Excel workbook or worksheet from accidental edits when sharing?

To protect a worksheet: Review → Protect Sheet → choose which actions are allowed (typically allow selection but not editing). Set a password if required. To protect specific cells while leaving others editable: select the cells that should remain editable → Format Cells → Protection tab → uncheck "Locked" → then protect the sheet. To protect the workbook structure (prevent adding or deleting sheets): Review → Protect Workbook. For sharing reports where you want users to input data in specific cells only, use a combination of unlocked input cells and a protected sheet.