36 practice questions with answers for Advanced Excel interviews in Lucknow.
Advanced Excel jobs in Lucknow
Lucknow's tech job market is smaller but growing, led by IT-services delivery centres, government-tech projects and start-ups. Many Lucknow candidates target Noida and Gurgaon roles or remote jobs, so interviews follow the NCR pattern.
Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Lucknow.
Solved for LucknowStart 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 do you build a KPI card showing achievement against target with an up or down indicator?
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.
02
What is Power Query and why use it instead of manual cleaning?
Power Query (Data > Get & Transform) is Excel's tool to import, clean and reshape data from files, folders, databases and the web. Every action is recorded as a step, so next month you just click Refresh and the same cleaning runs again on new data. This removes repeated copy-paste work, reduces errors and keeps the original source untouched, which manual cleaning cannot offer.
03
What checks do you run when cleaning a new dataset?
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.
04
How do you get a distinct count, such as unique customers, in a pivot table?
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.
05
Tell me about a time you got a last-minute analysis request. How did you handle it?
Situation: at 5 PM, my manager needed customer-wise outstanding for a client call at 9 AM. Task: deliver an accurate summary quickly. Action: I confirmed the exact question and cut-off date, pulled the ledger, built a pivot by customer and ageing bucket, cross-checked the total with the trial balance and flagged three unusual balances. Result: the report was ready by 7 PM, and my notes helped the manager answer follow-up questions in the call.
06
What are dynamic arrays and what causes a #SPILL! error?
In Microsoft 365, a formula can return many values that spill into nearby cells, as with UNIQUE, FILTER, SORT and SEQUENCE. Other formulas can refer to the whole result with the spill operator, for example =COUNTA(E2#). #SPILL! appears when the spill area is blocked by data, the formula sits inside a Table, the area overlaps merged cells, or the result would run past the sheet edge. Clear the blocking cells to fix it.
07
How do you look up a value using multiple criteria, such as Employee ID and Month?
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.
08
An IF formula says 100 is not equal to 100. Why might that happen?
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.
09
Explain the join kinds available in Power Query Merge.
Left Outer keeps all rows from the first table and matches from the second, the most common choice. Right Outer does the reverse. Full Outer keeps all rows from both. Inner keeps only matching rows. Left Anti keeps rows in the first table with no match, and Right Anti keeps rows in the second with no match. Anti joins are excellent for finding missing invoices or customers during reconciliation.
10
How do you show only the top 10 customers by revenue in a pivot?
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.
11
What is the difference between nested IF and IFS? Show an example.
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.
12
Your manager wants each product's share of sales within its region, not of the grand total. How do you show it?
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.
13
How do you show actual versus target in a compact bullet-style chart?
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.
14
How do you highlight an entire row when the Status column says Overdue?
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.
15
Why are data types important in Power Query, and what problems does the automatic Changed Type step cause?
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.
16
Tell me about a time data from two teams did not match. How did you resolve it?
Situation: finance and sales reported different revenue for the same quarter. Task: find the gap before the review meeting. Action: I pulled both datasets, built a key on invoice number, and used lookups to list invoices present in only one file. Most of the gap came from credit notes that sales did not deduct and invoices booked on the last day. Result: both teams agreed a single definition and I added a reconciliation tab to the monthly pack.
17
How does INDEX/MATCH work and why is it often preferred over VLOOKUP?
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.
18
Explain the match_mode and search_mode arguments of XLOOKUP.
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.
19
What is the difference between RELATED and RELATEDTABLE?
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.
20
You change input values but the totals do not update until you press something. What is happening and how do you fix it?
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.
21
A workbook with the Data Model has grown to 300 MB. How do you reduce its size?
VertiPaq size depends mostly on columns and how many unique values each has. Remove columns not used in reports, especially IDs and free text. Split date-time columns into date and time or drop the time, round decimals to required precision, and replace calculated columns with measures. Use integer keys instead of long text keys, and load only the years required. Filtering at Power Query level avoids loading unneeded rows.
FILTER returns all rows that meet a condition and spills them into neighbouring cells:
=FILTER(Sales,Sales[Region]="South","No data")
=FILTER(Sales,(Sales[Region]=G1)*(Sales[Amount]>100000))
Multiplying conditions means AND; adding them with + means OR. Because the output updates live, FILTER is useful for searchable lists and drill-down sheets without needing pivot tables or macros. It requires Microsoft 365 or Excel 2021.
CALCULATE evaluates an expression under modified filters. It is the most important DAX function:
North Sales := CALCULATE([Total Sales],Region[Region]="North")
Online Sales 2026 := CALCULATE([Total Sales],Sales[Channel]="Online",'Date'[Year]=2026)
Each filter argument replaces any existing filter on that column, while filters on other columns from the pivot still apply. Wrapping a filter in KEEPFILTERS adds to the existing filter instead of replacing it.
24
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?
Use a function that only handles missing values, not every error:
=XLOOKUP(A2, Codes[Code], Codes[Price], "Not Found")
or =IFNA(VLOOKUP(A2, Codes, 2, FALSE), "Not Found").
Avoid IFERROR here, because it also hides #REF! or #VALUE! caused by broken ranges. Add =COUNTIF(D:D, "Not Found") at the top so missing codes are counted and followed up.
25
What are the main elements of an Excel chart and how do you format them?
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.
26
Tell me about a time your automation failed after you handed it over. What did you do?
Situation: a report macro I wrote stopped working for the accounts team. Task: fix it quickly and stop it happening again. Action: I found that a sheet had been renamed and the macro used a fixed sheet name and file path. I fixed it with ThisWorkbook.Path and code names, added an error handler with a clear message, and wrote a short usage note. Result: it ran reliably for the rest of the year and I now test macros on another user's machine before handover.
27
Write a custom number format that shows positives in green, negatives in red brackets and zero as a dash.
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.
28
How do you stop a pivot from losing formatting and column widths on refresh?
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.
29
How do you write a user-defined function (UDF) in VBA? Give an example.
Write a Public Function in a standard module and use it like any worksheet function:
Function GSTAmount(amount As Double, Optional rate As Double = 0.18) As Double
GSTAmount = Round(amount * rate, 2)
End Function
In a cell: =GSTAmount(B2) or =GSTAmount(B2,5%). UDFs cannot change other cells or formats, and they recalculate more slowly than built-in functions, so avoid them in very large ranges.
30
Why does a date show as a number like 46023 when joined with text, and how do you fix it?
Excel stores dates as serial numbers, so joining with & uses the raw number instead of the formatted view. Wrap the date in TEXT with a format code:
="Report date: "&TEXT(A2,"dd-mmm-yyyy")
="Sales: Rs "&TEXT(B2,"#,##0")
TEXT returns text, so use it only for labels and titles, never for values that need further calculation.
31
Why do you want a role that uses Excel heavily, such as MIS or reporting?
I enjoy turning raw numbers into reports that people actually use to decide things. During my studies and projects I built pivot dashboards and lookup-based trackers, and I liked seeing a three-hour manual task become a ten-minute refresh. An MIS role lets me build those skills daily, understand how the business runs, and grow towards analytics tools like Power BI and SQL, which build directly on Excel.
32
What is the difference between a calculated field and a calculated item?
A calculated field creates a new value field from other fields, such as Margin = Profit / Sales, and it works on the summed totals. A calculated item creates a new member inside a field, such as North + East as a combined region. Calculated items cannot be used with the Data Model, can double-count in totals and slow pivots, so many analysts prefer grouping or a source column instead.
33
Build a live leaderboard of the top 5 sales reps by total sales using dynamic array functions.
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}).
34
What is the difference between Protect Sheet, Protect Workbook and Encrypt with Password?
Protect Sheet stops edits to locked cells; you first unlock input cells through Format Cells > Protection so users can type only there. Protect Workbook locks the structure, so sheets cannot be added, deleted or renamed. Encrypt with Password (File > Info) is the only option that blocks opening the file. Sheet and workbook protection are about preventing accidents, not real security, because they are easy to bypass.
35
Walk through creating a pivot showing monthly sales by region.
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.
36
Why do you need a separate Date table in the Data Model?
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.
Few big employers listed in Lucknow?
Many Lucknow candidates also apply to teams in Noida and to remote or hybrid roles. Practise the Noida set as well.
Which companies hire Advanced Excel professionals in Lucknow?
Companies with large offices in Lucknow include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are Advanced Excel interviews in Lucknow different from other cities?
The questions depend more on the company than the city. Lucknow's mix of employers decides the style: Many Lucknow candidates target Noida and Gurgaon roles or remote jobs, so interviews follow the NCR pattern
How do I find Advanced Excel jobs in Lucknow?
Shortlist companies with Lucknow offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Lucknow filter, and practise the questions on this page before each interview.