Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Data Analyst (Excel focused) Interview Questions & Answers

40 questions for Data Analyst (Excel focused) interviews — 30 easy, 9 medium, 1 hard.

About the Data Analyst (Excel focused) role

A Data Analyst who works mainly in Excel answers business questions using formulas, pivots, the Data Model and charts. They clean messy data, calculate KPIs such as growth, margin and conversion, and present findings in clear dashboards. Analytics firms and in-house analytics teams in India hire for this role, usually expecting Excel plus basic SQL and a visualisation tool.

Advanced formulas DAX measures Data visualisation KPI analysis Business storytelling
Solved for this roleStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

How is the Excel Data Model related to Power BI?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Both use the same VertiPaq engine and the DAX language, and both use Power Query for data preparation. A Power Pivot model can be imported into Power BI Desktop through File > Import, carrying queries, relationships and measures. This makes Advanced Excel with Power Pivot a natural stepping stone, since most of the model and DAX skills transfer directly to Power BI.
02

Write basic DAX measures for total sales, order count and average order value.

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
In Power Pivot > Measures > New Measure, or in the pivot field list: Total Sales := SUM(Sales[Amount]) Orders := DISTINCTCOUNT(Sales[OrderID]) Avg Order Value := DIVIDE([Total Sales],[Orders]) Referencing measures inside other measures keeps logic in one place. Set the number format on the measure itself, so every pivot that uses it shows the same format.
03

Explain TRIM, CLEAN, PROPER, UPPER, LOWER and SUBSTITUTE.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
TRIM removes leading, trailing and repeated spaces between words. CLEAN removes non-printable characters such as line breaks from system exports. PROPER, UPPER and LOWER change case, for example PROPER turns RAHUL SHARMA into Rahul Sharma. SUBSTITUTE replaces specific text, like =SUBSTITUTE(A2,"-","") to strip hyphens from a phone number. They are often nested together when cleaning customer master data.
04

Extract the first and last name from a full name like Rahul Kumar Sharma.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Classic formulas: First: =LEFT(A2,FIND(" ",A2)-1) Last: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) In Microsoft 365 it is simpler: First: =TEXTBEFORE(A2," ") Last: =TEXTAFTER(A2," ",-1) The -1 tells TEXTAFTER to use the last space, which handles middle names. Run TRIM on the source first to remove double spaces.
05

You are given raw sales data and asked to build an interactive dashboard. Walk through your approach.

Charts and DashboardsMedium
Save to ▾
Show answer
Clarify the audience, KPIs and refresh frequency first. Load and clean the data through Power Query into a Table or the Data Model. Build pivots for each view on a calculation sheet, create pivot charts, and connect slicers for region, product and month to all pivots. Add KPI cards using GETPIVOTDATA, format consistently, protect the layout and test that Refresh All updates everything when new data is added.
06

What are the main elements of an Excel chart and how do you format them?

Charts and DashboardsEasy
Save to ▾
Show answer
A chart has a chart title, plot area, horizontal and vertical axes, axis titles, data series, data labels, legend and gridlines. Click the plus button next to a chart to switch elements on or off, and select any element and press Ctrl+1 to open its format pane. Removing unnecessary gridlines and legends, and labelling series directly, usually makes charts cleaner.
07

What is the difference between IFERROR and IFNA?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
IFERROR replaces any error, such as #N/A, #DIV/0!, #VALUE! or #REF!, with a value you choose. IFNA replaces only #N/A, which is the normal result when a lookup finds no match. IFNA is safer around lookups because a genuine problem like a deleted column (#REF!) still shows up instead of being hidden as a blank or zero. Use IFERROR only where every error truly means the same thing.
08

What are the common mistakes with pie charts?

Charts and DashboardsEasy
Save to ▾
Show answer
Pie charts become unreadable with more than five or six slices, because people are poor at comparing angles. They cannot show change over time, and 3D or exploded pies distort sizes. Slices should add up to a meaningful whole; percentages from different bases should never be in one pie. A sorted bar chart almost always communicates the same information more clearly.
09

What is a pivot table and when should you use one?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
A pivot table summarises large tabular data by dragging fields into rows, columns, values and filters, without writing formulas. Use it when you need totals, counts or averages broken down by categories such as region, product and month, and when the breakdown changes often. It works best on clean data with one header row, no blank rows and one record per row, ideally stored in an Excel Table.
10

How do you speed up a slow VBA macro?

Macros and VBA AutomationHard
Save to ▾
Show answer
Switch off screen updates, automatic calculation and events while the macro runs, and always restore them: Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' ... main code ... Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.EnableEvents = True Also avoid Select, process data in arrays instead of cell by cell, and use an error handler so settings are restored on failure.
11

What does SUMPRODUCT do and when would you use it?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
SUMPRODUCT multiplies matching items in arrays and adds the results. It is ideal for weighted averages and for conditional sums in older Excel versions: =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10) weighted average price =SUMPRODUCT((A2:A100="North")*(MONTH(D2:D100)=4)*C2:C100) The second example handles a condition on MONTH, which SUMIFS cannot do directly. Avoid full-column references because SUMPRODUCT evaluates every row.
12

You added new rows of data but the pivot does not show them. Why, and how do you prevent it?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
The pivot's source is probably a fixed range like A1:H500 and the new rows are below it, or the pivot has not been refreshed. Fix it with PivotTable Analyze > Change Data Source, then Refresh. To prevent it, convert the source to an Excel Table so the range expands automatically, use Data > Refresh All, and enable Refresh data when opening the file in PivotTable Options.
13

Walk through creating a pivot showing monthly sales by region.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Click inside the data, press Ctrl+T to make it a Table, then Insert > PivotTable > New Worksheet. Drag Region to Rows, Order Date to Columns and Amount to Values. Right-click a date and choose Group > Months (and Years if data covers more than one year). Set Value Field Settings > Number Format to #,##0 and add a slicer for Product if needed. Refresh after new data arrives.
14

What principles do you follow when designing an Excel dashboard?

Charts and DashboardsMedium
Save to ▾
Show answer
Start from the questions the audience asks. Put three to five headline KPIs at the top, trends and breakdowns below, and filters such as slicers in one consistent place. Use one colour palette with a highlight colour for what matters, avoid 3D and decorative effects, label units clearly like Rs lakh, and fit the view on one screen. Keep raw data and calculations on separate hidden sheets.
15

What is Data Validation and how do you create a dropdown list?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Data Validation restricts what users can type in a cell, which prevents bad input in forms and trackers. For a dropdown, select the cells, go to Data > Data Validation, choose Allow: List and set Source to a range such as =$K$2:$K$10 or a typed list like Open,Closed,On Hold. Pointing the source at a Table column keeps the list updated automatically when new options are added.
16

Dates in an exported column are left-aligned and formulas like DATEDIF return #VALUE!. How do you fix them?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Left alignment usually means the dates are stored as text. Select the column, use Data > Text to Columns, choose Date with the correct order such as DMY and finish; Excel converts them to real dates. For mixed or messy text, DATEVALUE or DATE with LEFT, MID and RIGHT works. If the file comes regularly, set the column type in Power Query using Using Locale so day and month are never swapped.
17

How do you build a KPI card showing achievement against target with an up or down indicator?

Charts and DashboardsMedium
Save to ▾
Show answer
Calculate achievement in a cell, for example =Actual/Target-1, and apply a custom number format that adds arrows and colours: [Color10]▲ 0.0%;[Red]▼ 0.0%;0.0% Place the actual value in a large font above it, link a text box to the cells for flexible placement, and keep labels short like Revenue vs Target. Conditional formatting icon sets are an alternative but are less precise.
18

What is the difference between Summarize Values By and Show Values As?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Summarize Values By chooses the calculation, such as Sum, Count, Average, Max or Min. Show Values As changes how that result is presented, for example % of Grand Total, % of Column Total, % of Parent Row Total, Running Total In, Difference From or Rank Largest to Smallest. Adding the same field twice, once as Sum and once as % of Grand Total, shows the amount and the share side by side.
19

What are Power Pivot and the Excel Data Model?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
The Data Model is an in-memory database inside the workbook, powered by the VertiPaq engine, which compresses data by column and can handle millions of rows, far beyond the sheet limit. Power Pivot is the window used to manage it: import tables, create relationships, and write DAX measures and calculated columns. Pivot tables built on the Data Model can combine several related tables without VLOOKUP.
20

What is the difference between SUMIF and SUMIFS?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
SUMIF handles one condition and puts the sum range last; SUMIFS handles many conditions and puts the sum range first: =SUMIF(A:A,"North",C:C) =SUMIFS(C:C,A:A,"North",B:B,"Laptop",D:D,">="&DATE(2026,4,1)) Because of the different order, many people use SUMIFS everywhere, even for a single condition, to stay consistent. COUNTIFS and AVERAGEIFS follow the same pattern.
21

Explain the common Excel errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME? and #SPILL!.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
#N/A means a lookup found no match. #VALUE! means a wrong data type, such as adding text. #REF! means a referenced cell or column was deleted. #DIV/0! is division by zero or a blank. #NAME? means a misspelt function or undefined name. #SPILL! means a dynamic array formula cannot spill because cells in its output area are not empty. Knowing the cause helps you fix the source instead of hiding errors.
22

You change input values but the totals do not update until you press something. What is happening and how do you fix it?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
The workbook is probably in Manual calculation mode, often inherited from another file opened first in the same session. Press F9 to recalculate now, then set Formulas > Calculation Options > Automatic. If only some cells stay stale, check whether they are formatted as Text or whether Show Formulas is on. For very heavy files, manual mode can be kept deliberately, but tell users to press F9 before reading results.
23

Count orders from the North region with amount above 50,000 placed in January 2026.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Use COUNTIFS with one pair of range and criteria per condition, with dates built using DATE so the formula does not depend on regional settings: =COUNTIFS(Orders[Region],"North", Orders[Amount],">50000", Orders[Date],">="&DATE(2026,1,1), Orders[Date],"<"&DATE(2026,2,1)) Using less than 1 February is safer than less than or equal to 31 January when dates contain time values.
24

What are slicers and timelines, and how do you connect one slicer to several pivots?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Slicers are clickable filter buttons for a field, and timelines are slider filters for dates by day, month, quarter or year. They make filtering obvious for dashboard users. To control several pivots, right-click the slicer and choose Report Connections, then tick each pivot. This works only for pivots built on the same source or data model, because they share the same pivot cache.
25

Why use DIVIDE instead of the / operator in DAX?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
DIVIDE handles division by zero safely and returns blank, or a value you choose, instead of an error: Margin % := DIVIDE([Total Profit],[Total Sales]) Margin % := DIVIDE([Total Profit],[Total Sales],0) Blank results are hidden in pivots, which keeps reports clean when a product has no sales in a month. Use the / operator only when the denominator can never be zero, because it is slightly faster.
26

Tell me about a time you had to explain a complex Excel report to a non-technical manager.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Situation: my manager was unsure why the new incentive sheet showed lower payouts. Task: explain the logic without formulas. Action: I added a one-page summary with the slab table, walked through two real employees step by step, and used colour to show which inputs drove the result. Result: the manager understood it in one meeting, approved the sheet and asked me to add the same summary to future models.
27

How do you choose the right chart type for business data?

Charts and DashboardsEasy
Save to ▾
Show answer
Match the chart to the question. Use column or bar charts to compare categories, line charts for trends over time, pie or doughnut only for a share of a whole with few slices, scatter charts for the relationship between two numbers, histograms for distribution, and combo charts when two measures with different scales belong together, such as sales and margin percentage. The simplest chart that answers the question is usually the best.
28

Tell me about a time you had to clean up a messy spreadsheet someone else built.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Situation: I inherited a sales tracker with merged cells, hard-coded totals and three different date formats. Task: my manager needed reliable weekly numbers. Action: I kept a backup copy, converted the data into an Excel Table, fixed dates with Text to Columns, replaced typed totals with SUMIFS and added data validation dropdowns. Result: weekly reporting time fell from two hours to twenty minutes and the totals stopped being questioned.
29

HR wants a column that marks employees Eligible for bonus if their rating is 4 or more and tenure is over 1 year, or if they appear in a Top Performers list. How do you write it?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Combine AND inside OR, and use COUNTIF to check the list: =IF(OR(AND(C2>=4, D2>1), COUNTIF(TopPerformers, A2)>0), "Eligible", "Not Eligible") Here C is rating, D is tenure in years, A is employee ID and TopPerformers is a named range. Matching on ID instead of name avoids errors from spelling differences.
30

What is the difference between nested IF and IFS? Show an example.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Nested IF places one IF inside another, which becomes hard to read after three levels. IFS (Excel 2019 and later) tests conditions in order and returns the value for the first true one: =IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Fail") The final TRUE acts as the else part. For many bands, a lookup table with XLOOKUP approximate match is easier to maintain than either.
31

What do EOMONTH and EDATE do? Give examples.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
EDATE moves a date by whole months, keeping the day where possible; EOMONTH returns the last day of a month that many months away. =EDATE(A2,3) date three months later, useful for EMI or renewal dates =EOMONTH(A2,0) last day of the same month =EOMONTH(A2,-1)+1 first day of the same month These are standard in billing cycles, month-end closing and ageing reports.
32

What is XLOOKUP and what advantages does it have?

Lookup and Dynamic Array FunctionsEasy
Save to ▾
Show answer
XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode) is available in Microsoft 365 and Excel 2021 or later: =XLOOKUP(A2,Emp[ID],Emp[Name],"Not found") It defaults to exact match, looks left or right, has a built-in not-found value, can return several columns at once, and can search from the bottom up. It replaces VLOOKUP, HLOOKUP and most INDEX/MATCH uses.
33

Explain the four areas of a pivot table.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Rows lists categories down the left side, such as Region. Columns spreads categories across the top, such as Month. Values holds the numbers being summarised, such as Sum of Sales or Count of Orders. Filters applies a filter to the whole pivot, such as Financial Year. Moving a field from Rows to Columns changes the layout instantly, which is why pivots are quick for exploring data.
34

How do you show only the top 10 customers by revenue in a pivot?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Put Customer in Rows and Revenue in Values. Click the Customer filter arrow > Value Filters > Top 10, choose Top, 10, Items, by Sum of Revenue. Then sort the Revenue column largest to smallest. The grand total by default still reflects only the visible rows; if you need overall totals, add a separate pivot or enable Include filtered items in totals when using the Data Model.
35

What is the difference between RELATED and RELATEDTABLE?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
RELATED is used on the many side to fetch a single value from the one side, such as getting a product's category in each sales row. RELATEDTABLE is used on the one side to return all matching rows from the many side, so in Products you could write Order Count = COUNTROWS(RELATEDTABLE(Sales)). Both need an existing relationship and a row context.
36

Describe a time you taught colleagues to use pivot tables.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Situation: three teammates built monthly summaries by filtering and copying data by hand. Task: my lead asked me to help them speed up. Action: I ran two short sessions using our own sales data, showing how to create a pivot, group dates, add slicers and refresh after new rows, and shared a one-page guide. Result: within a month each of them produced their summary in minutes, and fewer copy-paste errors reached the manager.
37

How do wildcards work in COUNTIF and SUMIF?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
An asterisk matches any number of characters, a question mark matches exactly one, and a tilde escapes a real * or ?. =COUNTIF(A:A,"*Pvt Ltd*") names containing Pvt Ltd =SUMIFS(C:C,B:B,"INV-2026-????") invoice codes of a fixed length =COUNTIF(A:A,"*~?*") cells that contain a question mark Wildcards work only on text criteria, not on numbers.
38

A manager wants a 50-row branch report where every row also shows a small trend of the last 12 months. What do you use?

Charts and DashboardsEasy
Save to ▾
Show answer
Use sparklines: select the 12 monthly values for all rows, choose Insert > Sparklines > Line and set the location to a column next to the data. Turn on high and low point markers, and under Axis choose the same minimum and maximum for all sparklines if branches should be compared fairly. They fit inside a cell, print well and update when the data changes.
39

Your manager wants each product's share of sales within its region, not of the grand total. How do you show it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Put Region and then Product in Rows, and add Sales to Values twice. For the second copy, open Value Field Settings > Show Values As > % of Parent Row Total. Each product then shows its share of its own region, and each region totals 100 percent. Using % of Grand Total instead would show share of the whole company, which answers a different question.
40

How do you see the rows behind a pivot value?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Double-click any value cell and Excel creates a new sheet listing every source row that makes up that number. This drill-down is very useful for checking totals or answering a manager's why question. If the file is shared and you do not want people extracting raw data, untick Enable show details in PivotTable Options > Data, though this is not a strong security control.

Frequently asked questions

What does a Data Analyst (Excel focused) do?

A Data Analyst who works mainly in Excel answers business questions using formulas, pivots, the Data Model and charts. They clean messy data, calculate KPIs such as growth, margin and conversion, and present findings in clear dashboards. Analytics firms and in-house analytics teams in India hire for this role, usually expecting Excel plus basic SQL and a visualisation tool.

Which skills are tested in a Data Analyst (Excel focused) interview?

Expect questions on Advanced formulas, DAX measures, Data visualisation, KPI analysis, Business storytelling. Most rounds also ask you to explain a project and solve one practical problem live.

How do I prepare for a Data Analyst (Excel focused) interview?

Revise Logical, Text and Date Functions, Power Pivot, Data Model and DAX, Pivot Tables and Data Summaries, Charts and Dashboards first using the topic sheets, practise the questions on this page aloud, then do one full mock interview to practise timing.

Topic sheets for this role

Logical, Text and Date Functions questionsPower Pivot, Data Model and DAX questionsPivot Tables and Data Summaries questionsCharts and Dashboards questions

Other roles

MIS ExecutiveExcel Reporting AnalystFinancial AnalystExcel VBA DeveloperMIS and Reporting Manager

More ways to practise

Company-wise Advanced Excel questionsExperience-wise Advanced Excel questionsRole-wise Advanced Excel questionsCity-wise Advanced Excel questionsMost asked Advanced Excel questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsAdvanced Excel mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsAdvanced Excel topic sheets