Preparing for a data analyst interview often means brushing up on Excel skills. Excel is one of the most trusted tools for sorting, analyzing, and presenting data, and many recruiters test how well you know it. Approximately 41% of data analyst job postings explicitly list Excel as a required skill. Employers want to see how you think through a problem, not just if you can write a formula. They may ask about pivot tables, VLOOKUP, or even how you clean a messy sheet. This list of 25+ Excel interview questions for data analyst roles will help you get ready.
Fun Fact: Data analysts are in high demand. On the Hirist job portal, you can find thousands of openings for data analyst roles from top IT companies in India.
Note: In this blog on Excel interview questions for data analyst, we have categorized the questions into basic, intermediate, advanced, and MCQs for easy practice.
The Use of Excel in Data Analysis
Excel is widely used because it helps analysts clean messy data, perform calculations, build reports, and create visuals quickly, making it essential for day-to-day analysis.
Data Analyst Interview Process Explained
The data analyst interview process usually includes 3–4 focused rounds that test technical skills, problem-solving, and communication.
Basic Excel Interview Questions for Data Analyst
Here are Excel interview questions and answers for data analyst roles that test your fundamental Excel skills.
1. What is a spreadsheet and what are its main parts?
A spreadsheet is a grid of rows and columns used to store, organize, and analyze data. The main parts are –
● Cells
● Rows
● Columns
● Worksheets
● Workbooks
Cells are the intersection of rows and columns, and they hold the actual values, formulas, or text.
2. What is a cell address?
A cell address identifies the location of a cell in a worksheet. It combines the column letter and the row number, such as A1 or C10.
Analysts often use cell addresses in formulas to reference specific data points.
3. What is the Ribbon in Excel?
The Ribbon is the menu bar at the top of Excel that groups commands into tabs. Tabs like Home, Insert, Data, and Formulas contain tools for formatting, analysis, and visualization.
4. What is the difference between relative and absolute cell references, and when would you use each in data analysis?
| Aspect | Relative Reference | Absolute Reference |
|---|---|---|
| Definition | Adjusts automatically when copied to another cell. | Remains fixed no matter where the formula is copied. |
| Notation | Example: A1 | Example: $A$1 |
| When to Use | When applying the same calculation across multiple rows or columns. | When you always need to refer to one constant cell, like a tax rate or fixed value. |
| Behavior in Copy/Paste | Changes based on new row/column position. | Does not change even if the formula is moved or copied. |
I use relative references for repeating calculations and absolute references when I always need one fixed cell, such as a tax rate.
5. What is the difference between a formula and a function?
A formula is any expression you write to perform a calculation, like =A1+B1. A function is a predefined formula built into Excel, such as SUM or AVERAGE, which saves time and reduces errors.
6. How do you sort a dataset in Excel by multiple columns?
Select the dataset, go to the Data tab, and click Sort. Add sorting levels by choosing columns one by one. For example, sort by Region first and then by Sales within each region.
7. What are the most common data types in Excel that a data analyst works with?
Numbers, text, dates, percentages, currency, and Boolean (TRUE/FALSE). Each data type affects how Excel reads and processes information, especially in formulas or pivot tables.
8. How do you freeze panes?
Click on the row or column where you want the freeze to start. Then go to View > Freeze Panes. This keeps headers visible while scrolling through large datasets.
9. How do you wrap text within a cell?
Select the cell, go to Home > Wrap Text. The content will break into multiple lines inside the same cell.
10. What are COUNT, COUNTA, COUNTBLANK, and COUNTIF functions?
COUNT counts numeric values.
COUNTA counts all non-empty cells.
COUNTBLANK counts only empty cells.
COUNTIF counts cells meeting a specific condition, such as values greater than 1000.
Note: Excel interview questions for data analyst fresher are often basic and focus on core concepts like formulas, data cleaning, formatting, and simple charts.
Also Read - Data Analyst Internship: Apply, Eligibility, Skills & Stipend
Intermediate Data Analyst Excel Interview Questions
Here are Excel interview questions data analyst candidates face at the intermediate level.
11. How do you create and interpret a Pivot Table?
To create a Pivot Table, select your dataset, go to Insert > PivotTable, and place fields into rows, columns, values, or filters.
Pivot Tables let you summarize large data quickly, spot patterns, and compare categories. For example, you can view sales by region or product without building multiple formulas.
12. What is the difference between COUNTIF and COUNTIFS?
COUNTIF applies one condition, like counting sales greater than 1000 in a single column.
COUNTIFS applies multiple conditions, such as counting sales greater than 1000 in January for one region. COUNTIFS is often more practical for real-world analysis where multiple rules apply.
13. How do you use INDEX and MATCH together?
INDEX returns the value of a cell based on row and column numbers. MATCH finds the position of a value in a row or column. Combined, they create flexible lookups.
For example, =INDEX(C2:C10, MATCH(“East”, A2:A10, 0)) returns sales from the East region. This method is more powerful than VLOOKUP because it works in any direction.
14. What is a dynamic named range and why use one?
A dynamic named range automatically expands or shrinks as data changes. You can create it using the OFFSET or newer Excel Table feature. It’s useful when building dashboards or PivotTables, because you don’t have to reset ranges each time new rows are added.
15. How would you build a drop-down list in Excel for data entry in a dashboard?
Select the input cell, go to Data > Data Validation > List, and enter the allowed values or select a source range. This restricts inputs and makes dashboards interactive.
16. How do you clear formatting without removing content?
Select the range, go to Home > Clear > Clear Formats. The data stays intact, but all colors, fonts, and styles are removed. The shortcut Alt + H + E + F does the same thing.
17. How do you use SUMIF?
SUMIF adds values that meet one condition. For example, =SUMIF(B2:B20, “East”, C2:C20) sums sales in the East region. It is widely used for conditional reporting.
18. What is the difference between VLOOKUP, HLOOKUP, and XLOOKUP? Which is most useful for analysts today?
| Aspect | VLOOKUP | HLOOKUP | XLOOKUP |
|---|---|---|---|
| Direction | Searches vertically (top to bottom). | Searches horizontally (left to right). | Searches both vertically and horizontally. |
| Limitations | Can’t look left, breaks if columns move. | Limited to rows, less commonly used. | No direction limit, more flexible. |
| Syntax | =VLOOKUP(lookup_value, table, col_index, [range_lookup]) | =HLOOKUP(lookup_value, table, row_index, [range_lookup]) | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
| Default Match | Approximate if not specified. | Approximate if not specified. | Exact match by default. |
XLOOKUP is the preferred choice for analysts because it can search in any direction.
Also Read - Top 35+ Data Analyst Interview Questions and Answers
Advanced Excel Interview Questions for Data Analyst
Let’s go through Excel data analysis interview questions that test advanced skills like complex formulas, dashboards, and automation.
19. How do you create a dynamic dashboard in Excel that updates when new data is added?
First, structure your data as an Excel Table so ranges expand automatically. Then build PivotTables or charts from that Table. Add slicers or timelines for interactivity. When new rows are added, refresh the PivotTable or chart and the dashboard updates instantly.
20. What is the default value of the last parameter in VLOOKUP?
The last parameter is the range_lookup argument. If you skip it, Excel uses TRUE by default, which means it looks for an approximate match. In practice, data analysts usually specify FALSE for exact matches to avoid mistakes.
21. How do you extract the first name from a full name?
One method is using =LEFT(A1,FIND(” “,A1)-1) if the first and last names are separated by a space. Another method is Text to Columns under the Data tab. Newer Excel versions also allow using TEXTSPLIT, which is faster.
22. How do you use Goal Seek or Solver for scenario modeling in Excel?
Goal Seek lets you set a target output and change one input cell until that target is reached. Solver is more advanced—it can handle multiple variables and constraints. For example, I have used Solver to find the best mix of marketing spend across channels while staying under budget.
23. How do you use IFERROR when formulas return errors?
Wrap the formula inside IFERROR to return a clear result when errors occur. For example, =IFERROR(VLOOKUP(A2, Table, 3, FALSE), “Not Found”). This keeps dashboards clean and avoids distracting error messages.
24. How do you automate tasks using Macros?
Record a macro to save repetitive steps, or write VBA code if the task is complex. For example, I once created a macro to clean raw sales data, apply formatting, and refresh PivotTables with one click. It saved hours each week.
25. How do you build and interact with a Pivot Table using slicers?
Create the PivotTable, then insert slicers from the Insert tab. Slicers let you filter categories like region or product with a single click. They make dashboards more interactive and user-friendly.
Excel MCQs for Data Analyst
Here are Excel data analyst interview questions in MCQ format to test your speed and accuracy.
1. Which of these creates an absolute cell reference?
a) A1
b) $A1
c) A$1
d) $A$1
Answer: d) $A$1
2. What shortcut applies a filter?
a) Ctrl + F
b) Ctrl + Shift + L
c) Alt + D + F
d) Ctrl + Alt + F
Answer: b) Ctrl + Shift + L
3. Which function joins text from multiple cells?
a) CONCAT / TEXTJOIN
b) JOIN
c) MERGE
d) APPEND
Answer: a) CONCAT / TEXTJOIN
4. Which chart shows values over time?
a) Pie Chart
b) Line Chart
c) Scatter Plot
d) Bar Chart
Answer: b) Line Chart
5. What feature locks headers while scrolling?
a) Split View
b) Freeze Panes
c) Page Layout
d) Data Filter
Answer: b) Freeze Panes
6. How do you open Data Validation options?
a) Data > Tools > Validation
b) Data > Data Tools > Data Validation
c) Insert > Options > Validation
d) Home > Format > Validation
Answer: b) Data > Data Tools > Data Validation
7. What does the $ symbol in a cell reference mean?
a) Marks a formula as active
b) Turns it into an absolute reference
c) Applies currency formatting
d) Locks the worksheet
Answer: b) Turns it into an absolute reference
8. How can you quickly fill down a formula in a selected column?
a) Ctrl + R
b) Ctrl + D
c) Shift + Enter
d) Alt + F4
Answer: b) Ctrl + D
9. Which error does Excel return if a lookup value is not found?
a) #REF!
b) #N/A
c) #VALUE!
d) #NAME?
Answer: b) #N/A
10. Which tool is best for combining and transforming large datasets from multiple sources?
a) Solver
b) Power Query (Get & Transform)
c) Data Validation
d) PivotTable
Answer: b) Power Query (Get & Transform)
Excel Cheat Sheet for Data Analysts
Here is a quick cheat sheet to help you tackle Excel data analysis interview questions.
How to Prepare for Data Analyst Interview?
Preparing for a data analyst interview takes practice and clear understanding of core tools. Here are some tips to help you:
● Review past projects and be ready to explain your process and results
● Practice SQL queries, Excel tasks, and data visualization questions
● Go through common Excel interview questions data analyst candidates face in real assessments
● Build a portfolio with clear dashboards and reports to show your skills
● Stay updated on the latest tools like Power BI and Tableau
● Prepare examples where you solved messy data problems or delivered insights fast
Wrapping
With these 25+ Excel interview questions for data analyst roles, you now have a solid base to practice and improve. Focus on both basics and advanced topics, as interviewers test practical problem-solving more than theory.
Looking for data analyst jobs? Visit Hirist to find the latest data analyst job postings from the top IT companies in India.
FAQs
The difficulty varies with experience: freshers face basic ETL concepts, while seasoned candidates encounter performance‑tuning and real‑world scenario questions.
Yes, SQL is essential for writing source qualifier overrides, lookups, and for optimizing transformation performance.
PowerCenter is an on‑premise ETL tool, whereas IICS (Informatica Intelligent Cloud Services) is a cloud‑based solution offering greater scalability, API integration, and real‑time processing.
Major employers include TCS, Infosys, Accenture, Cognizant, Deloitte, Wipro, and Capgemini, among others.
