Advanced Excel Technical Round 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.

Finance wants the sales pivot by quarter of the Indian financial year, starting in April. Date grouping only gives calendar quarters. How do you solve it?

Show answer

Pivot date grouping uses calendar quarters, so add a helper column in the source data. For example, fiscal quarter ="Q"&ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0) and a fiscal year column. Use them as row fields instead of grouped dates. In Power Query or a Data Model, put these columns in a proper Date table so every report uses the same April to March logic.

What are the limitations of the macro recorder, and what is relative recording?

Macros and VBA AutomationEasy

Show answer

The recorder writes code for every click, including unnecessary Select statements and fixed cell addresses, so the macro breaks when data size changes. It cannot record loops, conditions or input boxes. By default it records absolute references; turning on Use Relative References records moves relative to the active cell. The recorder is great for learning syntax, but recorded code should be cleaned up by hand.

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.

Why do you need a separate Date table in the Data Model?

Power Pivot, Data Model and DAXMedium

Show answer

Time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a continuous date column with no gaps, covering full years. A Date table also adds columns like Month Name, Fiscal Year and Quarter for slicing. Create it with Power Query or DAX, for example: Date = CALENDAR(DATE(2024,4,1),DATE(2027,3,31)) Then mark it as the date table in Power Pivot and relate it to every fact table's date column.

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

Charts and DashboardsMedium

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 checks do you run when cleaning a new dataset?

Show answer

Check row counts against the source, then look for duplicate keys, blanks in mandatory fields, inconsistent text such as Delhi and New Delhi, extra spaces and mixed case, dates stored as text or in the wrong locale, negative or extreme numbers that look like typos, and codes losing leading zeros. Power Query's Column Quality and Column Profile views show errors, empties and distinct counts for each column quickly.

Your manager wants a fixed-format P&L with rows in a custom order, but the data is in the Data Model and the pivot keeps reshaping itself. What do you do?

Power Pivot, Data Model and DAXHard

Show answer

Use CUBE functions instead of a pivot. Select the pivot and use PivotTable Analyze > OLAP Tools > Convert to Formulas, or write them directly, for example CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]", $A5, B$3). Each cell becomes an independent formula, so you can reorder rows, insert subtotal lines and apply fixed formatting, while numbers still refresh from the model.

How do you highlight an entire row when the Status column says Overdue?

Show answer

Select the full data range, for example A2:H500, then Home > Conditional Formatting > New Rule > Use a formula. Enter: =$F2="Overdue" Choose a fill colour and click OK. The dollar before F locks the column, while the relative row 2 lets the rule move down row by row, so every column in that row gets coloured when column F matches.

An IF formula says 100 is not equal to 100. Why might that happen?

Logical, Text and Date FunctionsMedium

Show answer

Usually one value is text and the other is a number, so =A2=B2 returns FALSE even though both show 100. Other causes are hidden spaces, or floating-point results like 99.9999999 that display as 100 because of formatting. Check with ISTEXT, LEN and by widening decimals. Fix with VALUE or TRIM on the text side, or compare with ROUND(A2,2)=ROUND(B2,2) for calculated numbers.

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.

How is the Scripting.Dictionary object used in VBA?

Macros and VBA AutomationHard

Show answer

A Dictionary stores key-value pairs with fast lookups, ideal for unique lists and grouping: Dim d As Object, i As Long, v As Variant Set d = CreateObject("Scripting.Dictionary") v = Range("A2:B10001").Value For i = 1 To UBound(v) d(v(i, 1)) = d(v(i, 1)) + v(i, 2) Next i Range("D2").Resize(d.Count, 1).Value = Application.Transpose(d.Keys) This totals amounts per customer far faster than repeated SUMIF calls.

A monthly process takes four hours of copy-paste from 30 branch files. How would you automate it?

Power Query and Data CleaningHard

Show answer

First agree a fixed template with the branches, ideally a named Table. Save all files to one SharePoint or shared folder, then build a From Folder query that combines them, adds the branch name from the file name, standardises types and filters bad rows. Load to the Data Model, build the pivots and dashboard on it, and add a check sheet comparing row counts per branch. Monthly work becomes refresh and review.

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 arrays faster than looping through cells in VBA?

Macros and VBA AutomationHard

Show answer

Each read or write to a cell crosses between VBA and Excel, which is slow when repeated thousands of times. Reading a whole range into a Variant array, processing it in memory and writing it back once is many times faster: Dim v As Variant, i As Long v = Range("A2:C50001").Value For i = 1 To UBound(v, 1) v(i, 3) = v(i, 1) * v(i, 2) Next i Range("A2:C50001").Value = v

How does INDEX/MATCH work and why is it often preferred over VLOOKUP?

Show answer

MATCH finds the position of a value in a single row or column, and INDEX returns the item at that position from another range: =INDEX(Emp!$C:$C,MATCH(A2,Emp!$F:$F,0)) The return column can be to the left of the lookup column, inserting columns does not break it, and it only scans the two columns used. It works in every Excel version, which matters when files go to clients on older Excel.

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.

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.

What is the difference between a calculated column and a measure in DAX?

Power Pivot, Data Model and DAXMedium

Show answer

A calculated column is computed row by row when data is refreshed and stored in the table, like Sales[Margin] = Sales[Amount] - Sales[Cost]. It uses memory and can be used in rows, columns and slicers. A measure is computed on the fly for each cell of a pivot based on its filters, like Total Margin = SUM(Sales[Margin]). Use measures for aggregations and calculated columns only for attributes you slice by.

Write a custom number format that shows positives in green, negatives in red brackets and zero as a dash.

Show answer

Custom formats have up to four sections separated by semicolons: positive; negative; zero; text. Press Ctrl+1 > Number > Custom and enter: [Green]#,##0;[Red](#,##0);"-";@ For Indian lakh-crore grouping you can use: [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0 The value stays unchanged; only the display changes.

Compare CONCAT, TEXTJOIN and the & operator.

Logical, Text and Date FunctionsEasy

Show answer

The & operator joins items one by one, CONCAT joins a list or range, and TEXTJOIN adds a delimiter and can skip blanks: =A2&" "&B2 =CONCAT(A2:C2) =TEXTJOIN(", ",TRUE,A2:E2) TEXTJOIN is the best choice for building address lines or a comma-separated list of selected items, because the TRUE argument avoids double commas where cells are empty.

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.

What are VBA events? Give an example of Worksheet_Change.

Macros and VBA AutomationMedium

Show answer

Events run code automatically when something happens, such as opening a workbook (Workbook_Open) or editing a cell (Worksheet_Change). In the sheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F:F")) Is Nothing Then Application.EnableEvents = False Target.Offset(0, 1).Value = Now Application.EnableEvents = True End If End Sub This timestamps column G when status in F changes.

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

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.

What is the difference between RELATED and RELATEDTABLE?

Power Pivot, Data Model and DAXMedium

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.

A column shows amounts rounded to whole rupees, but its total differs from a manual total of the displayed values by a few rupees. Why?

Show answer

Number formatting only changes how a value looks; Excel still adds the full decimals stored in each cell. So the sum of hidden paise differs from the sum of the rounded figures shown. If the report must add up exactly as displayed, round the values with =ROUND(value,0) in the source formulas rather than using the Precision as displayed option, which permanently changes stored data for the whole workbook.

What are histogram and Pareto charts used for?

Charts and DashboardsMedium

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.

What are the common mistakes with pie charts?

Charts and DashboardsEasy

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.

How do you get a distinct count, such as unique customers, in a pivot table?

Show answer

A normal pivot only offers Count, which counts every row. When inserting the pivot, tick Add this data to the Data Model. Then in Value Field Settings choose Distinct Count, which counts unique customer IDs per region or month. Alternatively, create a DAX measure =DISTINCTCOUNT(Sales[CustomerID]) in Power Pivot, which can be reused across pivots.

What principles do you follow when designing an Excel dashboard?

Charts and DashboardsMedium

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.

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.

What are parameters in Power Query and when do you use them?

Show answer

A parameter is a named value, created in Manage Parameters, that queries can reference instead of a hard-coded value. A common use is a FolderPath parameter so the same workbook works when moved to another computer or shared drive. Parameters can also hold a cut-off date or a region filter. Changing one value updates every query that uses it, which makes files easier to hand over.

How do you use approximate match to apply discount or tax slabs?

Show answer

Create a slab table with the lower limit of each band in ascending order and the rate next to it. Approximate match returns the row for the largest limit not greater than the value: =VLOOKUP(B2,Slabs!$A$2:$B$6,2,TRUE) =XLOOKUP(B2,Slabs[From],Slabs[Rate],,-1) If B2 is 7.5 lakh and the bands start at 0, 3, 7 and 10 lakh, the 7 lakh row is returned. Unsorted data gives wrong results silently.

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.

Write a Power Query custom column that classifies orders into High, Medium and Low by amount.

Power Query and Data CleaningHard

Show answer

Add Column > Custom Column and enter an M if expression; M is case-sensitive and uses lowercase if, then, else: if [Amount] >= 100000 then "High" else if [Amount] >= 25000 then "Medium" else "Low" Then set the new column type to Text. The Conditional Column dialog can build the same logic without typing, but writing M is faster for nested or combined conditions using and/or.

Calculate working days between two dates excluding Sundays and a holiday list.

Logical, Text and Date FunctionsMedium

Show answer

NETWORKDAYS excludes Saturday and Sunday by default. For a six-day week common in India, use NETWORKDAYS.INTL with weekend code 11 (Sunday only): =NETWORKDAYS.INTL(A2,B2,11,Holidays[Date]) To find a due date 10 working days after A2: =WORKDAY.INTL(A2,10,11,Holidays[Date]) Keep the holiday list in a Table so it can be updated each year.

Write VBA to save each worksheet as a separate PDF.

Macros and VBA AutomationMedium

Show answer

Use ExportAsFixedFormat inside a loop: Sub SheetsToPDF() Dim ws As Worksheet, p As String p = ThisWorkbook.Path & "\" For Each ws In ThisWorkbook.Worksheets ws.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=p & ws.Name & ".pdf", _ Quality:=xlQualityStandard, IgnorePrintAreas:=False Next ws End Sub Set print areas and page setup first so each PDF fits the page properly.

How do you look up a value using multiple criteria, such as Employee ID and Month?

Lookup and Dynamic Array FunctionsHard

Show answer

In Microsoft 365, multiply logical tests so only the row matching all conditions becomes 1: =XLOOKUP(1,(Pay[EmpID]=A2)*(Pay[Month]=B2),Pay[Salary],"Not found") In older Excel use INDEX/MATCH entered as an array formula (Ctrl+Shift+Enter): =INDEX(Pay[Salary],MATCH(1,(Pay[EmpID]=A2)*(Pay[Month]=B2),0)) For very large data a helper column joining ID and Month is faster.

What is the difference between IFERROR and IFNA?

Logical, Text and Date FunctionsEasy

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.

How do you build a dependent dropdown, where the City list changes based on the selected State?