iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Excel interviews usually test more than whether you can write =SUM(A1:A10). Interviewers look for accurate data handling, dependable lookup logic, clean reporting, troubleshooting skill, and awareness of which features work in the candidate’s Excel version.
Use these questions as practice prompts rather than scripts to memorize. Strong answers explain both what a feature does and when you would use it. Examples below target current Excel for Microsoft 365 and Excel 2024 unless a version limitation is mentioned.
Excel fundamentals and formula questions
-
What is the difference between a workbook and a worksheet?
A workbook is the Excel file, such as an.xlsxfile. A worksheet is an individual tab inside that workbook. One workbook can contain multiple worksheets.Recommended: PC Feels Slow? A Free Scan Shows What's Dragging Windows Down →Recommended: Update Every Outdated Driver on Your PC in One Scan - Free →Recommended: Fix Windows Errors and Clear Junk Files in Minutes - Free Scan →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What is a cell reference?
A cell reference identifies a cell or range, such asA1,A1:A10, orSheet2!B4. If the sheet name contains spaces, enclose it in single quotation marks:='Quarterly Data'!D3. -
What is the difference between relative, absolute, and mixed references?
A1changes its row and column when copied.$A$1never changes.$A1fixes the column, whileA$1fixes the row. In desktop Excel, pressF4while editing a reference to cycle through these forms. -
How does Excel formula syntax work?
A formula begins with=, followed by operators or a function. For example,=SUM(B2:B20). Function arguments normally use commas, although regional settings may require semicolons. -
What is the difference between COUNT, COUNTA, and COUNTBLANK?
COUNTcounts numeric values,COUNTAcounts non-empty cells, andCOUNTBLANKcounts blank cells. Be careful: a formula that returns an empty text string is not always treated the same way as a genuinely empty cell.Free tools Windows power users keep installed
One-click scans. No signup required.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What do SUMIF and SUMIFS do?
SUMIFadds values that meet one condition.SUMIFShandles multiple conditions and places the sum range first:=SUMIFS(D2:D100,B2:B100,"East",C2:C100,">=1000") -
What is the difference between COUNTIF and COUNTIFS?
COUNTIFcounts matches against one criterion.COUNTIFScounts rows that satisfy two or more criteria, such as a region and a date range. -
What do AND and OR do?
ANDreturnsTRUEonly when every test is true.ORreturnsTRUEwhen at least one test is true. For example:=IF(AND(B2="East",C2>1000),"Approve","Review") -
What is IFERROR, and when should you avoid it?
IFERROR(value,value_if_error)substitutes a result when a formula returns an Excel error, such as#N/A,#VALUE!, or#REF!. It is useful for user-facing reports, but using it everywhere can conceal data-quality problems that should be investigated.Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What is the difference between IFERROR and IFNA?
IFERRORcatches several error types.IFNAhandles only#N/A. UseIFNAwhen a missing lookup is expected but other errors should remain visible. -
What does LET do?
LETnames intermediate calculations inside a formula, making complex formulas easier to read and sometimes more efficient:=LET(total,SUM(B2:B20),tax,total*0.2,total+tax)It is available in newer Excel versions, including Microsoft 365 and Excel 2021 or later.
-
What does LAMBDA do?
LAMBDAcreates reusable custom worksheet functions without VBA. A LAMBDA can accept up to 253 parameters. Usually, you define it in Name Manager and then call the named function from cells.Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What does TEXT do?
TEXT(value,format_text)formats a number or date and returns text. For example,=TEXT(A2,"mmm yyyy")displays a date as month and year. Because the result is text, do not use it when the result must later be calculated numerically. -
How do you preserve leading zeros?
Format the destination cells as Text before entering or pasting values such as00127. Alternatively, use=TEXT(A2,"00000"); that method returns text rather than a number. -
What do TRIM and CLEAN do?
TRIMremoves excess spaces, whileCLEANremoves non-printing characters. A common cleanup expression is=TRIM(CLEAN(A2)). These functions are useful when values look identical but fail to match in a lookup. -
What are TEXTBEFORE, TEXTAFTER, and TEXTSPLIT used for?
They are modern text functions for parsing delimited data. For example,=TEXTBEFORE(A2,"-")extracts text before the first hyphen, and=TEXTSPLIT(A2,",")separates comma-delimited content into cells. They are not available in every legacy Excel edition.DriversCrashes, No Sound, or Screen Glitches?PerformanceWindows Errors? Fix Them Before They SpreadDriversOutdated Drivers Are Slowing You DownSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
Lookup and matching questions
-
Why is XLOOKUP generally preferred over VLOOKUP?
XLOOKUP can search in any direction, returns exact matches by default, and does not require a column-number argument that can break when columns are inserted. It is the preferred choice when the target Excel version supports it. -
What is the XLOOKUP syntax?
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])For example:
=XLOOKUP(A2,Products[ID],Products[Price],"Not found"). -
What is XLOOKUP’s default match behavior?
Its defaultmatch_modeis0, which means exact match. If no match exists and the optionalif_not_foundargument is omitted, XLOOKUP returns#N/A. -
What are XLOOKUP match modes?
Mode Meaning 0 Exact match -1 Exact match or next smaller item 1 Exact match or next larger item 2 Wildcard match -
What are XLOOKUP search modes?
1searches first to last,-1searches last to first,2uses an ascending binary search, and-2uses a descending binary search. Binary modes require correctly sorted data; otherwise, they can return invalid results.What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Does XLOOKUP work in Excel 2016 and Excel 2019?
No. Microsoft does not provide XLOOKUP in Excel 2016 or 2019. Those versions may open a workbook containing XLOOKUP created in another version, but the function is not available for normal use there. -
What is the VLOOKUP syntax?
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])The lookup value must be in the first column of the selected table array, and VLOOKUP returns a value from a column to its right.
-
What is the dangerous VLOOKUP default?
If the last argument is omitted, VLOOKUP uses approximate matching. For ordinary ID, product, or employee lookups, explicitly useFALSE:=VLOOKUP(A2,$D$2:$F$100,3,FALSE) -
Why might VLOOKUP return the wrong value?
Typical causes are omittedFALSE, unsorted data with approximate matching, numbers stored as text, hidden spaces, non-printing characters, mismatched date types, and an incorrect column index.Recommended Free Tools
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What are VLOOKUP’s limitations?
It searches only the first column of its table array and returns values to the right. It cannot naturally look left. XLOOKUP orINDEX/MATCHavoids that restriction. -
What is the standard INDEX/MATCH pattern?
=INDEX(return_range,MATCH(lookup_value,lookup_range,0))The
0tells MATCH to find an exact match. This pattern remains useful in older Excel versions that lack XLOOKUP. -
Is the Excel Lookup Wizard still available?
No. The old Lookup Wizard is no longer available. Use XLOOKUP, VLOOKUP, INDEX/MATCH, or another supported lookup approach.
Dynamic arrays, tables, sorting, and filtering
-
What is a spilled array?
A dynamic-array formula entered in one cell can return multiple results into adjacent cells. For example,=SORT(D2:D11,1,-1)spills a sorted list from its top-left formula cell.Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What causes #SPILL!?
Excel cannot place the results because the intended spill range is blocked. Existing values, merged cells, or another obstruction can cause it. Clear the blocking cells or move the formula. -
Can a spilled formula be entered inside an Excel Table?
Not in the table body. Place the dynamic-array formula outside the Table or convert the Table to a normal range. -
Which cell in a spilled range can be edited?
Only the top-left cell containing the formula is directly editable. The remaining cells are calculated spill results.Also popular on Amazon
-
Do legacy Ctrl+Shift+Enter array formulas still work?
Yes, they remain supported for compatibility. For new workbooks, dynamic-array formulas are generally easier to create and maintain.Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What does the @ operator mean?
@requests implicit intersection: it selects the value relevant to the current row or cell instead of returning an entire array. Excel may add it automatically when older formulas are opened in a modern version. -
Why can linked dynamic-array formulas fail between workbooks?
Microsoft documents that linked dynamic-array formulas work only while both workbooks are open. If the source workbook is closed, the link can return#REF!when refreshed. -
What is an Excel Table?
An Excel Table is a structured data range with headers, filters, automatic expansion, calculated columns, and structured references. Select the data and pressCtrl+T, or use Home > Format as Table. -
What are structured references?
They use Table and column names instead of cell addresses. For example,=SUM(Sales[Amount])is easier to understand and automatically adjusts as the Table changes.Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What is the difference between sorting and filtering?
Sorting changes the order of records. Filtering temporarily hides records that do not meet selected criteria; it does not delete them. -
What is a common sorting mistake?
Sorting one column instead of the complete data set can separate values from their records. Select the full range or Table and choose Expand the selection when Excel prompts.
PivotTables and reporting
-
How do you create a PivotTable?
Select the source data, choose Insert > PivotTable, select the destination, and place fields in the Filters, Columns, Rows, and Values areas. -
What is a PivotTable cache?
The cache is Excel’s stored copy of source data used to build and operate a PivotTable or PivotChart. It helps the report summarize data without repeatedly reading every source cell.The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
How do you refresh a PivotTable?
Right-click inside it and select Refresh, or use the refresh command on the PivotTable ribbon. If the source is an Excel Table, newly added Table rows are included after refresh. -
Can you type directly over a PivotTable value?
No. PivotTable cells are report output. Modify the source data or change the field arrangement, aggregation, filter, or calculated field instead. -
What is a PivotChart?
A PivotChart is a chart connected to a PivotTable. It turns the summarized data into visual comparisons, trends, or category-level patterns and responds to PivotTable filters. -
Can one PivotTable use multiple related tables?
Yes. Create relationships between tables and use the Data Model. This allows a PivotTable to combine fields from related tables instead of forcing everything into one flat range.Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
Formatting and data-entry questions
-
How do you apply conditional formatting?
Select the range and choose Home > Conditional Formatting > New Rule. Available rule types include highlighted cells, top or bottom values, data bars, color scales, and icon sets. -
How do you create formula-based conditional formatting?
Choose Home > Conditional Formatting > New Rule, select the formula option, and enter a formula that evaluates to TRUE or FALSE. For example, for a range beginning at row 3:Also popular on Amazon
=AND($B3="Grain",$D3<500) -
How do you control competing conditional-formatting rules?
Open Home > Conditional Formatting > Manage Rules. Move rules up or down to change priority, and use Stop If True when later rules should not be evaluated after a match. -
How do you create a drop-down list?
Select the cells, then choose Data > Data Validation. On Settings, select List under Allow, specify the source, and enable In-cell dropdown.Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Why use an Excel Table as a drop-down source?
A Table-based list expands automatically when items are added or removed, reducing maintenance compared with a fixed range. -
Does data validation block every invalid value?
No. It can warn about or reject direct typed entries, but copying, filling, or pasting data can bypass validation. Validation should be combined with checks when data integrity matters. -
How do you freeze rows and columns?
Select the cell below the rows and to the right of the columns you want to keep visible. Then choose View > Freeze Panes > Freeze Panes. Excel freezes everything above and to the left of that cell. -
How do you freeze only the first row or column?
Use View > Freeze Panes > Freeze Top Row or Freeze First Column. To remove the setting, select View > Freeze Panes > Unfreeze Panes.PerformanceWindows Errors? Fix Them Before They SpreadDriversCrashes, No Sound, or Screen Glitches?PerformancePC Slower Than It Used to Be?Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
Excel errors and troubleshooting
-
What does #N/A mean?
A required value is unavailable. The most common example is a lookup that found no matching value. -
What does #VALUE! mean?
The formula received an incompatible value, data type, or argument. Arithmetic involving text is a frequent cause. -
What does #REF! mean?
The formula contains an invalid reference, often because a referenced row, column, or worksheet was deleted. -
What does #NAME? mean?
Excel does not recognize text in the formula. Check for misspelled function names, unquoted text, or an unsupported function in the current Excel version.Free tools Windows power users keep installed
One-click scans. No signup required.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What does #DIV/0! mean?
The formula divides by zero or a blank cell. You can test the denominator first, for example=IF(B1=0,0,A1/B1), while also checking whether a zero result is logically appropriate. -
What does ##### mean?
Usually the column is too narrow to display the value. Widen the column. It can also indicate a negative date or time value. -
Why is a formula displayed as text?
The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the format to General using Home > Number Format > General orCtrl+1, then pressF2andEnterto re-enter it. -
Which multiplication operator does Excel use?
Use the asterisk,*, as in=A1*B1. The letterxis not Excel’s multiplication operator and can produce#NAME?.Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
Power Query, VBA, and file-format questions
-
What is Power Query called in Excel?
Microsoft presents Power Query as Get & Transform. It imports or connects to data and can clean columns, set data types, merge tables, and refresh the resulting query.Also popular on Amazon
Quick Flip Questions for Critical Thinking- Hand-held flip chart
- Make learning theories and planning lessons easy
- Develop higher levels of thinking
- Use for classrooms, home schooling and tutoring
- Ideal for all grade levels
-
How do you import data with Power Query?
Use Data > Get Data > From File, then choose a connector such as From Excel Workbook or From Text/CSV. -
How do you open Power Query Editor?
Choose Data > Get Data > Launch Power Query Editor, or select an imported result and choose Query > Edit. -
Why should you check Power Query data types?
Power Query may automatically detect CSV headers, delimiters, and types, but automatic detection can be wrong. Review and explicitly set types for dates, currency, IDs, and other fields where accuracy matters.Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Which file format stores VBA macros?
.xlsmis the macro-enabled workbook format..xlsxcannot store VBA macro code. -
How do you save a workbook containing macros?
Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Saving it as.xlsxremoves the VBA project. -
Do macros run in Excel for the web?
No. An.xlsmworkbook can be opened in the browser, but VBA macros do not run there. Use desktop Excel when the workbook depends on VBA execution. -
What happens when you save a workbook as CSV?
CSV stores plain delimited text, not workbook formatting, formulas, multiple sheets, or VBA. Excel saves only the active worksheet to the CSV file.Recommended Free Tools
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
What is an XLSB file?
.xlsbis Excel’s binary workbook format. It can be useful for large workbooks, but it does not replace.xlsmwhen VBA code must be retained.
Excel questions specific to 2026
-
Is Python in Excel available in every Excel edition?
No. Current Microsoft documentation describes Python in Excel for Excel for Microsoft 365 on Windows and Mac. You can start it through Formulas > Insert Python or by entering=PY. -
Can Python in Excel use pandas.read_csv or pandas.read_excel for external files?
No. Those common external-file functions are not compatible with Python in Excel. Bring external data into the workbook through the worksheet or Power Query, then use Python on the available data. -
Are Excel functions available in every 2026 version?
No. Availability depends on the edition and update channel. For example, Microsoft marks FILTER and LET as Excel 2021 functions, BYROW and BYCOL as Excel 2024 functions, and GROUPBY as a Microsoft 365 function. Check the target environment before using newer formulas in a shared workbook.Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Is Copilot available to every Excel user?
No. It depends on the Excel app, subscription or Copilot license, privacy settings, network access, and organizational tenant configuration. A feature visible to one user may not appear for another. -
Can Copilot-generated formulas be trusted without checking them?
No. AI-generated formulas and analysis can misunderstand the prompt or produce incorrect results. Check the formula, source range, assumptions, and output—especially for financial, legal, or medical decisions.
FAQ
Which Excel questions should I practice first for an interview?
Prioritize relative and absolute references, SUMIFS, IFERROR, XLOOKUP or INDEX/MATCH, Excel Tables, PivotTables, Power Query, and formula-error troubleshooting. These topics reveal whether you can build and audit practical spreadsheets.
Should I answer with XLOOKUP or VLOOKUP?
Lead with XLOOKUP when the interview environment supports Microsoft 365 or a recent Excel version, then mention VLOOKUP and INDEX/MATCH for compatibility with Excel 2016 or 2019. Always explain exact versus approximate matching.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →What is a good way to explain an Excel project in an interview?
Describe the source data, the problem, the tools or formulas you used, the validation checks you performed, and the measurable result. Mention issues such as duplicate IDs, inconsistent types, missing values, or refresh requirements.
How can I avoid version-related Excel mistakes?
Ask which Excel edition and platform the team uses. If the workbook must support older versions, avoid relying exclusively on newer functions such as XLOOKUP, TEXTSPLIT, GROUPBY, or Python in Excel, and test the final file in the target environment.
The Bottom Line
A strong Excel interview answer is specific: name the function or menu path, show a short example, identify a limitation, and explain how you would verify the result. For 2026 roles, combine core spreadsheet skills with Tables, PivotTables, Power Query, dynamic arrays, and careful discussion of version and license limitations.
