Excel is tested in interviews for MIS, accounting, finance, data analyst, operations and HR roles. These Excel interview questions start with formulas, references and formatting for freshers, then cover lookups, pivot tables, Power Query, dynamic arrays, VBA macros and dashboards for advanced Excel users.
30 questions · Updated
Fundamentals, definitions and simple scenarios. Good for freshers and warm-ups.
A formula is any calculation you type into a cell starting with an equals sign, such as =A1+B1*10. A function is a built-in, named calculation that you use inside a formula, such as SUM, AVERAGE, IF or XLOOKUP. =SUM(A1:A10)*2 is a formula that uses the SUM function.
COUNT counts cells containing numbers, COUNTA counts non-empty cells of any type, and COUNTBLANK counts empty cells.
COUNTIF counts cells that meet one condition, such as =COUNTIF(B:B,"Delhi"), and COUNTIFS handles multiple conditions.
SUMIF, SUMIFS, AVERAGEIF and AVERAGEIFS work the same way for sums and averages.
IF tests a condition and returns one value if true and another if false: =IF(C2>=40,"Pass","Fail").
For several conditions I combine it with AND or OR, such as =IF(AND(B2>=75,C2>=75),"Distinction","No").
For many tiers I use IFS, such as =IFS(A2>=90,"A",A2>=75,"B",TRUE,"C"), instead of deeply nested IFs, or a lookup table with XLOOKUP for readability.
A relative reference such as A1 adjusts when a formula is copied, so it points to a cell in the same relative position.
An absolute reference such as $A$1 never changes, which suits fixed inputs like a tax rate.
Mixed references lock only the column ($A1) or only the row (A$1), which is useful in tables such as a multiplication grid.
F4 cycles through the options.
SUMIF adds values that meet one condition: =SUMIF(B:B, "North", D:D) adds sales in column D where the region in column B is North.
SUMIFS handles several conditions, with the sum range first: =SUMIFS(D:D, B:B, "North", C:C, ">=1/4/2026").
Conditions can use wildcards such as "Del*" and comparison operators, and can refer to cells.
LEFT, RIGHT and MID extract parts of text, LEN counts characters, FIND and SEARCH locate text, TRIM removes extra spaces, UPPER, LOWER and PROPER change case, SUBSTITUTE replaces text, TEXT formats numbers and dates as text (=TEXT(A2, "dd-mmm-yyyy")), and CONCAT and TEXTJOIN join values, with TEXTJOIN adding a separator and skipping blanks. Newer versions add TEXTSPLIT, TEXTBEFORE and TEXTAFTER.
TODAY and NOW return the current date and time, DATE builds a date from year, month and day, YEAR, MONTH and DAY extract parts, EOMONTH returns the last day of a month (useful for due dates), EDATE adds months, NETWORKDAYS counts working days excluding weekends and holidays, WORKDAY adds working days, and DATEDIF calculates age or tenure in years, months or days.
Data > Sort sorts by one or more columns, including custom lists and colours.
AutoFilter (Ctrl+Shift+L) adds dropdowns to filter by values, text, numbers, dates or colour, and Advanced Filter handles complex criteria.
Data > Remove Duplicates deletes repeated rows based on chosen columns, and UNIQUE, SORT and FILTER do the same with formulas without changing the source.
Data validation restricts what can be entered in cells, such as whole numbers in a range, dates after today, text of a certain length, or values from a dropdown list based on a range or table.
Input messages guide users and error alerts block or warn about invalid entries.
It keeps shared templates and forms clean, and dependent dropdowns can use INDIRECT or FILTER.
A table automatically expands when you add rows or columns, so formulas, charts, pivot tables and Power Query that use it update automatically.
It adds filter buttons, banded formatting, a total row and structured references such as =SUM(Sales[Amount]), which are easier to read than cell ranges.
Tables should have one header row and no blank rows or merged cells.
Use a column or bar chart to compare categories, a line chart for trends over time, a pie or doughnut chart only for a few parts of a whole, a scatter chart for relationships between two numbers, a histogram for distributions, a combo chart for values on different scales such as revenue and margin percentage, and a waterfall chart for how a total changes. Keep charts simple and clearly labelled.
Applied problems, trade-offs and questions about your own projects.
VLOOKUP looks up a value in the first column of a range and returns a value from a column number to the right; it cannot look left and breaks when columns are inserted.
INDEX MATCH uses MATCH to find the row position and INDEX to return the value from any column, so it can look left.
XLOOKUP, in Excel 365 and 2021, does this in one function with exact match by default, a not-found argument, and search from the bottom.
I convert the data into a table (Ctrl+T) so new rows are included, then Insert > PivotTable.
I drag fields into Rows, Columns, Values and Filters, change the value summary to sum, count or average, group dates by month or quarter, and use Show Values As for percentages of total or running totals.
Slicers and timelines add interactive filters, and I refresh after data changes.
Conditional formatting changes a cell's format based on its value, such as highlighting values above target, duplicates, data bars or colour scales. With "Use a formula", you can format based on any logic, for example highlight the whole row where the status is overdue with =$E2="Overdue", locking the column but not the row so it applies across each row.
Power Query (Get & Transform) imports and cleans data from files, folders, databases and the web.
You apply steps such as removing columns, splitting text, changing types, unpivoting, merging tables (like a lookup) and appending files, and it records every step.
Next time the source data changes, one Refresh repeats all the cleaning, which saves hours on monthly reports.
IFERROR returns a chosen value if a formula produces any error, such as =IFERROR(A2/B2, 0) for division by zero.
IFNA catches only the #N/A error, which is safer for lookups because it does not hide other mistakes such as a wrong range.
XLOOKUP has a built-in "if not found" argument, which often removes the need for either.
A two-way lookup finds a value at the intersection of a row and a column, such as the sales for a given product and month.
INDEX with two MATCH functions does it: =INDEX(B2:M20, MATCH(P2, A2:A20, 0), MATCH(Q2, B1:M1, 0)).
In Excel 365, a nested XLOOKUP works too: =XLOOKUP(P2, A2:A20, XLOOKUP(Q2, B1:M1, B2:M20)).
Goal Seek finds the input value needed to reach a target result, such as the sales needed to reach a profit of ₹10 lakh.
Scenario Manager saves sets of input values, such as best, expected and worst case, and creates a summary.
Data Tables show how results change for one or two varying inputs, such as loan payments for different interest rates and terms.
Unlock the cells users should edit (Format Cells > Protection), then use Review > Protect Sheet to lock the rest and choose allowed actions such as sorting or filtering.
Protect Workbook prevents adding, deleting or moving sheets.
File passwords encrypt the whole file.
Sheet protection prevents accidental changes but is not strong security, so sensitive data needs proper access control.
A named range gives a meaningful name to a cell or range, such as TaxRate or SalesData, created in the Name Box or Name Manager.
Formulas such as =Price*TaxRate become easier to read and maintain, names work as absolute references, and they can be used in data validation lists and navigation.
Dynamic named ranges and tables keep ranges up to date as data grows.
SUMPRODUCT multiplies corresponding values in arrays and adds the results, such as total revenue from quantity and price columns: =SUMPRODUCT(B2:B100, C2:C100). With conditions written as logical tests, it performs complex conditional sums and counts, such as =SUMPRODUCT((A2:A100="North")*(MONTH(D2:D100)=4)*E2:E100), which SUMIFS cannot do directly.
The Data Model stores several tables inside the workbook with relationships between them, like a small database, and can hold millions of rows beyond the sheet limit.
Power Pivot manages it, adding relationships, hierarchies and DAX measures such as total sales or year-over-year growth.
Pivot tables built on the Data Model can combine fields from related tables without VLOOKUPs.
System design, deep internals, leadership and tough follow-ups.
In Excel 365 and 2021, a formula can return many values that "spill" into neighbouring cells.
FILTER returns rows that meet conditions, SORT and SORTBY sort data, UNIQUE returns distinct values, SEQUENCE generates numbers, and newer functions such as TEXTSPLIT, VSTACK, TAKE and GROUPBY reshape data.
They replace many complex array formulas and helper columns.
LET names intermediate results to make long formulas readable.
VBA (Visual Basic for Applications) is Excel's programming language for automating tasks.
A macro can be recorded or written in the VBA editor (Alt+F11).
For example, I wrote a macro that splits a master sheet into one file per branch, formats each and saves it as PDF, turning an hour of manual work into a few seconds.
Files with macros are saved as .xlsm, and I add error handling and avoid Select and Activate for speed.
I keep raw data in a table or load it with Power Query, build calculations with pivot tables or the Data Model with DAX measures, and create charts from them.
Slicers connected to all pivots make the whole dashboard filter together.
I put KPIs at the top with clear labels, keep consistent colours, hide helper sheets, and protect the layout so users can only filter.
Refresh All updates everything.
LET assigns names to intermediate calculations inside a formula, so a long formula is easier to read and each calculation runs only once, for example =LET(total, SUM(B2:B10), tax, total*0.18, total+tax). LAMBDA creates custom reusable functions from formulas, which can be saved in Name Manager, such as a GSTAMOUNT function, and used like built-in functions.
DAX measures calculate results based on the current filter context of the pivot table, for example Total Sales := SUM(Sales[Amount]), Profit Margin := DIVIDE([Profit], [Total Sales]) and Sales LY := CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date])). Measures are reusable across pivot tables, faster than formula columns, and use the same language as Power BI.
Avoid volatile functions such as OFFSET, INDIRECT, TODAY and NOW in large ranges, avoid whole-column references in complex formulas, replace repeated lookups with one helper column or XLOOKUP, use tables, move heavy transformations into Power Query and the Data Model, remove unused formatting and styles, set calculation to manual while building, and save as .xlsb for very large files.
Office Scripts, written in TypeScript, automate Excel for the web and can be shared with a team.
Power Automate runs flows that use Excel files in OneDrive or SharePoint, such as adding rows from form submissions, emailing reports on a schedule or running Office Scripts.
Power Query automates data preparation, and Python in Excel adds advanced analysis.
PMT calculates loan instalments (EMI), PV and FV calculate present and future values, RATE finds the interest rate, NPV and IRR evaluate investments with regular cash flows, XNPV and XIRR handle irregular dates, which suits mutual fund returns, and SLN and DB calculate depreciation. Combined with Data Tables, they support sensitivity analysis in financial models.
See all MIS Executive interview questions, covering Advanced Excel, VBA Macros, Google Sheets, Power BI, Tableau and more.