I am learning flutter from JustAcademy, They provide very much great environment where people gather and work simultaneously. totally project based training institute.
Awesome Experience. I am a Front-end Web Designer working at Star India for 3 years now. I applied for Full-stack development and my experience has been phenomenal & they really do help with placements exceptionally. Thank you Roshan sir so much.
Data Analytics Bootcamp Mumbai | Data Analytics Bootcamp Online | Register for a Free Demo | Download Brochure
Excel remains one of the most widely used tools in data analytics and is assessed in the majority of data analyst interviews in India in 2026. Despite the rise of Python, Power BI, and Tableau, Excel is the daily working tool for analysts at banks, insurance companies, consulting firms, and operations-heavy organizations across every industry. A fresher who cannot demonstrate strong Excel skills confidently in a technical interview is at a significant disadvantage regardless of their other qualifications.
This blog covers the most important excel interview questions for data analysts, organized from basic to advanced, with clear answers that you can study, practice, and reference before your next interview. Whether you are appearing for your first analyst role or refreshing your preparation after a gap, this guide gives you the coverage you need across Excel formulas, pivot tables, reporting skills, data cleaning functions, and analytical techniques that interviewers consistently assess. Each question is numbered so you can track your progress and revisit specific topics efficiently.
Despite all the modern analytics tools available in 2026, Excel remains installed on virtually every business computer in India and used daily by finance teams, operations departments, HR functions, and analytics teams alike. Its accessibility and flexibility mean that it is often the first tool a business reaches for when analyzing data, building a report, or creating a financial model. For freshers entering analyst roles, Excel proficiency is both expected from day one and assessed in almost every hiring process.
Excel interview rounds for data analyst roles typically cover four areas. First, formula knowledge including lookup functions, logical functions, text functions, and statistical functions. Second, pivot table proficiency including creating, customizing, and interpreting pivot tables. Third, data cleaning techniques including removing duplicates, handling blanks, and standardizing formats. Fourth, reporting skills including building charts, using conditional formatting, and structuring dashboards. Interviewers assess both theoretical knowledge and the ability to apply functions to realistic business scenarios under time pressure.
Microsoft Excel is a spreadsheet application developed by Microsoft that allows users to store, organize, calculate, and visualize data in a grid of rows and columns. In data analytics, Excel is used for data cleaning, exploratory analysis, building reports, creating charts, performing financial modeling, summarizing datasets with pivot tables, and automating repetitive tasks with formulas and macros. Its accessibility and built-in functions make it one of the most widely used analytics tools across industries.
A workbook is the entire Excel file saved with a .xlsx or .xlsm extension. A worksheet, also called a sheet, is a single tab within that workbook. A workbook can contain multiple worksheets, each with its own independent grid of rows and columns. Data analysts often use multiple worksheets within one workbook to separate raw data, cleaned data, calculations, and dashboard views.
The most frequently used Excel functions for data analysts include VLOOKUP and XLOOKUP for looking up values, IF and IFS for conditional logic, SUMIF and SUMIFS for conditional aggregation, COUNTIF and COUNTIFS for conditional counting, INDEX and MATCH for flexible lookups, TEXT functions like LEFT, RIGHT, MID, and CONCATENATE for string manipulation, DATE functions like TODAY, DATEDIF, and YEAR for date calculations, and statistical functions like AVERAGE, MEDIAN, STDEV, and PERCENTILE.
VLOOKUP stands for Vertical Lookup and searches for a value in the first column of a range and returns a value from a specified column to the right. HLOOKUP stands for Horizontal Lookup and searches for a value in the first row of a range and returns a value from a specified row below. VLOOKUP is far more commonly used in data analytics because data is typically organized in columns rather than rows. Both have largely been superseded by XLOOKUP in modern Excel versions.
XLOOKUP is a newer and more powerful lookup function introduced in Excel 365 that addresses the main limitations of VLOOKUP. Unlike VLOOKUP, XLOOKUP can search in any direction including to the left of the lookup column. It does not require the lookup column to be the first column in the range. It handles errors more cleanly with a built-in if-not-found argument. It supports exact match, approximate match, and wildcard matching in a single function. For freshers, knowing XLOOKUP in addition to VLOOKUP signals awareness of modern Excel capabilities.
A relative cell reference like A1 changes automatically when a formula is copied to another cell, adjusting to maintain the same relative position. An absolute cell reference like dollar A dollar 1 remains fixed regardless of where the formula is copied. A mixed reference locks either the row or the column but not both. Understanding the difference is essential for building formulas that work correctly when applied across a range of cells, which is one of the most common sources of formula errors in analyst work.
Conditional formatting automatically changes the appearance of cells based on their values or formulas. It is used in reporting to highlight cells above or below a threshold, apply color scales to show relative magnitude, flag duplicates, identify blank cells, and create visual heat maps within a data table. For analysts, conditional formatting is a quick way to make reports more readable and to draw attention to key data points without building a separate chart.
COUNT counts only numeric values in a range. COUNTA counts all non-empty cells regardless of data type. COUNTBLANK counts empty cells in a range. COUNTIF counts cells that meet a single specified condition. COUNTIFS extends this to multiple conditions. These functions are foundational for data quality checks and summary statistics in analyst work.
A named range assigns a meaningful name to a cell or group of cells, making formulas easier to read and maintain. Instead of writing SUMIF of A2 colon A1000, you can write SUMIF of SalesData. Named ranges reduce errors in complex formulas, make workbooks easier to audit, and are especially useful in large models where cell references become difficult to track. They are also used in data validation dropdowns and in referencing ranges across worksheets.
IFERROR wraps another formula and returns a specified value if that formula produces an error instead of displaying the error code. It is commonly used with VLOOKUP and other lookup functions to return a blank, a zero, or a custom message like Not Found when a lookup value does not exist in the reference range. Proper use of IFERROR makes reports cleaner and prevents error codes from propagating through dependent calculations.
INDEX MATCH is a combination of two functions that together perform a more flexible lookup than VLOOKUP. INDEX returns the value at a specified position in a range. MATCH returns the position of a lookup value within a range. Combined, they can look up values in any direction, are not limited to searching in the first column, perform better with large datasets, and do not break when columns are inserted or deleted in the lookup range. Interviewers often ask freshers to explain this combination as a test of formula depth beyond basic VLOOKUP knowledge.
A pivot table is an interactive summary tool that allows analysts to quickly aggregate, group, and cross-tabulate large datasets without writing formulas. It can calculate sums, averages, counts, percentages, and other aggregations across multiple dimensions simultaneously. Common uses in analytics include summarizing sales by region and product, calculating average values by category, identifying top performers, building cross-tabulation reports, and serving as the data source for pivot charts. Pivot tables are one of the most assessed skills in Excel analyst interviews.
A pivot table displays summarized data in a tabular grid format. A pivot chart is a visual representation of the same summarized data in a chart format. Both are connected to the same underlying data source and update automatically when the source data is refreshed. A pivot chart inherits the filters and groupings of the pivot table it is linked to, making it easy to build interactive visual reports with minimal effort.
Power Query is a data transformation and connection tool built into Excel that allows analysts to connect to multiple data sources, clean and reshape data using a visual interface, and load the transformed data into the workbook. It records all transformation steps as a reusable query that can be refreshed with new data automatically. Common Power Query tasks include removing duplicates, splitting columns, changing data types, filtering rows, merging tables from different sources, and unpivoting data from wide to long format. Power Query has largely replaced manual data cleaning in workbooks for analysts who know how to use it.
SUMIF calculates the sum of values in a range that meet a single condition. SUMIFS extends this to support multiple conditions simultaneously, all of which must be true for a row to be included in the sum. SUMIFS is the more commonly used function in analyst work because real-world data almost always requires filtering on more than one dimension. For example, summing sales for a specific region in a specific quarter requires two conditions, which SUMIFS handles directly.
There are two main approaches to removing duplicates in Excel. The Remove Duplicates feature under the Data tab identifies and deletes duplicate rows based on one or more selected columns. The UNIQUE function available in Excel 365 returns a list of unique values from a range without modifying the original data. For data cleaning purposes, analysts often prefer using UNIQUE or Power Query to handle duplicates in a non-destructive way that preserves the original dataset.
The TEXT function converts a number or date into a text string formatted according to a specified format code. It is used when you need to combine a number or date with text in a formula, display a value in a specific format like currency or percentage within a text string, or standardize the format of values that have been imported inconsistently. For example, TEXT of a date value with the format code DD-MM-YYYY returns the date as a formatted text string that can be concatenated with other text.
Both FIND and SEARCH locate the position of one text string within another. The key difference is that FIND is case-sensitive while SEARCH is not. SEARCH also supports wildcard characters while FIND does not. In data cleaning work, SEARCH is generally more useful for identifying substrings within messy text data because it is more forgiving of case variations.
Array formulas perform calculations on multiple values simultaneously and return either a single result or an array of results. In older Excel versions, array formulas are entered with Ctrl Shift Enter and display curly braces around the formula. In Excel 365, dynamic array functions like FILTER, SORT, UNIQUE, SEQUENCE, and XLOOKUP return arrays automatically without special entry. Array formulas are used for complex calculations that cannot be performed with standard single-value functions, such as summing values that meet multiple criteria without a helper column.
OFFSET returns a reference to a range that is a specified number of rows and columns away from a starting cell. It is used to create dynamic ranges that expand or contract based on the size of the data, to build flexible chart data sources, and to reference cells relative to a changing position. OFFSET is often combined with COUNTA to create dynamic named ranges that automatically include new rows of data as they are added to a dataset.
Protecting a sheet restricts what users can do within a specific worksheet, such as preventing edits to locked cells, hiding formulas, or restricting formatting changes. Protecting a workbook restricts structural changes at the workbook level such as adding, deleting, renaming, or moving sheets. Analysts use protection to prevent accidental changes to report templates, lock formula cells while keeping data entry cells editable, and maintain the integrity of shared workbooks that multiple users access.
Data validation restricts the type or range of values that can be entered into a cell. Common applications include creating dropdown lists from a predefined range of options, allowing only numbers within a specified range, requiring dates within a certain period, and displaying a custom error message when an invalid value is entered. In reporting work, data validation is used to create user-friendly input forms and filter interfaces that control data entry without requiring users to know the underlying data structure.
GETPIVOTDATA retrieves specific data from a pivot table based on the field names and item values you specify. It is automatically generated when you reference a pivot table cell in a formula. While it can be useful for building formulas that reference specific pivot table values dynamically, many analysts prefer to turn off the automatic GETPIVOTDATA behavior and reference pivot table cells directly to avoid formula complexity. Understanding what GETPIVOTDATA does and when it appears is a common intermediate interview question.
While standard conditional formatting rules handle common scenarios like highlighting values above a threshold or duplicates, formula-based conditional formatting allows you to apply formatting based on any custom logic you can express in a formula. For example, you can highlight an entire row based on the value in a specific column, apply formatting based on text contained in another cell, or create rules that compare values across different columns. This flexibility makes formula-based conditional formatting a powerful reporting tool for analysts who need to build visually informative dashboards in Excel.
A standard chart is a standalone graphical object that visualizes data from a range of cells and can be sized, positioned, and formatted independently. A sparkline is a miniature chart that fits inside a single cell and is used to show a trend or pattern inline within a table. Sparklines are useful for dashboard reports where you want to show a trend for each row of data without creating a separate chart for every item. They are commonly used in executive summary tables to show sales trends, performance over time, or variance patterns.
Blank cells and errors are common in real-world datasets and need to be handled explicitly in formulas to prevent incorrect calculations. Common approaches include using IFERROR or IFNA to substitute a default value when a formula returns an error, using IF with ISBLANK to test for empty cells before performing calculations, using COALESCE-style logic with IF and ISBLANK to return the first non-blank value from multiple columns, and using TRIM and CLEAN to remove invisible characters that make cells appear blank but are not technically empty.
A macro is a recorded or written sequence of actions in Excel that can be replayed to automate repetitive tasks. Macros are written in Visual Basic for Applications, which is Microsoft's scripting language for Excel automation. For data analysts, macros are useful for automating recurring data cleaning processes, generating standard reports from refreshed data, applying consistent formatting to imported data, and building interactive buttons that trigger specific actions in a dashboard. While Python has largely replaced Excel macros for complex automation in modern analytics environments, knowledge of basic macro recording and VBA is still assessed in interviews for roles at traditional industries like banking and insurance.
AVERAGE calculates the arithmetic mean of all values in a range. AVERAGEIF calculates the average of values that meet a single condition. AVERAGEIFS calculates the average of values that meet multiple conditions simultaneously. These functions are used in analyst work for calculating conditional metrics such as the average order value for a specific product category, the average transaction size for a particular customer segment, or the average processing time for a specific type of request.
Slicers are visual filter controls that can be connected to pivot tables and pivot charts to allow report users to filter data interactively by clicking buttons rather than using the standard filter dropdowns. They make Excel dashboards more user-friendly and accessible to non-technical stakeholders who need to explore data without understanding pivot table mechanics. Slicers can be formatted to match a report's color scheme and can be connected to multiple pivot tables simultaneously so that a single slicer filters all related visuals on a dashboard at once.
Building a basic Excel dashboard involves several connected steps. First, organize the raw data on a separate sheet and clean it thoroughly. Second, build pivot tables on a separate analysis sheet to summarize the key metrics. Third, create charts from those pivot tables. Fourth, create a dedicated dashboard sheet and paste or link the charts and key metric values to it. Fifth, add slicers connected to the pivot tables to make the dashboard interactive. Sixth, apply consistent formatting, remove gridlines, and clean up the visual layout. Finally, protect the formula and pivot table sheets while leaving the dashboard accessible to report users. This workflow is a standard component of Excel reporting skills assessments in analyst interviews.
Reading about Excel functions is not the same as being able to use them under interview pressure. The most effective preparation involves working through real business datasets that require multiple functions in combination. Download publicly available datasets from Kaggle or government data portals and challenge yourself to answer specific business questions using only Excel. Build pivot table summaries, write SUMIFS formulas, clean messy data with text functions, and create a simple dashboard. This hands-on practice builds the muscle memory and problem-solving instinct that interviews assess.
Several Excel interview questions are designed specifically to catch candidates who have memorized definitions without genuine understanding. Common traps include asking the difference between VLOOKUP and INDEX MATCH to see if you know the practical limitations of VLOOKUP, asking about absolute versus relative references by presenting a broken formula and asking why it is not working, and asking you to explain how to build a SUMIFS formula for a described business scenario rather than just defining what SUMIFS does. Practicing applied questions rather than only definitional ones is essential preparation.
Self-study through YouTube tutorials and blog posts can introduce you to Excel functions but it rarely builds the systematic understanding and hands-on confidence needed to perform well under interview pressure. Structured training programs that use real datasets, include live doubt resolution, and assess your ability to apply functions to business problems produce significantly better interview outcomes than self-directed study for the majority of freshers.
The best interactive online Excel training programs and the best Excel courses in Mumbai include practical assessments, instructor feedback on your work, and mock interview preparation that prepares you specifically for the types of questions covered in this blog and beyond.
JustAcademy's Data Analytics Bootcamp in Mumbai covers Excel as a core module within a comprehensive analytics curriculum that also includes SQL, Python, Power BI, and statistics. It is one of the best data analytics courses in Mumbai for freshers who want to build complete interview readiness across every tool that analysts are assessed on. For learners anywhere in India or globally, the Data Analytics Bootcamp Online delivers the same live interactive training with real-world project work and placement support from any location.
Building proficiency across the full analyst tool stack makes you a stronger candidate in every interview round. Explore these programs at JustAcademy:
Excel interview questions for data analysts test far more than formula memorization. They assess your ability to apply functions to real business problems, clean and structure messy data efficiently, build readable and insightful reports, and explain your approach clearly under pressure. The thirty questions covered in this blog span the full range of what interviewers assess, from basic cell references and COUNT functions to advanced pivot table techniques, Power Query, dynamic arrays, and dashboard construction.
Preparation that combines thorough knowledge of these questions with hands-on practice on real datasets will put you in a strong position for any analyst interview that includes an Excel component, which is the majority of them in India in 2026.
The fastest and most reliable way to build that preparation is through a structured training program that covers Excel within the full context of an analytics workflow, includes live instruction and real project work, and prepares you specifically for the interview formats you will face. For learners in Maharashtra, the Data Analytics Bootcamp in Mumbai is one of the best data analytics courses in Mumbai available today, combining Excel, SQL, Python, Power BI, and placement support in one comprehensive program. For learners anywhere in the world, the Data Analytics Bootcamp Online delivers the same live interactive training experience with the flexibility to learn from wherever you are.
Register for a Free Demo to experience the curriculum and speak with an advisor about your interview preparation goals, or Download the Brochure to review full course details, batch schedules, and fees before you decide.
Selenium WebDriver Interview Questions and Answers for Freshers and Experienced Testers in 2026
UI UX Interview Questions and Answers for Freshers and Beginners Covering Design Thinking, Figma, Wireframing, and UX Research in 2026
Figma Interview Questions and Answers for UI UX Designers, Freshers, and Experienced Designers in 2026
SQL, ORM, and Database Interview Questions and Answers for Python Backend Developers and Freshers in
OOP Concepts, Python Classes Interview Questions and Answers for Beginners and Freshers in 2026
REST API, FastAPI, and Django REST Framework Interview Questions and Answers for Python Freshers and Developers in 2026
Flask API, Python Backend Interview Questions and Answers for Beginners and Freshers in 2026
Django Interview, Python Backend Questions and Answers for Freshers and Beginners in 2026
Python DSA, Coding Interview Questions and Answers for Freshers and Beginners in 2026
Version Control, GitHub Interview Questions and Answers for Python Developers and Freshers in 2026
