36 practice questions with answers for Advanced Excel interviews in Jaipur.
Advanced Excel jobs in Jaipur
Jaipur has a growing IT and IT-enabled services market, with delivery centres, SaaS start-ups and back-office analytics teams around Sitapura and Mahindra World City. Many candidates here also apply to remote and hybrid roles with Delhi NCR teams.
Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Jaipur.
Solved for JaipurStart 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
What does SUMPRODUCT do and when would you use it?
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.
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.
03
Your pivot chart changes whenever someone filters the pivot, but your manager wants a fixed chart for a presentation. What do you do?
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.
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.
05
Write basic DAX measures for total sales, order count and average order value.
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.
A waterfall chart shows how a starting value moves to an ending value through a series of positive and negative steps. It is common in finance for profit bridges, such as last year's profit, plus volume growth, minus price discount, minus higher costs, giving this year's profit. Excel 2016 and later include it under Insert > Waterfall; set the first and last bars as totals so they start from zero.
07
VLOOKUP returns #N/A even though you can see the value in the table. What do you check?
Check that the last argument is FALSE, then compare the two values directly with =A2=Sheet2!A5. Common causes are trailing or non-breaking spaces, one side being a number and the other text (like employee codes), and the lookup value sitting outside the first column of the range. Use LEN to spot hidden characters, and fix with TRIM, CLEAN, VALUE or by making both columns the same data type.
08
Budgets are set by category and month, but sales are recorded by product and day. How do you compare them in one pivot?
Do not relate the two fact tables directly. Create shared dimension tables at a grain both can link to: a Category table and a Date table with a Month column. Relate Budget[Category] and Sales via Products to Category, and both date columns to the Date table, using the first day of the month for budgets. Then measures [Total Sales] and [Total Budget] work together when the pivot uses Category and Month.
09
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.
10
You need product category in the Sales table to create a calculated column. How do you do it in the Data Model?
Create a relationship from Sales[ProductID] to Products[ProductID], then add a calculated column in Sales:
Category = RELATED(Products[Category])
RELATED follows the many-to-one relationship and works only in a row context. Often you do not need the column at all, because the pivot can use Products[Category] directly through the relationship, which saves memory.
11
Get the latest price of each product from a transaction log sorted by date.
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.
12
Describe a time you found an error in a report just before it was sent.
Situation: a monthly MIS was due to leadership in an hour. Task: I was doing a final review. Action: I noticed the region totals did not match the grand total, traced it to a SUMIFS range that stopped at row 500 while data had grown to 620 rows, converted the source to a Table and re-checked totals against the raw export. Result: the report went out on time with correct numbers, and I added a reconciliation check cell to the template.
13
Calculate working days between two dates excluding Sundays and a holiday list.
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.
14
How do form controls like dropdowns, check boxes and scroll bars make a dashboard interactive?
Form controls are added from the Developer tab > Insert. Each control writes a value into a linked cell: a combo box writes the position of the selected item, a check box writes TRUE or FALSE, and a scroll bar writes a number within a set range. Formulas such as INDEX, CHOOSE or FILTER read that cell to change the chart data, so the dashboard reacts without any macros.
15
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.
16
How do you automatically highlight the highest and lowest points in a line chart?
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.
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
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.
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.
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.
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.
22
Tell me about a time you had to explain a complex Excel report to a non-technical manager.
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.
23
Calculate an employee's age or tenure in years and months from the joining date.
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!.
24
What causes the Formula.Firewall error in Power Query and how do you resolve it?
The error appears when a query combines data from sources with different privacy levels, for example using a value from an Excel sheet to filter a web or database query, because Power Query prevents data from one source leaking to another. Fix it by setting correct privacy levels in Data Source Settings, restructuring so the combination happens in separate staging queries, or, for trusted personal files, choosing to ignore privacy levels.
25
What are parameters in Power Query and when do you use them?
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.
26
How do you combine all files in a folder using Power Query?
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.
27
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.
28
Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?
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.
29
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.
30
Write VBA to loop through all worksheets and apply the same formatting.
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
31
Ten regional heads each need the same dashboard but must see only their own region. How do you deliver it?
Since Excel files cannot restrict data inside a workbook securely, create separate files. Add a Region parameter cell that drives Power Query filters or pivot filters, then loop through regions with a short VBA macro that sets the region, refreshes, removes other data and saves a copy or PDF per region. For regular use at scale, Power BI with row-level security is the better solution.
32
A sales file has employee names but the master list has employee IDs, and some names repeat. How do you join them safely?
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.
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
You need to reconcile a bank statement with the ledger and list entries missing from either side. How?
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.
35
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.
36
What is the difference between a workbook, a worksheet and a range?
A workbook is the whole file (.xlsx, .xlsm or .xlsb). A worksheet is one tab inside it with 1,048,576 rows and 16,384 columns. A range is any group of cells such as A2:D100 that a formula, chart or format refers to. Good practice is to keep raw data, calculations and output on separate worksheets of the same workbook so each layer is easy to audit.
Few big employers listed in Jaipur?
Many Jaipur candidates also apply to teams in Delhi and to remote or hybrid roles. Practise the Delhi set as well.
Which companies hire Advanced Excel professionals in Jaipur?
Companies with large offices in Jaipur include many IT services and product firms. Use their company pages below to practise employer-specific sets.
Are Advanced Excel interviews in Jaipur different from other cities?
The questions depend more on the company than the city. Jaipur's mix of employers decides the style: Many candidates here also apply to remote and hybrid roles with Delhi NCR teams
How do I find Advanced Excel jobs in Jaipur?
Shortlist companies with Jaipur offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Jaipur filter, and practise the questions on this page before each interview.