Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Advanced Excel Interview Questions & Answers

All 200 unique questions in one place — 60 easy, 95 medium, 45 hard — grouped by topic.

Practise by topic

Excel Fundamentals and Formatting · 25Logical, Text and Date Functions · 25Lookup and Dynamic Array Functions · 25Pivot Tables and Data Summaries · 25Charts and Dashboards · 25Power Query and Data Cleaning · 25Power Pivot, Data Model and DAX · 25Macros and VBA Automation · 25

Practise the way your interview works

Company-wise Advanced Excel questionsExperience-wise Advanced Excel questionsRole-wise Advanced Excel questionsCity-wise Advanced Excel questionsMost asked Advanced Excel questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsAdvanced Excel mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsAdvanced Excel topic sheets
Solved overallStart 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 is the difference between relative, absolute and mixed cell references?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A relative reference like A1 shifts when a formula is copied, an absolute reference like $A$1 never shifts, and a mixed reference like $A1 or A$1 locks only the column or only the row. In a multiplication grid, =$A2*B$1 copied across and down keeps the header column and header row fixed. Press F4 while editing a reference to cycle through the four forms.
02

What is the difference between a workbook, a worksheet and a range?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A workbook is the whole file (.xlsx, .xlsm or .xlsb). A worksheet is one tab inside it with 1,048,576 rows and 16,384 columns. A range is any group of cells such as A2:D100 that a formula, chart or format refers to. Good practice is to keep raw data, calculations and output on separate worksheets of the same workbook so each layer is easy to audit.
03

What does converting a range into an Excel Table (Ctrl+T) give you?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A Table expands automatically when new rows or columns are added, so formulas, charts and pivot tables based on it pick up new data without editing ranges. It also gives banded rows, header filters, a Total Row, calculated columns that fill down by themselves, and structured references like Sales[Amount]. For any data that grows every month, a Table is the safest starting point.
04

What are structured references? Give an example.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Structured references use Table and column names instead of cell addresses, which makes formulas readable and self-adjusting. For a Table named Sales: =SUM(Sales[Amount]) totals the whole column =[@Qty]*[@Price] multiplies values in the same row =COUNTIFS(Sales[Region],"North") If rows are added or a column moves, the formula still points to the right data.
05

What is conditional formatting? Give two business uses.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Conditional formatting changes a cell's colour, font, icon or data bar based on a rule, so patterns stand out without reading every number. Common uses are highlighting invoices overdue by more than 30 days in red and adding data bars to a sales column so top performers are visible at a glance. Keep rules few and consistent, because many overlapping rules slow down large files.
06

What is Data Validation and how do you create a dropdown list?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Data Validation restricts what users can type in a cell, which prevents bad input in forms and trackers. For a dropdown, select the cells, go to Data > Data Validation, choose Allow: List and set Source to a range such as =$K$2:$K$10 or a typed list like Open,Closed,On Hold. Pointing the source at a Table column keeps the list updated automatically when new options are added.
07

Tell me about a time you had to clean up a messy spreadsheet someone else built.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
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.
08

Explain the useful options inside Paste Special.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Paste Special (Ctrl+Alt+V) lets you paste only part of what was copied. Values removes formulas, Formats copies only styling, Transpose flips rows into columns, and Skip Blanks avoids overwriting with empty cells. The Operation options can multiply or divide a whole range by a copied number, for example converting rupees to lakhs by copying 100000 and choosing Divide.
09

SUM returns 0 on a column of numbers exported from software. How do you fix it?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
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.
10

You change input values but the totals do not update until you press something. What is happening and how do you fix it?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
The workbook is probably in Manual calculation mode, often inherited from another file opened first in the same session. Press F9 to recalculate now, then set Formulas > Calculation Options > Automatic. If only some cells stay stale, check whether they are formatted as Text or whether Show Formulas is on. For very heavy files, manual mode can be kept deliberately, but tell users to press F9 before reading results.
11

What are named ranges and why are they useful?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A named range gives a meaningful name, such as GST_Rate or Target_FY26, to a cell, range or constant. Formulas like =Amount*GST_Rate are easier to read and audit than =B2*$Z$1. Names are managed in Formulas > Name Manager and can be scoped to the workbook or a single sheet. They also work as navigation targets in the Name Box and as Data Validation sources.
12

Which Excel keyboard shortcuts do you use daily?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Ctrl+Arrow jumps to the edge of data and Ctrl+Shift+Arrow selects to it. Ctrl+T creates a Table, Ctrl+Shift+L toggles filters, Ctrl+1 opens Format Cells, Alt+= inserts AutoSum, F4 toggles absolute references or repeats the last action, F2 edits a cell, Ctrl+; inserts today's date, Ctrl+Alt+V opens Paste Special and Ctrl+E runs Flash Fill. Mentioning a few you genuinely use is better than reciting a long list.
13

What is the difference between Flash Fill and Text to Columns?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Text to Columns splits a column by a fixed delimiter or fixed width in one go, which suits clean data like comma-separated codes. Flash Fill (Ctrl+E) learns a pattern from one or two examples you type, such as extracting first names or formatting phone numbers. Both produce static values, so if the source changes you must redo them, unlike formulas or Power Query steps.
14

Tell me about a time you used Excel to save time for your team.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Situation: in my internship, the team spent two hours every morning copying sales figures from three exports into one summary. Task: I was asked to make it faster. Action: I converted the exports to Tables, used XLOOKUP and SUMIFS to build a summary sheet, and added data validation for the date. Result: the report took ten minutes, errors from manual copying stopped, and the format was reused by another team.
15

An imported report has merged cells, blank spacer rows and subtotal lines, so sorting and filtering keep failing. How do you make it usable?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
Work on a copy. Unmerge all cells, then fill the blanks with the value above using Go To Special > Blanks and =cell above, followed by paste as values. Filter out blank rows and subtotal lines and delete them, keep one header row and convert the range to a Table. If the same report arrives every month, repeat these steps once in Power Query so the cleanup becomes a single refresh.
16

How do you highlight an entire row when the Status column says Overdue?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
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

How do you build a dependent dropdown, where the City list changes based on the selected State?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
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#.
18

A column shows amounts rounded to whole rupees, but its total differs from a manual total of the displayed values by a few rupees. Why?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Number formatting only changes how a value looks; Excel still adds the full decimals stored in each cell. So the sum of hidden paise differs from the sum of the rounded figures shown. If the report must add up exactly as displayed, round the values with =ROUND(value,0) in the source formulas rather than using the Precision as displayed option, which permanently changes stored data for the whole workbook.
19

Write a custom number format that shows positives in green, negatives in red brackets and zero as a dash.

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Custom formats have up to four sections separated by semicolons: positive; negative; zero; text. Press Ctrl+1 > Number > Custom and enter: [Green]#,##0;[Red](#,##0);"-";@ For Indian lakh-crore grouping you can use: [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0 The value stays unchanged; only the display changes.
20

What is the difference between Protect Sheet, Protect Workbook and Encrypt with Password?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
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.
21

You need to find and remove duplicate customer records but keep the original data safe. How do you do it?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
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.
22

Describe a time you found an error in a report just before it was sent.

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
Situation: a monthly MIS was due to leadership in an hour. Task: I was doing a final review. Action: I noticed the region totals did not match the grand total, traced it to a SUMIFS range that stopped at row 500 while data had grown to 620 rows, converted the source to a Table and re-checked totals against the raw export. Result: the report went out on time with correct numbers, and I added a reconciliation check cell to the template.
23

A shared Excel file has grown to 40 MB and takes ages to recalculate. What do you check?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
Start with Ctrl+End to see if the used range is far beyond the real data and delete the extra rows and formats. Then look for volatile functions like OFFSET, INDIRECT and TODAY, whole-column references inside SUMPRODUCT or array formulas, and duplicated conditional formatting rules. Replace repeated lookups with one helper column, move heavy transforms to Power Query, and save as .xlsb if size still matters.
24

What are volatile functions and why should you limit them?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
Volatile functions recalculate every time anything in the workbook changes, not only when their inputs change. Examples are NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, CELL and INFO. One TODAY cell is harmless, but thousands of OFFSET or INDIRECT formulas force the whole dependency chain to recalculate on every keystroke. Replace OFFSET with INDEX-based ranges or Tables, and put TODAY in a single cell that others reference.
25

Fifteen people edit the same tracker and you keep getting conflicting versions over email. How would you fix the process?

Excel Fundamentals and FormattingHard
Save to ▾
Show answer
Move the file to OneDrive or SharePoint so everyone co-authors one live copy, and stop emailing attachments. Restructure it into a single input Table with Data Validation, lock formula columns with sheet protection, and give each person a filtered view or their own sheet view. Use version history for recovery. If edits keep growing, move the data to a SharePoint list or database and keep Excel for reporting.
26

What is the difference between nested IF and IFS? Show an example.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Nested IF places one IF inside another, which becomes hard to read after three levels. IFS (Excel 2019 and later) tests conditions in order and returns the value for the first true one: =IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Fail") The final TRUE acts as the else part. For many bands, a lookup table with XLOOKUP approximate match is easier to maintain than either.
27

HR wants a column that marks employees Eligible for bonus if their rating is 4 or more and tenure is over 1 year, or if they appear in a Top Performers list. How do you write it?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Combine AND inside OR, and use COUNTIF to check the list: =IF(OR(AND(C2>=4, D2>1), COUNTIF(TopPerformers, A2)>0), "Eligible", "Not Eligible") Here C is rating, D is tenure in years, A is employee ID and TopPerformers is a named range. Matching on ID instead of name avoids errors from spelling differences.
28

What is the difference between SUMIF and SUMIFS?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
SUMIF handles one condition and puts the sum range last; SUMIFS handles many conditions and puts the sum range first: =SUMIF(A:A,"North",C:C) =SUMIFS(C:C,A:A,"North",B:B,"Laptop",D:D,">="&DATE(2026,4,1)) Because of the different order, many people use SUMIFS everywhere, even for a single condition, to stay consistent. COUNTIFS and AVERAGEIFS follow the same pattern.
29

What is the difference between IFERROR and IFNA?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
IFERROR replaces any error, such as #N/A, #DIV/0!, #VALUE! or #REF!, with a value you choose. IFNA replaces only #N/A, which is the normal result when a lookup finds no match. IFNA is safer around lookups because a genuine problem like a deleted column (#REF!) still shows up instead of being hidden as a blank or zero. Use IFERROR only where every error truly means the same thing.
30

Extract the first and last name from a full name like Rahul Kumar Sharma.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Classic formulas: First: =LEFT(A2,FIND(" ",A2)-1) Last: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) In Microsoft 365 it is simpler: First: =TEXTBEFORE(A2," ") Last: =TEXTAFTER(A2," ",-1) The -1 tells TEXTAFTER to use the last space, which handles middle names. Run TRIM on the source first to remove double spaces.
31

Explain TRIM, CLEAN, PROPER, UPPER, LOWER and SUBSTITUTE.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
TRIM removes leading, trailing and repeated spaces between words. CLEAN removes non-printable characters such as line breaks from system exports. PROPER, UPPER and LOWER change case, for example PROPER turns RAHUL SHARMA into Rahul Sharma. SUBSTITUTE replaces specific text, like =SUBSTITUTE(A2,"-","") to strip hyphens from a phone number. They are often nested together when cleaning customer master data.
32

Dates in an exported column are left-aligned and formulas like DATEDIF return #VALUE!. How do you fix them?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Left alignment usually means the dates are stored as text. Select the column, use Data > Text to Columns, choose Date with the correct order such as DMY and finish; Excel converts them to real dates. For mixed or messy text, DATEVALUE or DATE with LEFT, MID and RIGHT works. If the file comes regularly, set the column type in Power Query using Using Locale so day and month are never swapped.
33

What do EOMONTH and EDATE do? Give examples.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
EDATE moves a date by whole months, keeping the day where possible; EOMONTH returns the last day of a month that many months away. =EDATE(A2,3) date three months later, useful for EMI or renewal dates =EOMONTH(A2,0) last day of the same month =EOMONTH(A2,-1)+1 first day of the same month These are standard in billing cycles, month-end closing and ageing reports.
34

Compare CONCAT, TEXTJOIN and the & operator.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
The & operator joins items one by one, CONCAT joins a list or range, and TEXTJOIN adds a delimiter and can skip blanks: =A2&" "&B2 =CONCAT(A2:C2) =TEXTJOIN(", ",TRUE,A2:E2) TEXTJOIN is the best choice for building address lines or a comma-separated list of selected items, because the TRUE argument avoids double commas where cells are empty.
35

How do wildcards work in COUNTIF and SUMIF?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
An asterisk matches any number of characters, a question mark matches exactly one, and a tilde escapes a real * or ?. =COUNTIF(A:A,"*Pvt Ltd*") names containing Pvt Ltd =SUMIFS(C:C,B:B,"INV-2026-????") invoice codes of a fixed length =COUNTIF(A:A,"*~?*") cells that contain a question mark Wildcards work only on text criteria, not on numbers.
36

Explain the common Excel errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME? and #SPILL!.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
#N/A means a lookup found no match. #VALUE! means a wrong data type, such as adding text. #REF! means a referenced cell or column was deleted. #DIV/0! is division by zero or a blank. #NAME? means a misspelt function or undefined name. #SPILL! means a dynamic array formula cannot spill because cells in its output area are not empty. Knowing the cause helps you fix the source instead of hiding errors.
37

Count orders from the North region with amount above 50,000 placed in January 2026.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Use COUNTIFS with one pair of range and criteria per condition, with dates built using DATE so the formula does not depend on regional settings: =COUNTIFS(Orders[Region],"North", Orders[Amount],">50000", Orders[Date],">="&DATE(2026,1,1), Orders[Date],"<"&DATE(2026,2,1)) Using less than 1 February is safer than less than or equal to 31 January when dates contain time values.
38

Why does a date show as a number like 46023 when joined with text, and how do you fix it?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Excel stores dates as serial numbers, so joining with & uses the raw number instead of the formatted view. Wrap the date in TEXT with a format code: ="Report date: "&TEXT(A2,"dd-mmm-yyyy") ="Sales: Rs "&TEXT(B2,"#,##0") TEXT returns text, so use it only for labels and titles, never for values that need further calculation.
39

Calculate working days between two dates excluding Sundays and a holiday list.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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.
40

Calculate an employee's age or tenure in years and months from the joining date.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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!.
41

What does SUMPRODUCT do and when would you use it?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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.
42

Describe a time a formula mistake of yours reached a stakeholder. What did you do?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Situation: a regional sales summary I sent showed one zone 18% higher than actual. Task: correct it before decisions were taken. Action: I traced it to a SUMIFS range that stopped one row short, sent a corrected file within the hour with a short note explaining the cause, and added a check cell comparing the summary total to the raw data total. Result: the manager appreciated the quick ownership and the check now catches such gaps automatically.
43

Commission is 2% up to 1 lakh, 5% up to 5 lakh and 8% above 5 lakh, applied as a flat rate on total sales. Write the formula.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
With IFS: =B2*IFS(B2>500000,8%,B2>100000,5%,TRUE,2%) A cleaner and more maintainable option is a slab table (Lower: 0, 100001, 500001; Rate: 2%, 5%, 8%) with approximate match: =B2*XLOOKUP(B2,Slab[Lower],Slab[Rate],,-1) The -1 match mode returns the next smaller value, so changing rates only means editing the table.
44

An IF formula says 100 is not equal to 100. Why might that happen?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Usually one value is text and the other is a number, so =A2=B2 returns FALSE even though both show 100. Other causes are hidden spaces, or floating-point results like 99.9999999 that display as 100 because of formatting. Check with ISTEXT, LEN and by widening decimals. Fix with VALUE or TRIM on the text side, or compare with ROUND(A2,2)=ROUND(B2,2) for calculated numbers.
45

Return the Indian financial year (April to March) for a date, like FY 2026-27.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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).
46

How do you count unique values in a column?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
In Microsoft 365: =COUNTA(UNIQUE(A2:A500)) =ROWS(UNIQUE(FILTER(A2:A500,A2:A500<>""))) ignores blanks In older versions: =SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&"")) The older formula slows down on large ranges, so for big data use a pivot table with Distinct Count via the Data Model.
47

Tell me about a time you had to explain a complex Excel report to a non-technical manager.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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.
48

What is the LET function and why is it useful?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
LET assigns names to intermediate results inside one formula, so a value is calculated once and the formula reads like steps: =LET(sales,SUMIFS(Data[Amt],Data[Rep],A2), target,XLOOKUP(A2,Targets[Rep],Targets[Target]), IF(target=0,"",sales/target)) Without LET, the SUMIFS would be repeated in each branch. It improves speed on large sheets and makes complex formulas easier to review.
49

What is LAMBDA and how do you create a custom function without VBA?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
LAMBDA lets you define a reusable function from a formula. Create it in Name Manager, for example name GSTInclusive with: =LAMBDA(amount,rate,ROUND(amount*(1+rate),2)) Then use =GSTInclusive(B2,18%) anywhere in the workbook. Helper functions like MAP, BYROW, REDUCE and SCAN apply a LAMBDA across arrays. Unlike VBA functions, LAMBDAs need no macro-enabled file, but they require Microsoft 365.
50

Your monthly report formulas break every time a column is added to the export. How do you make them robust?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
Avoid hard-coded column numbers and fixed ranges. Load the export into a Table or through Power Query, so columns are referenced by name. Replace VLOOKUP with column index 7 by XLOOKUP or INDEX/MATCH, keep constants like rates in labelled assumption cells, and use LET for readability. Add a check cell that compares the report total to the raw total, so any break is caught immediately.
51

Explain VLOOKUP syntax and the difference between exact and approximate match.

Lookup and Dynamic Array FunctionsEasy
Save to ▾
Show answer
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.
52

What are the main limitations of VLOOKUP?

Lookup and Dynamic Array FunctionsEasy
Save to ▾
Show answer
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.
53

What is XLOOKUP and what advantages does it have?

Lookup and Dynamic Array FunctionsEasy
Save to ▾
Show answer
XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode) is available in Microsoft 365 and Excel 2021 or later: =XLOOKUP(A2,Emp[ID],Emp[Name],"Not found") It defaults to exact match, looks left or right, has a built-in not-found value, can return several columns at once, and can search from the bottom up. It replaces VLOOKUP, HLOOKUP and most INDEX/MATCH uses.
54

You need serial numbers in a list that stay continuous after rows are filtered or deleted. How do you do it?

Lookup and Dynamic Array FunctionsEasy
Save to ▾
Show answer
Plain typed numbers or =ROW()-1 break when rows are hidden by a filter. Use SUBTOTAL, which ignores filtered-out rows: =SUBTOTAL(103, $B$2:B2) Function 103 is COUNTA that skips hidden rows, and the expanding range counts non-blank cells up to the current row. Now the numbering restarts from 1 for whatever is visible, which is useful in printed or shared filtered views.
55

How does INDEX/MATCH work and why is it often preferred over VLOOKUP?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
MATCH finds the position of a value in a single row or column, and INDEX returns the item at that position from another range: =INDEX(Emp!$C:$C,MATCH(A2,Emp!$F:$F,0)) The return column can be to the left of the lookup column, inserting columns does not break it, and it only scans the two columns used. It works in every Excel version, which matters when files go to clients on older Excel.
56

Explain the match_mode and search_mode arguments of XLOOKUP.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. search_mode: 1 first to last (default), -1 last to first, 2 binary search ascending, -2 binary search descending. =XLOOKUP("*Infra*",Cust[Name],Cust[ID],,2) wildcard match =XLOOKUP(A2,Log[Item],Log[Price],,0,-1) last occurrence Binary modes are fast but give wrong answers if the data is not sorted.
57

How do you do a two-way lookup that picks both a row and a column?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Use two MATCH functions inside INDEX, one for the row and one for the column: =INDEX($B$2:$M$50,MATCH($P$2,$A$2:$A$50,0),MATCH($Q$2,$B$1:$M$1,0)) Here P2 holds the product and Q2 the month. With XLOOKUP you can nest one inside another: =XLOOKUP(P2,A2:A50,XLOOKUP(Q2,B1:M1,B2:M50)) The inner XLOOKUP returns the whole month column, and the outer one picks the product row.
58

VLOOKUP returns #N/A even though you can see the value in the table. What do you check?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Check that the last argument is FALSE, then compare the two values directly with =A2=Sheet2!A5. Common causes are trailing or non-breaking spaces, one side being a number and the other text (like employee codes), and the lookup value sitting outside the first column of the range. Use LEN to spot hidden characters, and fix with TRIM, CLEAN, VALUE or by making both columns the same data type.
59

Tell me about a time a lookup gave wrong results that nobody noticed at first.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Situation: a monthly incentive sheet used VLOOKUP with the last argument left blank, so it ran an approximate match. Task: I was asked to verify payouts before release. Action: I spotted employees getting a neighbour's target, changed every lookup to exact match with XLOOKUP, and added a count of missing IDs. Result: twelve wrong payouts were corrected before payment, and exact match became our team's standard rule.
60

How do you use approximate match to apply discount or tax slabs?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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.
61

What does INDIRECT do and what are its risks?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
INDIRECT converts text into a reference, so =SUM(INDIRECT(B1&"!C2:C100")) sums the sheet whose name is typed in B1. It is useful for summary sheets that switch between months and for dependent dropdowns. The risks are that it is volatile, it breaks silently when sheets are renamed, it does not work with closed external workbooks, and formula auditing tools cannot trace it. Use it sparingly.
62

Explain the FILTER function with an example.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
FILTER returns all rows that meet a condition and spills them into neighbouring cells: =FILTER(Sales,Sales[Region]="South","No data") =FILTER(Sales,(Sales[Region]=G1)*(Sales[Amount]>100000)) Multiplying conditions means AND; adding them with + means OR. Because the output updates live, FILTER is useful for searchable lists and drill-down sheets without needing pivot tables or macros. It requires Microsoft 365 or Excel 2021.
63

Build a live leaderboard of the top 5 sales reps by total sales using dynamic array functions.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Get unique reps, total their sales, sort descending and take the top five: =LET(r,UNIQUE(Sales[Rep]), t,SUMIFS(Sales[Amount],Sales[Rep],r), TAKE(SORTBY(HSTACK(r,t),t,-1),5)) HSTACK puts names and totals side by side, SORTBY orders by totals, and TAKE keeps five rows. Without TAKE, use INDEX(...,SEQUENCE(5),{1,2}).
64

What are dynamic arrays and what causes a #SPILL! error?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
In Microsoft 365, a formula can return many values that spill into nearby cells, as with UNIQUE, FILTER, SORT and SEQUENCE. Other formulas can refer to the whole result with the spill operator, for example =COUNTA(E2#). #SPILL! appears when the spill area is blocked by data, the formula sits inside a Table, the area overlaps merged cells, or the result would run past the sheet edge. Clear the blocking cells to fix it.
65

Your team wants lookups to show Not Found instead of #N/A for missing codes, without hiding genuine formula errors. How do you do it?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Use a function that only handles missing values, not every error: =XLOOKUP(A2, Codes[Code], Codes[Price], "Not Found") or =IFNA(VLOOKUP(A2, Codes, 2, FALSE), "Not Found"). Avoid IFERROR here, because it also hides #REF! or #VALUE! caused by broken ranges. Add =COUNTIF(D:D, "Not Found") at the top so missing codes are counted and followed up.
66

You need to reconcile a bank statement with the ledger and list entries missing from either side. How?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Create a common key, for example date plus amount plus cheque or UTR number. On each sheet add =IF(COUNTIFS(Other[Key],[@Key])=0,"Missing","Matched") and filter the Missing rows. Duplicated amounts need COUNTIFS on both sides so counts are compared, not just presence. For repeated monthly work, load both into Power Query and use Left Anti and Right Anti merges, which list unmatched rows directly.
67

How do you return all matching values for a lookup, not only the first?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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],""))
68

A sales file has employee names but the master list has employee IDs, and some names repeat. How do you join them safely?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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.
69

Get the latest price of each product from a transaction log sorted by date.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
If the log is sorted oldest to newest, search from the bottom: =XLOOKUP(A2,Log[Product],Log[Price],,0,-1) If it is not sorted, find the latest date first and then match on both: =LET(d,MAXIFS(Log[Date],Log[Product],A2), XLOOKUP(1,(Log[Product]=A2)*(Log[Date]=d),Log[Price])) The second version is order-independent and safer for appended data.
70

Tell me about a time data from two teams did not match. How did you resolve it?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
Situation: finance and sales reported different revenue for the same quarter. Task: find the gap before the review meeting. Action: I pulled both datasets, built a key on invoice number, and used lookups to list invoices present in only one file. Most of the gap came from credit notes that sales did not deduct and invoices booked on the last day. Result: both teams agreed a single definition and I added a reconciliation tab to the monthly pack.
71

Your manager asks you to replace all VLOOKUPs in a legacy workbook with XLOOKUP. How do you approach it?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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.
72

How do you look up a value using multiple criteria, such as Employee ID and Month?

Lookup and Dynamic Array FunctionsHard
Save to ▾
Show answer
In Microsoft 365, multiply logical tests so only the row matching all conditions becomes 1: =XLOOKUP(1,(Pay[EmpID]=A2)*(Pay[Month]=B2),Pay[Salary],"Not found") In older Excel use INDEX/MATCH entered as an array formula (Ctrl+Shift+Enter): =INDEX(Pay[Salary],MATCH(1,(Pay[EmpID]=A2)*(Pay[Month]=B2),0)) For very large data a helper column joining ID and Month is faster.
73

Why is INDEX preferred over OFFSET for building dynamic ranges?

Lookup and Dynamic Array FunctionsHard
Save to ▾
Show answer
OFFSET is volatile, so it recalculates whenever anything changes, which slows big models. INDEX returns a reference too but is not volatile: OFFSET: =SUM(OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)) INDEX: =SUM($B$2:INDEX($B:$B,COUNTA($B:$B))) Both sum a growing column, but the INDEX version only recalculates when column B changes. Converting the data to a Table is simpler still.
74

Lookups across 5 lakh rows make your workbook extremely slow. How do you speed it up?

Lookup and Dynamic Array FunctionsHard
Save to ▾
Show answer
Avoid repeating the same search: run one MATCH in a helper column and use several INDEX formulas on that position. Sort the lookup table and use XLOOKUP binary search (search_mode 2), which is much faster than a linear scan. Replace full-column references with Tables, set calculation to manual while editing, and for regular jobs move the join into Power Query or the Data Model with relationships.
75

When does binary search in XLOOKUP or approximate VLOOKUP give wrong results?

Lookup and Dynamic Array FunctionsHard
Save to ▾
Show answer
Binary search halves the range repeatedly, assuming the data is sorted. If the lookup column is unsorted, has mixed text and numbers or contains blanks, it can stop at the wrong row and return a plausible but incorrect value with no error. Use it only on data you sort in a controlled step, such as a Power Query output, and test a few values against an exact-match lookup.
76

What is a pivot table and when should you use one?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
A pivot table summarises large tabular data by dragging fields into rows, columns, values and filters, without writing formulas. Use it when you need totals, counts or averages broken down by categories such as region, product and month, and when the breakdown changes often. It works best on clean data with one header row, no blank rows and one record per row, ideally stored in an Excel Table.
77

Explain the four areas of a pivot table.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Rows lists categories down the left side, such as Region. Columns spreads categories across the top, such as Month. Values holds the numbers being summarised, such as Sum of Sales or Count of Orders. Filters applies a filter to the whole pivot, such as Financial Year. Moving a field from Rows to Columns changes the layout instantly, which is why pivots are quick for exploring data.
78

Walk through creating a pivot showing monthly sales by region.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Click inside the data, press Ctrl+T to make it a Table, then Insert > PivotTable > New Worksheet. Drag Region to Rows, Order Date to Columns and Amount to Values. Right-click a date and choose Group > Months (and Years if data covers more than one year). Set Value Field Settings > Number Format to #,##0 and add a slicer for Product if needed. Refresh after new data arrives.
79

What is the difference between Summarize Values By and Show Values As?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
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.
80

What are slicers and timelines, and how do you connect one slicer to several pivots?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Slicers are clickable filter buttons for a field, and timelines are slider filters for dates by day, month, quarter or year. They make filtering obvious for dashboard users. To control several pivots, right-click the slicer and choose Report Connections, then tick each pivot. This works only for pivots built on the same source or data model, because they share the same pivot cache.
81

You added new rows of data but the pivot does not show them. Why, and how do you prevent it?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
The pivot's source is probably a fixed range like A1:H500 and the new rows are below it, or the pivot has not been refreshed. Fix it with PivotTable Analyze > Change Data Source, then Refresh. To prevent it, convert the source to an Excel Table so the range expands automatically, use Data > Refresh All, and enable Refresh data when opening the file in PivotTable Options.
82

The pivot shows Count of Sales instead of Sum of Sales when you drag the field. Why?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Excel defaults to Count when a value column contains blanks or text, such as NA or a number stored as text. Change it through Value Field Settings > Sum, but also fix the source: replace text placeholders with zero or blank, convert text numbers to real numbers, and refresh. Otherwise the text entries are silently left out of the sum and totals will be understated.
83

How do you show only the top 10 customers by revenue in a pivot?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Put Customer in Rows and Revenue in Values. Click the Customer filter arrow > Value Filters > Top 10, choose Top, 10, Items, by Sum of Revenue. Then sort the Revenue column largest to smallest. The grand total by default still reflects only the visible rows; if you need overall totals, add a separate pivot or enable Include filtered items in totals when using the Data Model.
84

Describe a time you taught colleagues to use pivot tables.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Situation: three teammates built monthly summaries by filtering and copying data by hand. Task: my lead asked me to help them speed up. Action: I ran two short sessions using our own sales data, showing how to create a pivot, group dates, add slicers and refresh after new rows, and shared a one-page guide. Result: within a month each of them produced their summary in minutes, and fewer copy-paste errors reached the manager.
85

How do you see the rows behind a pivot value?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Double-click any value cell and Excel creates a new sheet listing every source row that makes up that number. This drill-down is very useful for checking totals or answering a manager's why question. If the file is shared and you do not want people extracting raw data, untick Enable show details in PivotTable Options > Data, though this is not a strong security control.
86

Describe a report you built with pivot tables that helped a decision.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Situation: our store team felt discounts were not driving sales. Task: check it with data. Action: I built a pivot from six months of bills with discount band in rows, month in columns, and sales and margin as values, plus a slicer for store. Result: the pivot showed discounts above 20 percent added little volume but cut margin sharply, and the manager capped discounts at 15 percent the following month.
87

How do you group dates and numbers in a pivot, and why does grouping sometimes fail?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
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.
88

What is the difference between a calculated field and a calculated item?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
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.
89

What is the pivot cache and why does it matter?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
When a pivot is created, Excel stores a snapshot of the source data called the pivot cache, and the pivot reads from it, not directly from the sheet. That is why changes need a refresh. Pivots built from the same Table share one cache, which keeps file size down and lets slicers connect across them. Creating pivots from different copies of the data creates separate caches and makes the file heavier.
90

What is GETPIVOTDATA and how do you control it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
When you click a pivot cell while writing a formula, Excel inserts GETPIVOTDATA, which pulls a value by field names instead of cell position: =GETPIVOTDATA("Amount",$A$3,"Region","North","Month","Apr") It keeps working when the pivot layout changes, which suits KPI cards in dashboards. If you want plain references, turn it off in PivotTable Analyze > Options > Generate GetPivotData.
91

How do you get a distinct count, such as unique customers, in a pivot table?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
A normal pivot only offers Count, which counts every row. When inserting the pivot, tick Add this data to the Data Model. Then in Value Field Settings choose Distinct Count, which counts unique customer IDs per region or month. Alternatively, create a DAX measure =DISTINCTCOUNT(Sales[CustomerID]) in Power Pivot, which can be reused across pivots.
92

Finance wants the sales pivot by quarter of the Indian financial year, starting in April. Date grouping only gives calendar quarters. How do you solve it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Pivot date grouping uses calendar quarters, so add a helper column in the source data. For example, fiscal quarter ="Q"&ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0) and a fiscal year column. Use them as row fields instead of grouped dates. In Power Query or a Data Model, put these columns in a proper Date table so every report uses the same April to March logic.
93

Your pivot chart changes whenever someone filters the pivot, but your manager wants a fixed chart for a presentation. What do you do?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
A pivot chart always follows its pivot, so it cannot be frozen while the pivot is filtered. Options: copy the chart and paste it as a picture into the slide; or copy the pivot values to a normal range and build a regular chart from them; or create a second pivot from the same cache, set its filters once and protect the sheet. For a live report, keep the interactive version on a separate sheet.
94

Your manager wants each product's share of sales within its region, not of the grand total. How do you show it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Put Region and then Product in Rows, and add Sales to Values twice. For the second copy, open Value Field Settings > Show Values As > % of Parent Row Total. Each product then shows its share of its own region, and each region totals 100 percent. Using % of Grand Total instead would show share of the whole company, which answers a different question.
95

How do you stop a pivot from losing formatting and column widths on refresh?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Open PivotTable Options > Layout & Format, untick Autofit column widths on update and tick Preserve cell formatting on update. Set number formats through Value Field Settings > Number Format rather than formatting cells directly, because field-level formats survive refresh and layout changes. For a consistent look across reports, create a custom PivotTable Style and apply it to every pivot.
96

You receive 12 monthly sales files and need one pivot for the year. How do you build it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Put all files in one folder and use Data > Get Data > From File > From Folder > Combine & Transform. Power Query appends them using the first file as a sample, adds the source file name as a column and applies the same cleaning steps. Load the result as a Table or to the Data Model and build the pivot on it. Next year, adding a file to the folder and clicking Refresh All updates everything.
97

Tell me about a time you got a last-minute analysis request. How did you handle it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Situation: at 5 PM, my manager needed customer-wise outstanding for a client call at 9 AM. Task: deliver an accurate summary quickly. Action: I confirmed the exact question and cut-off date, pulled the ledger, built a pivot by customer and ageing bucket, cross-checked the total with the trial balance and flagged three unusual balances. Result: the report was ready by 7 PM, and my notes helped the manager answer follow-up questions in the call.
98

You have Sales, Products and Regions in three tables. How do you build one pivot from all of them?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
Instead of merging with VLOOKUP, load each Table into the Data Model (Insert > PivotTable > Add this data to the Data Model, or Power Pivot). In Data > Relationships, link Sales[ProductID] to Products[ProductID] and Sales[RegionID] to Regions[RegionID]. The pivot field list then shows all three tables, so category and zone come from the lookup tables while amounts come from Sales.
99

The pivot grand total does not match SUM of the source column. How do you investigate?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
Check for active filters on any field, slicers or timelines, and whether the source range excludes recent rows. Look for text values in the amount column, which pivots ignore, and calculated items that double-count. Confirm date grouping is not hiding rows outside the grouped range. Then double-click the grand total to see the rows the pivot used and compare the row count with the source.
100

Why is averaging a percentage column in a pivot often wrong, and what should you do instead?

Pivot Tables and Data SummariesHard
Save to ▾
Show answer
An average of row-level margins gives each order equal weight, so a 100 rupee order counts as much as a 10 lakh order. The correct margin is total profit divided by total sales. Create a calculated field Margin = Profit / Sales, which Excel evaluates on summed values at every level, or a DAX measure =DIVIDE(SUM(Sales[Profit]),SUM(Sales[Amount])). Interviewers use this to test real analytical thinking.
101

How do you choose the right chart type for business data?

Charts and DashboardsEasy
Save to ▾
Show answer
Match the chart to the question. Use column or bar charts to compare categories, line charts for trends over time, pie or doughnut only for a share of a whole with few slices, scatter charts for the relationship between two numbers, histograms for distribution, and combo charts when two measures with different scales belong together, such as sales and margin percentage. The simplest chart that answers the question is usually the best.
102

A manager wants a 50-row branch report where every row also shows a small trend of the last 12 months. What do you use?

Charts and DashboardsEasy
Save to ▾
Show answer
Use sparklines: select the 12 monthly values for all rows, choose Insert > Sparklines > Line and set the location to a column next to the data. Turn on high and low point markers, and under Axis choose the same minimum and maximum for all sparklines if branches should be compared fairly. They fit inside a cell, print well and update when the data changes.
103

What are the common mistakes with pie charts?

Charts and DashboardsEasy
Save to ▾
Show answer
Pie charts become unreadable with more than five or six slices, because people are poor at comparing angles. They cannot show change over time, and 3D or exploded pies distort sizes. Slices should add up to a meaningful whole; percentages from different bases should never be in one pie. A sorted bar chart almost always communicates the same information more clearly.
104

What are the main elements of an Excel chart and how do you format them?

Charts and DashboardsEasy
Save to ▾
Show answer
A chart has a chart title, plot area, horizontal and vertical axes, axis titles, data series, data labels, legend and gridlines. Click the plus button next to a chart to switch elements on or off, and select any element and press Ctrl+1 to open its format pane. Removing unnecessary gridlines and legends, and labelling series directly, usually makes charts cleaner.
105

Tell me about a time you presented data to senior leadership.

Charts and DashboardsMedium
Save to ▾
Show answer
Situation: I had to present quarterly branch performance to regional directors. Task: make twenty pages of tables understandable in ten minutes. Action: I built one dashboard sheet with four KPIs against target, a trend chart and a ranked bar chart of branches, and kept detail tables as backup. I opened with the key message and the two branches needing action. Result: the meeting ended with clear decisions, and the format became the standard quarterly review pack.
106

What is a combo chart with a secondary axis and when is it useful?

Charts and DashboardsMedium
Save to ▾
Show answer
A combo chart shows two series with different chart types, for example monthly sales as columns and margin percentage as a line. The percentage would be invisible on the same scale as crores of sales, so it is plotted on a secondary vertical axis on the right. Label both axes clearly, because readers can misread which series belongs to which scale.
107

List the steps to build a sales and margin percentage combo chart.

Charts and DashboardsMedium
Save to ▾
Show answer
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.
108

What is a waterfall chart and where is it used?

Charts and DashboardsMedium
Save to ▾
Show answer
A waterfall chart shows how a starting value moves to an ending value through a series of positive and negative steps. It is common in finance for profit bridges, such as last year's profit, plus volume growth, minus price discount, minus higher costs, giving this year's profit. Excel 2016 and later include it under Insert > Waterfall; set the first and last bars as totals so they start from zero.
109

How do you make a chart title change automatically with the selected region or month?

Charts and DashboardsMedium
Save to ▾
Show answer
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.
110

What principles do you follow when designing an Excel dashboard?

Charts and DashboardsMedium
Save to ▾
Show answer
Start from the questions the audience asks. Put three to five headline KPIs at the top, trends and breakdowns below, and filters such as slicers in one consistent place. Use one colour palette with a highlight colour for what matters, avoid 3D and decorative effects, label units clearly like Rs lakh, and fit the view on one screen. Keep raw data and calculations on separate hidden sheets.
111

You are given raw sales data and asked to build an interactive dashboard. Walk through your approach.

Charts and DashboardsMedium
Save to ▾
Show answer
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.
112

How do you build a KPI card showing achievement against target with an up or down indicator?

Charts and DashboardsMedium
Save to ▾
Show answer
Calculate achievement in a cell, for example =Actual/Target-1, and apply a custom number format that adds arrows and colours: [Color10]▲ 0.0%;[Red]▼ 0.0%;0.0% Place the actual value in a large font above it, link a text box to the cells for flexible placement, and keep labels short like Revenue vs Target. Conditional formatting icon sets are an alternative but are less precise.
113

How do form controls like dropdowns, check boxes and scroll bars make a dashboard interactive?

Charts and DashboardsMedium
Save to ▾
Show answer
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.
114

How do you show actual versus target in a compact bullet-style chart?

Charts and DashboardsMedium
Save to ▾
Show answer
Create a clustered column chart with Target and Actual series. Format the Target series with no fill and a thick outline or a light colour, set Series Overlap to 100 percent so both bars sit on top of each other, and make the Actual bar narrower by putting it on the secondary axis with a larger gap width. Match both axis scales. Readers immediately see whether Actual falls short of or crosses the target.
115

What are histogram and Pareto charts used for?

Charts and DashboardsMedium
Save to ▾
Show answer
A histogram groups numeric values into bins and shows how often each bin occurs, for example how many orders fall between 0 to 1,000 rupees or 1,000 to 5,000 rupees. A Pareto chart sorts categories by frequency and adds a cumulative percentage line, showing the vital few, such as the 20 percent of complaint types causing 80 percent of complaints. Both are available under Insert > Statistic Charts.
116

How do you automatically highlight the highest and lowest points in a line chart?

Charts and DashboardsMedium
Save to ▾
Show answer
Add two helper columns next to the data that show a value only at the max or min and #N/A elsewhere, because charts skip #N/A: Max: =IF(B2=MAX($B$2:$B$13),B2,NA()) Min: =IF(B2=MIN($B$2:$B$13),B2,NA()) Add both as series, set them to markers only with distinct colours and data labels. When the data changes, the highlighted points move automatically.
117

Your manager says your chart is misleading. What could be wrong?

Charts and DashboardsMedium
Save to ▾
Show answer
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.
118

A few very large values make every other bar in your chart look tiny. How do you present the data clearly?

Charts and DashboardsMedium
Save to ▾
Show answer
Options depend on the message. Sort bars and show the top items with the rest grouped as Others; split into two charts, one for the giants and one for the rest; add data labels so small values are still readable; or use a logarithmic axis for technical audiences, clearly labelled. Avoid cutting the axis on bar charts, because bar length then stops representing value and misleads readers.
119

Your weekly dashboard must go to 40 people who cannot access the source files. How do you distribute it safely?

Charts and DashboardsMedium
Save to ▾
Show answer
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.
120

Ten regional heads each need the same dashboard but must see only their own region. How do you deliver it?

Charts and DashboardsMedium
Save to ▾
Show answer
Since Excel files cannot restrict data inside a workbook securely, create separate files. Add a Region parameter cell that drives Power Query filters or pivot filters, then loop through regions with a short VBA macro that sets the region, refreshes, removes other data and saves a copy or PDF per region. For regular use at scale, Power BI with row-level security is the better solution.
121

Tell me about a time stakeholder feedback changed your dashboard.

Charts and DashboardsMedium
Save to ▾
Show answer
Situation: I built a sales dashboard with eight charts for the regional team. Task: get it adopted. Action: in the review, managers said they only checked target achievement and the bottom five branches each morning, so I moved those to the top, removed four rarely used charts and added a branch slicer. Result: usage went up, the meeting started using it daily, and I learned to ask what decisions a dashboard supports before building it.
122

How do you create a chart that automatically shows the last 12 months as new data is added?

Charts and DashboardsHard
Save to ▾
Show answer
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.
123

A dashboard with 20 pivots and 6 slicers takes several seconds to react on every click. How do you speed it up?

Charts and DashboardsHard
Save to ▾
Show answer
Make sure all pivots share one cache or the Data Model, so a slicer click filters one source. Reduce slicer cross-filtering by unticking Visually indicate items with no data, remove slicers that are rarely used, and replace pivots that show the same data with GETPIVOTDATA from one pivot. Remove volatile formulas, limit conditional formatting, and move large data to the Data Model with measures.
124

When should a team move from an Excel dashboard to Power BI?

Charts and DashboardsHard
Save to ▾
Show answer
Excel dashboards work well for small teams, one-off analysis and data up to a few lakh rows. Move to Power BI when data comes from several live sources, needs scheduled refresh, row-level security for different users, mobile access, or a large audience who should not receive files. The Data Model and DAX skills learned in Power Pivot transfer directly, so the move is usually smooth.
125

How do you handle two senior managers asking for conflicting versions of the same dashboard?

Charts and DashboardsHard
Save to ▾
Show answer
Situation: sales wanted revenue by booking date while finance wanted it by invoice date. Task: avoid two dashboards with different numbers. Action: I met both, wrote down each definition and its use, and proposed one dashboard with a clearly labelled toggle and a note explaining the difference. Result: both agreed, the numbers stopped being argued over in meetings, and the definition was added to our data glossary.
126

What is Power Query and why use it instead of manual cleaning?

Power Query and Data CleaningEasy
Save to ▾
Show answer
Power Query (Data > Get & Transform) is Excel's tool to import, clean and reshape data from files, folders, databases and the web. Every action is recorded as a step, so next month you just click Refresh and the same cleaning runs again on new data. This removes repeated copy-paste work, reduces errors and keeps the original source untouched, which manual cleaning cannot offer.
127

What are Applied Steps and the M language in Power Query?

Power Query and Data CleaningEasy
Save to ▾
Show answer
Each transformation you click, like removing columns or changing types, is saved as a step in the Applied Steps pane, and the steps run in order on every refresh. Behind them is M, a functional formula language visible in the Advanced Editor. You can rename steps, delete or reorder them, and edit M directly for logic the interface does not provide, such as custom functions or dynamic column lists.
128

What is the difference between Append and Merge in Power Query?

Power Query and Data CleaningEasy
Save to ▾
Show answer
Append stacks tables one below another, like combining January, February and March sales that have the same columns; it is similar to SQL UNION. Merge joins two tables side by side on a matching key, like adding product category from a Products table to Sales; it is similar to a SQL JOIN or VLOOKUP. Choosing the right one is the first step in any multi-table task.
129

How do you use Group By in Power Query?

Power Query and Data CleaningEasy
Save to ▾
Show answer
Select Transform > Group By, choose one or more grouping columns such as Region and Month, then add aggregations like Sum of Amount, Count Rows and Max of Order Date. The Advanced option allows several groupings and aggregations together. The All Rows operation keeps the detail rows as a nested table, useful for ranking within groups or picking the top record per customer.
130

How do you combine all files in a folder using Power Query?

Power Query and Data CleaningMedium
Save to ▾
Show answer
Place files with the same structure in one folder. Go to Data > Get Data > From File > From Folder, select the folder and click Combine & Transform. Choose the sheet or table from the sample file; Power Query creates a helper function and appends all files, adding a Source.Name column with the file name. Apply cleaning steps once, Close & Load, and later just drop new files in and Refresh.
131

Explain the join kinds available in Power Query Merge.

Power Query and Data CleaningMedium
Save to ▾
Show answer
Left Outer keeps all rows from the first table and matches from the second, the most common choice. Right Outer does the reverse. Full Outer keeps all rows from both. Inner keeps only matching rows. Left Anti keeps rows in the first table with no match, and Right Anti keeps rows in the second with no match. Anti joins are excellent for finding missing invoices or customers during reconciliation.
132

Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?

Power Query and Data CleaningMedium
Save to ▾
Show answer
Load the data into Power Query, select the identifying columns such as Branch and Product, then right-click and choose Unpivot Other Columns. The month headers become an Attribute column and the numbers become a Value column; rename them to Month and Sales. Using Unpivot Other Columns rather than Unpivot Columns means new months added later are included automatically on refresh.
133

Your Power Query output has 20 lakh rows and Excel says it cannot load it to a sheet. What do you do?

Power Query and Data CleaningMedium
Save to ▾
Show answer
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.
134

How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?

Power Query and Data CleaningMedium
Save to ▾
Show answer
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.
135

Why are data types important in Power Query, and what problems does the automatic Changed Type step cause?

Power Query and Data CleaningMedium
Save to ▾
Show answer
Data types decide how values are sorted, summed and loaded, so a number stored as text will not total and a date stored as text will not group by month. Power Query adds an automatic Changed Type step that hard-codes every column name, so a renamed or removed column later causes refresh errors. Many analysts delete it and set types once, deliberately, after the structure is final.
136

Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?

Power Query and Data CleaningMedium
Save to ▾
Show answer
The source file's header changed, for example Amount became Amount (Rs), and a step that refers to the old name, often Changed Type or Renamed Columns, breaks. Open the query, click through the steps to find the first error, and update the name or rename the column back early in the query. To prevent repeats, agree a fixed template with the data owner and avoid steps that list every column unnecessarily.
137

Dates from a vendor file in dd/mm/yyyy format show as errors or swap day and month. How do you fix it?

Power Query and Data CleaningMedium
Save to ▾
Show answer
The system locale is probably English (United States), which reads dates as mm/dd/yyyy, so 13/04/2026 becomes an error and 05/04/2026 becomes 4 May. In Power Query, right-click the column > Change Type > Using Locale, choose Date and English (India). This tells Power Query how to read the text regardless of the computer's settings, so the file refreshes correctly on any machine.
138

How do you remove duplicate customers but keep the most recent record in Power Query?

Power Query and Data CleaningMedium
Save to ▾
Show answer
Sort by Updated Date descending, then buffer the table so the sort order is respected before removing duplicates on the key: Sorted = Table.Sort(Source,{{"UpdatedOn", Order.Descending}}), Buffered = Table.Buffer(Sorted), Latest = Table.Distinct(Buffered,{"CustomerID"}) Without Table.Buffer, Power Query may not keep the sorted order, so the record kept could be an older one.
139

Describe a time you convinced your team to move from manual cleaning to Power Query.

Power Query and Data CleaningMedium
Save to ▾
Show answer
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.
140

What are parameters in Power Query and when do you use them?

Power Query and Data CleaningMedium
Save to ▾
Show answer
A parameter is a named value, created in Manage Parameters, that queries can reference instead of a hard-coded value. A common use is a FolderPath parameter so the same workbook works when moved to another computer or shared drive. Parameters can also hold a cut-off date or a region filter. Changing one value updates every query that uses it, which makes files easier to hand over.
141

What is the difference between Reference and Duplicate in Power Query?

Power Query and Data CleaningMedium
Save to ▾
Show answer
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.
142

A report exported from PDF repeats the header row on every page and has page-footer lines. How do you clean it?

Power Query and Data CleaningMedium
Save to ▾
Show answer
Use Get Data > From PDF or import the converted file, then promote the first row to headers. Filter out rows where the first column equals the header text, such as Name, and rows containing footer text like Page or Printed on, using Text Filters > Does Not Contain. Remove blank rows, Fill Down any group labels, and set types. The steps then run automatically on next month's PDF.
143

What checks do you run when cleaning a new dataset?

Power Query and Data CleaningMedium
Save to ▾
Show answer
Check row counts against the source, then look for duplicate keys, blanks in mandatory fields, inconsistent text such as Delhi and New Delhi, extra spaces and mixed case, dates stored as text or in the wrong locale, negative or extreme numbers that look like typos, and codes losing leading zeros. Power Query's Column Quality and Column Profile views show errors, empties and distinct counts for each column quickly.
144

Tell me about a time you automated a repetitive task.

Power Query and Data CleaningMedium
Save to ▾
Show answer
Situation: every Monday I merged five regional order files and cleaned them by hand, which took about three hours. Task: reduce the effort and errors. Action: I built a Power Query folder import, standardised columns, fixed date locale issues and loaded the result to a pivot dashboard, then wrote a one-page guide for the team. Result: the job took ten minutes, copy-paste errors disappeared and two other teams adopted the same setup.
145

Write a Power Query custom column that classifies orders into High, Medium and Low by amount.

Power Query and Data CleaningHard
Save to ▾
Show answer
Add Column > Custom Column and enter an M if expression; M is case-sensitive and uses lowercase if, then, else: if [Amount] >= 100000 then "High" else if [Amount] >= 25000 then "Medium" else "Low" Then set the new column type to Text. The Conditional Column dialog can build the same logic without typing, but writing M is faster for nested or combined conditions using and/or.
146

What is query folding in Power Query?

Power Query and Data CleaningHard
Save to ▾
Show answer
Query folding means Power Query translates your steps into the source's own query language, such as SQL, so filtering and grouping happen inside the database and only the result is sent to Excel. This is much faster for large sources. Steps like adding index columns or some custom M functions can break folding. Right-click a step and check View Native Query to see whether it still folds.
147

A monthly process takes four hours of copy-paste from 30 branch files. How would you automate it?

Power Query and Data CleaningHard
Save to ▾
Show answer
First agree a fixed template with the branches, ideally a named Table. Save all files to one SharePoint or shared folder, then build a From Folder query that combines them, adds the branch name from the file name, standardises types and filters bad rows. Load to the Data Model, build the pivots and dashboard on it, and add a check sheet comparing row counts per branch. Monthly work becomes refresh and review.
148

What causes the Formula.Firewall error in Power Query and how do you resolve it?

Power Query and Data CleaningHard
Save to ▾
Show answer
The error appears when a query combines data from sources with different privacy levels, for example using a value from an Excel sheet to filter a web or database query, because Power Query prevents data from one source leaking to another. Fix it by setting correct privacy levels in Data Source Settings, restructuring so the combination happens in separate staging queries, or, for trusted personal files, choosing to ignore privacy levels.
149

Refreshing a Power Query that combines 200 CSV files from a folder takes ten minutes. How do you speed it up?

Power Query and Data CleaningHard
Save to ▾
Show answer
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.
150

How do you create and use a custom function in Power Query?

Power Query and Data CleaningHard
Save to ▾
Show answer
Write a query that takes a parameter, then call it for each row or file: (filePath as text) as table => let Src = Excel.Workbook(File.Contents(filePath)), Data = Src{[Item="Sales",Kind="Table"]}[Data] in Data Save it as fnGetSales, then Add Column > Invoke Custom Function on a table of file paths and expand the result. This gives more control than the automatic Combine Files helper.
151

What are Power Pivot and the Excel Data Model?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
The Data Model is an in-memory database inside the workbook, powered by the VertiPaq engine, which compresses data by column and can handle millions of rows, far beyond the sheet limit. Power Pivot is the window used to manage it: import tables, create relationships, and write DAX measures and calculated columns. Pivot tables built on the Data Model can combine several related tables without VLOOKUP.
152

Write basic DAX measures for total sales, order count and average order value.

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
In Power Pivot > Measures > New Measure, or in the pivot field list: Total Sales := SUM(Sales[Amount]) Orders := DISTINCTCOUNT(Sales[OrderID]) Avg Order Value := DIVIDE([Total Sales],[Orders]) Referencing measures inside other measures keeps logic in one place. Set the number format on the measure itself, so every pivot that uses it shows the same format.
153

Why use DIVIDE instead of the / operator in DAX?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
DIVIDE handles division by zero safely and returns blank, or a value you choose, instead of an error: Margin % := DIVIDE([Total Profit],[Total Sales]) Margin % := DIVIDE([Total Profit],[Total Sales],0) Blank results are hidden in pivots, which keeps reports clean when a product has no sales in a month. Use the / operator only when the denominator can never be zero, because it is slightly faster.
154

What is the difference between a calculated column and a measure in DAX?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
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.
155

Why do you need a separate Date table in the Data Model?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a continuous date column with no gaps, covering full years. A Date table also adds columns like Month Name, Fiscal Year and Quarter for slicing. Create it with Power Query or DAX, for example: Date = CALENDAR(DATE(2024,4,1),DATE(2027,3,31)) Then mark it as the date table in Power Pivot and relate it to every fact table's date column.
156

You need product category in the Sales table to create a calculated column. How do you do it in the Data Model?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Create a relationship from Sales[ProductID] to Products[ProductID], then add a calculated column in Sales: Category = RELATED(Products[Category]) RELATED follows the many-to-one relationship and works only in a row context. Often you do not need the column at all, because the pivot can use Products[Category] directly through the relationship, which saves memory.
157

What is the difference between RELATED and RELATEDTABLE?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
RELATED is used on the many side to fetch a single value from the one side, such as getting a product's category in each sales row. RELATEDTABLE is used on the one side to return all matching rows from the many side, so in Products you could write Order Count = COUNTROWS(RELATEDTABLE(Sales)). Both need an existing relationship and a row context.
158

Users of your Data Model pivot keep dragging Year, Quarter and Month separately and make mistakes. How do you make it easier?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Create a hierarchy in the Date table in Power Pivot diagram view, such as Year > Quarter > Month, so users drag one field and drill down. Set Month Name to sort by Month Number so months do not appear alphabetically. Hide key columns and raw fields that should not be used directly, and give measures clear business names. This keeps the field list small and the pivots consistent.
159

How is the Excel Data Model related to Power BI?

Power Pivot, Data Model and DAXMedium
Save to ▾
Show answer
Both use the same VertiPaq engine and the DAX language, and both use Power Query for data preparation. A Power Pivot model can be imported into Power BI Desktop through File > Import, carrying queries, relationships and measures. This makes Advanced Excel with Power Pivot a natural stepping stone, since most of the model and DAX skills transfer directly to Power BI.
160

What is a star schema and why is it recommended for the Data Model?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
A star schema has one central fact table holding events and numbers, such as Sales with date, product ID, customer ID and amount, surrounded by dimension tables that describe them, such as Date, Product and Customer. Each dimension links to the fact table with a one-to-many relationship. It keeps the model small, makes filters flow predictably and keeps DAX measures simple.
161

Explain relationships, cardinality and filter direction in the Data Model.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
A relationship links a key column in one table to a matching column in another. Cardinality describes how many rows match: one-to-many is the standard case, where each product appears once in Products but many times in Sales. In Excel's Data Model, filters flow from the one side to the many side, so selecting a category in Products filters Sales, but not the reverse.
162

What does CALCULATE do in DAX?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
CALCULATE evaluates an expression under modified filters. It is the most important DAX function: North Sales := CALCULATE([Total Sales],Region[Region]="North") Online Sales 2026 := CALCULATE([Total Sales],Sales[Channel]="Online",'Date'[Year]=2026) Each filter argument replaces any existing filter on that column, while filters on other columns from the pivot still apply. Wrapping a filter in KEEPFILTERS adds to the existing filter instead of replacing it.
163

Explain row context, filter context and context transition.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Filter context is the set of filters applied to a calculation, coming from pivot rows, columns, slicers and CALCULATE. Row context exists when DAX iterates rows, as in a calculated column or SUMX, and lets you refer to the current row's values; it does not filter other tables by itself. Context transition happens when CALCULATE, or a measure reference, is used inside a row context and turns the current row into an equivalent filter context.
164

What is the difference between SUM and SUMX?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
SUM adds a single column. SUMX iterates a table, evaluates an expression for each row and adds the results: Revenue := SUMX(Sales,Sales[Qty]*Sales[Price]) This avoids storing a Qty times Price calculated column. Other X functions such as AVERAGEX, MAXX and COUNTX work the same way. Iterating a very large table with complex expressions can be slower, so keep the expression simple.
165

Write DAX measures for year-to-date sales and sales for the same period last year.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
With a proper Date table marked as a date table and related to Sales: Sales YTD := TOTALYTD([Total Sales],'Date'[Date],"3/31") Sales LY := CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date])) YoY % := DIVIDE([Total Sales]-[Sales LY],[Sales LY]) The "3/31" year-end argument gives an Indian April to March financial year; without it, YTD resets on 1 January.
166

Write a DAX measure for each product's percentage of total sales.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Remove the product filter in the denominator so it always returns the overall total: % of All Products := DIVIDE([Total Sales], CALCULATE([Total Sales],ALL(Products))) Use ALLSELECTED(Products) instead of ALL if the percentage should respect slicer selections, so the visible products add up to 100 percent. Other filters such as region or month still apply in both versions.
167

Explain ALL, ALLEXCEPT, ALLSELECTED and REMOVEFILTERS.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
ALL removes all filters from a table or column and can also return all its rows. REMOVEFILTERS does the same filter removal but is clearer in intent and can only be used as a CALCULATE modifier. ALLEXCEPT removes filters from a table except the columns you name, such as keeping Region. ALLSELECTED removes filters coming from inside the pivot but keeps outer slicer and filter selections, which suits visual totals.
168

Your manager wants a fixed-format P&L with rows in a custom order, but the data is in the Data Model and the pivot keeps reshaping itself. What do you do?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Use CUBE functions instead of a pivot. Select the pivot and use PivotTable Analyze > OLAP Tools > Convert to Formulas, or write them directly, for example CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]", $A5, B$3). Each cell becomes an independent formula, so you can reorder rows, insert subtotal lines and apply fixed formatting, while numbers still refresh from the model.
169

A measure shows the same value on every row of the pivot. What is wrong?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
The field in the rows is not filtering the table the measure uses. Usually there is no relationship between the two tables, the relationship is inactive, or it points the wrong way, for example the row field comes from the many side of a different fact table. Excel often shows a warning that relationships may be needed. Check Manage Relationships and use fields from dimension tables linked to the fact table.
170

Write a month-over-month growth percentage measure.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Use DATEADD on the Date table to shift the period one month back: Sales PM := CALCULATE([Total Sales],DATEADD('Date'[Date],-1,MONTH)) MoM % := DIVIDE([Total Sales]-[Sales PM],[Sales PM]) Place Month from the Date table in the pivot rows. For the first month in the data, Sales PM is blank, so DIVIDE returns blank rather than an error.
171

Tell me about a time you had to explain a DAX number that looked wrong to a manager.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Situation: a manager said our margin percentage in the Data Model pivot did not match his calculator. Task: show whether the measure was right. Action: I took one region, showed that he was averaging row percentages while the measure divided total margin by total sales, and walked through both in a small table. Result: he agreed the measure was correct, and I added a note to the report explaining how the KPI is calculated.
172

A workbook with the Data Model has grown to 300 MB. How do you reduce its size?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
VertiPaq size depends mostly on columns and how many unique values each has. Remove columns not used in reports, especially IDs and free text. Split date-time columns into date and time or drop the time, round decimals to required precision, and replace calculated columns with measures. Use integer keys instead of long text keys, and load only the years required. Filtering at Power Query level avoids loading unneeded rows.
173

Write a running total measure for cumulative sales across dates.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Keep all dates up to the last date in the current filter: Running Sales := CALCULATE([Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date]<=MAX('Date'[Date]))) Place Month or Date from the Date table in the pivot rows. To restart every financial year, use TOTALYTD with the "3/31" year-end instead.
174

Tell me about a time you had to learn a new tool quickly to deliver a project.

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Situation: our sales data crossed two million rows and normal pivots crashed. Task: deliver a monthly dashboard within two weeks. Action: I learned Power Pivot and DAX through documentation and practice files, built a star schema with a Date table, wrote measures for YTD and growth, and checked every number against old reports. Result: the dashboard refreshed in under a minute, and I ran a short session to train two colleagues.
175

Budgets are set by category and month, but sales are recorded by product and day. How do you compare them in one pivot?

Power Pivot, Data Model and DAXHard
Save to ▾
Show answer
Do not relate the two fact tables directly. Create shared dimension tables at a grain both can link to: a Category table and a Date table with a Month column. Relate Budget[Category] and Sales via Products to Category, and both date columns to the Date table, using the first day of the month for budgets. Then measures [Total Sales] and [Total Budget] work together when the pivot uses Category and Month.
176

What is a macro and what file format is needed to save it?

Macros and VBA AutomationEasy
Save to ▾
Show answer
A macro is a recorded or written set of instructions in VBA (Visual Basic for Applications) that automates repeated tasks, such as formatting a report or exporting PDFs. Normal .xlsx files cannot store macros, so the workbook must be saved as .xlsm (macro-enabled) or .xlsb. Macros can be run from the Developer tab, a button, a keyboard shortcut or automatically through events.
177

What are the limitations of the macro recorder, and what is relative recording?

Macros and VBA AutomationEasy
Save to ▾
Show answer
The recorder writes code for every click, including unnecessary Select statements and fixed cell addresses, so the macro breaks when data size changes. It cannot record loops, conditions or input boxes. By default it records absolute references; turning on Use Relative References records moves relative to the active cell. The recorder is great for learning syntax, but recorded code should be cleaned up by hand.
178

What is the difference between a Sub and a Function in VBA?

Macros and VBA AutomationEasy
Save to ▾
Show answer
A Sub performs actions, such as formatting cells or saving files, and is run as a macro. A Function returns a value and can be used in worksheet formulas as a user-defined function or called from other code. Both are written in a standard module, opened through Alt+F11 > Insert > Module. Event code goes in the sheet or ThisWorkbook module instead.
179

How do you find the last used row in a column with VBA?

Macros and VBA AutomationEasy
Save to ▾
Show answer
The most reliable common method starts from the bottom of the sheet and moves up: Dim lastRow As Long With ThisWorkbook.Worksheets("Data") lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With Using Long, not Integer, avoids overflow beyond 32,767 rows. If the data is an Excel Table, ListObjects("Sales").ListRows.Count is even cleaner.
180

Why should you avoid Select and Activate in VBA?

Macros and VBA AutomationEasy
Save to ▾
Show answer
Recorded macros use lines like Range("A1").Select followed by Selection.Copy, which are slow, flicker the screen and break if the user clicks another sheet while the macro runs. Refer to objects directly instead: Worksheets("Data").Range("A1:D100").Copy Worksheets("Report").Range("A1"). Fully qualifying the workbook and worksheet, often with a With block, makes the code faster and predictable.
181

A macro produces wrong totals because a misspelt variable name was silently treated as a new empty variable. How do you prevent this?

Macros and VBA AutomationEasy
Save to ▾
Show answer
Add Option Explicit at the top of every module, and enable Tools > Options > Require Variable Declaration so new modules get it automatically. VBA then refuses to run code that uses an undeclared variable and highlights the typo at compile time. Also declare proper types such as Long or Double instead of Variant, which catches type mistakes and makes the code faster and easier to read.
182

How does macro security work in Excel?

Macros and VBA AutomationEasy
Save to ▾
Show answer
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.
183

Why do you want a role that uses Excel heavily, such as MIS or reporting?

Macros and VBA AutomationEasy
Save to ▾
Show answer
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.
184

Write VBA to loop through all worksheets and apply the same formatting.

Macros and VBA AutomationMedium
Save to ▾
Show answer
Use For Each over the Worksheets collection and skip any sheet you do not want to touch: Sub FormatAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then ws.Rows(1).Font.Bold = True ws.UsedRange.Columns.AutoFit End If Next ws End Sub
185

How do you handle errors in VBA?

Macros and VBA AutomationMedium
Save to ▾
Show answer
Use On Error GoTo to jump to a handler, report the problem and clean up: Sub ImportData() On Error GoTo Fail Application.ScreenUpdating = False ' ... main code ... Done: Application.ScreenUpdating = True Exit Sub Fail: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Done End Sub Avoid blanket On Error Resume Next, which hides real failures.
186

What are VBA events? Give an example of Worksheet_Change.

Macros and VBA AutomationMedium
Save to ▾
Show answer
Events run code automatically when something happens, such as opening a workbook (Workbook_Open) or editing a cell (Worksheet_Change). In the sheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F:F")) Is Nothing Then Application.EnableEvents = False Target.Offset(0, 1).Value = Now Application.EnableEvents = True End If End Sub This timestamps column G when status in F changes.
187

Write VBA to save each worksheet as a separate PDF.

Macros and VBA AutomationMedium
Save to ▾
Show answer
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.
188

How do you write a user-defined function (UDF) in VBA? Give an example.

Macros and VBA AutomationMedium
Save to ▾
Show answer
Write a Public Function in a standard module and use it like any worksheet function: Function GSTAmount(amount As Double, Optional rate As Double = 0.18) As Double GSTAmount = Round(amount * rate, 2) End Function In a cell: =GSTAmount(B2) or =GSTAmount(B2,5%). UDFs cannot change other cells or formats, and they recalculate more slowly than built-in functions, so avoid them in very large ranges.
189

A macro works on your computer but fails on a colleague's. What could be the reasons?

Macros and VBA AutomationMedium
Save to ▾
Show answer
Common causes are hard-coded file paths or user names, missing library references shown as MISSING in Tools > References, macros blocked by Trust Center settings or because the file came from the internet, and 32-bit versus 64-bit Office, which needs PtrSafe in API declarations. Different regional settings for dates and decimals, or a renamed sheet, also break code. Use ThisWorkbook.Path and late binding to make macros portable.
190

What are UserForms in VBA and when would you use one?

Macros and VBA AutomationMedium
Save to ▾
Show answer
A UserForm is a custom dialog box with text boxes, dropdowns, option buttons and command buttons, created in the VBA editor through Insert > UserForm. It is useful for controlled data entry, such as an employee or expense entry form that validates inputs before writing a row to a Table, or for letting users choose options before a report macro runs. It reduces errors from typing directly into sheets.
191

Tell me about a time you built a macro that others in your team relied on. How did you keep it maintainable?

Macros and VBA AutomationMedium
Save to ▾
Show answer
Situation: I wrote a macro that split a master file into branch-wise reports for 30 branches. Task: others had to run it while I was on leave. Action: I added Option Explicit, comments, a settings sheet for paths and branch lists, error handling with clear messages and a short user guide. Result: colleagues ran it for months without my help, and adding new branches needed only a row in the settings sheet.
192

Your macro accidentally deleted data, and Undo does not work. How do you prevent this in future?

Macros and VBA AutomationMedium
Save to ▾
Show answer
Macro actions clear Excel's undo history, so prevention matters. Add a confirmation prompt before destructive steps, test on a copy of the file, and have the macro save a backup copy with a timestamp before it changes data. Restrict deletion to clearly identified ranges such as a named Table, not ActiveSheet. For files on OneDrive or SharePoint, version history can usually restore the earlier copy.
193

Write VBA to delete rows where column A is blank.

Macros and VBA AutomationMedium
Save to ▾
Show answer
Loop from the bottom up so deleting a row does not skip the next one: Sub DeleteBlankRows() Dim r As Long, ws As Worksheet Set ws = ActiveSheet For r = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row To 2 Step -1 If Trim(ws.Cells(r, "A").Value) = "" Then ws.Rows(r).Delete Next r End Sub For big data, filtering blanks and deleting visible rows in one action is faster.
194

Tell me about a time your automation failed after you handed it over. What did you do?

Macros and VBA AutomationMedium
Save to ▾
Show answer
Situation: a report macro I wrote stopped working for the accounts team. Task: fix it quickly and stop it happening again. Action: I found that a sheet had been renamed and the macro used a fixed sheet name and file path. I fixed it with ThisWorkbook.Path and code names, added an error handler with a clear message, and wrote a short usage note. Result: it ran reliably for the rest of the year and I now test macros on another user's machine before handover.
195

How do you speed up a slow VBA macro?

Macros and VBA AutomationHard
Save to ▾
Show answer
Switch off screen updates, automatic calculation and events while the macro runs, and always restore them: Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' ... main code ... Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.EnableEvents = True Also avoid Select, process data in arrays instead of cell by cell, and use an error handler so settings are restored on failure.
196

Write VBA to consolidate the first sheet of every Excel file in a folder into one sheet.

Macros and VBA AutomationHard
Save to ▾
Show answer
Loop with Dir, open each file, copy its data below the last row and close it: Sub Consolidate() Dim f As String, wb As Workbook, dest As Worksheet, r As Long Const P As String = "C:\Reports\" Set dest = ThisWorkbook.Worksheets("All") f = Dir(P & "*.xlsx") Do While f <> "" Set wb = Workbooks.Open(P & f, ReadOnly:=True) r = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row + 1 wb.Worksheets(1).UsedRange.Offset(1).Copy dest.Cells(r, 1) wb.Close False f = Dir Loop End Sub
197

Why are arrays faster than looping through cells in VBA?

Macros and VBA AutomationHard
Save to ▾
Show answer
Each read or write to a cell crosses between VBA and Excel, which is slow when repeated thousands of times. Reading a whole range into a Variant array, processing it in memory and writing it back once is many times faster: Dim v As Variant, i As Long v = Range("A2:C50001").Value For i = 1 To UBound(v, 1) v(i, 3) = v(i, 1) * v(i, 2) Next i Range("A2:C50001").Value = v
198

How do you send personalised emails from Excel through Outlook using VBA?

Macros and VBA AutomationHard
Save to ▾
Show answer
Use late binding so no library reference is needed, and loop through the rows: Sub SendMails() Dim ol As Object, m As Object, r As Long Set ol = CreateObject("Outlook.Application") For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row Set m = ol.CreateItem(0) m.To = Cells(r, 2).Value m.Subject = "Payment reminder" m.Body = "Dear " & Cells(r, 1).Value & ", your due amount is Rs " & Cells(r, 3).Value m.Display Next r End Sub Use .Display to review before switching to .Send.
199

How is the Scripting.Dictionary object used in VBA?

Macros and VBA AutomationHard
Save to ▾
Show answer
A Dictionary stores key-value pairs with fast lookups, ideal for unique lists and grouping: Dim d As Object, i As Long, v As Variant Set d = CreateObject("Scripting.Dictionary") v = Range("A2:B10001").Value For i = 1 To UBound(v) d(v(i, 1)) = d(v(i, 1)) + v(i, 2) Next i Range("D2").Resize(d.Count, 1).Value = Application.Transpose(d.Keys) This totals amounts per customer far faster than repeated SUMIF calls.
200

When would you choose VBA, Power Query, Office Scripts or Python in Excel?

Macros and VBA AutomationHard
Save to ▾
Show answer
Power Query is best for importing, cleaning and combining data, because it is refreshable and needs no code security. VBA suits desktop automation such as formatting, emailing, file handling and interacting with other Office apps. Office Scripts, written in TypeScript, automate Excel on the web and work with Power Automate flows. Python in Excel suits statistics and advanced analysis inside a cell. Pick the simplest tool your users can run and maintain.

Frequently asked questions

What jobs can I get after learning Advanced Excel?

Advanced Excel opens roles such as MIS Executive, Reporting Analyst, Data Analyst, Financial Analyst, Operations Analyst and Excel VBA Developer. Almost every company with finance, sales, HR or operations teams needs people who can build reliable reports and dashboards. For analyst roles, combining Excel with SQL and Power BI makes your profile much stronger, and Excel remains the tool most interviewers test first in practical rounds.

Who can learn Advanced Excel? Is there any eligibility requirement?

Anyone comfortable with a computer can learn it: students from any stream, commerce and BBA graduates, engineers, job seekers and working professionals in finance, HR, sales or operations. No coding background is needed for formulas, pivots and Power Query. The VBA part introduces programming gently, and basic comfort with numbers and logic is enough to follow along.

How long does it take to learn Advanced Excel?

With regular practice of one to two hours a day, most learners become comfortable with formulas, lookups and pivots in three to four weeks, and cover Power Query, Power Pivot and VBA in about eight weeks. Speed comes from practice on real datasets rather than watching videos, so building small projects such as trackers, reconciliations and dashboards is the fastest way to become job ready.

Is an Advanced Excel certification useful for jobs?

A certificate helps show structured learning, and the Microsoft Office Specialist Excel Expert exam is widely recognised. However, recruiters mainly judge Excel through practical tests where you build formulas, pivots or a dashboard within a time limit. A portfolio of two or three dashboards and automation projects, along with the ability to explain your logic, usually matters more than the certificate itself.

Which version of Excel should I use to practise?

Use Microsoft 365 if possible, because it includes XLOOKUP, dynamic arrays like FILTER and UNIQUE, LET, LAMBDA and the latest Power Query features. Excel 2021 also has most of these. Many companies still use older versions, so also learn INDEX/MATCH and SUMPRODUCT, which work everywhere. Power Pivot and VBA are available in the Windows desktop version.

Should I learn Power BI or SQL after Advanced Excel?

Yes, if you are aiming for analyst roles. Power BI uses the same Power Query and DAX you learn in Excel's Data Model, so the move is natural. SQL lets you pull data directly from databases instead of waiting for exports. Excel plus SQL plus Power BI is the most common skill combination asked for in Indian data analyst and MIS job descriptions.