Excel Interview Questions

"VLOOKUP takes four arguments. First the value I'm looking for, say an employee ID. Second the table, where the IDs must be in the first column. Third the column number to bring back, counting from the left of that table, so 3 means the third column. Fourth is the match type. If I put FALSE or 0, it only returns an exact match and gives #N/A when there isn't one. If I leave it out, it defaults to approximate match, which assumes the first column is sorted in ascending order and returns the largest value that's less than or equal to mine. On an unsorted list of IDs that gives a wrong answer with no error, which is the dangerous part. So for IDs, names and codes I always use FALSE. Approximate match is for things like tax bands or commission tiers, where I want the band a value falls into."

"INDEX and MATCH split the lookup into two steps. MATCH finds where my value sits in one column, so MATCH of the employee ID in column A, with 0 for exact match, might give me position 57. INDEX then returns whatever is in position 57 of the column I choose. The first advantage is direction: the return column can be to the left of the key, which VLOOKUP can't do. The second is that there's no column number typed into the formula, so when someone inserts a column in the source sheet, my formula still points at the right data. It also only references the two columns it needs rather than a whole block. In newer Excel I'd often use XLOOKUP instead, but INDEX and MATCH works in every version, so it's still what I use in files that get shared widely."

"XLOOKUP takes a lookup range and a return range separately, so it can return from any column, left or right, and inserting columns doesn't break it. It defaults to exact match, which removes the most common VLOOKUP mistake. It has a fourth argument for what to show when nothing is found, so I don't need to wrap it in IFERROR. It can search from the last row upward, which is handy for the latest price or the most recent status, and if I give it a return range several columns wide, it spills all of them in one go. The one reason to hold back is compatibility. Excel 2019 and earlier don't have it, so if a client or another team opens the file in an older version, every XLOOKUP shows #NAME?. For those files I stick with INDEX and MATCH."

"First I test the two cells directly. If a formula comparing them returns FALSE when they look identical, it's a data problem, not a lookup problem. The most common cause is a trailing space from an export, so I check LEN on both and fix it with TRIM, or SUBSTITUTE for the non-breaking spaces you get from web data. The second cause is type: 1001 as a number and 1001 as text look the same but don't match. Left alignment or a green triangle gives it away, and I convert one side so both are numbers. If the data's fine, I look at the formula. Is the match type exact? For VLOOKUP, is the key in the first column of the range? And if it works in the top row but fails further down, the table range probably wasn't locked with dollar signs, so it slid down as I copied the formula."

"There are three ways, and I pick based on the file. The simplest is a helper column in the source that joins the two keys, say name, a separator and month, and then an ordinary lookup on the same joined value. It's easy for others to follow. Without a helper column, in newer Excel I use XLOOKUP looking for the value 1. I multiply two tests, name matches and month matches, which gives a list of ones and zeros, and XLOOKUP finds the first 1 and returns the sales from that row. INDEX with MATCH on 1 does the same in older versions, entered with Ctrl+Shift+Enter. And if I'm returning a number and each name and month appears once, SUMIFS with two conditions is the simplest of all. I just keep in mind that SUMIFS quietly adds up duplicates instead of flagging them."

"With nested IF, I test from the top down: if the score is at least 90 it's A, otherwise if it's at least 75 it's B, otherwise C. The order matters. If I tested 75 first, a score of 95 would stop there and get a B. With IFS, which is in newer versions, I write the same thing as pairs of condition and result, and I put TRUE as the last condition so everything else gets C. Without that, a score below 75 would return #N/A. Once there are more than four or five bands, I stop writing conditions at all. I put the thresholds in a small table, 0 for C, 75 for B, 90 for A, and use XLOOKUP with the match mode for the next smaller value, or VLOOKUP with approximate match on the sorted table. Then changing a threshold means editing a cell, not a formula."

"IFERROR is fine when I know which error I expect and it's harmless. A lookup against a price list where some new products aren't listed yet will return #N/A, and showing 'Not listed' is clearer than a wall of errors. The problem is that IFERROR catches everything. If someone deletes a column and the formula turns into #REF!, or a function name is mistyped and gives #NAME?, IFERROR shows the same friendly text and nobody notices the report is broken. So for lookups I use IFNA, which only catches the not-found case and lets real errors through. For division I test the denominator with an IF instead. And I'm careful with zero as the fallback inside totals, because it makes missing data look like a real zero and the total looks complete when it isn't."

"A relative reference like A1 moves when I copy the formula. Copy it one row down and it becomes A2, because Excel stores it as 'the cell this far away'. An absolute reference has a dollar sign before the column letter and the row number, so it always points at the same cell. I use that for a single input, like an exchange rate in E1 that every row multiplies by. Mixed references lock just one part. The classic case is a grid, like quantities down column A and prices across row 1, with the total in each cell. The formula must always read the quantity from column A, so I lock the column, and always read the price from row 1, so I lock the row. Then one formula fills the whole grid. While typing a reference, F4 cycles through all four versions."

"I press Ctrl+T on the data and give the Table a proper name, like Sales. From then on, when someone pastes new rows underneath, the Table grows to include them, and any pivot, chart or formula pointing at it picks them up without anyone editing a range. The formulas get easier to read too. Instead of a sum of D2 to D5000, I write the sum of the Amount column in Sales, and inside the Table a formula like quantity times price uses the at sign to mean this row. When I type that once, Excel fills it down the whole column and keeps it consistent, so there's no row where someone overwrote the formula. The one thing to know is that a few places, like data validation lists, don't accept Table references directly, so I point those at a named range or use INDIRECT."

"SUMIF handles one condition and SUMIFS handles several, all of which must be true. The trap is the argument order. SUMIF puts the range to add at the end, SUMIFS puts it first, so I just use SUMIFS everywhere to avoid mixing them up. For one region in one month, the range to add is the sales amount, the first condition is region equals North, and the month needs two conditions on the date column: greater than or equal to the first of the month, and less than the first of the next month. I build those with the comparison sign in quotes, joined to a cell holding the date. Using 'before the next month's first day' rather than 'up to the last day' also catches dates that carry a time, like the afternoon of the 31st."

"COUNTIFS takes pairs of a range and a condition, and a row only counts if all of them are true. So North orders above the value in E1 is the region column equals North, and the amount column is greater than E1. The condition has to be text, so I write the greater-than sign in quotes and join it to E1 with an ampersand. Wildcards help with messy labels, like an asterisk on both sides of North to catch North East and North West. OR is the part people get wrong. COUNTIFS can't do OR on its own, so for North or South I either add two COUNTIFS, or give the region condition a list in curly braces and wrap it all in SUM. I'm careful when the two OR conditions sit on different columns, because adding them counts rows that match both twice."

"At its simplest, SUMPRODUCT multiplies two ranges item by item and adds the results. Quantity times unit price across a list gives total revenue in one cell, and SUMPRODUCT of scores and weights divided by the sum of the weights gives a weighted average. The more useful side is conditions. A test like region equals North gives a list of TRUE and FALSE, and multiplying tests together turns them into ones and zeros, so only matching rows survive. I reach for it when SUMIFS can't do the job, usually because I need a function applied to the range itself. SUMIFS can't say 'where the month of the date is 3' across every year, but SUMPRODUCT can. The cost is speed, since it works through every cell, so I keep ranges tight and avoid whole columns. In newer Excel a plain SUM with the same logic works too."

"A pivot table summarises a big list without formulas. I drag fields into rows, columns and values, and Excel groups and totals them. Before building it I check the source: one header row, no blank rows, no merged cells, and each column holding one type of data. I convert it to a Table first so new rows are included later. Then Insert, PivotTable. Region goes to Rows, the order date to Columns, and the amount to Values. For the dates, I group by Months and Years together, because grouping by month alone would lump January of two different years into one column. I check the value field says Sum and not Count, and format the numbers. The thing that catches beginners is that a pivot doesn't update on its own. After new data arrives, I press Refresh, or Refresh All for every pivot in the file."

"A slicer is a panel of buttons for one field, like Region. Clicking North filters the pivot, and unlike the old filter drop-down, it shows everyone exactly what's selected, which matters on a dashboard. I add it from the PivotTable Analyze tab with Insert Slicer. To make one slicer drive two pivots, say sales by product and sales by month feeding a chart, I right-click the slicer, choose Report Connections, and tick both. That only works if both pivots are built from the same source, which is another reason to build everything from one Table. For dates I use a Timeline instead, which lets people pick months or quarters by dragging. Then I line the slicers up, set the number of button columns so they fit, and test the dashboard with someone who didn't build it."

"I don't assume either is wrong yet. First I'd ask finance how their number is defined: net of returns or not, by order date or invoice date, and whether cancelled orders count. Mismatches are often definition gaps, not errors. Then I'd check my side. Is the pivot refreshed? Is a filter hiding something? Are some amounts stored as text, so they aren't summed? Are there duplicate rows from two overlapping exports? And if the dates carry times, a cut-off written as 'up to the 31st' can miss the afternoon of the 31st. If it still doesn't match, I break both numbers down the same way, by region, then by day, until the gap sits in a small set of rows. Double-clicking a pivot value lists the rows behind it, which makes that quick. Then I explain the difference in one clear line."

"The first question is what a duplicate means here. Two rows with the same customer name might be two real orders, so I decide which columns together make a row the same, often an ID or a combination like customer, date and amount. Then I look before deleting. The Duplicate Values rule in conditional formatting highlights repeats in one column, and for several columns I add a COUNTIFS helper that counts matching rows, then filter for anything above 1. That tells me how many there are and whether they're true copies. To remove them, I work on a copy of the sheet and use Data, Remove Duplicates, ticking only the columns that define a duplicate. It keeps the first row it meets and deletes the rest, so I sort first if I want to keep, say, the latest record. In newer Excel, UNIQUE gives a clean list without touching the original."

"I clean into helper columns first so I can compare with the original. For spaces, TRIM removes spaces at the start and end and cuts runs of spaces inside the text down to one. It doesn't touch the non-breaking space you often get from web pages, which is character 160, so I SUBSTITUTE that for a normal space first and then TRIM. CLEAN removes non-printing characters like line breaks from system exports. For numbers stored as text, the signs are left alignment, a green triangle, and a SUM that comes out too low because SUM skips text. I fix a whole column with Data, Text to Columns and just press Finish, or wrap the cleaned text in VALUE. Once it's clean, I paste the results as values over the original and delete the helpers. If the export arrives every week, I'd move all of this into Power Query so it's one refresh."

"I select the whole data range, say A2 to F500, making sure A2 is the active cell. Then Conditional Formatting, New Rule, and the option to use a formula. The formula is written as if for the first row: status in E2 equals Overdue. The key is the reference. I put a dollar sign before the E but not before the 2. Locking the column means every cell across the row, A to F, looks at column E. Leaving the row free means row 3 checks E3 and row 4 checks E4. If I lock both, every row copies row 2's answer, and if I lock neither, each cell checks a different column. Then I pick a fill colour. Rather than relying on someone typing Overdue, I'd often compare the due date with TODAY, so rows turn red on their own when the date passes."

"For a simple list, I select the cells, go to Data, Data Validation, choose List, and point the source at a range on a lookup sheet. I avoid typing values into the box, because then changing the list means editing every rule. For a dependent list, like Category then Product, the classic method is a named range for each category's products, named exactly like the category. The second cell's validation source is INDIRECT of the first cell, which turns the chosen text into that range. Names can't contain spaces, so I use underscores and SUBSTITUTE spaces inside the INDIRECT. For a single input cell, like a form, newer Excel lets me put a FILTER formula in a helper cell and point the validation at its spill range, which avoids a pile of named ranges. Either way, changing the category leaves the old product sitting there, so I add a check that flags mismatches."

"First, a one-minute question to my manager: which numbers do you need, and is this for a meeting or a decision? That usually cuts the job down to a couple of measures by a couple of groupings. Then I copy the export to a new sheet and never touch the original. I don't try to clean everything. I make it a Table, filter each key column to spot blanks, text in number columns and obvious duplicates, and fix only what affects those measures. Then a pivot gives the summary in minutes. Before sending, I sanity-check one total against something I trust, like last month's report or a figure from the system. I send it on time with a line or two saying what I cleaned, what I didn't, and anything that might move the numbers, and I offer a fuller version by the end of the day."

"For a one-off job, Flash Fill is quickest. I type the first name for two rows, press Ctrl+E, and Excel copies the pattern. Text to Columns on a space works too, but middle names push the surname into a third column. When the list will be refreshed, I use formulas. The first name is LEFT of the text up to the first space, which I locate with FIND. The last name is trickier with middle names, because I want the text after the last space. In newer Excel, TEXTAFTER with an instance number of minus one does exactly that. In older versions there's a trick: swap each space for a hundred spaces, take the right-most hundred characters and TRIM them. I TRIM the source first, because double spaces break everything, and I wrap the first-name formula in IFERROR so a single-word name doesn't error."

"Excel stores a date as a serial number counting days from the start of 1900, and the time is the decimal part, so noon is point five. That's why a date formatted as General shows a five-digit number, and why subtracting two dates gives days directly. For age or tenure in whole years, I use DATEDIF with the start date, TODAY, and the unit y. It's an old compatibility function that doesn't show up in the suggestions as you type, so people think it doesn't exist, but it works. YEARFRAC gives years as a decimal, which is useful for things like pro-rating. What I avoid is days divided by 365, because leap years make it drift, and someone's age changes a day early or late. And TODAY recalculates every time the file opens, so for a report as at a fixed date, I point at a date cell instead."

"For a due date, I use WORKDAY with today's date, 10, and a range holding the company's holiday list. It skips Saturdays, Sundays and anything in that list. I keep the holidays on their own sheet as a Table, so next year's dates just get added. If the question is the other way round, how many working days between two dates, that's NETWORKDAYS, which counts both the start and the end day. Where the weekend isn't Saturday and Sunday, WORKDAY.INTL and NETWORKDAYS.INTL take a weekend code, or a seven-character string of ones and zeros. For month ends, EOMONTH with 0 gives the last day of the same month, with minus 1 it gives last month's end, and adding one day to that gives the first of this month. That's the pattern I use for reporting-period formulas."

"I start from what the reader should see. Comparing categories, like sales by region, is a bar or column chart. A trend over months is a line. A pie only works for a handful of parts that add up to one whole, and even then a bar is usually easier to read. For actual against target, I select both series and use a combo chart: actual as columns and target as a line across them, so any month below the line jumps out. I'd only put the target on a secondary axis if it were in different units, because two axes for the same units make gaps look bigger or smaller than they are. Then I make the title say the point, like 'North missed target in March and April', remove 3D effects and clutter, and base the chart on a Table so new months appear by themselves."

"All three live under Data, What-If Analysis, and they answer different questions. Goal Seek works backwards. I tell it the profit cell should equal zero by changing units sold, and it finds the break-even volume. It changes one input and gives one answer. A Data Table shows many answers at once. For a loan, I put interest rates down the side and terms across the top, link the corner cell to the payment formula, and get a full grid of payments. That's my go-to for sensitivity. Scenario Manager stores named sets of several inputs, like best, expected and worst case for price, volume and cost, and builds a summary comparing them. If the question has limits, like the most profit within a budget, that's Solver, an add-in. One caution: big data tables slow recalculation, so I sometimes set calculation to automatic except for data tables."

"Yes. I'd start by writing down every step the person does, because much of it is usually cleaning that Power Query handles better: import the export, fix types, remove blanks, merge a lookup, and it becomes one refresh. For the rest, like formatting, sorting and saving a PDF, I'd record a macro while doing the steps once, then open it in the VBA editor. Recorded code is brittle. It selects cells and hard-codes ranges like A1 to F200, so next week's 250 rows get cut off. I replace those with code that finds the last filled row, and remove the Select statements. I save it as .xlsm, since a normal .xlsx drops macros. Excel now blocks macros by default in files that came from email or the internet, and many IT teams lock them down further, so I check how people will open it before promising anything. I also add a short note on what the macro does and who owns it."

"First I'd save a copy and set calculation to manual, so people can keep working while I investigate. Then I'd look for the usual causes. Volatile functions like OFFSET, INDIRECT, TODAY and NOW recalculate after every edit and drag everything that depends on them along. SUMPRODUCT or array formulas over entire columns are the next suspect, because each one checks over a million rows. I'd also press Ctrl+End on each sheet. If it jumps far past the real data, the used range is bloated with old formatting, which I clear. Hundreds of copied conditional formatting rules are another quiet cost. For fixes, I replace repeated lookups with one MATCH in a helper column, point formulas at Tables instead of whole columns, and paste closed months as values. If the heavy part is summarising raw data, a pivot or Power Query does it far faster than thousands of formulas."

"At my last company I inherited a monthly commission sheet. While preparing my first run, I noticed the team total didn't match the sales system. I traced it to a SUMIFS whose range stopped at row 400, and the data had grown past that, so the last few deals were never counted, and it had been that way for two months. I fixed the range, worked out which reps were underpaid and by how much, and took it to my manager first, with the numbers and the cause. We paid the difference in the next cycle. Then I converted the data to a Table so the ranges grow on their own, and added a check cell comparing the sheet's total with the system export, which turns red if they differ. It has caught two other problems since."

"In my internship, the operations team built a weekly stock report by copying three exports into one sheet, matching item codes by eye, and colouring low-stock rows by hand. It took someone most of a Monday morning. I set up a template where the three exports landed in Tables, used XLOOKUP to pull the supplier and reorder level onto the main list, and added a formula-based conditional format to flag anything below its reorder level. A pivot summarised stock by warehouse. After that, the job was paste three files and refresh, about fifteen minutes. The bigger win was accuracy, because matching by eye had been missing items with codes that looked alike. I wrote a one-page note so anyone could run it, and my manager asked me to do the same for the monthly version."

"In my last role, store managers filled in a weekly expense sheet, and every week a few came back with formulas typed over, dates in odd formats, or categories spelled five different ways. I rebuilt it with one input sheet and a separate calculation sheet. Input cells had a light fill colour, and everything else was locked with sheet protection. Categories came from a drop-down, dates had validation that only allowed dates in the current month, and amounts had to be positive numbers. I added a line of instructions at the top and a check column that showed a message if a row was incomplete. Errors went from a few sheets a week to almost none, and the questions I got changed from 'why is it broken' to requests for new categories, which I could add in one place."