Practical Advanced Excel Interview Questions

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.

Create a list of cities under each state name and give each list a named range matching the state, for example Maharashtra or Karnataka (no spaces). In B2 make a State list. In C2 use Data Validation > List with source: =INDIRECT(B2) In Microsoft 365 you can avoid named ranges with a spill formula in a helper cell: =FILTER(Cities[City],Cities[State]=B2) and point the validation at that spill range using =$H$2#.

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.

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.

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.

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.

DATEDIF is an older hidden function that still works but does not appear in autocomplete: =DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months" Units: Y full years, M full months, D days, YM months after full years, MD days after full months. For a decimal tenure use =YEARFRAC(B2,TODAY()). The start date must be earlier than the end date or DATEDIF returns #NUM!.

If the month is April or later, the FY starts in the same year; otherwise it started the previous year: =LET(y,IF(MONTH(A2)>=4,YEAR(A2),YEAR(A2)-1), "FY "&y&"-"&RIGHT(y+1,2)) For 15-May-2026 this gives FY 2026-27, and for 10-Feb-2026 it gives FY 2025-26. The fiscal quarter is =ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0).

In Microsoft 365: =COUNTA(UNIQUE(A2:A500)) =ROWS(UNIQUE(FILTER(A2:A500,A2:A500<>""))) ignores blanks In older versions: =SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&"")) The older formula slows down on large ranges, so for big data use a pivot table with Distinct Count via the Data Model.

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.

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.

Get unique reps, total their sales, sort descending and take the top five: =LET(r,UNIQUE(Sales[Rep]), t,SUMIFS(Sales[Amount],Sales[Rep],r), TAKE(SORTBY(HSTACK(r,t),t,-1),5)) HSTACK puts names and totals side by side, SORTBY orders by totals, and TAKE keeps five rows. Without TAKE, use INDEX(...,SEQUENCE(5),{1,2}).

In Microsoft 365, FILTER returns every match as a spilled list, and TEXTJOIN can join them into one cell: =FILTER(Orders[OrderID],Orders[Customer]=A2) =TEXTJOIN(", ",TRUE,FILTER(Orders[OrderID],Orders[Customer]=A2,"")) In older versions, use TEXTJOIN with IF as an array formula: =TEXTJOIN(", ",TRUE,IF(Orders[Customer]=A2,Orders[OrderID],""))

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.

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.

Create named ranges with INDEX so they move with the data, assuming dates in column A from row 2: LastRow: =COUNTA(Data!$A:$A) ChartDates: =INDEX(Data!$A:$A,LastRow-11):INDEX(Data!$A:$A,LastRow) ChartSales: =INDEX(Data!$B:$B,LastRow-11):INDEX(Data!$B:$B,LastRow) Then edit the chart series to =Book1.xlsx!ChartSales and the axis to ChartDates. A Table-based chart grows automatically but shows all months, not a rolling window.

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.

Add two helper columns next to the data that show a value only at the max or min and #N/A elsewhere, because charts skip #N/A: Max: =IF(B2=MAX($B$2:$B$13),B2,NA()) Min: =IF(B2=MIN($B$2:$B$13),B2,NA()) Add both as series, set them to markers only with distinct colours and data labels. When the data changes, the highlighted points move automatically.

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.

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.

Write a query that takes a parameter, then call it for each row or file: (filePath as text) as table => let Src = Excel.Workbook(File.Contents(filePath)), Data = Src{[Item="Sales",Kind="Table"]}[Data] in Data Save it as fnGetSales, then Add Column > Invoke Custom Function on a table of file paths and expand the result. This gives more control than the automatic Combine Files helper.

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.

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.

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.

Remove the product filter in the denominator so it always returns the overall total: % of All Products := DIVIDE([Total Sales], CALCULATE([Total Sales],ALL(Products))) Use ALLSELECTED(Products) instead of ALL if the percentage should respect slicer selections, so the visible products add up to 100 percent. Other filters such as region or month still apply in both versions.

Use DATEADD on the Date table to shift the period one month back: Sales PM := CALCULATE([Total Sales],DATEADD('Date'[Date],-1,MONTH)) MoM % := DIVIDE([Total Sales]-[Sales PM],[Sales PM]) Place Month from the Date table in the pivot rows. For the first month in the data, Sales PM is blank, so DIVIDE returns blank rather than an error.

Keep all dates up to the last date in the current filter: Running Sales := CALCULATE([Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date]<=MAX('Date'[Date]))) Place Month or Date from the Date table in the pivot rows. To restart every financial year, use TOTALYTD with the "3/31" year-end instead.

The most reliable common method starts from the bottom of the sheet and moves up: Dim lastRow As Long With ThisWorkbook.Worksheets("Data") lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With Using Long, not Integer, avoids overflow beyond 32,767 rows. If the data is an Excel Table, ListObjects("Sales").ListRows.Count is even cleaner.

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

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.

Use On Error GoTo to jump to a handler, report the problem and clean up: Sub ImportData() On Error GoTo Fail Application.ScreenUpdating = False ' ... main code ... Done: Application.ScreenUpdating = True Exit Sub Fail: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Done End Sub Avoid blanket On Error Resume Next, which hides real failures.

Loop with Dir, open each file, copy its data below the last row and close it: Sub Consolidate() Dim f As String, wb As Workbook, dest As Worksheet, r As Long Const P As String = "C:\Reports\" Set dest = ThisWorkbook.Worksheets("All") f = Dir(P & "*.xlsx") Do While f <> "" Set wb = Workbooks.Open(P & f, ReadOnly:=True) r = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row + 1 wb.Worksheets(1).UsedRange.Offset(1).Copy dest.Cells(r, 1) wb.Close False f = Dir Loop End Sub

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.

Use late binding so no library reference is needed, and loop through the rows: Sub SendMails() Dim ol As Object, m As Object, r As Long Set ol = CreateObject("Outlook.Application") For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row Set m = ol.CreateItem(0) m.To = Cells(r, 2).Value m.Subject = "Payment reminder" m.Body = "Dear " & Cells(r, 1).Value & ", your due amount is Rs " & Cells(r, 3).Value m.Display Next r End Sub Use .Display to review before switching to .Send.

Loop from the bottom up so deleting a row does not skip the next one: Sub DeleteBlankRows() Dim r As Long, ws As Worksheet Set ws = ActiveSheet For r = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row To 2 Step -1 If Trim(ws.Cells(r, "A").Value) = "" Then ws.Rows(r).Delete Next r End Sub For big data, filtering blanks and deleting visible rows in one action is faster.