Excel Interview
Questions and answers (2026)

Relative reference — A1. Both column and row change when you copy the formula. This is the default.

Absolute reference — $A$1. Both column and row stay locked when you copy. Used for fixed values like tax rates and lookup tables.

Mixed reference — $A1 or A$1. One part is locked. $A1 locks the column; A$1 locks the row. Used in multiplication tables and grid calculations.

Press F4 while editing a reference to cycle through relative, absolute and both mixed forms.

VLOOKUP searches for a value in the first column of a range and returns a value from a specified column in the same row.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Four arguments:

• lookup_value — the value to search for, e.g. A2.
• table_array — the range to search, e.g. Products!A:C.
• col_index_num — which column in that range to return, e.g. 3.
• range_lookup — FALSE for exact match (almost always), TRUE for approximate match.

• Only looks right. The lookup column must be the first column of the range. You cannot return a value that is to the left.
• Breaks on column insert. If a column is inserted inside the range, the hard-coded column index becomes wrong.
• Case-insensitive and default approximate. Forgetting FALSE gives a silently wrong result.
• Slower on large datasets. INDEX-MATCH or XLOOKUP are faster on tens of thousands of rows.
• Returns #N/A when not found, without a clean built-in alternative.

These limitations are the reason interviewers ask about INDEX-MATCH and XLOOKUP next.

// VLOOKUP — return price for the product in A2
=VLOOKUP(A2, Products!A:C, 3, FALSE)

// INDEX-MATCH — same result, more stable
=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

In an interview, say: "I use VLOOKUP for quick one-off lookups, and INDEX-MATCH where the data will change or the lookup column is to the right."

SUMIFS adds values in a range when one or more conditions are met. It is the modern version of SUMIF with support for multiple criteria.

// One condition
=SUMIF(B:B, "North", D:D)

// Two conditions — SUMIFS puts the sum range first
=SUMIFS(D:D, B:B, "North", C:C, "Electronics")

Note the argument order changes between SUMIF and SUMIFS. In SUMIF, the range comes first; in SUMIFS, the sum_range comes first, then pairs of criteria_range and criteria.

COUNTIFS counts rows that meet one or more criteria. Each pair of range and criteria is an AND condition — the row must meet all of them.

// Count orders from the North region in Electronics
=COUNTIFS(B:B, "North", C:C, "Electronics")

// Count rows where sales is greater than 5000
=COUNTIFS(D:D, ">5000")

Common mistake: writing COUNTIFS with the criteria before the range. The pattern is always range, criteria, range, criteria.

IFERROR returns a specified value when a formula results in an error — #N/A, #VALUE!, #DIV/0!, #REF! or #NUM!.

// Show blank instead of #N/A
=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "")

// Show a message for any error
=IFERROR(100/B2, "Not available")

Use it at the top level of a formula to clean the report output. Do not wrap it around every inner function — you will hide real problems and make debugging harder.

A pivot table summarises a large table interactively — group by one field, calculate against another, and change the layout by dragging fields.

Four areas:

• Rows — the group-by field on the left, e.g. Region.
• Columns — the group-by field across the top, e.g. Category.
• Values — the numbers to aggregate, e.g. Sum of Sales. Default is SUM for numbers, COUNT for text.
• Filters — a top-level filter, e.g. Year.

Modern Excel adds Slicers and Timelines as better-looking filters. Use them for dashboards; use the Filters area for one-off analysis.

In a data-quality check, COUNTA shows the number of filled rows, and COUNTBLANK shows how many are missing. If COUNT and COUNTA differ on a "numeric" column, some values are stored as text.

COUNTIF takes a single range and a single criteria. COUNTIFS takes multiple range-criteria pairs, all combined with AND.

=COUNTIF(B:B, "North")
=COUNTIFS(B:B, "North", C:C, "Electronics")

Use COUNTIFS in MIS reporting — a report that needs "count of orders by region and category" cannot be built with COUNTIF alone.

Conditional formatting changes a cell's appearance based on its value or a formula. It makes a report readable without adding extra columns.

Common uses:

• Highlight duplicates in a customer ID column.
• Colour-scale a sales column so high values stand out.
• Data bars for values in a range to show relative size.
• Icon sets for KPI traffic lights — green / amber / red.
• Formula-based rules to highlight an entire row based on one column's value.

Rules live on the Home tab → Conditional Formatting. Use Manage Rules to review every rule on the sheet — orphan rules are a common bug in shared files.

Data validation restricts what a user can type into a cell. It prevents bad data at the point of entry, which is far better than cleaning it later.

Common validation rules:

• List — a dropdown of allowed values, e.g. a Region list.
• Whole number — between 1 and 100, for a quantity field.
• Date — after today, for an order date.
• Text length — exactly 10, for a phone number.
• Custom formula — like =ISNUMBER(A1) to force a number.

Use an Input Message to show the rule on selection, and an Error Alert to block invalid input. On the Data tab → Data Validation.

A named range gives a cell or range a meaningful name — TaxRate instead of Sheet1!$B$2.

Benefits:

• Formulas read like sentences — =Sales * TaxRate instead of =D2*Sheet1!$B$2.
• The name is absolute by default, so you never forget the $ signs.
• Changing the reference is done in one place, in Name Manager.
• Named ranges work across sheets and in data validation dropdowns.

Create from Formulas → Define Name, or select the cells and type the name into the Name Box at the top-left.

Three methods, used for different situations:

1. Data → Remove Duplicates — permanently deletes duplicate rows based on the columns you select. Fast, but no preview. Work on a copy.
2. Conditional Formatting → Highlight Duplicates — shows duplicates without deleting them. Useful when you want to review before deleting.
3. UNIQUE function (Excel 2021 / Microsoft 365) — returns a list of unique values from a range, without touching the source.

=UNIQUE(A2:A1000)

Spill ranges from UNIQUE refresh automatically when the source data changes — better than Remove Duplicates for a live report.

Flash Fill (Ctrl+E) fills a column by detecting the pattern from your first one or two examples. It is a fast way to split, join or reformat without a formula.

Common uses:

• Split "Ravi Prakash" into First Name and Last Name columns.
• Join First and Last into a Full Name column.
• Extract the domain from an email address.
• Reformat "2026-10-14" into "14 October 2026".

Flash Fill is a one-time fill — it does not update when the source changes. For a live report, use TEXTSPLIT, TEXTBEFORE or TEXTAFTER instead.

A formula is any expression you type into a cell that starts with =. It can be a simple calculation like =A1+B1 or a complex combination.

A function is a built-in operation with a name — SUM, VLOOKUP, IF, COUNTIFS. Functions are the building blocks you assemble into a formula.

"Formula" is the broad term; "function" is a specific component inside it.

CONCATENATE joins values but requires every value as a separate argument, and there is no delimiter. It cannot take a range.

TEXTJOIN takes a delimiter, an ignore-empty flag and a range or list. It replaces all the manual &" "& patterns.

// Old way
=CONCATENATE(A2, " ", B2)

// New way with a delimiter and range support
=TEXTJOIN(" ", TRUE, A2:B2)

// Join a whole column, ignoring blanks
=TEXTJOIN(", ", TRUE, A2:A100)

Modern alternative: the & operator still works for two or three values. Use TEXTJOIN for ranges.

Match the chart to the question:

• Column or bar chart — compare categories, e.g. sales by region.
• Line chart — show a trend over time, e.g. monthly revenue.
• Pie or doughnut chart — show parts of a whole, only when there are fewer than six slices.
• Scatter chart — show a relationship between two numbers, e.g. price vs quantity sold.
• Combo chart — two different measures on the same chart, like revenue on bars and margin % on a line.

Keep it clean: one message per chart, axis labels in plain words, and no 3-D effects.

Freeze Panes locks the top rows and left columns so they stay visible while you scroll. Use it on every long sheet.

Split divides the window into two or four panes, each of which can scroll independently in the same sheet. Both halves show the same data.

Freeze Panes is for keeping headers visible. Split is for comparing two distant parts of a large sheet at the same time.

Use IFERROR at the top level to clean the output; fix the underlying issue rather than hiding it.

Read the formula from the inside out:

1. MATCH finds the position of the lookup value in a column: MATCH(A2, Products!A:A, 0) returns the row number where A2 is found. The 0 means exact match.
2. INDEX returns the value from that row in the result column: INDEX(Products!C:C, position).

=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

For a two-way lookup — matching both a row and a column — use two MATCH functions:

=INDEX(Data!B2:M50,
       MATCH(A2, Data!A2:A50, 0),
       MATCH(B2, Data!B1:M1, 0))

XLOOKUP is the modern replacement for both VLOOKUP and INDEX-MATCH. It is available in Microsoft 365 and Excel 2021 and later.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

// Return the price, or a message if not found
=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

// Return multiple columns at once
=XLOOKUP(A2, Products!A:A, Products!B:D)

Advantages over VLOOKUP:

• Can look left or right — the return column does not need to be after the lookup column.
• Default match mode is exact — you cannot accidentally get an approximate match.
• Built-in if_not_found argument replaces IFERROR.
• Can return multiple columns or rows in one formula.
• Faster on large datasets, and does not break when columns are inserted.

1. Select the data range and go to Insert → PivotTable.
2. Drag Region to Rows, Category to Columns, and Sales to Values.
3. Open PivotTable Analyze → Fields, Items & Sets → Calculated Field.
4. Name the field — for example, "Profit".
5. Write the formula using field names — =Sales - Cost.
6. Click Add → OK. The calculated field appears in the Values area and can be dragged into the pivot table.

A calculated field is evaluated after the pivot table aggregates — so the average of a calculated field is not the same as a calculated field of an average. Be careful with ratio fields.

GETPIVOTDATA reads a specific value from a pivot table by naming the row, column and measure — not by cell reference. It is stable when the pivot table layout changes.

=GETPIVOTDATA("Sales", $A$3,
              "Region", "North",
              "Category", "Electronics")

Use it inside a dashboard where other cells need to read from a pivot table. Turn off the automatic GETPIVOTDATA when you want a plain reference — PivotTable Analyze → Options → Generate GetPivotData.

Use both on a dashboard: a timeline for the period, and slicers for the categories.

Dynamic array functions return a range of values that "spills" into neighbouring cells. You write one formula, and Excel fills the result range automatically.

Key functions:

• FILTER — returns rows that meet a condition.
• UNIQUE — returns a list of distinct values.
• SORT and SORTBY — sort a range by one or more columns.
• SEQUENCE — generates a series of numbers.
• RANDARRAY — generates random numbers.

// Unique product list, sorted
=SORT(UNIQUE(Sales!B2:B1000))

// North region orders above 10,000
=FILTER(Sales!A2:E1000,
        (Sales!B2:B1000="North") * (Sales!E2:E1000>10000))

Dynamic arrays are available in Microsoft 365 and Excel 2021 and later. Say this in the interview — older versions of Excel will show #NAME? for these functions.

Use IF with AND / OR for compound conditions, or nest IFs for a tiered rule.

// AND — both conditions must be true
=IF(AND(B2>=5000, C2="North"), "Bonus", "No bonus")

// OR — either condition is true
=IF(OR(B2>=5000, C2="North"), "Qualified", "Not qualified")

// Tiered grading with nested IF
=IF(B2>=90, "A",
  IF(B2>=75, "B",
  IF(B2>=60, "C", "F")))

For many tiers, use the newer IFS function — it reads top-down and returns the first true condition:

=IFS(B2>=90, "A", B2>=75, "B", B2>=60, "C", TRUE, "F")

Chain them for a full cleanup:

=PROPER(TRIM(SUBSTITUTE(A2, "_", " ")))

This turns " ravi_prakash " into "Ravi Prakash".

Use DATEDIF for age in years and EDATE / EOMONTH for month-end and billing logic. NETWORKDAYS is a standard MIS function for turnaround time.

Power Query is the data preparation tool inside Excel (Data → Get & Transform Data). It connects to files, folders, databases and web pages, cleans them step by step, and loads a clean table into Excel or the Data Model.

Use it when:

• You receive the same monthly file and want the same cleaning done automatically.
• You need to combine data from many files in one folder.
• You need to unpivot a wide table into a tidy format for a pivot table.
• You want the cleaning steps recorded and auditable, not typed into cells.

Every step is recorded in the Applied Steps pane. Click Refresh and the whole pipeline reruns on the new data.

SUMIFS is a purpose-built multi-condition sum. It is fast and reads clearly.

SUMPRODUCT multiplies two or more arrays and sums the result. It can do everything SUMIFS does, plus things SUMIFS cannot — like sum a calculation, or count with OR logic.

// SUMIFS — simple two-condition sum
=SUMIFS(D:D, B:B, "North", C:C, "Electronics")

// SUMPRODUCT — sum a calculation with conditions
=SUMPRODUCT((B2:B100="North") * (C2:C100="Electronics") * (D2:D100 * E2:E100))

// SUMPRODUCT — count with OR
=SUMPRODUCT((B2:B100="North") + (B2:B100="South") > 0)

Use SUMIFS where possible — it is faster on large ranges. Reach for SUMPRODUCT when the value to sum is a calculation.

Reference the second sheet by name inside the table_array. Use absolute references (or a named range) so the formula copies cleanly down the column.

=VLOOKUP(A2, Products!$A$2:$C$1000, 3, FALSE)

If the sheet name contains spaces, wrap it in single quotes:

=VLOOKUP(A2, 'Product Master'!$A$2:$C$1000, 3, FALSE)

For a workbook with many sheets, define a named range for the lookup table — the formula reads better and is easier to audit.

Three approaches, used for different jobs:

1. Conditional Formatting → Highlight Cells Rules → Duplicate Values. Shows duplicates in a colour. Good for a quick visual.
2. COUNTIF helper column. Shows exactly how many times each value appears — =COUNTIF($A$2:$A$1000, A2). Filter on values greater than 1.
3. UNIQUE function. Lists distinct values only. Combine with SORT for a clean unique list.

// Flag duplicates in A2:A1000
=COUNTIF($A$2:$A$1000, A2) > 1

// Unique customer list
=SORT(UNIQUE(A2:A1000))

Always use COUNTIF when you need to know how many duplicates exist, not just that they exist.

VLOOKUP searches vertically — down the first column of a range. The lookup values are in a column.

HLOOKUP searches horizontally — across the first row of a range. The lookup values are in a row.

Most data in Excel is arranged in columns, so VLOOKUP is far more common. Use HLOOKUP only when your data is genuinely laid out in rows — for example, a monthly sales table with months across the top.

XLOOKUP replaces both — it works vertically or horizontally depending on what you give it.

A waterfall chart shows how a starting value moves to an ending value through a series of positive and negative changes. It is ideal for P&L bridges, cash flow and variance analysis.

Typical MIS uses:

• Revenue bridge — opening revenue, add new business, subtract churn, closing revenue.
• Cost walk — budget, plus new hires, minus savings, actual cost.
• Profit reconciliation — gross profit, minus marketing, minus overheads, net profit.

Insert → Waterfall Chart. Set the opening and closing totals to "Total" in the chart's data settings so the bridge reads correctly.

Step 1 — Define the one question. A dashboard that answers "how is the business performing this month?" is different from one that answers "where is my cost overrun?".

Step 2 — Separate sheets. One for raw data, one for calculations, one for the dashboard. Never mix them.

Step 3 — Structure the layout. KPIs at the top (revenue, margin, orders, AOV), trends in the middle (line charts), breakdowns at the bottom (category, region, product).

Step 4 — Interactivity. Add a timeline for the period and slicers for region, category and segment. Connect slicers to all relevant pivot tables with Report Connections.

Step 5 — Formatting. Conditional formatting for KPI cards. Colour-blind safe palette. Axis labels in plain words. No 3-D, no gradients.

Step 6 — Refresh process. Data on one sheet, one Refresh All button, and a note on the sheet explaining where the data comes from.

Order of impact, from largest to smallest:

1. Replace whole-column references. A:A in a formula forces Excel to check a million rows. Use A2:A50000 instead.
2. Remove volatile functions. NOW, TODAY, RAND, OFFSET and INDIRECT recalculate on every change. Replace with static values or a single cell that you update manually.
3. Convert formulas to values. Once a lookup or calculation is stable, paste as values and stop the recalc.
4. Reduce array formulas. A single large array formula over 50,000 rows can be slower than a smaller SUMIFS.
5. Use the Data Model / Power Pivot. Pivot tables on a million rows are faster from the Data Model than from the worksheet.
6. Switch to manual calculation. Formulas → Calculation Options → Manual while you work, then calculate once at the end.
7. Clean up Conditional Formatting rules. Hundreds of overlapping rules slow the sheet down. Consolidate and use Manage Rules.

Use Power Pivot when the analysis needs multiple tables joined, large data, or DAX measures like year-over-year and running totals.

Build it as if a stranger will maintain it in six months:

• One sheet per role — README, Data, Calc, Report. The README sheet explains the purpose, the refresh process and the source of every input.
• Named ranges for key inputs and lookup tables. No magic numbers inside formulas.
• No hidden sheets, no hard-coded values inside formulas. Everything comes from a documented cell.
• Comments on complex formulas — a short note explaining what the formula does and why.
• Data validation and conditional formatting that flag bad input, not silent failures.
• Protection on the calculation and report sheets, with a note explaining how to unlock.
• Version and date in a fixed cell — "v3.2 · 14 October 2026" — so the team knows what they are looking at.

Three tools on the Data tab → What-If Analysis:

1. Scenario Manager — name and store several sets of input values — best case, base case, worst case — and switch between them. Each scenario shows the result of the changing cells.
2. Goal Seek — work backwards from a target. "What price do we need to hit ₹50 lakh revenue, given this volume?" Goal Seek adjusts one input cell until the output cell reaches the target.
3. Data Tables — show a grid of results for two changing inputs. A one-variable table varies one input; a two-variable table varies two — like price and volume — and shows profit for every combination.

Data Tables recalculate on every change, so keep them on a separate sheet with manual calculation for large models.

Three methods, chosen based on how often the data changes:

1. Direct reference — =Sheet1!A1 on the summary sheet. Fast for two or three static sheets, fragile when sheet names change.
2. 3-D reference — =SUM(Jan:Dec!B2) adds the same cell across every sheet between Jan and Dec. Good for identical monthly sheets, breaks when you insert a sheet out of order.
3. Power Query — Append Queries combines multiple tables into one, and refreshes automatically when the source sheets change. The right answer for a recurring monthly report.

In an interview, mention Power Query — it signals you think about maintainability, not just today's task.

A standard checklist:

• Row count — does the number of rows match the source?
• Duplicate IDs — COUNTIF on the key column for values greater than 1.
• Missing values — COUNTBLANK on key columns.
• Data types — numbers stored as text, dates stored as text. ISTEXT, ISNUMBER to catch them.
• Range checks — negative quantities, dates in the future, amounts above a threshold.
• Referential integrity — every product ID in the sales table exists in the product master.
• Format consistency — same date format, same case, no trailing spaces. TRIM and PROPER.
• Reconciliation — sum of sales matches the source system total.

Document the checks in a small sheet at the top of the workbook. It shows your work and saves the next person hours.

Standard MATCH returns the first match. To return the Nth match, wrap MATCH inside SMALL with an IF array, or use FILTER.

=INDEX(Products!C:C,
       SMALL(IF(Products!A:A=A2, ROW(Products!A:A)), 2))

In Microsoft 365, use FILTER instead:

=FILTER(Products!C:C, Products!A:A = A2)

FILTER returns every match as a spill range. Wrap in INDEX if you need only the second result: =INDEX(FILTER(...), 2).

A KPI card is a small block on the dashboard that shows one number, its target, and a status indicator — green, amber or red.

How to build one:

1. Create the KPI measure in a helper cell using SUMIFS or a lookup on the pivot table.
2. Add a target cell next to it.
3. Add a variance cell — =Actual - Target and =DIVIDE(Actual - Target, Target) for the percentage.
4. Apply conditional formatting to colour the variance cell: green if positive, red if negative.
5. Use a large font for the actual number, a smaller font for the target, and a sparkline or trend arrow for context.

Line up four or five cards across the top of the dashboard — that is the first thing a business user looks at.

Use Scenario Manager for board presentations with three clearly named cases. Use a Data Table when you want to see how profit behaves across a whole range of prices and volumes.

Step 1 — Make a copy of the raw file. Never clean the source in place.

Step 2 — Check row count, then look at the types: is amount stored as text? Are dates in two different formats?

Step 3 — Load the file into Power Query and do the cleaning as steps — change types, TRIM text, PROPER names, replace values.

Step 4 — Remove duplicates on the order ID, and remove blank rows.

Step 5 — Add a DateKey column for month-level grouping in the pivot.

Step 6 — Load the cleaned table to the Data Model, and build the pivot table from there.

Next month, drop the new file into the same folder and click Refresh — the whole pipeline reruns.

Step 1 — Check for trailing or leading spaces. Use =LEN(A2) on both the lookup value and the matching value in the table — a difference of 1 means a space.

Step 2 — Check data types. A number stored as text looks identical to a number but never matches. Use ISNUMBER on both.

Step 3 — Check case. VLOOKUP is case-insensitive, so this is rarely the cause, but INDEX-MATCH with a case-sensitive lookup is a different story.

Step 4 — Check the lookup column is truly the first column of the range. A mistake here returns #N/A even if the value exists.

Step 5 — Wrap the result in TRIM and VALUE — =VLOOKUP(TRIM(A2), ...) — to normalize the input before lookup.

Step 1 — Ensure the date column is a real date, not text.

Step 2 — Add a helper column for the month: =TEXT(A2, "YYYY-MM").

Step 3 — Build a pivot table with Region in Rows, Month in Columns, and Sum of Sales in Values.

Step 4 — Add a "Previous month" column and a growth column:

// Previous month value
=B2

// Month-over-month growth %
=IFERROR((B2 - A2) / A2, "")

Step 5 — Apply conditional formatting — green for positive growth, red for negative.

Step 6 — For a fully dynamic report, replace the manual columns with a DAX measure inside a Power Pivot table.

Step 1 — Align both files on a common key. If the transaction ID is present on both, use that. Otherwise, combine date + amount as a composite key.

Step 2 — Create a helper column on each sheet that concatenates the key: =A2&"|"&B2&"|"&C2.

Step 3 — Use COUNTIF to flag rows that appear in one file but not the other.

// On ledger, check the bank file
=COUNTIF(BankKeys, LedgerKey) > 0

// On bank, check the ledger file
=COUNTIF(LedgerKeys, BankKey) > 0

Step 4 — Filter both sheets for FALSE rows — those are the unmatched transactions.

Step 5 — For the matched rows, check the amounts. A small difference is likely a bank charge or rounding.

Step 6 — Document the reconciliation: matched count, unmatched count and the difference in totals. Share the summary, not the 8,000 rows.

Step 1 — Open with manual calculation (Formulas → Calculation Options → Manual). If it opens fast, formulas are the problem.

Step 2 — Find volatile functions. Use Ctrl+F on OFFSET, INDIRECT, NOW, TODAY, RAND. Each is a candidate for replacement.

Step 3 — Find whole-column references. Use Ctrl+F on ":" to spot something like A:A. Replace with bounded ranges.

Step 4 — Check for large array formulas. One SUM(IF()) over 100,000 rows is expensive — convert it to SUMIFS or a pivot table.

Step 5 — Clean Conditional Formatting. Home → Conditional Formatting → Manage Rules on each sheet. Remove rules that no longer apply.

Step 6 — Move large data to the Data Model or Power Query. Pivot tables on the Data Model are much faster than on the sheet.

Step 1 — Build a pivot table with Region and Product in Rows, and Sum of Revenue in Values.

Step 2 — Sort the revenue column in descending order.

Step 3 — Apply a Top 10 filter to the Product row label:

• Right-click a Product cell → Filter → Top 10 Items.
• Set 10, and Top by "Sum of Revenue".

Step 4 — The filter applies per region, so each region shows its own top 10 products.

Step 5 — Add a Region slicer so the user can isolate one region at a time.

Step 6 — For a chart, right-click the pivot → PivotChart → Bar chart, sorted descending.

Step 1 — Put all 12 files in one folder. Naming matters — Jan 2026.xlsx, Feb 2026.xlsx and so on.

Step 2 — Data → Get Data → From File → From Folder. Power Query reads all the files in the folder.

Step 3 — Combine & Transform. Power Query uses the first file as a template and applies the same cleaning steps to every file.

Step 4 — Add a "Source File" column so you can tell which row came from which month.

Step 5 — Load the combined table to the Data Model and build one pivot table across all months.

Step 6 — Next month, drop the new file into the folder and click Refresh. The pipeline reruns automatically.

Step 1 — Choose a clustered bar chart. Two bars per region — Actual and Target — side by side. This is the clearest visual for variance.

Step 2 — Prepare the data: Region, Actual, Target. Sort descending by Actual so the highest performers are at the top.

Step 3 — Insert a clustered bar chart. Reverse the category axis so the top region appears at the top.

Step 4 — Format the Target bar as a lighter shade or an outline. The Actual bar is the primary series.

Step 5 — Add data labels with a thousand separator.

Step 6 — Optional: add a second chart below that shows the variance % as a colour- coded bar — green above target, red below.

Step 1 — Click the cell and read the formula in the formula bar. #VALUE! means a wrong data type is being used somewhere in the calculation.

Step 2 — Use Evaluate Formula on the Formulas tab. It steps through the formula one part at a time and shows which part produces the error.

Step 3 — Check the inputs. ISNUMBER, ISTEXT and ISBLANK on each referenced cell.

Step 4 — Common culprits: a number stored as text, a date stored as text, an empty cell used in a math operation.

Step 5 — Fix the input, or wrap in a helper — =VALUE(TRIM(A2)) to convert text to number safely.

Step 6 — Add IFERROR only after you understand what caused the error. Never hide an error you have not diagnosed.

Step 1 — Provide a realistic messy dataset — 500 to 1,000 rows, with a few duplicates, some text-formatted numbers and a missing value or two.

Step 2 — Ask four or five tasks of increasing difficulty:

• Clean the data — TRIM, PROPER, remove duplicates.
• Build a pivot table — sales by region and category.
• Write a VLOOKUP or INDEX-MATCH to bring in a product name.
• Add a SUMIFS measure with two criteria.
• Create a simple chart or dashboard for the result.

Step 3 — Time-box the test to 45–60 minutes.

Step 4 — Score for correctness, method and explanation — not for cosmetic formatting. A candidate who explains their choices scores higher than one who only gets the right number.

Step 5 — Share the expected output with the interviewer so scoring is consistent across candidates.

=E2 * F2

Expected result: H2 = 30,000; H3 = 22,000; H4 = 15,000; H5 = 66,000; H6 = 60,000; H7 = 44,000; H8 = 15,000; H9 = 44,000; H10 = 45,000; H11 = 22,000. Total = 363,000.

=SUMIF(C2:C11, "North", H2:H11)

Expected result: 30,000 + 15,000 + 44,000 + 45,000 = 134,000.

=SUMIFS(H2:H11, C2:C11, "North", D2:D11, "Electronics")

Expected result: ORD-001 (30,000) + ORD-003 (15,000) + ORD-009 (45,000) = 90,000.

=COUNTIF(C2:C11, "East")

Expected result: 3 — ORD-004, ORD-007, ORD-010.

=VLOOKUP(J1, A2:G11, 7, FALSE)

Expected result: Beta Corp — because Customer is the 7th column in the range A:G.

=INDEX(G2:G11, MATCH(J1, A2:A11, 0))

Expected result: Beta Corp. This version does not break if a column is inserted between A and G.

=AVERAGE(H2:H11)

Expected result: 363,000 ÷ 10 = 36,300.

=IF(H2 > 40000, "High", "Normal")

Expected result: ORD-005 (60,000), ORD-006 (44,000), ORD-009 (45,000) and ORD-010 (22,000) is Normal. Row 5, 7 and 10 = High; the rest = Normal.

=PROPER(TRIM(A1))

Expected result: Acme Ltd — leading, trailing and extra spaces removed, and the first letter of each word capitalised.

Steps: Select A1:H11 → Insert → PivotTable → New worksheet. Drag Region to Rows, Category to Columns, Total Sales to Values.

Grand total 363,000 matches the Total Sales column sum, which confirms the pivot is correct.