Advanced Excel Interview Questions and Answers: 60 Questions from Easy to Hard
This guide is for freshers and experienced professionals in India preparing for the Excel round of MIS executive, accounts, operations, back-office and data analyst interviews, where a practical Excel test is common. It has 60 questions with answers in three levels: the basics every fresher must know (references, VLOOKUP, IF, SUMIFS, text and date functions, pivot tables), real work problems for 1-4 years of experience (lookup errors, dynamic arrays, slicers, Goal Seek), and senior topics such as Power Query, the Data Model, large-file performance and building a monthly MIS report. Sample data and formulas are shown as small tables with their results. Rebuild each example in Excel (Microsoft 365) as you read, then take the mock test.
- 60 questions, 3 levels
- Updated
- CoreHRx Jobs
- 2,030 open Advanced Excel jobs
What Excel interviews cover
- Formulas and references: relative and absolute references, IF and IFS, SUMIFS and COUNTIFS, and text and date functions. Almost every MIS or accounts test starts here.
- Lookups: VLOOKUP and its limits, INDEX with MATCH, and XLOOKUP in Microsoft 365. Expect at least one question on why a lookup returns #N/A.
- Summaries and presentation: pivot tables, slicers, charts, conditional formatting and data validation.
- Cleaning and automation: removing duplicates, fixing numbers and dates stored as text, and Power Query for work that repeats every month.
- For experienced roles: what-if analysis, protecting templates, large-file performance and designing a complete MIS report with checks.
How to prepare (freshers and experienced)
- Freshers: practise on a realistic dataset of 500 to 1,000 rows (sales, attendance or invoices) and build lookups, SUMIFS and a pivot table from it until you can do each without help.
- Learn the shortcuts for moving, selecting, filtering and AutoSum. Practical tests are often timed, and the keyboard is much faster than the mouse.
- Experienced candidates: be ready to walk through a report you built, where the data came from, how you checked it and how long the monthly refresh takes.
- Learn the Microsoft 365 functions (XLOOKUP, FILTER, UNIQUE, LET) and also their older equivalents, because some offices still use Excel 2016 or 2019.
- Practise explaining a formula aloud in plain words. Interviewers often ask why you chose a function, not just what it returns.
Common mistakes and test-day tips
- Leaving out FALSE in VLOOKUP, forgetting the $ signs before copying a formula, and typing numbers into formulas instead of referring to cells.
- Hiding errors with IFERROR instead of finding out why the lookup failed. Check for extra spaces and numbers stored as text first.
- Hard-coding totals or pasting values in the middle of a report, so the file stops updating when the data changes.
- In a live test, read the whole task first, keep the raw data untouched on its own sheet and check your totals against the source before you say you are done.
- If you do not know a feature, say how you would solve the problem another way instead of guessing a menu path.
Advanced Excel interview questions: Easy questions (freshers)
20 questions1. What is the difference between relative, absolute and mixed cell references?
A relative reference such as B2 shifts when you copy the formula, an absolute reference such as $C$2 never moves, and a mixed reference such as $C2 or C$2 locks only the column or only the row. In the sample, =B2*$C$2 copied down becomes =B3*$C$2, so every item uses the one GST rate in C2 and the results are 9360, 2610 and 117. Press F4 while editing a reference to cycle through the four forms. A common mistake is leaving the rate relative: the copied formula then points at the empty C3 and returns 0.
A B C
1 Item Amount GST rate
2 Laptop 52000 18%
3 Printer 14500
4 Mouse 650
D2: =B2*$C$2 -> 9360
D3: =B3*$C$2 -> 2610 (copied down: B moves, $C$2 stays)
D4: =B4*$C$2 -> 1172. How does VLOOKUP work, and why do you usually set its last argument to FALSE?
VLOOKUP searches the first column of a table for a value and returns the value from a column you name by number, in the same row. Its arguments are the lookup value, the table range, the column index and the match type. FALSE (or 0) asks for an exact match, which is what you want for IDs, invoice numbers and names. If you leave it out, Excel assumes TRUE, an approximate match that expects sorted data and can return a wrong row without any error. Here E103 returns Chennai, and an ID that is not in the list returns #N/A.
A B C
1 EmpID Name City
2 E101 Asha Rao Pune
3 E102 Vikram Shah Surat
4 E103 Meena Iyer Chennai
5 E104 Rohit Das Kolkata
F2: E103 F3: E109
G2: =VLOOKUP(F2, $A$2:$C$5, 3, FALSE) -> Chennai
G3: =VLOOKUP(F3, $A$2:$C$5, 3, FALSE) -> #N/A3. How do you combine IF with AND and OR?
IF tests one condition and returns one value when it is TRUE and another when it is FALSE. AND returns TRUE only when every condition inside it is true; OR returns TRUE when at least one is. To give a bonus only to staff with sales of at least 4,00,000 and attendance of at least 90%, put AND inside IF: Kiran qualifies, Pooja misses on attendance and Arjun on sales. With OR(B2>=500000, C2>=95) instead, all three would qualify. Keep thresholds in cells rather than typing them into the formula, so a policy change is one edit.
A B C
1 Name Sales Attendance %
2 Kiran 410000 96
3 Pooja 520000 88
4 Arjun 380000 97
D2: =IF(AND(B2>=400000, C2>=90), "Eligible", "Not eligible")
Kiran -> Eligible, Pooja -> Not eligible, Arjun -> Not eligible
E2: =IF(OR(B2>=500000, C2>=95), "Yes", "No")
Kiran -> Yes, Pooja -> Yes, Arjun -> Yes4. What is the difference between SUMIF and SUMIFS?
SUMIF adds values that meet one condition; SUMIFS handles one or more conditions, all of which must be true. The trap is the argument order. SUMIF takes the range to test, the criterion and then the range to add, while SUMIFS puts the sum range first, followed by pairs of criteria range and criterion. In the sample, North sales total 24000 and North sales of pens total 19000. Many people use SUMIFS even for one condition so they remember only one order. All the ranges in SUMIFS must be the same size, or it returns #VALUE!.
A B C
1 Region Product Amount
2 North Pens 12000
3 South Pens 8000
4 North Files 5000
5 North Pens 7000
6 West Files 9000
=SUMIF(A2:A6, "North", C2:C6) -> 24000
=SUMIFS(C2:C6, A2:A6, "North", B2:B6, "Pens") -> 190005. What do COUNT, COUNTA, COUNTBLANK and COUNTIF count?
COUNT counts cells that hold numbers, COUNTA counts every non-empty cell including text, COUNTBLANK counts empty cells, and COUNTIF counts cells that meet a condition. In the sample, COUNT gives 3 because the text NA is skipped while the zero is counted, COUNTA gives 4, COUNTBLANK gives 1 and COUNTIF with ">1000" gives 2. One subtle point: a formula that returns empty text ("") looks blank, yet COUNTA counts it and so does COUNTBLANK. Use COUNT to see how many amounts are really numeric before you total them.
A
1 Invoice amount
2 4500
3 (empty)
4 NA
5 12000
6 0
=COUNT(A2:A6) -> 3
=COUNTA(A2:A6) -> 4
=COUNTBLANK(A2:A6) -> 1
=COUNTIF(A2:A6, ">1000") -> 26. How do you extract parts of a code with LEFT, MID, RIGHT and FIND?
LEFT takes characters from the start, RIGHT from the end, and MID from any position for a given length; LEN gives the total length. For the employee code PUN-2024-0137, LEFT gives PUN, MID from position 5 for 4 characters gives 2024 and RIGHT gives 0137. These results are text, so wrap them in VALUE when you need a number (0137 becomes 137). When the parts vary in length, find the separator instead of hard-coding positions: LEFT(A2, FIND("-", A2) - 1) returns everything before the first hyphen, whatever its length.
A2: PUN-2024-0137
=LEFT(A2, 3) -> PUN
=MID(A2, 5, 4) -> 2024
=RIGHT(A2, 4) -> 0137
=LEN(A2) -> 13
=VALUE(RIGHT(A2, 4)) -> 137
=LEFT(A2, FIND("-", A2) - 1) -> PUN7. How do you join text, and why does a joined number or date lose its format?
Use the & operator or CONCAT to join pieces, and TEXTJOIN when you want a separator and want to skip blank cells. Formatting belongs to the cell, not to the value, so when you join a number Excel uses the raw value: 45230.5 instead of 45,230.50, and a date becomes its serial number, such as 46127 for 15-Apr-2026. Wrap the value in TEXT with a format code to control how it appears, for example TEXT(C2, "#,##0.00"). Remember that the result is text, so do not use it in later calculations.
A2: Priya B2: Nair C2: 45230.5 D2: 15-Apr-2026
A3: Ravi A4: (empty) A5: Sunita
=A2 & " " & B2 -> Priya Nair
=CONCAT(A2, " ", B2) -> Priya Nair
=TEXTJOIN(", ", TRUE, A2:A5) -> Priya, Ravi, Sunita
="Total: " & C2 -> Total: 45230.5
="Total: " & TEXT(C2, "#,##0.00") -> Total: 45,230.50
="Due on " & D2 -> Due on 46127
="Due on " & TEXT(D2, "dd-mmm-yyyy") -> Due on 15-Apr-20268. How does Excel store dates, and how do you find the days between two dates?
Excel stores a date as a serial number, the count of days in which 1 January 1900 is day 1, and the cell format only decides how it looks. So 1-Apr-2026 is stored as 46113, and subtracting one date from another gives the days between them: 75 days from 1 April to 15 June 2026. Times are fractions of a day, so 6:00 PM is 0.75. If the result shows as a date, change its format to General. DATEDIF(start, end, "m") counts completed months, 2 here, and TODAY() returns the current date and updates whenever the sheet recalculates.
A2: 01-Apr-2026 B2: 15-Jun-2026
A2 formatted as General -> 46113
=B2 - A2 -> 75
=DATEDIF(A2, B2, "m") -> 2
=TODAY() -> current date (recalculates)9. What is an Excel Table, and why is it better than a plain range?
A Table, created with Ctrl+T, turns a range with headers into a named object that grows when you add rows. Formulas and charts that point to the Table include new rows automatically, and a pivot built on it picks them up on refresh, so you stop editing ranges every month. Formulas use structured references such as tblSales[Amount], which read better than C2:C5000, and a formula typed in one row fills the whole column. You also get filter buttons, banded rows, a Total Row and slicers. Keep one header row and no blank rows or merged cells inside.
Table name: tblSales (columns: Date, Branch, Qty, Rate, Amount)
Amount column: =[@Qty] * [@Rate] fills every row
Total: =SUM(tblSales[Amount])
Pune only: =SUMIFS(tblSales[Amount], tblSales[Branch], "Pune")10. How do you remove duplicate rows safely?
Data > Remove Duplicates deletes repeated rows based on the columns you tick and keeps the first occurrence of each. It deletes for good, so work on a copy or check first. A safer habit is to flag duplicates before deleting: COUNTIF with an expanding range, as in the sample, returns TRUE from the second appearance of an ID onwards, so you can filter and review them. Decide which columns define a duplicate, because the same customer ID on two dates may be a genuine repeat order. Run TRIM first: an extra space makes two IDs look different to Excel.
A B
1 Customer ID Repeat?
2 C-1001 FALSE
3 C-1002 FALSE
4 C-1001 TRUE
5 C-1003 FALSE
6 C-1002 TRUE
B2: =COUNTIF($A$2:A2, A2) > 1 (copy down; the range grows)11. How do you clean names and split columns with TRIM, PROPER, Text to Columns and Flash Fill?
TRIM removes spaces at the start and end and reduces runs of spaces inside the text to one, and PROPER capitalises each word, so PROPER(TRIM(A2)) turns the messy entry in the sample into Rahul Verma, cutting it from 16 to 11 characters. Text to Columns on the Data tab splits one column into several by a delimiter, such as a comma or space, or by fixed width. Flash Fill (Ctrl+E) copies a pattern from an example you type. Flash Fill and Text to Columns produce fixed values that do not update when the source changes; formulas do.
A2: " rahul VERMA " (LEN = 16)
=TRIM(A2) -> "rahul VERMA" (LEN = 11)
=PROPER(TRIM(A2)) -> "Rahul Verma"
Text to Columns: Data > Text to Columns > Delimited > Space
Flash Fill: type Rahul in B2, then press Ctrl+E in B312. How do you create a drop-down list with data validation?
Select the cells, go to Data > Data Validation, choose List under Allow and give the source: items typed with commas, such as Pending,Approved,Rejected, or a range like $H$2:$H$4. A range is better because you can change the list without editing the rule. The Input Message tab shows a hint, and on the Error Alert tab the Stop style blocks entries that are not in the list. Other rules restrict whole numbers, decimals, dates or text length, such as a quantity between 1 and 500. Validation checks what users type; it does not fix data already in the cells.
Allow: List
Source: Pending,Approved,Rejected
or =$H$2:$H$4 (list kept on a setup sheet)
Allow: Whole number, between 1 and 500 (quantity)
Allow: Date, between 01-Apr-2026 and 31-Mar-202713. What is conditional formatting, and which built-in rules do you use most?
Conditional formatting changes how a cell looks based on its value, and the format updates when the value changes. Highlight Cells Rules cover greater than, less than, between, text that contains, dates such as the last 7 days, and duplicate values. Top/Bottom Rules pick the top 10 items or values above average. Data bars, colour scales and icon sets show size at a glance. For example, colour stock quantities red when they fall below the reorder level. Point the rule at a cell that holds the threshold instead of typing a number, and use Manage Rules to see what is already applied.
14. What is a pivot table, and how do you build a simple sales summary with one?
A pivot table summarises a list without formulas: you drag fields into Rows, Columns, Values and Filters. Click any cell in the data, choose Insert > PivotTable, then put Region in Rows, Month in Columns and Amount in Values, which shows Sum of Amount. The sample gives North 24000 and South 19000, with a grand total of 43000. You can switch Sum to Count or Average, sort and filter in seconds. Keep the source clean: one header row, no blank columns and numbers stored as numbers. A pivot does not update by itself, so refresh it after the data changes.
Source data:
Region Month Amount
North Apr 12000
South Apr 8000
North May 9000
South May 11000
North Apr 3000
Rows = Region, Columns = Month, Values = Sum of Amount
Region Apr May Grand Total
North 15000 9000 24000
South 8000 11000 19000
Grand Total 23000 20000 4300015. How do you choose the right chart type for a report?
Pick the chart from the question it answers. Use a column chart to compare a few categories, such as sales by branch, and a bar chart when the labels are long or you are ranking many items. Use a line chart for a trend over time, such as daily orders across a month. A pie chart only works for parts of one whole with a handful of slices; with more than five or six, use a bar chart. Use a scatter chart for the relationship between two numbers, such as discount and quantity sold. Avoid 3D effects, and give the chart a title that states the message.
16. How do you sort and filter data without breaking the rows?
Click one cell inside the data and use Data > Sort, so Excel takes the whole block and keeps each row together. Add levels for a multi-key sort, such as Region A to Z and then Amount largest to smallest, and use a custom list to sort months in calendar order instead of alphabetically. Never sort one selected column on its own with Continue with the current selection: that scrambles the data. Ctrl+Shift+L turns filters on and off. To total only the rows a filter leaves visible, use SUBTOTAL(9, range), because SUM still adds the hidden rows.
=SUM(C2:C500) adds every row, including rows a filter hides
=SUBTOTAL(9, C2:C500) adds only rows still visible after filtering
=SUBTOTAL(109, C2:C500) also skips rows hidden by hand17. What do the common Excel error values mean, and how do you handle them?
#N/A means a lookup did not find the value. #DIV/0! is division by zero or by an empty cell. #VALUE! means the wrong type, such as text in arithmetic. #REF! means a referenced cell was deleted. #NAME? means Excel does not recognise a name, usually a misspelt function or text without quotes. #SPILL! means a dynamic array formula is blocked. IFERROR(formula, value) replaces any error, but it can hide real problems, so for lookups prefer IFNA, which catches only #N/A. Fix the cause first and use the wrapper only for expected cases.
B2: 5000 C2: 0
=B2/C2 -> #DIV/0!
=IFERROR(B2/C2, 0) -> 0
=IFNA(VLOOKUP(F2, A2:D50, 4, FALSE), "Not found") -> Not found
(when F2 is not in column A)18. Why does a total sometimes not match the numbers shown, and how does ROUND help?
Number formatting changes only what you see, not the stored value. In the sample each cell holds 1.333 but shows 1.33, so a reader expects 3.99, yet the SUM is 3.999 and shows as 4.00. When displayed figures must add up, round each value with ROUND(value, 2) before adding, and the total becomes 3.99. ROUND rounds halves away from zero (ROUND(2.5, 0) is 3), ROUNDUP and ROUNDDOWN always go one way, and MROUND rounds to a multiple, such as the nearest 5 rupees. Avoid the Precision as displayed option, which permanently changes stored values.
A (format: 2 decimals)
2 1.333 shows 1.33
3 1.333 shows 1.33
4 1.333 shows 1.33
=SUM(A2:A4) -> 3.999, shows 4.00
=ROUND(A2,2)+ROUND(A3,2)+ROUND(A4,2) -> 3.99
=ROUND(2.5, 0) -> 3 =ROUND(-2.5, 0) -> -3
=ROUNDUP(12.01, 0) -> 13 =ROUNDDOWN(12.99, 0) -> 12
=MROUND(127, 5) -> 12519. What can Paste Special do besides pasting values?
Paste Special (Ctrl+Alt+V) chooses what to paste and can calculate while pasting. Values replaces formulas with their results, which is useful before sending a report or to break links to another file. Formats copies only the look, and Transpose turns rows into columns. The Operation options add, subtract, multiply or divide the copied number into the selected cells: to show amounts in lakhs, type 100000 in a spare cell, copy it, select the amounts and choose Divide, so 2500000 becomes 25. Skip blanks avoids overwriting with empty cells. Pasting values removes the formulas for good.
20. Which keyboard shortcuts save the most time in Excel?
In Excel for Windows, Ctrl+arrow jumps to the edge of the data and Ctrl+Shift+arrow selects up to it. Ctrl+T creates a Table, Ctrl+Shift+L turns filters on or off and Alt+= inserts AutoSum. F2 edits the active cell, F4 cycles the $ signs in a reference while editing, Ctrl+1 opens Format Cells, Ctrl+D fills down and Ctrl+; enters today's date. Ctrl+E runs Flash Fill, Ctrl+Page Down moves to the next sheet and Alt+Enter starts a new line inside a cell. In a live test, moving with the keyboard instead of scrolling makes you visibly faster.
Ready to check yourself? Take the free Advanced Excel mock test
Advanced Excel interview questions: Medium questions (1-3 years)
20 questions21. What are the limitations of VLOOKUP?
VLOOKUP can only look to the right: the lookup column must be the first column of the range, so it cannot return a value from a column on its left. The column number is a typed constant, so inserting or deleting a column inside the range silently returns the wrong field. If the last argument is left out it does an approximate match, which on unsorted data can return a wrong row with no error. It returns only the first match, ignores case, and is slow when thousands of rows each search a large table. INDEX with MATCH, or XLOOKUP, avoids most of these problems.
=VLOOKUP(F2, A2:D500, 3) risky: no 4th argument = approximate
=VLOOKUP(F2, A2:D500, 3, FALSE) exact, but column 3 is hard-coded
=VLOOKUP(F2, A2:D500, MATCH("City", A1:D1, 0), FALSE)
column number found from the header22. How do INDEX and MATCH work together, and why use them instead of VLOOKUP?
MATCH finds the position of a value in a single row or column, and INDEX returns the item at a given position in another range. Combined, MATCH(F2, C2:C5, 0) finds E104 at position 4 and INDEX(A2:A5, 4) returns Rohit Das, even though the name column is to the left of the ID column. Because the return range is a reference, not a column number, inserting columns does not break it. Always use 0 as the third argument of MATCH for an exact match. Both ranges must be the same size and start on the same row, or the result comes from the wrong row.
A B C
1 Name Dept EmpID
2 Asha Rao Finance E101
3 Vikram Shah Sales E102
4 Meena Iyer HR E103
5 Rohit Das Sales E104
F2: E104
=MATCH(F2, $C$2:$C$5, 0) -> 4
=INDEX($A$2:$A$5, MATCH(F2, $C$2:$C$5, 0)) -> Rohit Das23. How does XLOOKUP improve on VLOOKUP and INDEX/MATCH?
XLOOKUP takes a lookup value, the column to search and the column to return, so it can look left or right and is not broken by inserted columns. It defaults to an exact match, has a built-in if_not_found argument instead of an IFERROR wrapper, and can return several columns at once, which spill into the next cells. Optional arguments set the match mode (exact, next smaller, next larger or wildcard) and the search mode (first to last, last to first, or binary search). It is available in Microsoft 365 and Excel 2021 onwards, not in Excel 2019 or older.
(same table: Name in A, Dept in B, EmpID in C)
=XLOOKUP("E102", C2:C5, A2:A5, "Not found") -> Vikram Shah
=XLOOKUP("E109", C2:C5, A2:A5, "Not found") -> Not found
=XLOOKUP("E102", C2:C5, A2:B5) -> Vikram Shah | Sales
(spills 2 cells)
XLOOKUP(lookup_value, lookup_array, return_array,
[if_not_found], [match_mode], [search_mode])24. How do you look up a rate from a slab table, such as commission by sales band?
Use an approximate match, which returns the row with the largest value less than or equal to the lookup value. With VLOOKUP and TRUE, the first column must hold the lower limit of each slab, sorted ascending and starting from 0; otherwise values below the first limit return #N/A and unsorted slabs give wrong answers. For sales of 2,35,000 the rate is 2%, so the commission is 4,700, and 6,00,000 falls in the 5% slab. XLOOKUP with match_mode -1 does the same without needing sorted data. This applies one rate to the whole amount; progressive income tax needs each slab worked out separately.
A B
1 Sales from Rate
2 0 0%
3 100000 2%
4 250000 3.5%
5 500000 5%
D2: 235000
E2: =VLOOKUP(D2, $A$2:$B$5, 2, TRUE) -> 2%
F2: =D2 * E2 -> 4700
=XLOOKUP(D2, A2:A5, B2:B5, , -1) -> 2%
D3: 600000 -> rate 5%25. How do you do a two-way lookup that matches both a row and a column?
Give INDEX the whole block of numbers and two positions: a MATCH on the row labels finds the row and a MATCH on the header row finds the column. For South and May in the sample, both positions are 2, so INDEX returns 10500. Put the region and month in input cells, ideally drop-downs, and one formula serves the whole report. In Microsoft 365 a nested XLOOKUP does the same job: the inner XLOOKUP returns the May column and the outer one picks South from it. Make sure the labels match exactly, including spaces, or MATCH returns #N/A.
A B C D
1 Region Apr May Jun
2 North 12000 15000 11000
3 South 9000 10500 13000
4 East 7000 8200 9100
G1: South G2: May
G3: =INDEX(B2:D4, MATCH(G1, A2:A4, 0), MATCH(G2, B1:D1, 0)) -> 10500
G4: =XLOOKUP(G1, A2:A4, XLOOKUP(G2, B1:D1, B2:D4)) -> 1050026. When do you use IFS or SWITCH instead of nested IF, and what can go wrong?
IFS takes pairs of condition and result and returns the result of the first condition that is TRUE, which reads better than three or four nested IFs. Order matters: with a score of 82, testing >=90, then >=75, then >=60 gives B, but testing >=60 first gives C, because 82 passes that test and IFS stops there. IFS has no default, so add TRUE as the last condition; without it, a value that meets no condition returns #N/A. SWITCH compares one value against a list of exact matches and accepts a default at the end, which suits codes such as N for North.
B2: 82 B3: 55 C2: S
=IFS(B2>=90,"A", B2>=75,"B", B2>=60,"C", TRUE,"D") -> B
=IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C","D"))) -> B
=IFS(B2>=60,"C", B2>=75,"B", B2>=90,"A", TRUE,"D") -> C (wrong order)
=IFS(B3>=90,"A", B3>=75,"B") -> #N/A
=SWITCH(C2, "N","North", "S","South", "Other") -> South27. How do you use SUMIFS with a date range, and why does it sometimes miss rows?
Join the operator and the cell with &, so the criteria become ">="&F1 and "<="&F2. Writing ">=F1" inside the quotes compares against the text F1 and returns 0. In the sample, Pune sales for April should be 40000, but the 30 April row carries a time of 6:00 PM from the ERP export. That value is slightly more than the date in F2, so "<="&F2 drops it and the total shows 22000. Using "<"&F2+1, meaning before the start of the next day, catches the whole last day. Also check that the dates are real dates and not text, or no row will match.
A B C
1 Date Branch Amount
2 28-Mar-2026 Pune 15000
3 02-Apr-2026 Pune 22000
4 15-Apr-2026 Nagpur 9000
5 30-Apr-2026 18:00 Pune 18000
6 03-May-2026 Pune 12000
F1: 01-Apr-2026 F2: 30-Apr-2026
=SUMIFS(C2:C6, B2:B6, "Pune", A2:A6, ">="&F1, A2:A6, "<="&F2) -> 22000
=SUMIFS(C2:C6, B2:B6, "Pune", A2:A6, ">="&F1, A2:A6, "<"&F2+1) -> 40000
=SUMIFS(C2:C6, B2:B6, "Pune", A2:A6, ">=F1", A2:A6, "<=F2") -> 028. How do you count with partial text matches and number bands using COUNTIFS?
COUNTIF and COUNTIFS accept wildcards in text criteria: * stands for any number of characters, ? for exactly one, and ~ lets you search for a real * or ?. The match ignores case. In the sample, "*Traders*" finds three customers, and "*Pune" together with ">30" days overdue finds two. To count a band, such as 31 to 60 days for an ageing report, test the same column twice with ">30" and "<=60". All criteria must be true for a row to count; for OR logic, add two COUNTIFS results, as long as one row cannot meet both conditions.
A B
1 Customer Days overdue
2 Shree Traders Pune 12
3 Om Agencies Nashik 45
4 Patel Traders 75
5 Sai Traders Pune 33
6 Gupta Stores Pune 95
=COUNTIF(A2:A6, "*Traders*") -> 3
=COUNTIFS(A2:A6, "*Pune", B2:B6, ">30") -> 2
=COUNTIFS(B2:B6, ">30", B2:B6, "<=60") -> 229. A VLOOKUP returns #N/A although you can see the value in the table. How do you find the cause?
Usually the two values only look the same. The common causes are a trailing space, a non-breaking space, CHAR(160), copied from a web page or an ERP export, or a number stored as text on one side and a real number on the other; text never equals a number. Test with =A2=F2, which returns FALSE, and LEN, which shows 5 characters for a four-digit ID. TRIM alone does not remove CHAR(160), so substitute it first, then TRIM and convert with VALUE. Fix the data rather than the formula, and also check that the range is locked with $.
A2: "1001 " (text from an ERP export, trailing space)
B2: Asha Rao
F2: 1001 (a real number)
=VLOOKUP(F2, A2:B2, 2, FALSE) -> #N/A
=A2 = F2 -> FALSE
=LEN(A2) -> 5
=VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), " "))) -> 100130. Which date functions do you use for month-end, probation and working-day calculations?
EOMONTH(date, 0) returns the last day of that month and EOMONTH(date, -1)+1 the first day, which is handy for monthly MIS periods. EDATE adds whole months, so a six-month probation from 15-Jan-2026 ends on 15-Jul-2026. NETWORKDAYS counts Monday to Friday days minus a holiday list: January 2026 has 21 working days with 26 January as a holiday. Many Indian offices work six days, and NETWORKDAYS.INTL with weekend code 11 (Sunday only) gives 26. DATEDIF with "y" and "ym" gives tenure in years and months, 6 years 8 months in the sample.
A2: 15-Jan-2026 (joining date) H2: 26-Jan-2026 (holiday)
(format the results as dates)
=EOMONTH(A2, 0) -> 31-Jan-2026
=EOMONTH(A2, -1) + 1 -> 01-Jan-2026
=EDATE(A2, 6) -> 15-Jul-2026
=NETWORKDAYS(DATE(2026,1,1), DATE(2026,1,31), H2) -> 21
=NETWORKDAYS.INTL(DATE(2026,1,1), DATE(2026,1,31), 11, H2) -> 26
=DATEDIF(DATE(2019,7,10), DATE(2026,3,31), "y") -> 6
=DATEDIF(DATE(2019,7,10), DATE(2026,3,31), "ym") -> 831. Dates imported from a CSV are a mix of real dates and text. Why, and how do you fix them?
The file holds DD/MM/YYYY dates, but when Windows is set to a US region Excel reads dates as MM/DD. So 07/03/2026 becomes 3 July, while 25/03/2026 cannot be read as a month and stays text, leaving one column with wrong dates and text mixed. ISNUMBER shows which cells are real dates. The clean fix is to import with the right date order: load the file through Data > From Text/CSV and set the column type Using Locale, English (India). For a column held as text, DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) rebuilds the date when day and month always have two digits.
A2: "07/03/2026" (text, means 7 March 2026)
A3: "25/03/2026" (text)
=ISNUMBER(A2) -> FALSE
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) -> 07-Mar-2026
=DATE(RIGHT(A3,4), MID(A3,4,2), LEFT(A3,2)) -> 25-Mar-202632. What are dynamic arrays and spilling, and what does #SPILL! mean?
In Microsoft 365 a formula that returns several values spills them into the cells below or beside it. You type it once in the top cell, and the spill range resizes when the data changes. =SORT(UNIQUE(A2:A6)) lists Indore, Nagpur and Pune, and the spill reference D2# means that whole result however long it is, so SUMIFS with D2# as the criterion returns 9000, 4000 and 8000 alongside. #SPILL! appears when something blocks the output range, such as a typed value or a merged cell; clear it and the formula spills. Spilling formulas are not allowed inside an Excel Table.
A B
1 Branch Amount
2 Pune 5000
3 Indore 7000
4 Pune 3000
5 Nagpur 4000
6 Indore 2000
D2: =SORT(UNIQUE(A2:A6)) -> Indore, Nagpur, Pune (D2:D4)
E2: =SUMIFS(B2:B6, A2:A6, D2#) -> 9000, 4000, 8000 (E2:E4)33. How do you use FILTER with more than one condition?
FILTER returns the rows of a range where a condition is TRUE and spills them. For several conditions, multiply them for AND and add them for OR, each in its own brackets, because each comparison becomes an array of TRUE and FALSE that arithmetic turns into 1s and 0s. In the sample, Pune invoices of at least 25,000 are INV-01 and INV-05, and invoices from Nagpur or above 40,000 are INV-01 and INV-04. Always give the third argument, the value to show when nothing matches; without it FILTER returns #CALC!. The result updates by itself when the data changes.
A B C
1 Invoice Branch Amount
2 INV-01 Pune 45000
3 INV-02 Indore 12000
4 INV-03 Pune 8000
5 INV-04 Nagpur 60000
6 INV-05 Pune 30000
=FILTER(A2:A6, (B2:B6="Pune")*(C2:C6>=25000), "No rows") -> INV-01, INV-05
=FILTER(A2:A6, (B2:B6="Nagpur")+(C2:C6>40000), "No rows") -> INV-01, INV-04
=FILTER(A2:A6, B2:B6="Surat", "No rows") -> No rows34. Your pivot table misses this month's new rows, or shows Count instead of Sum. What went wrong?
A pivot reads a cache of its source, so it changes only when you refresh it (Refresh, or Ctrl+Alt+F5 for Refresh All). If the source is a fixed range such as A1:F5000, rows added below it are never included; build the pivot on an Excel Table so the source grows by itself. Count of Amount instead of Sum appears when the values column contains blank cells or text, often numbers stored as text in an export. Fix and convert the data, then set the field to Sum. A (blank) item in the rows means some records have an empty label, which is worth fixing at the source.
35. How do you group dates, show percentages and add a calculated field in a pivot table?
Right-click a date in the pivot and choose Group to roll it up by months, quarters and years; this needs real dates, and text dates in the field can block it. Numbers can be grouped into bands too. Show Values As turns a value into % of Grand Total, % of Column Total, Running Total or Difference From the previous month. A calculated field works on summed values, so Margin = Profit / Sales gives the correct 22.73% in the sample, while averaging each row's margin gives a misleading 35%. Ratios should come from totals, not from averages of ratios.
A B C
1 Product Sales Profit
2 Laptops 100000 20000 margin 20%
3 Cables 10000 5000 margin 50%
Average of row margins: =AVERAGE(20%, 50%) -> 35% (misleading)
Ratio of totals: =SUM(C2:C3)/SUM(B2:B3) -> 22.73%
Calculated field (PivotTable Analyze > Fields, Items & Sets):
Margin = Profit / Sales36. How do slicers and timelines work, and how do you make one slicer control several pivot tables?
A slicer is a panel of buttons that filters a pivot table or an Excel Table and shows clearly what is selected; Ctrl+click picks several items and the clear button resets it. A timeline is a filter for a date field that lets you pick months, quarters or years by dragging, and it works with pivot tables. To drive several pivots from one slicer, right-click it, choose Report Connections and tick the pivots. They must share the same pivot cache, which happens when they are built from the same source, such as one Excel Table. Pivots built from separate copies of the data cannot be connected.
37. How do you highlight an entire row with conditional formatting based on one column?
Select the whole data area, such as A2:F200, then choose New Rule > Use a formula to determine which cells to format. Write the formula for the top-left cell of the selection and lock only the column: =$F2="Overdue". Excel applies it to every cell, moving the row number down but always checking column F, so the whole row changes colour. With $F$2 every row would follow row 2, and with F2 the column would shift as well. Conditions can be combined, such as an invoice past its due date that is not yet paid. The formula must return TRUE or FALSE.
Applies to: =$A$2:$F$200
Rule 1: =$F2="Overdue"
Rule 2: =AND($E2<TODAY(), $F2<>"Paid") due date in E, status in F
Wrong: =$F$2="Overdue" every row follows row 2
Wrong: =F2="Overdue" column shifts: A checks F, B checks G ...38. How do you build a dependent drop-down, where the second list depends on the first choice?
Keep each sub-list on a setup sheet and name it after its category, such as Electronics and Furniture, with Formulas > Create from Selection. The first cell gets a normal list of categories; the second uses the source =INDIRECT($A2), which turns the chosen text into a reference to that named range. Names cannot contain spaces, so a category like Office Supplies needs an underscore and SUBSTITUTE. In Microsoft 365 you can instead spill the items with FILTER in a helper cell and point the validation at it with #. Changing the first choice does not clear the second, so add a check.
Setup sheet (names made with Formulas > Create from Selection):
Electronics: Laptop, Printer, Router
Furniture: Chair, Desk, Cabinet
A2 Validation > List, Source: Electronics,Furniture
B2 Validation > List, Source: =INDIRECT($A2)
Microsoft 365 option:
J2: =FILTER(Items[Item], Items[Category]=$A$2)
B2 Validation > List, Source: =$J$2#39. How do you show sales and margin percentage on one chart?
Use a combo chart with a secondary axis. The two series have very different scales, sales in lakhs and margin between 0 and 100%, so on one axis the margin line would lie flat along the bottom. Select the data, choose Insert > Combo Chart, keep Sales as clustered columns and set Margin % to a line on the secondary axis. Add titles to both axes so no one reads the percentage against the rupee scale. Build the chart on an Excel Table so a new month appears automatically. Do not use two axes for series in the same unit, because it exaggerates differences.
A B C
1 Month Sales (Rs) Margin %
2 Apr 1820000 14.2%
3 May 2045000 12.8%
4 Jun 1975000 15.1%
Insert > Combo Chart:
Sales -> Clustered Column, primary axis
Margin % -> Line with Markers, secondary axis40. How do you use Goal Seek, for example to find the loan amount for a target EMI?
Goal Seek works backwards: it changes one input cell until a formula cell reaches the value you want. Here the EMI cell uses PMT on a 25,00,000 loan at 9% a year for 240 months, which gives about 22,493. To find the loan that fits an EMI of 25,000, open Data > What-If Analysis > Goal Seek and set B4 to 25000 by changing B1; it finds about 27,78,624, the same as PV gives directly. The set cell must hold a formula that depends on the changing cell, and the changing cell must hold a value, not a formula. For several inputs or constraints, use Solver.
B1: 2500000 loan amount (input)
B2: 9% annual interest rate
B3: 240 months
B4: =PMT(B2/12, B3, -B1) -> 22493 (rounded)
Goal Seek: Set cell B4, To value 25000, By changing cell B1
-> B1 becomes about 2778624
Check: =PV(9%/12, 240, -25000) -> 2778624 (rounded)Ready to check yourself? Take the free Advanced Excel mock test
Advanced Excel interview questions: Hard questions (experienced)
20 questions41. A large workbook recalculates slowly after every edit. What do you check first?
Start with volatile functions. OFFSET, INDIRECT, TODAY, NOW, RAND and RANDBETWEEN recalculate after any change in the workbook, along with all their dependents, so one dynamic range built with OFFSET can make thousands of cells recalculate on each entry. Replace them with Excel Tables or INDEX-based ranges, which are not volatile. Next, look for whole-column references inside SUMPRODUCT or array formulas, repeated lookups of the same key and long dependency chains. During heavy editing, set calculation to Manual and press F9, but switch it back so no one reads stale numbers.
A1: Amount A2:A4: 100, 250, 400
Volatile: =SUM(OFFSET(A2, 0, 0, COUNTA(A:A)-1, 1)) -> 750
Not volatile: =SUM(A2:INDEX(A:A, COUNTA(A:A))) -> 750
Better still: =SUM(tblSales[Amount])
Formulas > Calculation Options > Manual, then F9 to calculate42. Why does an Excel file grow to 50 MB with little data, and how do you shrink it?
Size usually comes from things you cannot see. Press Ctrl+End: if it lands far below the data, formatting or stray entries have stretched the used range, so delete those rows and columns and save. Copy-pasting splits conditional formatting into thousands of rule fragments; clear and recreate them in Manage Rules. Each pivot built from a separate source keeps its own cache in the file, so base pivots on one Table. Remove unused styles, broken names and hidden sheets, compress images, and try saving as .xlsb, which is usually smaller and opens faster. Move very large raw data into Power Query.
43. How do you make lookups fast on 2 lakh rows?
An exact-match lookup scans the list from the top, so 2 lakh lookups into 2 lakh rows means billions of comparisons. First, search once: put MATCH in a helper column and let several INDEX formulas reuse that position, instead of one VLOOKUP per returned column. Second, sort the master by key and use a binary search, XLOOKUP with search_mode 2, which needs only about 18 comparisons per lookup. On unsorted data a binary search returns wrong results, so make the sort part of the refresh. For a repeated monthly job, a Power Query merge or a Data Model relationship is often faster still.
Helper column, one search per row:
H2: =MATCH(A2, Master!$A$2:$A$200001, 0)
I2: =INDEX(Master!$C$2:$C$200001, $H2) name
J2: =INDEX(Master!$F$2:$F$200001, $H2) city
Sorted master, binary search with exact match:
=XLOOKUP(A2, Master!$A$2:$A$200001, Master!$C$2:$C$200001,
"Missing", 0, 2)44. What does LET do, and when does it make a formula faster as well as clearer?
LET assigns names to values or calculations inside one formula and then uses those names in a final expression. Each name is calculated once and reused, so a SUMIFS that would otherwise appear three times in a long IF runs only once, which matters when the formula is copied down thousands of rows. It also makes the logic readable, because sales, target and pct say what each part is. In the sample, North sales of 4,50,000 against a target of 5,00,000 return Short by 10%, while South returns Achieved. LET is in Microsoft 365 and Excel 2021, and its names exist only inside that formula.
B C F G
1 Region Sales Region Target
2 North 300000 North 500000
3 North 150000 South 200000
4 South 200000
H2: =LET(sales, SUMIFS($C$2:$C$100, $B$2:$B$100, F2),
target, G2,
pct, sales / target,
IF(pct >= 1, "Achieved", "Short by " & TEXT(1 - pct, "0%")))
H2 -> Short by 10% H3 (copied down) -> Achieved45. How would you build a live top-5 customers report with one formula?
Combine the dynamic array functions of Microsoft 365. UNIQUE lists the customers, SUMIFS totals each one, HSTACK puts names and totals side by side, SORTBY orders them by total, largest first, and TAKE keeps the first rows. LET names each step so the formula reads like a recipe. The sample keeps 3 rows to stay short and returns Shree Traders 57000, Om Agencies 48000 and Patel Stores 43000. The result updates when the data changes, with no pivot to refresh. Point it at Table columns so new rows are included, and decide how to treat ties, because TAKE simply cuts at the row count.
A B
1 Customer Amount
2 Shree Traders 42000
3 Om Agencies 18000
4 Shree Traders 15000
5 Patel Stores 39000
6 Om Agencies 30000
7 Gupta Mart 12000
8 Patel Stores 4000
9 Sai Foods 26000
D2: =LET(c, UNIQUE(A2:A9),
t, SUMIFS(B2:B9, A2:A9, c),
TAKE(SORTBY(HSTACK(c, t), t, -1), 3))
-> Shree Traders 57000 / Om Agencies 48000 / Patel Stores 4300046. How do you handle lookups when the key appears more than once?
Standard lookups return the first match, which is often not what the business wants. In a purchase log sorted by date, the first Cement rate is 410 but the latest is 418. XLOOKUP with search_mode -1 searches from the bottom and returns the last match. To see every match, FILTER returns them all, and TEXTJOIN can show them in one cell as 410, 425, 418. The last match depends on the sort order, so if the log is not in date order, find the latest date with MAXIFS and look up on item and date together. Duplicates in a master table are a data problem: flag them with COUNTIF.
A B C
1 Date Item Rate
2 05-Apr-2026 Cement 410
3 12-Apr-2026 Steel 68
4 20-Apr-2026 Cement 425
5 02-May-2026 Cement 418
=VLOOKUP("Cement", B2:C5, 2, FALSE) -> 410 (first)
=XLOOKUP("Cement", B2:B5, C2:C5, , 0, -1) -> 418 (last)
=TEXTJOIN(", ", TRUE, FILTER(C2:C5, B2:B5="Cement")) -> 410, 425, 41847. When do you need SUMPRODUCT instead of SUMIFS, and what traps does it have?
SUMIFS needs plain ranges as criteria ranges, so it cannot test a calculated value such as MONTH(date). SUMPRODUCT can: each condition becomes an array of TRUE and FALSE, multiplying turns them into 1s and 0s, and the products are added. In the sample, January Pune sales give 24000, but that includes January 2025; adding a YEAR test gives 19000. Another trap is that a blank date counts as 0 and MONTH(0) is 1, so a row with no date is counted as January, giving 28000 once row 7 is included. SUMPRODUCT evaluates every cell, so avoid whole-column ranges.
A B C
1 Date Branch Amount
2 10-Jan-2026 Pune 12000
3 25-Jan-2026 Indore 8000
4 03-Feb-2026 Pune 9000
5 18-Jan-2025 Pune 5000
6 29-Jan-2026 Pune 7000
7 (blank) Pune 4000
=SUMPRODUCT((MONTH(A2:A6)=1)*(B2:B6="Pune")*C2:C6) -> 24000
=SUMPRODUCT((MONTH(A2:A6)=1)*(YEAR(A2:A6)=2026)
*(B2:B6="Pune")*C2:C6) -> 19000
=SUMPRODUCT((MONTH(A2:A7)=1)*(B2:B7="Pune")*C2:C7) -> 2800048. What is Power Query, and why is it better than cleaning the same export by hand every month?
Power Query, under Data > Get Data, is the built-in tool for importing and cleaning data. Each action, such as promoting headers, trimming text, changing types or removing rows, is recorded as an applied step in the M language. Next month you replace the file and click Refresh, and every step runs again in the same order, so the cleaning is repeatable and auditable instead of dozens of manual clicks. The source file is never changed. Set data types deliberately, using a locale such as en-IN for DD/MM dates, and load the result to a Table, as a connection only, or into the Data Model.
let
Source = Csv.Document(File.Contents("D:\MIS\sales_apr.csv"),
[Delimiter = ",", Encoding = 65001]),
Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Trimmed = Table.TransformColumns(Headers,
{{"Customer", Text.Trim, type text}}),
Typed = Table.TransformColumnTypes(Trimmed,
{{"Invoice Date", type date}, {"Amount", type number}},
"en-IN")
in
Typed49. How do you combine monthly files from 20 branches with Power Query?
Put the files in one folder and use Get Data > From File > From Folder, then Combine and Transform Data. Power Query builds a sample-file query and a function that applies the same steps to every file, then appends the results. Keep the Source.Name column, because the file name often carries the branch or the month. Filter the file list to the right extension and exclude temporary files that start with ~$. Every file needs the same column names; a renamed header leaves that column empty, so agree a fixed template. Next month, save the new files in the folder and press Refresh.
let
Source = Folder.Files("D:\MIS\Branch files"),
Files = Table.SelectRows(Source, each [Extension] = ".xlsx"
and not Text.StartsWith([Name], "~$"))
in
Files
// then Combine Files: pick the sheet, and the steps run for every file50. A branch sends data with months as columns. Why is that a problem, and how do you unpivot it?
A crosstab with Apr, May and Jun as columns is easy to read but hard to analyse, because pivots, SUMIFS and charts want one column per field, such as Branch, Month and Units. In Power Query, select the Branch column and choose Unpivot Other Columns; every month column turns into rows of Month and Units. Choosing Unpivot Other Columns rather than Unpivot Only Selected Columns matters: when July arrives, the new column is included without editing the query. After unpivoting, convert month names to real dates so they sort and group properly. Budget sheets need the same treatment.
Before (crosstab):
Branch Apr May Jun
Pune 120 135 128
Indore 90 98 105
After (one row per branch and month):
Branch Month Units
Pune Apr 120
Pune May 135
Pune Jun 128
Indore Apr 90
...
M: Table.UnpivotOtherColumns(Source, {"Branch"}, "Month", "Units")51. How do you use Power Query merges to reconcile two lists, such as the purchase register against GSTR-2B?
Merge Queries joins two tables on one or more key columns; Ctrl+click to pick several, such as GSTIN and invoice number. The join kind decides the result. Left Outer keeps every row of the first table, like a lookup, and Inner keeps only matches. Left Anti returns rows of the first table with no match in the second, which is exactly the list of purchase invoices missing from GSTR-2B, and Right Anti gives the reverse. Matching is exact and case-sensitive, so clean the keys first: trim, uppercase and standardise invoice formats. Fuzzy matching exists, but its matches need review.
MissingIn2B = Table.NestedJoin(
Books, {"GSTIN", "InvoiceNo"},
GSTR2B, {"GSTIN", "InvoiceNo"},
"Match", JoinKind.LeftAnti)
Join kinds: LeftOuter, RightOuter, FullOuter, Inner, LeftAnti, RightAnti52. When do you use the Data Model and Power Pivot instead of a normal pivot table?
Use the Data Model when the data is split across tables or is too big for a sheet. You load a Sales fact table plus Product and Branch tables, relate them by key, and one pivot uses fields from all of them without a single VLOOKUP. The engine compresses data, so it can hold far more than the 1,048,576 rows a sheet allows. Ticking Add this data to the Data Model also unlocks Distinct Count in the pivot. With Power Pivot you write DAX measures, such as sales per active customer, which stay correct at every level of the pivot. Power Pivot is not available in Excel for Mac.
Relationships: Sales[ProductID] -> Products[ProductID]
Sales[BranchCode] -> Branches[BranchCode]
Total Sales := SUM ( Sales[Amount] )
Active Customers := DISTINCTCOUNT ( Sales[CustomerID] )
Sales per Cust := DIVIDE ( [Total Sales], [Active Customers] )53. What is GETPIVOTDATA, and why do MIS reports use it?
When you click a pivot value while typing a formula, Excel writes GETPIVOTDATA instead of a cell address. It asks for a value by field and item names, such as Amount for Region North and Month Apr, so it keeps returning the right number when the pivot is sorted, filtered or rearranged, where a plain reference like C7 would quietly point at another figure. This lets you keep a fixed, formatted report layout fed by a pivot. Replace the typed items with cell references so one formula fills a grid. It returns #REF! when the item is not visible in the pivot, so trap that case.
=GETPIVOTDATA("Amount", $A$3, "Region", "North", "Month", "Apr")
=GETPIVOTDATA("Amount", $A$3, "Region", $G5, "Month", H$4) grid
=IFERROR(GETPIVOTDATA("Amount", $A$3, "Region", $G5, "Month", H$4), 0)
Switch off: PivotTable Analyze > Options > Generate GetPivotData54. How do data tables and Scenario Manager differ for sensitivity analysis?
A data table, under What-If Analysis, recalculates one formula for a grid of input values. With the EMI formula in the corner cell, loan amounts down the side and interest rates across the top, Excel fills the grid: 16,729 for 20 lakh at 8% up to 28,951 for 30 lakh at 10%. It stays live but slows big models; Automatic except for data tables helps. Scenario Manager stores named sets of inputs, such as Base, Best and Worst, switches between them and builds a summary report, which is a static snapshot. Use data tables to see how an output moves and scenarios to present a few cases.
B1: 2500000 B2: 9% B3: 240
B5: =PMT(B2/12, B3, -B1) -> 22493
Two-variable data table in E2:H5 (EMI, rounded):
E2: =B5 8% 9% 10%
2000000 16729 17995 19300
2500000 20911 22493 24126
3000000 25093 26992 28951
Data > What-If Analysis > Data Table:
Row input cell = B2, Column input cell = B155. How do you protect a shared template, and what are the limits of Excel protection?
Every cell is Locked by default, but locking only takes effect when the sheet is protected. So unlock the input cells first (Format Cells > Protection), tick Hidden for formulas you do not want shown, then use Review > Protect Sheet and choose what users may still do, such as using filters. Protect Workbook stops sheets from being added, deleted, renamed or unhidden. This prevents accidents, not determined users: sheet passwords are easy to remove. For confidential data such as payroll, use File > Info > Protect Workbook > Encrypt with Password, or restrict access to the file itself.
56. How do you trace a wrong number in a complex workbook, and when is a circular reference acceptable?
Use the auditing tools on the Formulas tab. Trace Precedents and Trace Dependents draw arrows between cells, Evaluate Formula steps through a formula one part at a time, and the Watch Window keeps key cells in view from any sheet. While editing, select part of a formula and press F9 to see its value, then press Esc so the formula is not replaced by that value. A circular reference, such as a total that includes itself, is usually a bug, and Error Checking > Circular References shows where it is. Deliberate loops, like interest on an average balance, need iterative calculation; document them.
C2:C4: 10, 20, 30
C5: =SUM(C2:C5) circular: the total includes its own cell
Fix: =SUM(C2:C4) -> 60
Find: Formulas > Error Checking > Circular References
Deliberate loops: File > Options > Formulas > Enable iterative
calculation (Maximum Iterations 100, Maximum Change 0.001)57. Walk through how you would build a monthly sales MIS report from an ERP export.
Separate the work into layers. Raw data goes into its own sheet or a Power Query output and is never edited by hand. A cleaning step fixes types, trims codes and adds helper columns such as Month and Zone. The report sheet has a fixed layout driven by a month selector and filled by SUMIFS or GETPIVOTDATA, with month-on-month and target variances and conditional formatting for exceptions. Then add control checks: the report total must equal the raw total for the month, row counts must match, and unmapped codes are listed. Finally, write down the refresh steps and protect the layout.
Raw tblSales (Power Query output, refreshed monthly)
Report B1 month selector (validation list), e.g. 01-Apr-2026
Report B4 =SUMIFS(tblSales[Amount], tblSales[Branch], $A4,
tblSales[Month], $B$1)
Variance =IFERROR(B4/C4 - 1, "") last month in column C
Check =ROUND(SUM(B4:B12) - SUMIFS(tblSales[Amount],
tblSales[Month], $B$1), 2) = 0 must be TRUE58. How do you reconcile a purchase register with GSTR-2B in Excel when the keys do not match exactly?
Build a clean composite key on both sides, GSTIN and invoice number joined with a separator after TRIM and UPPER, because suppliers type invoice numbers inconsistently. Look up each book entry in the 2B list and classify it as missing in 2B, amount differs, or matched within a small tolerance such as one rupee for rounding. In the sample, a lowercase GSTIN with a stray space still matches, and 11,800.60 against 11,800 counts as matched. Check duplicates with COUNTIFS on the key, run the lookup the other way to find entries missing from the books, and keep totals that tie back to both sources.
Books sheet:
B C E
2 " 27abcde1234f1z5" "inv-0457 " 11800
3 "29PQRSX5678K1Z2" "INV-0458" 5900
F2: =UPPER(TRIM(B2)) & "|" & UPPER(TRIM(C2))
-> 27ABCDE1234F1Z5|INV-0457
G2: =XLOOKUP(F2, GSTR2B!$F$2:$F$5000, GSTR2B!$E$2:$E$5000, "Missing")
H2: =IF(G2="Missing", "Missing in 2B",
IF(ABS(E2-G2)<=1, "Matched", "Amount differs"))
Row 2 -> Matched (2B shows 11800.60) Row 3 -> Missing in 2B59. What are the weaknesses of data validation, and how do you make data entry rules stronger?
Validation only checks values typed into a cell. Pasting over a validated cell replaces the rule along with the value, and data that was already there when the rule was added is never checked; Data Validation > Circle Invalid Data marks those cells. Formula results are not checked either. Custom rules go beyond lists: COUNTIF can force unique IDs, and a comparison can require an end date on or after the start date. For data that matters, add a check column that flags breaks with the same logic, protect the layout, and validate again in Power Query when the data is loaded.
Custom rule, unique employee ID (applied to A2:A1000):
=COUNTIF($A$2:$A$1000, A2) = 1
Custom rule, end date on or after start date (applied to C2:C1000):
=C2 >= B2
Check column for data already in the sheet:
D2: =COUNTIF($A$2:$A$1000, A2) = 1 FALSE marks a repeated ID60. What happens when you share a Microsoft 365 workbook with someone on an older Excel version?
Functions added after their version do not exist for them. XLOOKUP, FILTER, UNIQUE, LET and the other newer functions show an _xlfn. prefix in the formula bar and return #NAME? once the sheet recalculates. A spilling formula built only from older functions opens as a legacy array formula in braces, fixed to its last size. In the other direction, older formulas that relied on implicit intersection open in Microsoft 365 with an @ sign, which keeps their old result. Before sending, run Check Compatibility (File > Info > Check for Issues), and for a mixed audience use INDEX/MATCH and SUMIFS.
In Microsoft 365: =XLOOKUP(F2, C2:C5, A2:A5, "Not found")
Opened in Excel 2016: =_xlfn.XLOOKUP(F2, C2:C5, A2:A5, "Not found")
-> #NAME? after recalculation
Works in both: =IFERROR(INDEX(A2:A5, MATCH(F2, C2:C5, 0)),
"Not found")Practice questions for November 2026
New every month3 questions from the Advanced Excel mock test, the way they appear in it. A new set comes on the 1st of every month. Pick your answer, then open it.
-
A2 holds 15-08-2025, a Friday. What does B2 return?
A 1 Date 2 15-08-2025 B2: =WEEKDAY(A2)- 5
- 6
- 7
- 4
Show the answer
Answer: 6. With no second argument WEEKDAY counts Sunday as 1 and Saturday as 7, so Friday is 6. =WEEKDAY(A2,2) counts Monday as 1 and gives 5 for Friday, which makes a weekend test as simple as >5.
-
A calculated field Margin = Profit / Sales is added, formatted as a whole percentage. What does the pivot show for Pune?
A B C 1 Branch Sales Profit 2 Pune 400000 60000 3 Pune 100000 25000 4 Nashik 250000 30000 Pivot: Rows = Branch, Values = Margin (calculated field =Profit/Sales)- 12%
- 20%
- 40%
- 17%
Show the answer
Answer: 17%. A calculated field works on the sums of its fields, not row by row: 85000 / 500000 = 17%. Averaging the two row margins (15% and 25%) would give 20%, which ignores that the first row is four times bigger.
-
16-digit card numbers are stored as text in A2:A500, and =COUNTIF($A$2:$A$500,A2) returns 2 for numbers that appear only once. Why?
- COUNTIF reads number-like text as numbers, so only the first 15 digits are compared
- COUNTIF counts the header cell as well when the range includes text
- Text cells are counted twice by COUNTIF when the column is filtered
- COUNTIF treats * inside card numbers as a wildcard
Show the answer
Answer: COUNTIF reads number-like text as numbers, so only the first 15 digits are compared. COUNTIF converts criteria and cells that look like numbers, and a number keeps 15 significant digits, so two cards that differ only in the 16th digit match. =SUMPRODUCT(--($A$2:$A$500=A2)) compares the text exactly.
Ready to apply?
There are 2,030 open jobs asking for Advanced Excel on CoreHRx right now. Create a free profile once and apply with it.