📄 Free PDF Download
Advanced Excel Interview Questions for Data Analysts 2026
Ace your data analyst interviews with these 50 Advanced Excel questions — covering VLOOKUP, INDEX-MATCH, pivot tables, Power Query, dynamic arrays, and dashboards.
🔒 Get the Free Excel Interview PDF
50 Advanced Excel questions for data analysts with formula examples — free instant download.
✅ Done! Your Excel PDF is ready.
Click below for instant access to your free Advanced Excel interview questions PDF.
📥 Download PDF NowChat on WhatsApp
Advanced Excel Interview Questions for Data Analysts: Why Excel is Still a Core Skill in 2026
These advanced Excel interview questions for data analysts cover the formulas, tools, and techniques that interviewers test most frequently — from VLOOKUP and INDEX-MATCH to Power Query and dynamic arrays. Despite the growth of Python and Power BI, Microsoft Excel remains one of the most widely used tools in data analysis — particularly in finance, consulting, FMCG, and SME sectors. Excel is often the first tool analysts use to explore new data and communicate findings to non-technical stakeholders. Advanced Excel skills — pivot tables, Power Query, dynamic arrays, and dashboards — are routinely tested in data analyst interviews.
This guide covers the 50 most important Advanced Excel interview questions for 2026, with formula syntax, practical examples, and tips on what interviewers look for in each answer.
Section 1 — Excel Lookup & Reference Functions
Q1. What is VLOOKUP and what are its limitations?
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches for a value in the leftmost column of a table and returns a value from a specified column to the right. Limitations: (1) only searches left-to-right; (2) returns the wrong value if columns are inserted or rearranged; (3) slower on very large datasets; (4) returns #N/A if the lookup value is not found. Wrap in IFERROR to handle missing values: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found").
Q2. How does INDEX-MATCH work and why is it better than VLOOKUP?
INDEX(array, row_num, [col_num]) returns a value from a specified position. MATCH(lookup_value, lookup_array, [match_type]) returns the position of a value. Combined: =INDEX(B:B, MATCH(A2, A:A, 0)) finds the position of A2 in column A and returns the corresponding value from column B. Advantages over VLOOKUP: works left-to-right or right-to-left, unaffected by column insertions, faster on large datasets, and supports approximate matching on unsorted data.
Q3. What is XLOOKUP and when was it introduced?
XLOOKUP (introduced in Excel 365/2019) replaces both VLOOKUP and INDEX-MATCH with a cleaner syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). It searches any direction, handles missing values natively without IFERROR, supports wildcard matching, and can return multiple columns at once as a spill array. For analysts on Excel 365, XLOOKUP is the recommended modern replacement for VLOOKUP.
Q4. What is the difference between SUMIF and SUMIFS?
SUMIF sums values in a range based on a single condition: =SUMIF(range, criteria, sum_range). SUMIFS supports multiple conditions: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). Example: =SUMIFS(D:D, B:B, "North", C:C, "Q1") sums column D where region is North AND quarter is Q1. COUNTIF/COUNTIFS and AVERAGEIF/AVERAGEIFS follow the same pattern.
Q5. What is the OFFSET function and when is it used?
OFFSET(reference, rows, cols, [height], [width]) returns a range that is offset from a starting cell by a specified number of rows and columns. It is used to create dynamic ranges that expand as data grows. Example: a dynamic named range for a chart that automatically includes new rows. However, OFFSET is volatile (recalculates on every change), which can slow large workbooks. Modern alternatives include INDIRECT, structured table references, or FILTER/OFFSET combinations in Excel 365.
Section 2 — Pivot Tables & Data Analysis
Q6. How do you create a pivot table and what are its key components?
To create: select your data, go to Insert > PivotTable. Key components: Rows (categories for rows), Columns (categories across columns), Values (numeric fields to aggregate), Filters (global filters for the entire table). Drag fields from the field list into these areas. Right-click any value to change the aggregation from Sum to Count, Average, Max, etc. Slicers (Insert > Slicer) add visual filter buttons for interactive dashboards.
Q7. What is a calculated field in a pivot table?
A calculated field is a custom formula you create inside a pivot table using existing fields. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field. Example: create a "Profit Margin" field with formula =Revenue - Cost to display profit margin per row directly in the pivot table without modifying the source data. Calculated fields are useful for deriving metrics that do not exist in the raw data but are needed in the summary view.
Q8. What is Power Query and how does it differ from standard Excel data import?
Power Query (Data > Get & Transform) is a self-service ETL tool inside Excel that allows you to import data from multiple sources (files, databases, web APIs), clean it (rename columns, remove nulls, unpivot, split columns), and load it into Excel or the data model. All transformation steps are recorded in a query and can be refreshed with one click when source data updates. Standard Excel data import is static; Power Query creates a live, reproducible data pipeline.
Q9. What are dynamic arrays and spill functions in Excel 365?
Dynamic arrays are Excel 365 functions that automatically spill results into multiple cells: FILTER() returns rows matching a condition, SORT() returns a sorted array, UNIQUE() returns distinct values, SEQUENCE() generates a number sequence. Example: =FILTER(A2:C100, B2:B100="North") returns all rows where region is North. These functions eliminate the need for helper columns and complex array-entered formulas, enabling analysts to build dynamic reports far more efficiently.
Q10. How do you build an interactive dashboard in Excel?
Steps: (1) Organise clean data in a structured table (Ctrl+T). (2) Create pivot tables linked to the table. (3) Build pivot charts from the pivot tables. (4) Add slicers and connect them to multiple pivot tables/charts. (5) Arrange charts on a dedicated dashboard sheet with no gridlines (View > Gridlines off). (6) Use named ranges and INDIRECT for dynamic chart titles. (7) Lock the dashboard sheet and hide source sheets for a polished, client-ready presentation.
Section 3 — Advanced Excel Functions
Q11. What is the difference between relative, absolute, and mixed cell references?
A relative reference (A1) adjusts when copied — row and column both shift. An absolute reference ($A$1) is locked and does not change when copied. A mixed reference locks either the row ($A1) or the column (A$1). Analysts use absolute references for tax rates, conversion factors, or lookup tables that must remain constant. Press F4 to cycle through reference types when editing a formula.
Q12. How do conditional formatting rules work and when do analysts use them?
Conditional formatting applies visual formatting (colour, data bars, icon sets) to cells based on rules. Analysts use it to highlight negative values in red, top 10% performers in green, duplicate values, or cells below a target threshold. Rules can use formulas for complex logic — e.g., colour an entire row red if the deadline column is in the past using =$C2<TODAY(). Multiple rules are evaluated in order — higher-priority rules override lower ones.
Q13. What is a named range and why is it used?
A named range assigns a meaningful name to a cell or range (Formulas > Name Manager). Instead of writing =SUM($B$2:$B$100), use =SUM(SalesData). Benefits: formulas become more readable and self-documenting, names are portable across sheets, and dynamic named ranges using OFFSET or INDIRECT auto-expand. Data validation dropdowns using named ranges update automatically when the range changes, essential for dynamic form-based Excel tools.
Q14. What is a macro in Excel and when should an analyst use it?
A macro is a recorded or written sequence of VBA (Visual Basic for Applications) instructions that automates repetitive tasks. Use cases: auto-formatting monthly reports, batch importing CSV files, sending emails via Outlook from Excel, or running a series of data transformations at the click of a button. Record macros for simple repetitive tasks; write VBA for logic-based automation. For complex data pipelines, Python is more scalable, but macros are still widely used in corporate Excel environments.
Q15. What is the IFERROR function and how do you use it?
IFERROR(value, value_if_error) returns the value if no error, or the specified alternative if the formula produces an error (#N/A, #DIV/0!, #VALUE!, etc.). Example: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found") — if VLOOKUP finds no match, it displays "Not Found" instead of #N/A. IFERROR is essential for production dashboards to prevent error values from breaking downstream formulas or confusing stakeholders reviewing reports.
Interview Tip: In Excel interviews, you may be given a raw dataset and asked to clean it, build a pivot table, and create a simple dashboard in 20-30 minutes. Practice this workflow end-to-end with real data before your interview.
Related Free Resources
Frequently Asked Questions
What are the most important Excel functions for data analysts?
VLOOKUP/INDEX-MATCH/XLOOKUP for lookups, SUMIFS/COUNTIFS/AVERAGEIFS for conditional aggregations, IF/IFS/IFERROR for logic, and dynamic array functions (FILTER, SORT, UNIQUE) in Excel 365. Pivot tables and Power Query are also essential.
What is Power Query in Excel?
Power Query is Excel's built-in ETL tool that lets you import, clean, reshape, and combine data from multiple sources. All steps are recorded and can be refreshed with one click when the source data updates.
What is the difference between VLOOKUP and INDEX-MATCH?
VLOOKUP only searches left-to-right and breaks if columns are reordered. INDEX-MATCH is more flexible, works in any direction, is unaffected by column changes, and handles larger datasets more efficiently.
Is Excel still important for data analytics in 2026?
Yes. Excel remains the dominant tool in finance, consulting, FMCG, and operations analytics. Advanced Excel skills — especially Power Query, pivot tables, and dynamic arrays — are routinely required in India's data analyst job market.
How do I download the Advanced Excel Interview Questions PDF?
Fill in your name and email in the download form at the top of this page. You will get instant free access to the complete PDF.
×
🆕 Get the Free Excel PDF
50 Advanced Excel interview questions with formula examples. Free instant access.
No thanks
