Excel Reporting Analyst Interview Questions &amp; Answers

Show answer

When you click a pivot cell while writing a formula, Excel inserts GETPIVOTDATA, which pulls a value by field names instead of cell position: =GETPIVOTDATA("Amount",$A$3,"Region","North","Month","Apr") It keeps working when the pivot layout changes, which suits KPI cards in dashboards. If you want plain references, turn it off in PivotTable Analyze > Options > Generate GetPivotData.

How do you show actual versus target in a compact bullet-style chart?

Show answer

Create a clustered column chart with Target and Actual series. Format the Target series with no fill and a thick outline or a light colour, set Series Overlap to 100 percent so both bars sit on top of each other, and make the Actual bar narrower by putting it on the secondary axis with a larger gap width. Match both axis scales. Readers immediately see whether Actual falls short of or crosses the target.

What is the pivot cache and why does it matter?

Show answer

When a pivot is created, Excel stores a snapshot of the source data called the pivot cache, and the pivot reads from it, not directly from the sheet. That is why changes need a refresh. Pivots built from the same Table share one cache, which keeps file size down and lets slicers connect across them. Creating pivots from different copies of the data creates separate caches and makes the file heavier.

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

Pivot Tables and Data SummariesEasy

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.

Your manager says your chart is misleading. What could be wrong?

Show answer

The most common cause is a column or bar chart whose vertical axis does not start at zero, which exaggerates small differences. Other issues are inconsistent scales between side-by-side charts, a secondary axis that is not labelled, 3D effects distorting sizes, missing months making a trend look smooth, or totals mixed with categories. Fix the axis, label units and state the time period in the title.

Write DAX measures for year-to-date sales and sales for the same period last year.

Power Pivot, Data Model and DAXHard

Show answer

With a proper Date table marked as a date table and related to Sales: Sales YTD := TOTALYTD([Total Sales],'Date'[Date],"3/31") Sales LY := CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date])) YoY % := DIVIDE([Total Sales]-[Sales LY],[Sales LY]) The "3/31" year-end argument gives an Indian April to March financial year; without it, YTD resets on 1 January.

Why is averaging a percentage column in a pivot often wrong, and what should you do instead?

Pivot Tables and Data SummariesHard

Show answer

An average of row-level margins gives each order equal weight, so a 100 rupee order counts as much as a 10 lakh order. The correct margin is total profit divided by total sales. Create a calculated field Margin = Profit / Sales, which Excel evaluates on summed values at every level, or a DAX measure =DIVIDE(SUM(Sales[Profit]),SUM(Sales[Amount])). Interviewers use this to test real analytical thinking.

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

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.

What are histogram and Pareto charts used for?

Show answer

A histogram groups numeric values into bins and shows how often each bin occurs, for example how many orders fall between 0 to 1,000 rupees or 1,000 to 5,000 rupees. A Pareto chart sorts categories by frequency and adds a cumulative percentage line, showing the vital few, such as the 20 percent of complaint types causing 80 percent of complaints. Both are available under Insert > Statistic Charts.

Your manager asks you to replace all VLOOKUPs in a legacy workbook with XLOOKUP. How do you approach it?

Show answer

First confirm everyone who opens the file has Microsoft 365 or Excel 2021, because older versions show #NAME? for XLOOKUP. Save a backup, list the VLOOKUPs using Find with Look in: Formulas, and replace them sheet by sheet, keeping an exact-match default. Compare key totals before and after, document the change in a notes sheet, and keep a version that still uses INDEX/MATCH if external clients need it.

What principles do you follow when designing an Excel dashboard?

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.

How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?

Show answer

Select the column, choose Split Column > By Delimiter, enter space-hyphen-space as a custom delimiter and split at each occurrence. This gives ID, Name and City columns. Then use Transform > Format > Trim and Clean to remove extra spaces and hidden characters, Capitalize Each Word for names, and set correct data types. Split by Number of Characters handles fixed-width codes like state and branch prefixes.

How do you stop a pivot from losing formatting and column widths on refresh?

Show answer

Open PivotTable Options > Layout & Format, untick Autofit column widths on update and tick Preserve cell formatting on update. Set number formats through Value Field Settings > Number Format rather than formatting cells directly, because field-level formats survive refresh and layout changes. For a consistent look across reports, create a custom PivotTable Style and apply it to every pivot.

What is XLOOKUP and what advantages does it have?

Lookup and Dynamic Array FunctionsEasy

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.

Get the latest price of each product from a transaction log sorted by date.

Show answer

If the log is sorted oldest to newest, search from the bottom: =XLOOKUP(A2,Log[Product],Log[Price],,0,-1) If it is not sorted, find the latest date first and then match on both: =LET(d,MAXIFS(Log[Date],Log[Product],A2), XLOOKUP(1,(Log[Product]=A2)*(Log[Date]=d),Log[Price])) The second version is order-independent and safer for appended data.

How do you remove duplicate customers but keep the most recent record in Power Query?

Show answer

Sort by Updated Date descending, then buffer the table so the sort order is respected before removing duplicates on the key: Sorted = Table.Sort(Source,{{"UpdatedOn", Order.Descending}}), Buffered = Table.Buffer(Sorted), Latest = Table.Distinct(Buffered,{"CustomerID"}) Without Table.Buffer, Power Query may not keep the sorted order, so the record kept could be an older one.

Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?

Show answer

Load the data into Power Query, select the identifying columns such as Branch and Product, then right-click and choose Unpivot Other Columns. The month headers become an Attribute column and the numbers become a Value column; rename them to Month and Sales. Using Unpivot Other Columns rather than Unpivot Columns means new months added later are included automatically on refresh.

How do you do a two-way lookup that picks both a row and a column?

Show answer

Use two MATCH functions inside INDEX, one for the row and one for the column: =INDEX($B$2:$M$50,MATCH($P$2,$A$2:$A$50,0),MATCH($Q$2,$B$1:$M$1,0)) Here P2 holds the product and Q2 the month. With XLOOKUP you can nest one inside another: =XLOOKUP(P2,A2:A50,XLOOKUP(Q2,B1:M1,B2:M50)) The inner XLOOKUP returns the whole month column, and the outer one picks the product row.

You receive 12 monthly sales files and need one pivot for the year. How do you build it?

Show answer

Put all files in one folder and use Data > Get Data > From File > From Folder > Combine & Transform. Power Query appends them using the first file as a sample, adds the source file name as a column and applies the same cleaning steps. Load the result as a Table or to the Data Model and build the pivot on it. Next year, adding a file to the folder and clicking Refresh All updates everything.

Tell me about a time you automated a repetitive task.

Show answer

Situation: every Monday I merged five regional order files and cleaned them by hand, which took about three hours. Task: reduce the effort and errors. Action: I built a Power Query folder import, standardised columns, fixed date locale issues and loaded the result to a pivot dashboard, then wrote a one-page guide for the team. Result: the job took ten minutes, copy-paste errors disappeared and two other teams adopted the same setup.

A report exported from PDF repeats the header row on every page and has page-footer lines. How do you clean it?

Show answer

Use Get Data > From PDF or import the converted file, then promote the first row to headers. Filter out rows where the first column equals the header text, such as Name, and rows containing footer text like Page or Printed on, using Text Filters > Does Not Contain. Remove blank rows, Fill Down any group labels, and set types. The steps then run automatically on next month's PDF.

What is a combo chart with a secondary axis and when is it useful?

Show answer

A combo chart shows two series with different chart types, for example monthly sales as columns and margin percentage as a line. The percentage would be invisible on the same scale as crores of sales, so it is plotted on a secondary vertical axis on the right. Label both axes clearly, because readers can misread which series belongs to which scale.

Your pivot chart changes whenever someone filters the pivot, but your manager wants a fixed chart for a presentation. What do you do?

Show answer

A pivot chart always follows its pivot, so it cannot be frozen while the pivot is filtered. Options: copy the chart and paste it as a picture into the slide; or copy the pivot values to a normal range and build a regular chart from them; or create a second pivot from the same cache, set its filters once and protect the sheet. For a live report, keep the interactive version on a separate sheet.

Your Power Query output has 20 lakh rows and Excel says it cannot load it to a sheet. What do you do?

Show answer

A worksheet holds at most 1,048,576 rows, so the result will not fit. Choose Close & Load To and select Only Create Connection with Add this data to the Data Model. The model compresses data and can handle many millions of rows, and you analyse it with a pivot table or DAX measures. If users only need summaries, group the data inside Power Query before loading.

What is the difference between Reference and Duplicate in Power Query?

Show answer

Duplicate copies all steps of a query into an independent new query, so changes to the original do not affect it. Reference creates a new query whose source is the output of the original, so it inherits any future changes. Use Reference to build several outputs from one cleaned staging query, such as a monthly summary and a customer list, so the cleaning logic lives in one place.

What does SUMPRODUCT do and when would you use it?

Logical, Text and Date FunctionsMedium

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.

List the steps to build a sales and margin percentage combo chart.

Show answer

Select the Month, Sales and Margin % columns and go to Insert > Recommended Charts > All Charts > Combo. Set Sales to Clustered Column and Margin % to Line with Markers, ticking Secondary Axis for Margin %. Add axis titles, format the secondary axis as 0%, add data labels only to the line if needed, and remove gridline clutter. Give it a title that states the insight, such as Margin fell while sales grew.

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

Logical, Text and Date FunctionsMedium

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.

How do you combine all files in a folder using Power Query?

Show answer

Place files with the same structure in one folder. Go to Data > Get Data > From File > From Folder, select the folder and click Combine & Transform. Choose the sheet or table from the sample file; Power Query creates a helper function and appends all files, adding a Source.Name column with the file name. Apply cleaning steps once, Close & Load, and later just drop new files in and Refresh.

A sales file has employee names but the master list has employee IDs, and some names repeat. How do you join them safely?

Show answer

Do not look up by name alone, because a duplicate name returns the first match and silently assigns sales to the wrong person. First flag duplicates in the master with COUNTIF(names,name)>1. Then build a composite key, such as name plus branch or name plus email, in both tables and look up on that. Rows still ambiguous should go back to the data owner for an ID rather than be guessed.

You need to reconcile a bank statement with the ledger and list entries missing from either side. How?

Show answer

Create a common key, for example date plus amount plus cheque or UTR number. On each sheet add =IF(COUNTIFS(Other[Key],[@Key])=0,"Missing","Matched") and filter the Missing rows. Duplicated amounts need COUNTIFS on both sides so counts are compared, not just presence. For repeated monthly work, load both into Power Query and use Left Anti and Right Anti merges, which list unmatched rows directly.

What is the difference between Append and Merge in Power Query?

Power Query and Data CleaningEasy

Show answer

Append stacks tables one below another, like combining January, February and March sales that have the same columns; it is similar to SQL UNION. Merge joins two tables side by side on a matching key, like adding product category from a Products table to Sales; it is similar to a SQL JOIN or VLOOKUP. Choosing the right one is the first step in any multi-table task.

Write VBA to loop through all worksheets and apply the same formatting.

Macros and VBA AutomationMedium

Show answer

Use For Each over the Worksheets collection and skip any sheet you do not want to touch: Sub FormatAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then ws.Rows(1).Font.Bold = True ws.UsedRange.Columns.AutoFit End If Next ws End Sub

Explain the match_mode and search_mode arguments of XLOOKUP.

Show answer

match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. search_mode: 1 first to last (default), -1 last to first, 2 binary search ascending, -2 binary search descending. =XLOOKUP("*Infra*",Cust[Name],Cust[ID],,2) wildcard match =XLOOKUP(A2,Log[Item],Log[Price],,0,-1) last occurrence Binary modes are fast but give wrong answers if the data is not sorted.

Why are data types important in Power Query, and what problems does the automatic Changed Type step cause?

Show answer

Data types decide how values are sorted, summed and loaded, so a number stored as text will not total and a date stored as text will not group by month. Power Query adds an automatic Changed Type step that hard-codes every column name, so a renamed or removed column later causes refresh errors. Many analysts delete it and set types once, deliberately, after the structure is final.

Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?

Show answer

The source file's header changed, for example Amount became Amount (Rs), and a step that refers to the old name, often Changed Type or Renamed Columns, breaks. Open the query, click through the steps to find the first error, and update the name or rename the column back early in the query. To prevent repeats, agree a fixed template with the data owner and avoid steps that list every column unnecessarily.

Refreshing a Power Query that combines 200 CSV files from a folder takes ten minutes. How do you speed it up?

Power Query and Data CleaningHard

Show answer

Reduce work as early as possible: remove unneeded columns and filter rows in the sample file transform so every file is smaller. Delete redundant Changed Type steps and set types once. Avoid sorting and merges against large tables before the combine. Set helper queries to connection only. Copy files to a local drive if SharePoint is slow, and archive old years into a single file so only recent files are combined each time.

When does binary search in XLOOKUP or approximate VLOOKUP give wrong results?

Lookup and Dynamic Array FunctionsHard

Show answer

Binary search halves the range repeatedly, assuming the data is sorted. If the lookup column is unsorted, has mixed text and numbers or contains blanks, it can stop at the wrong row and return a plausible but incorrect value with no error. Use it only on data you sort in a controlled step, such as a Power Query output, and test a few values against an exact-match lookup.

A few very large values make every other bar in your chart look tiny. How do you present the data clearly?

Show answer

Options depend on the message. Sort bars and show the top items with the rest grouped as Others; split into two charts, one for the giants and one for the rest; add data labels so small values are still readable; or use a logarithmic axis for technical audiences, clearly labelled. Avoid cutting the axis on bar charts, because bar length then stops representing value and misleads readers.

Your team wants lookups to show Not Found instead of #N/A for missing codes, without hiding genuine formula errors. How do you do it?