36 practice questions with answers for Advanced Excel interviews in Chandigarh.
Advanced Excel jobs in Chandigarh
Chandigarh, Mohali and Panchkula form the Tricity IT cluster, with IT-services firms, SaaS companies and start-ups in Mohali's IT City and Chandigarh's IT Park. Interviews usually check strong fundamentals and practical tool skills.
Below are 36 questions that match the mix of service, product and domain employers hiring mis executives in Chandigarh.
Solved for ChandigarhStart 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.
02
SUM returns 0 on a column of numbers exported from software. How do you fix it?
The numbers are most likely stored as text, often with leading apostrophes, trailing spaces or non-breaking spaces (CHAR(160)). Check with =ISTEXT(A2) or LEN. Quick fixes are Data > Text to Columns > Finish, the green error triangle > Convert to Number, or Paste Special > Multiply by 1. For stubborn cases use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) or set the column type to Decimal in Power Query.
03
How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?
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.
04
How do you create a chart that automatically shows the last 12 months as new data is added?
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.
05
Refreshing a Power Query that combines 200 CSV files from a folder takes ten minutes. How do you speed it up?
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.
06
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.
07
Describe a time you convinced your team to move from manual cleaning to Power Query.
Situation: our team spent a day each month cleaning vendor files by hand. Task: I believed Power Query could fix it but the team feared a new tool. Action: I rebuilt one month's cleanup as a query, then ran it side by side with the manual process and showed identical results in five minutes. I documented each step and trained two colleagues. Result: the team adopted it, saving about eight hours a month.
08
Your Power Query output has 20 lakh rows and Excel says it cannot load it to a sheet. What do you do?
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.
09
How do you make a chart title change automatically with the selected region or month?
Build the title text in a cell with a formula, for example in B1:
="Sales trend - "&G1&" - FY 2026-27"
where G1 is the selected region. Click the chart title, click in the formula bar, type = and select B1, then press Enter:
=Dashboard!$B$1
The title now updates whenever G1 changes. The same trick works for data labels and text boxes.
10
Your weekly dashboard must go to 40 people who cannot access the source files. How do you distribute it safely?
Decide what they need: a view or a working file. For a view, export the dashboard sheet to PDF or share a view-only link from SharePoint or OneDrive. For a file, save a copy, break external links, paste pivots or formulas as values if needed, remove hidden sheets and personal data with Inspect Document, and protect the sheet. Automate the steps with a macro or Office Script so every week is identical.
11
What is the difference between Summarize Values By and Show Values As?
Summarize Values By chooses the calculation, such as Sum, Count, Average, Max or Min. Show Values As changes how that result is presented, for example % of Grand Total, % of Column Total, % of Parent Row Total, Running Total In, Difference From or Rank Largest to Smallest. Adding the same field twice, once as Sum and once as % of Grand Total, shows the amount and the share side by side.
12
How do you use approximate match to apply discount or tax slabs?
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.
13
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!.
14
Your manager asks you to replace all VLOOKUPs in a legacy workbook with XLOOKUP. How do you approach it?
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.
VLOOKUP can only look to the right of the key column, so it cannot return a value from a column on the left. The column index is a hard-coded number that breaks when columns are inserted. It returns only the first match, defaults to approximate match if the last argument is forgotten, and can be slow on big ranges. XLOOKUP or INDEX/MATCH solve all of these problems.
16
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.
17
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.
18
Tell me about a time you had to clean up a messy spreadsheet someone else built.
Situation: I inherited a sales tracker with merged cells, hard-coded totals and three different date formats. Task: my manager needed reliable weekly numbers. Action: I kept a backup copy, converted the data into an Excel Table, fixed dates with Text to Columns, replaced typed totals with SUMIFS and added data validation dropdowns. Result: weekly reporting time fell from two hours to twenty minutes and the totals stopped being questioned.
19
List the steps to build a sales and margin percentage combo chart.
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.
20
Your manager says your chart is misleading. What could be wrong?
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.
21
How do you build a dependent dropdown, where the City list changes based on the selected State?
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#.
22
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.
23
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.
24
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.
25
Write VBA to save each worksheet as a separate PDF.
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.
26
What is the difference between a calculated column and a measure in DAX?
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.
27
You are given raw sales data and asked to build an interactive dashboard. Walk through your approach.
Clarify the audience, KPIs and refresh frequency first. Load and clean the data through Power Query into a Table or the Data Model. Build pivots for each view on a calculation sheet, create pivot charts, and connect slicers for region, product and month to all pivots. Add KPI cards using GETPIVOTDATA, format consistently, protect the layout and test that Refresh All updates everything when new data is added.
28
Return the Indian financial year (April to March) for a date, like FY 2026-27.
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).
29
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.
30
How do you return all matching values for a lookup, not only the first?
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],""))
31
Explain VLOOKUP syntax and the difference between exact and approximate match.
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) searches the first column of the table and returns a value from the column number given:
=VLOOKUP(A2,Products!$A:$D,3,FALSE)
FALSE (or 0) means exact match and is what you need for IDs and codes. TRUE, the default, means approximate match and needs the first column sorted ascending; it is used for slabs like tax or discount bands.
32
What is the difference between Reference and Duplicate in Power Query?
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.
33
You need to find and remove duplicate customer records but keep the original data safe. How do you do it?
First highlight duplicates with Conditional Formatting > Highlight Cells Rules > Duplicate Values to review them. Then work on a copy of the sheet and use Data > Remove Duplicates, selecting the key columns such as Mobile and Email. In Microsoft 365 a non-destructive option is =UNIQUE(A2:C500) on a new sheet. Always record how many rows were removed so the change can be explained later.
34
How do you group dates and numbers in a pivot, and why does grouping sometimes fail?
Right-click a date in Rows and choose Group to group by days, months, quarters or years; for numbers, group by a range such as 0 to 100000 in steps of 10000 to create sales bands. Grouping fails with the message Cannot group that selection when the column contains blanks, text values or dates stored as text. Clean the source column, refresh, and grouping works.
Trust Center settings control whether macros run: disabled with notification is typical. Files in a trusted location, or signed with a trusted digital certificate, run without prompts. Since 2022, Office blocks macros by default in files downloaded from the internet or email attachments that carry the Mark of the Web; users must unblock the file in its Properties. Organisations usually manage these settings through policy.
36
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.
Which companies hire Advanced Excel professionals in Chandigarh?
Companies with large offices in Chandigarh include Grant Thornton Bharat. Use their company pages below to practise employer-specific sets.
Are Advanced Excel interviews in Chandigarh different from other cities?
The questions depend more on the company than the city. Chandigarh's mix of employers decides the style: Interviews usually check strong fundamentals and practical tool skills
How do I find Advanced Excel jobs in Chandigarh?
Shortlist companies with Chandigarh offices, keep your CV focused on Advanced Excel projects, apply on company career pages and job portals with the Chandigarh filter, and practise the questions on this page before each interview.