Your Excel path

Cells, navigation and the shortcuts every Excel test assumes.

• 1Cells, rows and columnsFreeLearn how every cell gets its name, and how to select cells and ranges. 3 min
• 2Select ranges with ShiftFree with accountHold Shift with the arrow keys to select as you go, then grab whole columns, rows and tables in one keystroke. 4 min
• 3Move around the sheetGet around a sheet from the keyboard: Enter and Tab, jumping to A1 and the last cell, and the Name Box. 4 min
• 4Edit, clear and undoFix part of a cell without retyping it, cancel an edit, clear cells and undo mistakes. 4 min
• 5Your first formulasType values, write formulas that start with =, and see how they recalculate. 4 min
• 6Jump to the edge of your dataJump to the edge of your data instantly instead of scrolling. 3 min
• 7Fill Down and Fill RightCopy a formula down a column or across a row in one keystroke. 4 min
• 8Copy, cut and pasteCopy and paste formulas, see how their references shift, and move cells with cut. 4 min
• 9Formatting shortcutsBold headers and format currency and percentages from the keyboard. 3 min
• 10More formattingItalic and underline, comma style, date format, and resetting a cell to General. 3 min
• 11Skill check: basicsFix, calculate, fill and format a supplier order on your own, with no hints. 5 min

SUM, AVERAGE, COUNT, rounding and cell references.

• 12SUM and AutoSumAdd up a column of numbers with SUM, then let AutoSum write the formula for you. 4 min
• 13AVERAGE, MIN and MAXSummarize a list with its average, smallest and largest values. 4 min
• 14MEDIAN and MODESee how one outlier skews an average, and describe typical values with MEDIAN and MODE. 4 min
• 15LARGE and SMALLFind the second-highest, top 3 or second-lowest value, a favorite interview question. 4 min
• 16COUNT, COUNTA and COUNTBLANKCount numbers, filled cells and empty cells, a quick way to audit data. 4 min
• 17ROUND, ROUNDUP and ROUNDDOWNControl decimals properly, which isn't the same as formatting. 4 min
• 18Percentages and % changeCalculate percent change with (new - old) / old, format it as a percentage, and grow a number by a percent. 5 min
• 19Relative vs absolute references ($)Lock a cell with $ so it doesn't move when you copy a formula, one of the most-tested Excel skills. 5 min
• 20Debugging SUMFind the three bugs behind most wrong totals: a range that misses a row, numbers stored as text, and a total that counts itself. 4 min
• 21Practice: department budgetCombine SUM, AVERAGE, ROUND and absolute references in a realistic budget sheet. 6 min

IF, AND, OR, IFS and IFERROR.

• 22TRUE, FALSE and comparisonsAsk Excel yes-or-no questions with =, <>, >, <, >= and <=, and get TRUE or FALSE back. The building block of every IF. 5 min
• 23The IF functionMake a cell show one result or another depending on a condition, like Pass/Fail or Over/Under budget. 5 min
• 24AND, OR and NOTCheck several conditions at once with AND and OR, flip a result with NOT, and combine them inside IF. 6 min
• 25Nested IF and IFSHandle more than two outcomes, like letter grades or commission bands, with nested IFs or the cleaner IFS. 7 min
• 26IS functions and SWITCHCheck what's really in a cell with ISBLANK, ISNUMBER and ISTEXT, and turn short codes into labels with SWITCH. 7 min
• 27IFERRORReplace ugly errors like #DIV/0! with a clean fallback value using IFERROR, and learn when hiding errors backfires. 6 min

COUNTIF, SUMIF and their multi-condition cousins.

• 28COUNTIFCount the cells that match a condition, from exact words to number checks like ">=8". 5 min
• 29SUMIFAdd up only the numbers that meet a condition, with and without a separate sum range. 5 min
• 30COUNTIFS and SUMIFSCount and sum with several conditions at once, and learn why SUMIFS puts the sum range first. 7 min
• 31AVERAGEIF, MAXIFS and MINIFSFind the average, highest and lowest values that meet one or more conditions. 6 min
• 32Wildcards and cell-based criteriaMatch partial text with * and ?, and build criteria from cells like ">"&H5 so your formulas adapt. 7 min
• 33Practice and skill check: summary gridsBuild a region by product summary grid with one COUNTIFS or SUMIFS formula, using mixed references like $G8 and H$7, then check your totals. 9 min

VLOOKUP, INDEX/MATCH and XLOOKUP, the interview favorites.

• 34VLOOKUP exact matchPull product names and prices from a catalog with VLOOKUP, and lock the table so the formula fills down cleanly. 6 min
• 35VLOOKUP approximate matchUse VLOOKUP with TRUE to place values into tiers like commission bands, tax brackets or grades. 5 min
• 36VLOOKUP practicePractice exact and approximate VLOOKUPs on a real order sheet: item names, prices, discount tiers and a mistyped code. 7 min
• 37HLOOKUP for horizontal tablesRead a sideways rate card with HLOOKUP, which searches across the top row instead of down the first column. 5 min
• 38INDEX and MATCHCombine INDEX and MATCH to look up in any direction, including returning a column to the left of the one you search. 7 min
• 39INDEX/MATCH skill checkTest your INDEX and MATCH on a stock report: look up to the left, find the best-stocked item, and run two-way lookups across a monthly table. 8 min
• 40XLOOKUPLook up in any direction with XLOOKUP and show a friendly message when nothing matches. 6 min
• 41XLOOKUP wildcards and match modesSearch partial names with XLOOKUP's wildcard mode, and place values into tiers with next-smaller and next-larger matches. 7 min
• 42Fixing lookup errorsDiagnose #N/A, #REF! and silent wrong answers in lookups, then handle missing items gracefully. 7 min

Clean, split and combine text data.

• 43LEFT, RIGHT, MID and LENSplit product codes and IDs into their parts by taking characters from the start, end or middle of the text. 5 min
• 44FIND and SEARCHLocate a character or word inside text with FIND or SEARCH, then use that position to split names and pull out order numbers. 6 min
• 45Joining text: &, CONCAT and TEXTJOINBuild full names, addresses and tag lists by joining text with &, CONCAT and TEXTJOIN. 6 min
• 46TEXT and VALUE: numbers to text and backFormat dates and dollar amounts inside sentences with TEXT, and turn digits stored as text into real numbers with VALUE. 6 min
• 47Cleaning messy textFix stray spaces, random capitals and inconsistent separators in imported data with TRIM, PROPER, UPPER, LOWER and SUBSTITUTE. 6 min
• 48TEXTBEFORE and TEXTAFTERSplit emails, "Last, First" names and file paths at a delimiter with TEXTBEFORE and TEXTAFTER, including picking which occurrence to split at. 6 min
• 49Text skill summary: clean a contact listPractice the text module on one messy contact list: tidy names and cities with TRIM and PROPER, split out first names and companies with TEXTBEFORE, and join a mailing line with &. 7 min

Build dates and calculate the time between them.

• 50How Excel stores dates: DATE, YEAR, MONTH, DAYLearn why every date is secretly a number, then build dates with DATE and take them apart with YEAR, MONTH and DAY. 5 min
• 51Days between dates: DAYS, DATEDIF and TODAYMeasure the gap between two dates in days, months and years, and calculate tenure as of today. 6 min
• 52Renewals and month-ends: EDATE, EOMONTH, WEEKDAYShift dates by whole months with EDATE, jump to month-end with EOMONTH, and find the day of the week with WEEKDAY. 6 min
• 53Dates in criteria: totals by date range and monthCount and total rows between two dates with COUNTIFS and SUMIFS, build a monthly summary, and drive the date range from input cells. 7 min
• 54Workdays: NETWORKDAYS and WORKDAYCount working days between two dates with NETWORKDAYS and find a deadline a number of working days away with WORKDAY, skipping weekends and a holiday list. 6 min
• 55Date skill summary: gym membershipsPractice the whole dates module on one sheet: rebuild dates with DATE, find renewals with EDATE, measure time with DATEDIF and TODAY, and write readable notes with TEXT. 7 min

UNIQUE, FILTER and SORT: modern Excel's power tools.

• 56UNIQUE: list and count distinct valuesPull a clean list of distinct regions or customers with UNIQUE, then count them with COUNTA. 5 min
• 57FILTER: pull matching rows with a formulaReturn every row that meets one or more conditions with FILTER, and show a friendly message when nothing matches. 6 min
• 58SORT: order data with a formulaSort a table by any column, ascending or descending, and combine SORT with FILTER and UNIQUE. 6 min
• 59Modern Excel: XMATCH, SORTBY, TEXTSPLIT and spill references (A2#)Split names with TEXTSPLIT, find positions with XMATCH, sort by another column with SORTBY, and refer to a whole spilled result with A2#. 7 min
• 60Pivot-style summary with formulasBuild a summary table like a pivot table with formulas: a sorted UNIQUE list of categories, then SUMIFS and COUNTIFS filled down beside it. 7 min

Work across worksheets, then filter, highlight and summarize data with filters, conditional formatting and pivot tables.

• 61Worksheets and cross-sheet referencesSwitch, add and rename worksheet tabs, then use other sheets in formulas: Prices!B3, a lookup against a price list tab, and a total across monthly tabs. 7 min
• 62Filter a tableTurn on filter buttons with Ctrl+Shift+L, filter by one or several values and by a number condition, total only the visible rows with SUBTOTAL, then clear the filters. 7 min
• 63Conditional formattingMake important numbers stand out: highlight values over a limit, duplicate IDs and the top 3, then color whole rows with a formula rule and a $ on the column. 7 min
• 64Pivot tables 1Build your first PivotTable on a new sheet: total sales by region, split them by product, then switch the summary from Sum to Count and Average and read the answer off the pivot. 8 min
• 65Pivot tables 2Go further with PivotTables: nest two row fields with subtotals, add a report filter, show values as a % of the grand total, update the source when new rows arrive, and know when SUMIFS is the better tool. 9 min

Realistic multi-step tasks like the ones in Excel tests.

• 66Interview drill: HR rosterWork through a timed-test style HR roster: look up a salary, count, total and list by department, flag bonuses, calculate tenure and handle a missing ID. 8 min
• 67Interview drill: sales reportBuild a sales report the way an Excel test expects: revenue, totals, revenue by region and by month, the top order and its rep, and a % of total column. 8 min
• 68Case study: real estate listingsAnalyze 12 home listings like a real-estate analyst: price per square foot, averages and counts by area, a filtered list of good-value homes, and the priciest listing. 8 min
• 69Case study: hospital admissionsWork a hospital admin case: length of stay, average stay and readmission rate for a ward, a by-ward summary table, and the patient with the longest stay. 9 min