ExcelReady Blog
Short, plain-English answers to the Excel questions job seekers search for: how a function works, which one to use, and what the test will ask. Every guide starts with the answer and ends with a live spreadsheet to practise on.
#SPILL! means a formula that returns several results can't place them, usually because a cell in the way isn't empty. Click the formula, then the warning icon, to see the reason. Clear or move whatever blocks the spill range, unmerge merged cells, move the formula out of an Excel table, or use a smaller range instead of a whole column.
Before an Excel test, practise ten tasks: fill a calculation down, build a % of total column, count by condition, count and sum with two conditions, fill a summary grid, look up to the left, find the name of the top item, do a two-way lookup, handle missing data, and summarize a case study by group.
Common Excel interview questions cover formulas and order of operations, relative and absolute ($) references, VLOOKUP, INDEX MATCH and XLOOKUP, IF with AND or OR, COUNTIFS and SUMIFS, pivot tables, filters and fixing errors. Good answers name the function, give its arguments in order and explain one limitation or edge case.
Two functions that look alike? Each comparison shows both on the same data and ends with a clear recommendation.
Use MEDIAN to describe a typical value when a few very large or very small numbers could skew the result, such as salaries, house prices or order values. Use AVERAGE when the values are fairly even, or when you need a number that multiplies back to the total, as in budgets. When the two differ a lot, report both.
Use & or CONCAT to join a few pieces of text, and TEXTJOIN when every piece gets the same separator or some cells may be empty. TEXTJOIN adds the separator for you and can skip blanks. CONCATENATE is the older version of CONCAT, kept for compatibility. CONCAT and TEXTJOIN need Excel 2019 or later.
COUNT counts only cells that hold numbers, including dates. COUNTA counts every cell that isn't empty: numbers, text, TRUE or FALSE, and errors. COUNTBLANK counts the empty cells. Use COUNT for numeric data, COUNTA for "how many entries" and COUNTBLANK for "how many are missing". All three work in every Excel version.
Excel on Mac and Windows has the same formulas and almost the same features. Most shortcuts swap Ctrl for ⌘ (Ctrl+C becomes ⌘C), but some differ: AutoSum is ⌘⇧T, locking a reference is ⌘T, editing a cell is Control+U or fn+F2, and the number-format shortcuts keep Control. Power Pivot is Windows-only.
For job tests, the core skills are the same in Excel and Google Sheets: cell references, SUM, IF, COUNTIFS, SUMIFS, VLOOKUP, XLOOKUP and pivot tables work almost identically. The differences are in details: FILTER takes different arguments, error messages differ, Sheets adds QUERY, and some shortcuts and features are Excel-only. Practise in the tool the test uses.
Use AutoFilter (Ctrl+Shift+L) to look at a subset of rows quickly: it hides the other rows in place and works in every Excel version. Use the FILTER function when you need the matching rows in a separate, live list that updates by itself, such as a report or a dashboard. FILTER needs Excel 2021, Excel 2024 or Microsoft 365.
What employers test, the Excel skills each role needs, and how to prepare for an Excel assessment with confidence.
Before an Excel test, practise ten tasks: fill a calculation down, build a % of total column, count by condition, count and sum with two conditions, fill a summary grid, look up to the left, find the name of the top item, do a two-way lookup, handle missing data, and summarize a case study by group.
Common Excel interview questions cover formulas and order of operations, relative and absolute ($) references, VLOOKUP, INDEX MATCH and XLOOKUP, IF with AND or OR, COUNTIFS and SUMIFS, pivot tables, filters and fixing errors. Good answers name the function, give its arguments in order and explain one limitation or edge case.
An Excel assessment test usually gives you a dataset and practical questions: totals, IF, COUNTIFS and SUMIFS, lookups, sorting, filtering and sometimes pivot tables or a short case study. Scoring commonly checks that the result is right, that it comes from a formula rather than a typed number, and that it still works when inputs change.
The Excel shortcuts that save the most time in a test are Ctrl+Arrow to jump to the edge of your data, Ctrl+Shift+Arrow to select to the edge, Ctrl+D and Ctrl+R to fill formulas, F4 to add $ signs, Alt+= for AutoSum and Ctrl+Shift+L for filters. On a Mac, most use ⌘ instead of Ctrl.
A data analyst needs Excel skills for every stage of an analysis: cleaning text and dates, joining tables with XLOOKUP or INDEX MATCH, pulling subsets with FILTER, UNIQUE and SORT, summarizing with COUNTIFS, SUMIFS and pivot tables, and checking results. Knowing when a pivot table or a formula is the better tool matters as much as the syntax.
The core Excel skills for admin jobs are entering and editing data quickly, moving and selecting with the keyboard, totals with SUM, counts with COUNTIF, sorting and filtering lists, and clear formatting for currency, dates and headers. Add SUMIF and a simple lookup and you can handle most trackers, logs and reports an office uses.
Cells, formulas, references and the everyday shortcuts every Excel user, and every Excel test, takes for granted.
The edit cell Excel shortcut is F2 on Windows. On a Mac, press Control+U or fn+F2. It opens the cell with the cursor at the end, so you can fix part of it without retyping. Esc cancels an edit, Delete clears the selected cells, Ctrl+Z (⌘Z) undoes your last change and Ctrl+Y (⌘Y) redoes it.
An Excel cell reference is the address of a cell: its column letter, then its row number, such as B3. Formulas use references instead of typed numbers, so the result updates when the data changes. References are relative (B3) by default and move when you copy them. Add dollar signs ($B$3) to lock them.
Excel calculates a formula in a fixed order: anything in parentheses first, then negative signs, percent, powers (^), then multiplication and division, then addition and subtraction. Signs at the same level go from left to right. So =2+3*4 is 14, not 20, because Excel multiplies 3*4 first. Write =(2+3)*4 to add first.
The Excel fill down shortcut is Ctrl+D (⌘D on a Mac). Select the cell with the formula and the empty cells below it, then press it. Ctrl+R (⌘R) fills right in the same way. Both copy the first cell's contents and format, and relative references shift, so each row or column uses its own cells.
To format numbers in Excel, select the cells and press a shortcut: Ctrl+Shift+$ for currency, Ctrl+Shift+% for percent and Ctrl+Shift+# for a date. On a Mac, use Control (⌃), not ⌘. Ctrl+1 (⌘1) opens Format Cells for more choices. Formatting changes only how a number looks; the value Excel stores stays the same.
To copy a formula without changing its references, lock them with dollar signs before you copy: $F$1 always points at F1. To keep a formula exactly as it is, cut and paste it instead (a moved formula keeps its references), copy its text from the formula bar, or press Ctrl+' (⌃' on a Mac) to copy the formula above.
SUM, AVERAGE, COUNT, rounding and percentages: the calculations behind almost every spreadsheet.
An Excel absolute reference uses dollar signs, like $F$1, to lock a cell so it doesn't change when you copy or fill a formula. Use it for a value every row shares, such as a tax rate: =B2*(1+$F$1). Press F4 while editing to add the $ signs. $F1 locks only the column; F$1 locks only the row.
The Excel AutoSum shortcut is Alt+= on Windows and ⌘+Shift+T on a Mac. Click the empty cell below a column of numbers, press the shortcut, and Excel writes a SUM formula with a guessed range, such as =SUM(C2:C5). Check the highlighted range, then press Enter to accept it.
AVERAGE returns the mean of a range, MIN returns the smallest number and MAX returns the largest. All three take the same input: =AVERAGE(B2:B6), =MIN(B2:B6) and =MAX(B2:B6). They skip empty cells and text but count zeros, and =MAX(B2:B6)-MIN(B2:B6) shows how spread out the values are.
The Excel COUNT function counts cells that contain numbers, such as =COUNT(B2:B7). COUNTA counts every cell that isn't empty, numbers and text alike, and COUNTBLANK counts the empty cells. Use =COUNTA(B2:B7)-COUNT(B2:B7) to find values that are filled in but aren't real numbers.
The Excel LARGE function returns the k-th largest number in a range: =LARGE(B2:B9,2) gives the second highest. SMALL works the same way from the bottom, so =SMALL(B2:B9,2) gives the second lowest. To total the top 3, add three LARGE results with k = 1, 2 and 3.
Use =ROUND(number, digits) to round to a set number of decimal places, so =ROUND(B2,2) turns 3.4567 into 3.46. ROUNDUP always rounds away from zero and ROUNDDOWN always rounds towards zero, using the same two arguments. Unlike number formatting, these functions change the stored value that later formulas use.
IF, AND, OR, IFS and IFERROR: make your formulas decide, check conditions and handle errors cleanly.
AND returns TRUE only when every condition is TRUE. OR returns TRUE when at least one condition is TRUE. NOT flips TRUE to FALSE and FALSE to TRUE. On their own they give TRUE or FALSE, so you usually put them inside IF's test, like =IF(AND(C2>=50000,D2>=4.5),"Bonus","No bonus").
In Excel, "not equal to" is written <>, so =A2<>"Delivered" returns TRUE when A2 is anything other than Delivered. Excel has six comparison operators: = equal, <> not equal, > greater than, < less than, >= greater than or equal, and <= less than or equal. Each returns TRUE or FALSE.
IFERROR returns a formula's normal result, or a value you choose if the formula gives an error: =IFERROR(value, value_if_error). For example, =IFERROR(B2/C2,0) shows 0 instead of #DIV/0!. It catches every error type, including mistakes in your own formula, so check the formula works first, and use IFNA for lookups.
ISBLANK returns TRUE when a cell is completely empty, ISNUMBER returns TRUE when it holds a real number, and ISTEXT returns TRUE when it holds text. Each takes one cell, like =ISBLANK(C2). They are most useful inside IF, for example =IF(ISNUMBER(C2),"OK","Check"), to catch bad data before you calculate with it.
A nested IF puts a second IF in the value_if_false slot of the first, so one formula can return three or more results. For grades: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))). Excel checks the tests in order and stops at the first TRUE one, so test the highest band first.
The Excel IF function checks a condition and returns one value when it's TRUE and another when it's FALSE. The syntax is =IF(logical_test, value_if_true, value_if_false). For example, =IF(B2>=70,"Pass","Fail") shows Pass for a score of 70 or more and Fail for anything lower. Text results go in double quotes.
COUNTIF, SUMIF, AVERAGEIF and their multi-condition versions, for totals and counts that meet your criteria.
AVERAGEIF averages the numbers in rows that meet one condition: =AVERAGEIF(range, criteria, average_range), for example =AVERAGEIF(B2:B9,"Marketing",D2:D9). AVERAGEIFS handles two or more conditions and puts the numbers to average first: =AVERAGEIFS(D2:D9,B2:B9,"Finance",C2:C9,"Senior"). If no row matches, both return the #DIV/0! error, so check your criteria or wrap the formula in IFERROR.
COUNTIFS counts rows that meet several conditions, and SUMIFS adds numbers from those rows. Each condition is a pair: a range, then its criteria. A row counts only if it matches every pair. SUMIFS puts the sum range first: =SUMIFS(E2:E9,A2:A9,"North",C2:C9,"Laptop") totals North laptop revenue.
Excel has three wildcard characters for criteria. An asterisk (*) matches any number of characters, so "CAB-*" means starts with CAB-. A question mark (?) matches exactly one character. A tilde (~) before * or ? makes it a normal character. Use them inside quotes in COUNTIF, SUMIF, COUNTIFS and SUMIFS, for example =COUNTIF(A2:A9,"CAB-*").
COUNTIF counts the cells in a range that meet one condition. Write =COUNTIF(range, criteria), for example =COUNTIF(D2:D9,"Open") to count open tickets. Put text and comparisons in double quotes, like ">=8" or "<>Low". Numbers and cell references go in without quotes. For two or more conditions, use COUNTIFS.
SUMIF adds up the numbers in the rows that meet one condition. Write =SUMIF(range, criteria, sum_range): the cells to check, what they must match, then the numbers to add. For example, =SUMIF(C2:C9,"Travel",D2:D9) totals all Travel claims. Leave out sum_range when the condition is about the numbers themselves.
MAXIFS returns the highest number among the rows that meet one or more conditions, and MINIFS returns the lowest. Both put the numbers first, then range/criteria pairs: =MAXIFS(D2:D9,B2:B9,"Marketing") finds the top Marketing salary. They need Excel 2019 or newer. If nothing matches, they return 0, not an error.
VLOOKUP, INDEX MATCH and XLOOKUP explained step by step, with the fixes for #N/A and other lookup errors.
HLOOKUP searches across the top row of a table and returns a value from a row below it: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE). Use it for sideways tables, like a rate card with zones across the top. It is VLOOKUP turned on its side. In Excel 2021 or Microsoft 365, XLOOKUP does the same job more safely.
#N/A means "not available": the lookup couldn't find your value. First find the cause: extra spaces (fix with TRIM), numbers stored as text, a typo, or a table range that moved because it wasn't locked with $. If the item is truly missing, show a message with IFNA, or with XLOOKUP's 4th argument.
VLOOKUP can't look to the left: it searches the first column of its table and only returns columns to the right. To return a column on the left, use INDEX MATCH, =INDEX(A2:A9,MATCH(G2,B2:B9,0)), which works in every version, or XLOOKUP, =XLOOKUP(G2,B2:B9,A2:A9), in Excel 2021 or Microsoft 365.
INDEX MATCH is a lookup built from two functions. MATCH finds the position of a value in one column, and INDEX returns the item at that position from another column: =INDEX(A2:A9,MATCH(G2,B2:B9,0)). It can look left, it keeps working when columns are inserted, and it runs in every version of Excel.
A two-way lookup finds the value where a row and a column meet, like units sold for one product in one month. Use INDEX with two MATCHes: =INDEX(B2:E6,MATCH(H1,A2:A6,0),MATCH(H2,B1:E1,0)). The first MATCH finds the row, the second finds the column, and INDEX returns the cell where they cross.
A VLOOKUP approximate match uses TRUE as the 4th argument, like =VLOOKUP(B2,$F$2:$H$5,3,TRUE). It finds the largest value in the first column that is less than or equal to your number, so 62,000 lands in the band that starts at 50,000. The first column must be sorted from smallest to largest.
Clean, split, join and search text data with TRIM, LEFT, MID, TEXTJOIN and friends.
Use TRIM to remove extra spaces, CLEAN to remove hidden non-printing characters, and PROPER to put a capital at the start of each word. Nest them to fix several problems at once: =PROPER(TRIM(A2)) turns " maria gonzalez" into "Maria Gonzalez". Build the clean version in a new column, check it, then paste it as values.
FIND and SEARCH return the position of some text inside a cell, as a number. =FIND(" ",A2) gives 5 for Dana Whitfield, because the space is the 5th character. FIND is case-sensitive; SEARCH ignores capitals and allows wildcards. Both return #VALUE! when the text isn't there, so wrap them in ISNUMBER to test whether a cell contains a word.
To combine text in Excel, join the pieces with &: =A2&" "&B2 turns Nora and Delgado into Nora Delgado. CONCAT does the same with a list of pieces. TEXTJOIN puts the same separator between every piece and can skip empty cells: =TEXTJOIN(", ",TRUE,C2:E2). Type spaces and commas yourself, inside double quotes.
To split a full name in Excel, use =TEXTBEFORE(A2," ") for the first name and =TEXTAFTER(A2," ",-1) for the last name, which also handles middle names. In Excel 2021 and older, use =LEFT(A2,FIND(" ",A2)-1) for the first name. For a one-off job, Data > Text to Columns or Flash Fill splits names without formulas.
LEFT, RIGHT and MID pull part of the text out of a cell. LEFT(text, num_chars) takes characters from the start, RIGHT(text, num_chars) takes them from the end, and MID(text, start_num, num_chars) takes them from any position. For TX-ELC-40512, LEFT(A2,2) gives TX, MID(A2,4,3) gives ELC and RIGHT(A2,5) gives 40512.
TEXTBEFORE returns the text before a delimiter, and TEXTAFTER returns the text after it. =TEXTBEFORE(A2,"@") gives the username of an email, and =TEXTAFTER(A2,"@") gives the domain. A third argument picks which occurrence: -1 means the last one. They need Microsoft 365, Excel for the web or Excel 2024; in older Excel, use LEFT or MID with FIND.
Build dates, count the days between them and work with today's date without surprises.
To calculate age in Excel, use =DATEDIF(B2,TODAY(),"Y"), where B2 is the birth date. It counts complete years, so the age only goes up on the birthday. Years of service work the same way with the hire date. For a report that must not change, replace TODAY() with a fixed "as of" date cell.
NETWORKDAYS counts the working days (Monday to Friday) between two dates, including both dates: =NETWORKDAYS(start_date, end_date, [holidays]). WORKDAY does the reverse and returns the date a number of working days away: =WORKDAY(start_date, days, [holidays]). Both skip weekends, and both skip any dates you list as holidays.
The Excel DATE function builds a real date from three numbers: =DATE(year, month, day), always in that order. YEAR, MONTH and DAY do the opposite and pull one part out of a date, like =YEAR(B2). Because Excel stores dates as serial numbers, the results can be added, subtracted and compared.
To count the days between two dates in Excel, subtract the earlier date from the later one: =C2-B2. DAYS does the same with the end date first: =DAYS(C2,B2). For whole months or years, use DATEDIF with the start date first and a unit in quotes, like =DATEDIF(B2,C2,"Y").
EDATE moves a date by whole months: =EDATE(start_date, months) keeps the day where it can, so Mar 15 plus 12 months is Mar 15 next year. EOMONTH moves by months and then jumps to the month's last day: =EOMONTH(B2,0) is the end of B2's month. Negative months go backwards.
The Excel WEEKDAY function returns the day of the week as a number: =WEEKDAY(date) gives 1 for Sunday through 7 for Saturday. Add a return type of 2, as in =WEEKDAY(date,2), to make Monday 1 and Sunday 7. Then =WEEKDAY(date,2)>5 is TRUE only for Saturdays and Sundays.
UNIQUE, FILTER and SORT: modern Excel formulas that return whole lists at once.
#SPILL! means a formula that returns several results can't place them, usually because a cell in the way isn't empty. Click the formula, then the warning icon, to see the reason. Clear or move whatever blocks the spill range, unmerge merged cells, move the formula out of an Excel table, or use a smaller range instead of a whole column.
SORT returns a sorted copy of a range: =SORT(A2:C9,3,-1) sorts by the 3rd column, largest first. SORTBY sorts one range by the values in another, so the column you sort by doesn't have to be in the result, and it handles several sort levels. Both leave the original data unchanged and need Excel 2021, Excel 2024 or Microsoft 365.
FILTER returns every row of a range that passes a test: =FILTER(A2:C9,B2:B9="East"). The matching rows spill into the cells below and update when the data changes. For AND, multiply tests in brackets; for OR, add them. The third argument shows a message when nothing matches. FILTER needs Excel 2021, Excel 2024 or Microsoft 365.
The UNIQUE function returns each different value in a range once, in the order it first appears: =UNIQUE(C2:C9). Type it in one cell and the results spill into the cells below. Wrap it in COUNTA to count distinct values, or in SORT to put them in order. UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365.
Filters, conditional formatting, pivot tables and multi-sheet work for summarizing real data.
Conditional formatting colors cells automatically when they meet a rule. Select the cells, go to Home > Conditional Formatting, and pick a rule such as Highlight Cells Rules > Greater Than. To color whole rows, use New Rule > Use a formula, with a $ before the column letter: =$E2>5000. Rules update whenever values change.
To filter data in Excel, click any cell in your table and press Ctrl+Shift+L (Mac: ⌘⇧F), or choose Data > Filter. Arrow buttons appear in the header row. Click an arrow, untick (Select All), tick the values you want and click OK. Hidden rows aren't deleted: clear the filter and they come back.
To reference another sheet, type the sheet name, an exclamation mark, then the cell: =Prices!B3. If the name has a space, put single quotes around it: ='Last Year'!B5. The easiest way is to type =, click the other sheet's tab, click the cell and press Enter, and Excel writes the reference for you.
To show a percent of total in a pivot table, click the ▾ on the field in the Values area, choose Value Field Settings, open Show Values As and pick % of Grand Total. Each number then shows its share of the total, and the parts add up to 100%. You can also right-click a value and choose Show Values As.
A pivot table summarizes a long table in a few clicks, without formulas. Click a cell in your data, choose Insert > PivotTable, and click OK to put it on a new sheet. Then drag fields into Rows, Columns and Values: Region in Rows and Sales in Values gives total sales for each region.
SUBTOTAL calculates a total that skips rows hidden by a filter: =SUBTOTAL(109,D2:D11) adds only the visible amounts, while SUM adds every row. The first argument picks the calculation: 9 or 109 sums, 1 or 101 averages, 3 or 103 counts non-empty cells. The 100s also skip rows you hid by hand.