Excel interview questions

MS Excel is a spreadsheet application used to organize, analyze, and visualize data. In data analysis, it is used for creating reports, performing calculations, data cleaning, and generating insights through charts and pivot tables.

Common data types in MS Excel include:

• Text
• Numbers
• Dates
• Boolean (TRUE/FALSE)
• Errors (e.g., #DIV/0!, #VALUE!)

• Relative Reference: Changes when a formula is copied to another cell (e.g.,

A1).

• Absolute Reference: Remains constant regardless of where the formula is copied (e.g., $A$1).

A Pivot Table is a powerful tool in Excel used to summarize, analyze, and present data from a larger dataset by grouping and filtering it.

The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from another column. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• COUNT: Counts numeric values only.
• COUNTA: Counts all non-blank cells.
• COUNTIF: Counts cells that meet a specific condition.

Go to the Data tab → Click on Remove Duplicates → Select columns to check for duplicates → Click OK.

Conditional formatting allows you to format cells based on specific conditions (e.g., highlight cells greater than 100). Go to Home → Conditional Formatting → Select a rule type → Apply the rule.

• Formula: Custom expressions created by the user (e.g., =A1+B1).
• Function: Predefined operations in Excel (e.g., =SUM(A1:A10)).

The IF function performs a logical test and returns one value if TRUE and another if FALSE. Syntax: =IF(logical_test, value_if_true, value_if_false).

Select the data → Go to the Insert tab → Choose a chart type (e.g., Bar, Pie) → Customize the chart as needed.

These functions combine text from multiple cells into one. Example: =CONCAT(A1, " ", B1) combines first and last names.

Slicers are visual tools for filtering data in Pivot Tables or Pivot Charts, making it easier to segment and analyze data.

• Use the IFERROR function to handle errors.

Example: =IFERROR(A1/B1, "Error").

• Check for blank cells or invalid references.

Data Validation is used to restrict the type of data or values entered in a cell (e.g., allow only numbers between 1 and 100).

Excel Tables are structured data ranges with features like automatic filtering, sorting, and dynamic referencing, simplifying data management.

Go to the Review tab → Click Protect Sheet → Set a password and select actions users are allowed to perform.

Text-to-Columns splits text into separate columns based on a delimiter (e.g., comma, space) or fixed width.

Select the data → Go to the Data tab → Click Filter → Use dropdown arrows to filter data by condition.

The XLOOKUP function searches for a value in a range and returns a corresponding value from another range. Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). WhatsApp: 91-9143407019 (for Personalise Coaching) 20 Intermediate QA

The INDEX function returns the value of a cell at a specific position, and the MATCH function finds the position of a value in a range. Example Dataset: Product Price Quantity A 100 50 B 150 30 C 200 40 Formula to find the quantity of "B": =INDEX(C2:C4, MATCH("B", A2:A4, 0)) Result: 30.

Array formulas perform multiple calculations and return a single or multiple results. Example: To find the total sales (Price × Quantity for all rows): =SUM(A2:A4 * B2:B4) Press Ctrl + Shift + Enter for array evaluation.

You can use formulas to create custom rules. Example: Highlight rows where the "Price" is greater than 150.

OFFSET returns a reference to a range that is offset from a starting cell. Example: To get the value 200 in the dataset: =OFFSET(A1, 3, 1) Result: 200 (moves 3 rows down, 1 column right).

CONCATENATE and "&" both combine text, but "&" is more concise. For example, =A1&B1 achieves the same result as =CONCATENATE(A1, B1).

Use the "Freeze Panes" option under the "View" tab. Select the cell below and to the right of the rows and columns you want to freeze, and then click on "Freeze Panes."

VLOOKUP searches for a value in the first column of a range and returns a corresponding value in the same row from another column. It's useful for looking up information in a table based on a specific criteria.

IFERROR is used to handle errors in Excel formulas. It returns a specified value if a formula results in an error, and the actual result if there's no error.

To create a PivotTable, select your data, go to the "Insert" tab, and choose "PivotTable." It summarizes and analyzes data in a spreadsheet, allowing you to make sense of large datasets.

Relative references change when you copy a formula to another cell, while absolute references stay fixed. Use a $ symbol to make a reference absolute (e.g., $A$1).

Use the "Remove Duplicates" feature under the "Data" tab. Select the range containing duplicates, go to "Data" → "Remove Duplicates," and choose the columns to check for duplicates.

A workbook is the entire Excel file, while a worksheet is a single sheet within that file. Workbooks can contain multiple worksheets.

Use the AND or OR functions. Example: Check if Price > 100 and Quantity > 40: =IF(AND(B2>100, C2>40), "Yes", "No").

A named range that auto-expands as data is added — formulas and charts using it never need re-pointing.

Method 1 — Excel Table (modern): Ctrl+T → use structured references like Table1[Sales] — inherently dynamic.

Method 2 — OFFSET formula (classic): Formulas → Name Manager → New → Name: SalesData → Refers to:

=OFFSET($A$2, 0, 0, COUNTA($A:$A)-1, 1)

COUNTA counts filled cells so the range height adjusts automatically. Now =SUM(SalesData) always covers all current data.

SUMIFS adds values that meet multiple criteria. Example Dataset: Product Region Sales A North 500 B South 300 A North 200 Formula to sum "Sales" where Product = "A" and Region = "North": =SUMIFS(C2:C4, A2:A4, "A", B2:B4, "North") Result: 700.

• LEN: Counts characters in a cell.
• TRIM: Removes extra spaces.

Example: If A1 = " Hello ", =LEN(A1) → 10. =LEN(TRIM(A1)) → 5.

Use TEXTSPLIT or MID with SEARCH. Example: Split "John_Doe" into first and last names: =LEFT(A1, SEARCH("_", A1) - 1) → John. =RIGHT(A1, LEN(A1) - SEARCH("_", A1)) → Doe.

Use Data Validation:

1. Select the cell(s) where the drop-down should appear
2. Data tab → Data Validation → Allow: List
3. In Source, either type values directly (North,South,East,West) or select a range (=$G$2:$G$5)
4. Optional: add an Input Message (hint on select) and an Error Alert (blocks invalid typing)

Pro tips: keep the source list in an Excel Table so new items appear in the drop-down automatically; uncheck "Ignore blank" to force a selection; for a searchable modern dropdown, Excel 365 auto-suggests as you type.

TRANSPOSE switches rows to columns or vice versa. Example: A B C 1 2 3 Use: =TRANSPOSE(A1:C1) Result: | 1 | | 2 | | 3 |

Right-click a field value in the Pivot Table → Group. Three common types:

• Dates: group by Days/Months/Quarters/Years — e.g. daily sales grouped into months for a trend view (Excel often auto-groups dates)
• Numbers: group into bins — e.g. ages into 18–25, 26–35, 36–45 by setting Start/End/By values
• Manual (text): select multiple items with Ctrl → Group — e.g. combine "Delhi + Gurgaon + Noida" into "NCR"

To remove: right-click → Ungroup. Note: grouping is shared between Pivot Tables using the same cache — create a separate cache if you need different groupings.

Use the UNIQUE function. Example: =UNIQUE(A2:A10) extracts distinct products.

Use the AVERAGE function with OFFSET. Example: =AVERAGE(OFFSET(B2,0,0,3)) calculates a 3-period moving average.

TEXT formats numbers/dates as strings. Example: Convert date 01/01/2024 to "January 1, 2024": =TEXT(A1, "MMMM D, YYYY").

Use VLOOKUP with IF. Example: Check if the price of Product A exceeds 100: =IF(VLOOKUP("A", A2:C4, 2, FALSE)>100, "Yes", "No").

Power Query is a tool to clean and transform data. Example: Import a CSV file and remove null rows using Power Query Editor.

FILTER extracts rows that meet criteria. Example: Extract rows where Sales > 400: =FILTER(C2:C10, C2:C10>400).

Use the RANK function. Example: Rank Sales values: =RANK(C2, C2:C10).

Consolidation combines data from multiple ranges/sheets into one summary:

1. Click the target cell → Data tab → Consolidate
2. Choose the Function (Sum, Average, Count…)
3. Add each source range (e.g. Jan!B2:D10, Feb!B2:D10, Mar!B2:D10) with the Add button
4. Tick "Top row" and "Left column" labels so Excel matches categories by name even if order differs
5. Tick "Create links to source data" if you want the summary to auto-update

Example: 12 monthly sheets with region-wise sales → one consolidated yearly sheet with total per region. Modern alternative: Power Query → Append Queries, which is refreshable and handles messy columns better.

Dynamic dashboards use Pivot Tables, Slicers, and charts linked to the data model. Example Dataset: Product Region Month Sales A North Jan 500 B South Jan 300 A North Feb 700

• Create Pivot Tables to summarize data.
• Add Slicers for "Region" and "Month".
• Create charts to visualize trends.

Power Pivot extends Excel's ability to analyze large datasets by allowing relationships between tables, advanced calculations, and data modeling. Example: Create a relationship between "Sales" and "Products" tables based on Product ID and calculate total sales per region.

Advanced Filter extracts rows matching complex AND/OR conditions:

1. Create a criteria range: copy the headers, then put conditions under them — conditions in the same row = AND, in different rows = OR
2. Data tab → Advanced → select the List range (your data) and Criteria range
3. Choose "Copy to another location" to extract results elsewhere, and tick "Unique records only" for dedup

Example: criteria Region = "North" AND Sales > 400 in one row extracts only northern high-sales rows. Add a second row with Region = "South", Sales > 800 to make it an OR of the two conditions — something normal filters can't do in one shot.

LET assigns names to calculations to reuse in formulas. Example: Calculate (Sales - Cost) / Sales: Sales Cost 500 300 Formula: =LET(profit, A2-B2, margin, profit/A2, margin) Result: 0.4 (40%).

LAMBDA lets you create your own reusable custom function without VBA:

1. Write the logic: =LAMBDA(sales, cost, (sales-cost)/sales)
2. Test it inline by calling immediately: =LAMBDA(sales,cost,(sales-cost)/sales)(500,300) → 0.4
3. Save it as a named function: Formulas → Name Manager → New → Name: PROFITMARGIN, Refers to: the LAMBDA formula
4. Now use it anywhere like a native function: =PROFITMARGIN(A2, B2)

Benefits: complex logic written once, reused everywhere, no macro security warnings — available in Excel 365.

INDEX returns a value in a specified range based on the row and column number, while MATCH searches for a value in a range and returns its relative position. Combined, they provide a flexible way to look up data.

=IF(B2>=100000, B2*10%,
   IF(B2>=50000, B2*7%,
      IF(B2>=25000, B2*5%, 0)))

Conditions are checked top-down; first true wins. For many tiers, IFS() or a lookup table with VLOOKUP approximate match is cleaner than deep nesting.

VLOOKUP — vertical lookup, left→right only, breaks if columns move. HLOOKUP — same but horizontal rows. INDEX-MATCH — any direction, robust to column changes, the classic pro combo. XLOOKUP — modern replacement: any direction, exact match by default, built-in if-not-found, can return ranges. Use XLOOKUP where available; INDEX-MATCH for older Excel.

Advanced Filter: criteria-range based filtering with AND/OR logic, extract unique records, copy results to another location — powerful for multi-condition extraction without formulas. Conditional Formatting: color scales for heatmaps, icon sets for KPI status, data bars for in-cell comparison, and custom formula rules (e.g. highlight whole row where =$E2="Pending") for review-ready reports.

1. Profile first: COUNTBLANK for gaps, Conditional Formatting → Duplicate Values.
2. Duplicates: Remove Duplicates on the true key columns; keep a raw copy.
3. Missing: fill with defaults/median where justified, Go To Special → Blanks for bulk fill, or flag as "Unknown".
4. Standardize: TRIM/CLEAN/PROPER for text, consistent date types.
5. Best: do all steps in Power Query so cleaning is recorded and refreshable.

Data → Data Validation: List for dropdowns (source a range/table for dynamic lists), whole number/date ranges to block invalid entries, custom formulas (e.g. =COUNTIF($A:$A, A2)=1 to prevent duplicate entry), plus input messages and error alerts. Cascading dropdowns: INDIRECT with named ranges.

A dependent (cascading) drop-down changes its options based on another cell's selection — e.g. select "Maharashtra" → city list shows only Mumbai/Pune/Nagpur.

1. Create lists for each parent value and name each range exactly as the parent value (select Mumbai/Pune/Nagpur → Name Box → type Maharashtra)
2. First drop-down (State): normal Data Validation list
3. Second drop-down (City): Data Validation → List → Source: =INDIRECT(A2) where A2 holds the selected state

INDIRECT converts the selected text into the matching named range. Note: named ranges can't contain spaces — use underscores and SUBSTITUTE: =INDIRECT(SUBSTITUTE(A2," ","_")).

Break them into helper columns or use LET to simplify. Example: Calculate bonuses: =IF(Sales>500, IF(Region="North", Sales*0.1, Sales*0.05), 0).

XLOOKUP searches both rows and columns. Example Dataset: Jan Feb North 500 600 South 300 400 Find "Feb" sales for "North": =XLOOKUP("North", A2:A3, XLOOKUP("Feb", A1:C1, B2:C3)) Result: 600.

Use statistical measures like the interquartile range (IQR). Example: Values 10 50 100 500 Find Q1, Q3, and IQR: =QUARTILE(A1:A4, 1) → 30. =QUARTILE(A1:A4, 3) → 125. Outlier threshold: Q3 + 1.5*IQR → 325.

Solver finds the best input values under constraints (enable: File → Options → Add-ins → Solver Add-in):

1. Data tab → Solver
2. Set Objective: the cell to optimize (e.g. Total Profit) → Max/Min/Value of
3. By Changing Variable Cells: the inputs Solver can adjust (e.g. units of each product)
4. Subject to Constraints: Add rules — e.g. Total_Hours <= 500, Units >= 0, integers only
5. Choose Simplex LP (linear) or GRG Nonlinear → Solve

Classic example: maximize profit deciding how many chairs vs tables to make, given limited wood and labour hours — Solver returns the optimal production mix.

Goal Seek back-solves ONE input to hit a target output:

1. Data tab → What-If Analysis → Goal Seek
2. Set cell: the formula cell (e.g. Profit = Sales × Margin − Fixed Costs)
3. To value: the target (e.g. 500)
4. By changing cell: the input to adjust (e.g. Sales)

Excel iterates until the formula hits the target — e.g. "you need ₹8,333 sales for ₹500 profit." Limits: only one variable and one target; for multiple inputs/constraints use Solver, for many scenarios use Data Tables.

Array formulas auto-fill adjacent cells when returning multiple values. Example: =SEQUENCE(3, 2, 1, 1) produces: 1 2 3 4 5 6

• Use Excel Tables for structured references.
• Filter data with Power Query.
• Summarize with Pivot Tables.

Power Query (Data tab → Get Data) records cleaning steps that replay on every refresh:

1. Load: Get Data → from File/Folder/Database → Transform Data (opens the editor)
2. Remove duplicates: select key columns → right-click → Remove Duplicates
3. Split "John_Doe": select column → Split Column → By Delimiter → underscore → two columns First/Last name
4. Other one-click cleans: change data types, Trim/Clean text, Replace Values, fill down blanks, unpivot wide columns, merge/append tables
5. Close & Load — later just right-click → Refresh and every step re-applies to new data

Interview line: "I do all repeatable cleaning in Power Query instead of manual edits — the steps are documented in Applied Steps and the workbook refreshes in one click."

Write macros to automate repetitive tasks. Example: Automatically color cells with values > 100. Sub ColorCells() Dim rng As Range For Each rng In Selection If rng.Value > 100 Then rng.Interior.Color = RGB(255, 0, 0) End If Next rng End Sub

Dynamic charts auto-update when data grows. Two methods:

• Excel Table method (best): convert data to a Table (Ctrl+T) → build the chart on the Table → new rows automatically appear in the chart. Zero maintenance.
• OFFSET named-range method (classic interview answer): define a name with =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1) → use the named range as the chart's series source → the range self-expands with data.

Combine with slicers/Pivot Charts for interactive dynamic dashboards.

Extract and sort unique values. Example: =SORT(UNIQUE(A2:A10)).

Use SUMPRODUCT and SUM. Example Dataset: Item Weight Score A 2 80 B 3 90 =SUMPRODUCT(B2:B3, C2:C3)/SUM(B2:B3) → 86.

Use COUNTIF with 3D referencing. Example: =COUNTIF(Sheet2!A:A, A1).

Three ways, best to mention all:

1. Data Analysis ToolPak: File → Options → Add-ins → enable → Data tab → Data Analysis → Regression → set Y range (dependent, e.g. Sales) and X range (independent, e.g. Ad Spend) → output includes R², coefficients, p-values
2. Functions: =SLOPE(Y,X), =INTERCEPT(Y,X), =RSQ(Y,X), or =LINEST() for full multi-variable stats
3. Visual: scatter chart → right-click points → Add Trendline → Linear → tick "Display Equation" and "Display R-squared"

Interpreting: equation y = 2.5x + 100 means every ₹1 of ad spend adds ₹2.5 sales; R² = 0.85 means 85% of sales variation is explained by ad spend.

=SUM((A2:A100="West")*(B2:B100="Laptop")*C2:C100)
=SUMPRODUCT((A2:A100="West")*(C2:C100))

Array formulas evaluate ranges element-wise in one formula. Modern Excel has dynamic arrays: FILTER, UNIQUE, SORT, SEQUENCE that spill results automatically — e.g. =UNIQUE(FILTER(A2:A100, B2:B100>1000)).

Power Query = ETL: connect (files/DB/web), clean, reshape, merge — steps recorded and refreshable. Power Pivot = modeling: load millions of rows into the in-memory data model, relate tables, write DAX measures, then analyse with pivot tables far beyond the 1,048,576-row sheet limit. Together they turn Excel into a mini BI tool — same engines as Power BI.

• Goal Seek: back-solve one input — "what sales hit ₹10L profit?"
• Data Table: outcome grid across 1–2 changing inputs (sensitivity analysis).
• Scenario Manager: save Best/Worst/Expected input sets and switch.
• Solver: optimize with constraints — maximize profit given budget and capacity limits.

Record a macro (View → Macros → Record) for formatting/cleanup routines, then edit VBA for logic — e.g. loop through 30 files, standardize, and consolidate into one sheet; auto-generate and email a daily report. Mention: store in .xlsm, add error handling, and that Power Query/Office Scripts now replace many macro use cases.