basicWhat's the difference between a formula and a function?
A formula is anything you write starting with '=' that calculates a result — it can be as simple as =A1+B1. A function is a predefined formula built into Excel, like SUM or VLOOKUP, that you use inside a formula to do a specific job.
basicWhat's the difference between COUNT, COUNTA, and COUNTIF?
COUNT counts only cells containing numbers. COUNTA counts any non-empty cell, numbers or text. COUNTIF counts cells that match a specific condition you set, like counting only cells that say "Finance".
Try COUNT live in the sheet →basicWhat's the difference between relative and absolute cell references?
A relative reference like A1 shifts automatically when you copy the formula elsewhere. An absolute reference like $A$1 stays locked to that exact cell no matter where you copy it — you toggle between them with the F4 key.
basicWhat is conditional formatting, and when would you use it?
It automatically changes a cell's appearance — color, bold, icons — based on its value. A common use is highlighting all figures below a target in red so problems are visible at a glance without reading every number.
basicHow would you remove duplicate rows from a dataset?
Select the data, then use Data → Remove Duplicates. Excel lets you choose which columns to check — it'll only remove rows that match exactly across the columns you select.
basicWhat's the difference between a .xlsx and a .csv file?
.xlsx is Excel's native format — it keeps formulas, formatting, multiple sheets, and charts. .csv is plain text with just the raw data, comma-separated, no formulas or formatting, but it's more universal for importing into other systems.
basicWhat is Data Validation used for?
It restricts what can be typed into a cell — like only allowing whole numbers, dates in a range, or values from a dropdown list. It's how you prevent bad data from entering a sheet in the first place.
basicWhat is the difference between a workbook and a worksheet?
A workbook is the whole Excel file (.xlsx). A worksheet is a single tab inside it. One workbook can hold many worksheets, and formulas can pull data from one sheet to another.
basicWhat are the different data types in Excel, and why do they matter?
The main ones are numbers, text, dates and times, and logical values (TRUE or FALSE). They matter because formulas treat them differently. A number stored as text will not add up in SUM, and a text date will not sort or filter as a real date.
basicWhat is the order of operations in an Excel formula?
Excel works in this order: brackets, exponents, multiplication and division (left to right), then addition and subtraction. So =2+3*4 gives 14, while =(2+3)*4 gives 20. Use brackets whenever you want to control the order.
basicWhat is the difference between sorting and filtering?
Sorting rearranges the rows in a new order, such as A to Z or largest to smallest. Filtering hides the rows that do not match your condition but does not move or delete them. You can use both on the same data.
basicWhat does Freeze Panes do?
It keeps chosen rows or columns visible while you scroll. The most common use is freezing the header row so you can still see column names in a long table. You find it under View, then Freeze Panes.
basicHow do you combine text from two cells?
Use the ampersand, like =A2&" "&B2, or the TEXTJOIN function, which can add a separator and skip blank cells. CONCATENATE still works but TEXTJOIN and CONCAT are the newer choices.
basicWhat does the TRIM function do, and when do you use it?
TRIM removes extra spaces from text, leaving only single spaces between words. It is one of the first things to use on imported data, because hidden spaces are a common reason why lookups and duplicate checks fail.
Try TRIM live in the sheet →basicWhat is the difference between LEFT, RIGHT and MID?
LEFT takes characters from the start of a text string, RIGHT takes them from the end, and MID takes characters from any position you choose. For example, =MID(A2,4,3) returns 3 characters starting at the 4th character.
Try LEFT live in the sheet →basicWhat is AutoSum and what is its shortcut?
AutoSum adds a range of numbers with one click or the shortcut Alt+= on Windows. Excel guesses the range for you, so always check that it picked the right cells.
basicWhat is the difference between SUM and SUBTOTAL?
SUM adds everything in the range. SUBTOTAL can ignore rows hidden by a filter (and, with the 100-series codes, rows you hid by hand), so the total changes when you filter. It also skips other SUBTOTAL results in the range, which avoids double counting.
Try SUM live in the sheet →basicWhat is Flash Fill?
Flash Fill spots a pattern from an example you type and fills the rest of the column for you, for example splitting first names from full names. Press Ctrl+E to use it. Check the results, because it is a pattern guess and not a formula.
basicHow do you freeze a cell reference in a formula?
Add dollar signs to make it absolute, like $B$1, or press F4 while editing. Use $B1 to lock only the column and B$1 to lock only the row.
basicWhat are the common Excel errors and what do they mean?
#DIV/0! means you divided by zero. #N/A means a lookup did not find the value. #REF! means a cell reference is gone, for example a deleted column. #VALUE! means the wrong type of data was used. #NAME? means Excel does not recognise a function or name, often a typo. ##### just means the column is too narrow.
basicWhat is the difference between Clear and Delete?
Clear removes what is inside the cells (contents, formats or both) but leaves the cells where they are. Delete removes the cells themselves and shifts the neighbouring cells to fill the gap.
basicHow do you find and remove duplicates?
To highlight them, use Conditional Formatting, then Highlight Cells Rules, then Duplicate Values. To remove them, use Data, then Remove Duplicates and choose the columns to compare. The UNIQUE function can also return a list without duplicates.
basicWhat is a cell range and how do you write it?
A range is a group of cells written with a colon, like A1:A10 for a column or A1:C10 for a block. Formulas such as SUM, AVERAGE and COUNT all work on ranges.
basicWhat is the difference between COUNTBLANK and COUNTA?
COUNTBLANK counts the empty cells in a range. COUNTA counts the cells that are not empty. Together they add up to the total number of cells in the range.
Try COUNTBLANK live in the sheet →basicHow do you make a dropdown list in a cell?
Select the cell, go to Data, then Data Validation, choose List and type the values or point to a range. This keeps entries consistent and stops typing mistakes.
basicWhat are the main chart types and when do you use each?
Column or bar charts compare categories, line charts show change over time, pie charts show parts of a whole with a few slices, and scatter charts show the relationship between two numbers. Choose the simplest chart that answers the question.
basicHow do you protect a cell from being edited?
Cells are locked by default, but the lock only works after you turn on Review, then Protect Sheet. To allow edits in some cells, unlock them first under Format Cells, then Protection, and then protect the sheet.
basicWhat is the difference between a function and a formula?
A formula is any calculation that starts with an equals sign, such as =A1+B1. A function is a built-in tool like SUM or IF that you can use inside a formula.
basicHow do you make Excel show a number as a percentage?
Select the cell and press Ctrl+Shift+% or choose Percentage on the Home tab. Excel multiplies the number by 100 for display only, so 0.25 shows as 25%.
basicWhat is the difference between CONCAT, CONCATENATE and TEXTJOIN?
CONCATENATE is the old function and CONCAT replaces it and accepts ranges. TEXTJOIN is the most flexible: it adds a separator between items and can skip empty cells.
Try CONCAT live in the sheet →basicHow do you copy a formula down a long column quickly?
Double-click the fill handle (the small square at the bottom right of the cell), or select the range and press Ctrl+D. Both fill down as far as the neighbouring column has data.
basicWhat is Paste Special and when do you use it?
Paste Special lets you paste only part of what you copied: values, formulas, formats, column widths and more. A common use is pasting values only, so a formula result becomes fixed text or numbers.
basicWhat is the difference between ROUND, ROUNDUP and ROUNDDOWN?
ROUND goes to the nearest value, ROUNDUP always rounds away from zero and ROUNDDOWN always rounds towards zero. They change the value itself, unlike number formatting, which only changes what you see.
Try ROUND live in the sheet →basicHow do you count the number of cells that contain text?
Use =COUNTIF(range,"*") to count cells with any text. COUNTA counts every non-empty cell, including numbers.
basicWhat does the IF function do?
IF tests a condition and returns one value if it is true and another if it is false, like =IF(B2>=50,"Pass","Fail"). It is the base of most decision-making formulas in Excel.
Try IF live in the sheet →basicWhat is the difference between the MAX, MIN and AVERAGE functions?
MAX returns the largest number, MIN the smallest and AVERAGE the mean of the numbers in a range. All three ignore blank cells and text.
Try MAX live in the sheet →basicHow do you change the case of text?
UPPER makes all letters capital, LOWER makes them small, and PROPER capitalises the first letter of each word. They are useful for cleaning names and city lists.
basicWhat is the difference between a row and a column?
Rows run left to right and are numbered (1, 2, 3). Columns run top to bottom and are lettered (A, B, C). A cell address such as C5 means column C, row 5.
basicHow do you print only a selected area of a sheet?
Select the area, then go to Page Layout, Print Area, Set Print Area. You can also pick Print Selection in the print settings. Use Page Break Preview to check how it will print.
basicHow do you hide and unhide rows or columns?
Select the rows or columns and press Ctrl+9 to hide rows or Ctrl+0 to hide columns. To unhide, select the rows or columns on either side and right-click, then choose Unhide.
basicWhat is the Name Box?
The Name Box is the small box to the left of the formula bar. It shows the address of the selected cell, and you can type a cell address or a range name into it to jump there.
advancedWhat is XLOOKUP, and how is it different from VLOOKUP?
XLOOKUP is the modern replacement for VLOOKUP — it can search in any direction, doesn't rely on column position, has a built-in "not found" fallback, and can return an entire row or column instead of a single value. It's the preferred answer in interviews when it's available.
Try XLOOKUP live in the sheet →advancedWhat's an array formula, and when would you need one?
An array formula performs a calculation across multiple values at once instead of one cell at a time — for example, summing the product of two ranges without a helper column. In modern Excel, functions like SUMPRODUCT often achieve the same result without needing a true array formula.
advancedHow would you build a dashboard that updates automatically?
Structure the raw data as a proper Excel Table so it auto-expands, connect PivotTables and charts to that table, and use slicers for interactive filtering. For live external data, Power Query can refresh the source automatically on open.
advancedHow do you protect a worksheet while still letting users enter data in specific cells?
By default all cells are locked when protection is turned on. Unlock only the specific input cells first (Format Cells → Protection → uncheck Locked), then apply Protect Sheet — everything stays locked except those cells.
advancedWhat would you check first if a VLOOKUP is returning #N/A unexpectedly?
Check for extra spaces or inconsistent formatting in the lookup value (text vs. number), confirm the lookup value actually exists in the first column of the range, and confirm FALSE is being used for an exact match rather than an unintended approximate match.
Try VLOOKUP live in the sheet →advancedTell me about a time you used Excel to solve a real business problem.
This is a behavioral question, not a formula question — the interviewer wants a specific story: what the problem was, what you built (a tracker, a dashboard, an automated report), and what the measurable outcome was (hours saved, errors reduced). Have one real example from your own work ready before the interview, not a hypothetical.
advancedWhat is the difference between XLOOKUP and INDEX-MATCH?
Both look in any direction. XLOOKUP is shorter, has a built-in not-found message, can search from the bottom, and can return several columns at once. INDEX-MATCH works in all Excel versions, so it is still the safe choice for files shared with people on old Excel.
Try XLOOKUP live in the sheet →advancedWhat are dynamic array functions and how do they change formulas?
Functions like FILTER, SORT, UNIQUE, SEQUENCE and XLOOKUP can return several values that spill into the cells below or beside the formula. You write one formula and the result expands by itself, which replaces many older array formulas and helper columns.
advancedHow would you return all matching rows, not just the first?
Use FILTER, such as =FILTER(A2:D100, B2:B100="North"). In older Excel without FILTER you need a helper column with a running count, or an INDEX and SMALL array formula.
advancedWhat does SUMPRODUCT do and when is it useful?
SUMPRODUCT multiplies matching items in arrays and adds the results. It is useful for weighted averages and for counting or summing with several conditions, including OR logic or conditions on calculated values, which SUMIFS cannot do.
Try SUMPRODUCT live in the sheet →advancedWhat is the LET function?
LET lets you name a calculation inside a formula and reuse it. It makes long formulas easier to read and can make them faster, because a repeated calculation is done once.
Try LET live in the sheet →advancedWhat is LAMBDA?
LAMBDA lets you create your own reusable function using Excel formulas, without VBA. You give it parameters and a calculation, name it in the Name Manager, and then use it like any other function.
Try LAMBDA live in the sheet →advancedWhat are the differences between OFFSET, INDEX and INDIRECT?
OFFSET and INDIRECT are volatile, so they recalculate all the time and can slow big files. INDEX is not volatile and can do most of the same jobs, so it is usually the better choice.
Try OFFSET live in the sheet →advancedHow do you make a dynamic named range?
Use an Excel Table, or define the name with INDEX, such as =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)). A table is simpler and is the better choice in modern Excel.
advancedWhat is Power Query and when do you use it instead of formulas?
Power Query is a tool for importing and cleaning data, and it records every step so it can be repeated. Use it when you get the same messy file every week, need to combine many files, merge tables or unpivot data. A refresh reruns everything.
advancedWhat is the difference between Merge and Append in Power Query?
Append stacks tables on top of each other, like combining monthly files with the same columns. Merge joins tables side by side using a matching column, like a lookup or a SQL join.
advancedWhat is the Data Model and Power Pivot?
The Data Model stores several related tables inside the workbook. Power Pivot lets you build relationships between them and write DAX measures, so one pivot table can use data from many tables without VLOOKUP.
advancedWhat is a pivot cache?
It is a copy of the source data that the pivot table keeps in memory. Pivot tables built from the same source can share one cache, which saves memory. It is also why you must refresh the pivot table to see new data.
advancedWhat are Goal Seek, Scenario Manager and Data Tables?
Goal Seek finds the single input value needed to reach a result you set. Scenario Manager stores sets of input values so you can switch between cases. Data Tables show how the result changes across a range of one or two inputs.
advancedWhat is Solver and when is it used?
Solver is an add-in that finds the best value for a target cell, such as lowest cost or highest profit, by changing several cells under limits you set. It is used for planning problems like budgets, staffing and product mix.
advancedHow do you make an Excel workbook run faster?
Remove unnecessary volatile functions and whole-column references, turn off automatic calculation while editing, replace repeated lookups with a helper column or XLOOKUP, use tables instead of huge ranges, and save as .xlsb for very large files.
advancedWhat does the Evaluate Formula tool do?
It steps through a formula part by part so you can see each intermediate result. It is the fastest way to find which part of a long formula is wrong. You find it under Formulas, then Evaluate Formula.
advancedHow would you audit an Excel file you did not build?
Check the sheet structure first, then use Trace Precedents and Dependents, Show Formulas (Ctrl+`), Go To Special for formulas, constants and errors, and the Name Manager. Look for hard-coded numbers inside formulas, broken links and mixed formulas down a column.
advancedHow do you reconcile two lists to find what is missing from each?
Use COUNTIF or XLOOKUP in a helper column on each list to flag items that are not found in the other, then filter on the flags. In Excel 365, FILTER with ISNA(XMATCH()) does it in one formula.
advancedWhat is the difference between a macro recorded and VBA written by hand?
A recorded macro copies your clicks exactly, including every unnecessary step, and uses fixed cell addresses. Hand-written VBA can use loops, variables and conditions, and can handle changing data. Many people record a macro and then clean up the code.
advancedHow do you design a clean Excel report or dashboard?
Keep raw data, calculations and the report on separate sheets. Build from a table, summarise with pivot tables or formulas, show a few key numbers first, use consistent formats, and add slicers for filtering. Always add checks that totals match the source.
advancedYou get a file where a total looks wrong. How do you investigate?
First check whether any numbers are stored as text and whether filters or hidden rows affect the total. Then check for duplicates, ranges that miss new rows, and manual overrides inside a formula column. Finally compare the total with a pivot table of the same data.
advancedHow do you handle very large data sets in Excel?
Keep the data in a table or the Data Model, use Power Query to load only the columns you need, avoid volatile and whole-column formulas, and summarise with pivot tables. If the data has more than a million rows, use a database or Power BI instead.
advancedHow do you find the top N items per group, such as the top 3 products in each region?
In Excel 365, use SORTBY and TAKE inside a GROUPBY or a MAP/UNIQUE setup. In older Excel, rank within each group with COUNTIFS, like =COUNTIFS(Region,A2,Sales,">"&B2)+1, then filter for ranks of 3 or less. A pivot table with a Top 10 value filter is another way.
advancedWhat is the difference between VLOOKUP, HLOOKUP, LOOKUP and XLOOKUP?
VLOOKUP searches down the first column, HLOOKUP searches across the first row, LOOKUP is an older version for sorted data, and XLOOKUP does the job of all three in any direction with a built-in not-found option.
Try VLOOKUP live in the sheet →advancedWhat is the difference between a calculated column and a measure in Power Pivot?
A calculated column is computed row by row and stored in the table. A measure is calculated when needed from the filters in the pivot table, and uses no storage. Measures are best for totals and ratios.
advancedHow do you create a dynamic chart that updates when new data is added?
Build the chart from an Excel Table, or from a dynamic named range, so the series range grows with the data. With a pivot chart, refresh after adding data.
advancedHow do you create a waterfall or bridge chart?
In Excel 2016 and later use Insert, Waterfall chart. Set the first and the total points as totals. Waterfall charts show how a start value changes through increases and decreases to an end value, like a profit bridge.
advancedWhat is the difference between a one-variable and a two-variable data table?
A one-variable data table shows how a result changes as one input changes. A two-variable table does it for two inputs at once, such as loan payment for different rates and terms. Both are found under What-If Analysis.
advancedHow do you create a formula-driven conditional format that highlights the top 10 percent?
Use Conditional Formatting, Top/Bottom Rules, Top 10%, or a formula rule such as =B2>=PERCENTILE.INC($B$2:$B$100,0.9).
advancedWhat does the INDIRECT function do and why is it risky?
INDIRECT turns text into a cell or range reference, for example to pick a sheet from a dropdown. It is volatile, slows large files, and breaks if sheet names change because Excel cannot track the text.
Try INDIRECT live in the sheet →advancedHow do you calculate a weighted average?
Use =SUMPRODUCT(values, weights)/SUM(weights). For example, marks and credits give the weighted score.
advancedHow do you calculate an XIRR or IRR, and what is the difference?
IRR assumes cash flows are at equal intervals, such as yearly. XIRR takes the actual dates of each cash flow, so it is more accurate for real investments.
Try XIRR live in the sheet →advancedWhat are the PMT, NPV and FV functions used for?
PMT gives the regular loan payment, NPV gives the present value of future cash flows at a discount rate, and FV gives the future value of regular savings. They are the three most asked finance functions.
Try PMT live in the sheet →advancedHow do you stop a lookup from returning wrong results when there are duplicates in the lookup column?
Find duplicates first, with COUNTIF or Conditional Formatting. Then either make the key unique (for example by joining ID and date), or use XLOOKUP with the right search mode, or FILTER to return all matches.
advancedWhat is data validation with a custom formula, and when would you use it?
Choose Custom in Data Validation and enter a formula that returns TRUE for valid entries, such as =COUNTIF($A:$A,A2)=1 to block duplicate IDs. It can enforce rules that the built-in options cannot.
advancedHow do you link data from another workbook, and what are the risks?
Type = and click a cell in the other workbook. The link breaks if the file is moved or renamed, and values can go out of date. Power Query is more reliable for repeating imports.
advancedHow do you combine data from multiple sheets with the same layout?
Use Power Query to append the sheets, or Data, Consolidate, or a 3D reference such as =SUM(Jan:Dec!B2). Power Query is best if sheets are added each month.
advancedHow would you automate a weekly report in Excel?
Load the raw file with Power Query, load the clean result into a table, build the report with pivot tables and formulas on top, and refresh everything with Data, Refresh All. Add a macro or Power Automate if the file must be sent too.
advancedWhat is the difference between Excel and Power BI for reporting?
Excel is flexible and good for analysis, quick calculations and one-off reports. Power BI handles larger data, has relationships and DAX, and is better for interactive dashboards that many people view online.
advancedHow do you check a report for accuracy before sharing it?
Compare totals with the source, add check cells that should equal zero, test with a few rows by hand, look for errors, blanks and duplicates, and confirm filters and date ranges. Peer review catches what you missed.
advancedWhat is a circular reference and how do you fix it?
It happens when a formula refers to its own cell, directly or through other cells. Excel shows a warning. Fix it by changing the formula, or use Formulas, Error Checking, Circular References to find the cell.
advancedHow do you use array constants in formulas?
An array constant is a list typed inside curly brackets, like {1,2,3}. For example =SUM(COUNTIF(A:A,{"North","South"})) counts two criteria in one formula.
advancedWhat is the difference between structured data, semi-structured and unstructured data?
Structured data fits into rows and columns, like a table. Semi-structured data has tags or keys, like JSON or XML. Unstructured data has no fixed layout, like emails, images or free text.
advancedWhat are best practices for building financial or business models in Excel?
Keep inputs, calculations and outputs separate, do not hard-code numbers inside formulas, use consistent formulas across rows, name key ranges, add checks and document the assumptions.
advancedWhat is the difference between XLOOKUP's match modes?
Match mode 0 is exact, -1 is exact or next smaller, 1 is exact or next larger, and 2 allows wildcards. The search mode controls the direction: first to last, last to first, or binary search.
Try XLOOKUP live in the sheet →advancedHow do you create a sparkline and what is it for?
Select the cell where it should go, then use Insert, Sparklines (Line, Column or Win/Loss) and point to the data range. A sparkline is a tiny chart in one cell that shows the trend of a row of data.
advancedHow would you explain a pivot table to a non-technical manager?
Say it is a tool that takes a long list and sums it up in a small table, by whatever you choose, such as by region or by month, and you can change the view in seconds without writing formulas.
