---
title: "Practice Excel Formulas with Instant Grading"
url: https://stacklist.com/card/6d60ca2f-f9ea-4088-8110-7eb98cb457d0
source_url: "https://sheetdo.com/practice/?utm_source=stacklist&utm_medium=directory&utm_campaign=excel-accounting-student-stack"
stack: https://stacklist.com/c/education/stack/ef471b61-81dc-4b55-ae0d-812c092b9c57
summary: "Sheetdo.com offers a practice platform with 113 graded Excel problems covering lookups, conditional logic, and data cleaning skills for interview preparation. Users solve real spreadsheet challenges with instant feedback, role-specific packs, and automatic progress tracking."
tags: "excel, practice, formulas, spreadsheet, interview-prep, skill-building, grading"
key_entities: "Excel (technology), VLOOKUP (technology), XLOOKUP (technology), INDEX+MATCH (technology), sheetdo.com (technology), Interview Prep (concept), Formula grading (concept)"
classification: "tutorial"
content_hash: "sha256:a3791bad8b54735ca307429fab306ca6a0a6cd11aafcee3642ed5b7807725078"
acp_version: "0.2"
token_counts_approximate: 12369
visibility: public
agent_accessible: true
status: "final"
---

# Practice Excel Formulas with Instant Grading

Home / Practice Practice hall Every problem is a locked Mini sheet plus an external formula console with instant grading. Pick one and build muscle memory. 113 problems Instant grading No signup Interview track 50 / 50 Excel Interview Prep: Top 50 Practice Problems A graded path through the exact skills employers screen for — lookups, nested IF, conditional sums, and error-proofing. Progress saves automatically. Role packs Twenty graded exercises for one job, with the workbook you keep. The free library above stays free. All packs and prices → Sales pack $19 $19 Quota, commission and pipeline maths, in one graded path. 20 graded exercises · .xlsx workbook See the pack → Accounting pack $19 $19 Reconciliation, aging and depreciation, with the rounding rules that matter. 20 graded exercises · .xlsx workbook See the pack → How to solve a practice problem in three steps Pick one: by difficulty (Easy / Medium / Hard) or from the function groups below. Not sure where you stand? Start with an Easy problem. Write the formula: the sheet is locked, so the data cannot be edited. You type into the formula bar below — the real situation, where a colleague hands you a sheet and asks for one formula. Press Enter: the engine computes the result inside a real spreadsheet and checks that you used the intended function, so typing the answer or hardcoding the number does not pass. Passed once? Do not leave yet: the explanation says why the formula works and shows an equivalent approach. VLOOKUP, XLOOKUP and INDEX+MATCH all pass — grading accepts every approach the guides teach and never locks you into one function. All practice problems 113 Easy 32 Easy Math ✓ Passed SUM SUMIF Ada's share of total revenue (locked denominator) The team lead wants each rep's slice of the month: Ada's revenue divided by the team total, as a decimal (0.08 means 8%). Revenue is in B2:B6 and Ada is the first row. Put her share in the Share of total cell D2 . Easy Math ✓ Passed AVERAGE Average three exam scores You're a teacher wrapping up an exam: the scores for Alex, Betty, and Chris are already in B2:B4 , and the report still needs the class average. Fill the Average cell B5 with the mean of the three scores. Easy Text ✓ Passed PROPER Capitalize customer names (PROPER) A partner exported their CRM with no data hygiene: customer names in A2:A5 arrive as john smith , MARY JOHNSON and worse. Before the names go into the quarterly report, fill Proper name with a PROPER formula so every name is capitalized cleanly, starting in C2 and filling down to C5 . Easy Data Cleaning Sales pack ✓ Passed TRIM PROPER Clean a CRM name export (TRIM + PROPER) The CRM export in A2:A4 brings padding and random capitalisation. Put a clean version of the name in A2 into the Clean name cell B2 : no leading or trailing spaces, and each word capitalised. Easy Data Cleaning ✓ Passed TRIM Clean extra spaces from imported names (TRIM) A CRM export landed in your inbox, and every customer name carries stray spaces — leading, trailing, even doubles in the middle. Your mail merge will bounce on all of them. Fill the Cleaned name cells C2:C5 so each name from A2:A5 is trimmed to a single normal form. Easy Logic ✓ Passed COUNTIF Count passing scores (COUNTIF) You just finished grading an exam for an adult-education class. The passing line is 60 points, and the grade report needs one number at the top: how many of the eight students in B2:B9 passed. Fill the Passed count cell D2 . Easy Text ✓ Passed LEN Count product code characters (LEN) Shipping labels only fit a fixed number of characters, and every product code in A2:A5 is a different length — PRD-1023 is not the same width as SKU-9 . Fill Code length in C2:C5 with how many characters each code contains. The dash counts. Easy Math ✓ Passed COUNTIF COUNTBLANK Count the stations with no reading (blank, not zero) Two stations in A2:B7 have not reported a reading yet, and two others genuinely measured zero. The operations report needs the number of *missing* readings in the No reading cell D2 . Easy Math ✓ Passed COUNTA Count who actually signed up (COUNTA) Ten colleagues were invited to a workshop and the sign-up sheet is in B2:B11 — some wrote their name, some left the cell blank. The venue only fits confirmed attendees, so count how many cells in B2:B11 are not empty. Put the headcount in the Confirmed cell D2 . Easy Lookup & Reference Sales pack ✓ Passed XLOOKUP INDEX MATCH Dashboard lookup with a friendly miss (XLOOKUP) The dashboard asks for one rep by name from F1 and shows their revenue in F2 . Names and revenue are in A2:B5 . If the name is not on the team, the cell should read "Not on team" instead of an error. Easy Math ✓ Passed SUMIF Expense claims over the approval limit (SUMIF) The finance export in A2:C8 lists every expense claim, big and small. Only the claims above the approval limit matter for the audit — and the limit itself sits in D2 because it moves every quarter. Put the total of the claims strictly above that limit in the Total above limit cell E2 . Easy Text ✓ Passed LEFT Extract a category code (LEFT) Your inventory sheet packs everything into one product code like PRD-1023 : the first three letters are the category, the rest is the serial. Before you can build a category filter, the report needs a separate Category code column. Fill C2:C5 from the codes in A2:A5 . Easy Text ✓ Passed MID Extract the serial from a product code (MID) Warehouse labels use one fixed format, PRD-1023-A : three letters, a dash, a four-digit serial, another dash, and a suffix letter. The packing report only needs the serial — characters 5 through 8. Fill Serial in C2:C5 from the codes in A2:A5 . Easy Lookup & Reference ✓ Passed COUNTIF Flag a double-booked order (COUNTIF) A customer swears they were charged twice, and the order log looks suspicious: order ID ORD-1042 might have been booked more than once. Before refunding anything, count how many rows in A2:A11 carry that exact ID. The disputed ID is already sitting in D1 . Put the answer in the Occurrences cell D2 . Easy Logic Accounting pack ✓ Passed IF ABS Flag items above the materiality threshold (ABS) The threshold is in E1 . Put "Review" or "Ignore" for the journal line in B2 in the Flag cell E2 , where anything at or above the threshold in absolute value needs review. Easy Math Accounting pack ✓ Passed SUM How much cash should be in the petty cash box? (SUM) The float is in E1 and the receipts collected are in B2:B4 . Put the cash that should remain in the box in the Cash left cell E2 . Easy Data Cleaning Accounting pack ✓ Passed COUNTIF IF Is this statement line already in the ledger? (COUNTIF) Reconciling a bank statement means matching each line to the ledger. The line to check is A2 , and the ledger's references are in D2:D5 . Put "Matched" or "Unmatched" in the Status cell B2 . Easy Text ✓ Passed CONCAT & Join first and last names (CONCAT) The payroll system imported first names into column A and last names into column B, but the email template needs one full-name field. In Full name , write a formula that joins A2 and B2 with a single space — C2 should read like Alice Wang . Start in C2 , then fill down to C5 . Easy Lookup & Reference ✓ Passed XLOOKUP Look up salary by employee ID (XLOOKUP) You are putting together a department salary report. Employee IDs and departments are already filled in, but the Matched salary column is still empty. Write a formula in G2:G6 so each row looks up the salary for the employee ID in column E from the staff roster in A2:C6 . Easy Lookup & Reference ✓ Passed VLOOKUP Look up unit price by SKU (VLOOKUP) You run a small warehouse. The price list on the left has every SKU, but the order sheet on the right still needs unit prices. Fill the Unit price cells F2:F4 so each order row pulls the price for its SKU from the price list in A2:C6 . Easy Math ✓ Passed PRODUCT Multiply quantity × unit price (* / PRODUCT) An order sheet lists quantity in A2:A5 and unit price in B2:B5 . Fill Line total in C2:C5 with quantity times price for each row. Excel has no dedicated “multiply function” — use * or PRODUCT. Easy Math Sales pack ✓ Passed SUMIFS SUMIF Quota attainment for one rep (SUMIFS ÷ quota) Bookings are logged per rep in A2:C7 , and the rep to report is F1 with their quota in F2 . Put that rep's attainment — bookings divided by quota, as a decimal — in the Attainment cell F3 . Easy Math ✓ Passed ROUND Round unit prices to whole numbers (ROUND) A price list stores messy decimals in A2:A5 . Finance wants whole-number amounts for the invoice. Fill Rounded in B2:B5 by rounding each price to 0 decimal places — half goes away from zero, same as Excel ROUND. Easy Text ✓ Passed TEXT WEEKDAY Show the weekday from a date (TEXT / WEEKDAY) A schedule sheet lists dates in A2:A5 , but the report needs the weekday name next to each one. Fill Weekday in B2:B5 so each row shows an English abbreviation such as Wed or Mon . Use a formula that follows the date in column A — a hardcoded Wed fails as soon as A2 changes. Easy Math ✓ Passed SUM Sum A1 and A2 with a formula Your shop just opened and its first two months of revenue are recorded in A1 and A2 . Fill the output cell A3 with the total revenue for both months. Easy Math Accounting pack ✓ Passed SUMIF SUMPRODUCT Total one expense category (SUMIF) Expenses are recorded line by line in A2:B6 . The category to total is in E1 . Put its total in the Total cell E2 . Easy Math Accounting pack ✓ Passed SUM SUMPRODUCT Trial balance: how far off are the debits and credits? (SUM) A trial balance balances when total debits equal total credits. Debits are in B2:B6 and credits in C2:C6 . Put debits minus credits in the Difference cell E2 — zero means it ties. Easy Logic ✓ Passed IFERROR Unit prices that survive bad data (IFERROR) An order's unit price is Total divided by Qty — until someone types 0 as the quantity and every division in the report turns into #DIV/0!. Build the safe version in Unit price cell D2 : show the normal price when the math works, and the text "Check qty" when the division blows up. Easy Math Sales pack ✓ Passed IF MAX Units still needed to hit quota (IF, never negative) The unit quota is in E1 and reps' unit sales are in B2:B4 . Row 2 has already sold some; put how many more units they need in the Still needed cell E2 . Easy Logic Sales pack ✓ Passed IF AND Who has to approve this discount? (IF + AND) Policy: a discount above 15% on a deal of at least 10,000 needs a manager. Anything else is fine for the rep. Row 2 is the one to judge — put "Manager" or "Rep" in the Approver cell D2 . Easy Math Sales pack ✓ Passed COUNTIF COUNTA Win rate over closed deals only (COUNTIF ratio) Every deal in A2:B7 carries an outcome: Won, Lost or Open. Put the win rate of the *closed* deals in the Win rate cell D2 , as a decimal. Easy Math ✓ Passed ROUND Year-over-year growth (percent change) A slide deck needs one number: how much revenue grew from last year to this year. B2:B3 holds the two totals. Calculate the growth rate in Growth cell D2 , rounded to three decimals — the deck formats it as a percentage afterwards. Medium 49 Medium Math Sales pack ✓ Passed IFERROR April growth over March, even when March is empty (IFERROR) Revenue by month is in A2:B5 . Put April's month-over-month growth in the April growth cell D2 , as a decimal. March is occasionally missing in the export, and a report cannot show an error. Medium Logic ✓ Passed ISNA MATCH Audit missing SKUs with ISNA Before the price report ships, QA wants a simple audit answer for the SKU in E2 : is it missing from the price list in A2:A6 or not? Put TRUE (missing) or FALSE (present) in the Missing? cell D2 . This is an audit question — you want the TRUE/FALSE itself, not a wrapped-up message. Medium Math ✓ Passed AVERAGEIF IFERROR Average deal size for one region (AVERAGEIF) The sales deck quotes an average deal size per region. The pipeline is in A2:C7 and the region to report is in E2 . Put the average deal size for that region in the Avg deal cell F2 , and 0 when the region has no deals at all. Medium Math Accounting pack ✓ Passed IFERROR ROUND Budget variance, with the divide-by-zero guarded (IFERROR) Budget and actual spend for row 2 are in B2:C2 . Put the variance — actual minus budget, over budget, as a decimal — in the Variance cell D2 . Medium Text ✓ Passed TEXTJOIN Build a mailing list in one cell (TEXTJOIN) Marketing needs the newsletter recipients as one paste-ready line for the email tool — not a column to copy cell by cell. The addresses sit in A2:A5 . Combine them into a single semicolon-separated line in the Mailing list cell C2 . Medium Math ✓ Passed NETWORKDAYS Business days to payment (NETWORKDAYS) Finance measures the payment window in working days, not calendar days — weekends do not count against the customer. Sent and paid dates for INV-701 are in B2 and C2 . Put the working-day count in the Business days cell D2 . Medium Data Cleaning ✓ Passed SUMPRODUCT COUNTIF TRIM COUNTIF says 2, the sheet shows 4 (hidden spaces) Four reps are classed as East in A2:A6 , but a plain COUNTIF only finds two of them: the export left trailing spaces and one lower-case entry. Put the real East count in the East count cell D2 . Medium Math ✓ Passed CAGR POWER Calculate 5-year CAGR in Excel A friend asks you to evaluate an investment: it grew from the start value in B1 to the end value in B2 over the years in B3 , and they want its annualized rate to compare with other options. Fill the CAGR cell B4 with the compound annual growth rate. Return the rate as a decimal (for example 0.1 for 10%). Medium Logic ✓ Passed IF SEARCH ISNUMBER Check if cell contains specific text (IF + SEARCH) Customer support needs to triage incoming tickets in A2:A5 . If a ticket's message contains the word Urgent (case-insensitive), label it as Priority ; otherwise, label it as Normal . Fill the Priority status cells B2:B5 using an IF formula that checks for substring presence. Medium Logic Accounting pack ✓ Passed IF SEARCH ISNUMBER Classify a ledger line by keyword (SEARCH) The description to classify is in A2 . Put "Travel" when it mentions fuel, "Rent" when it mentions rent, and "Other" otherwise, in the Account cell B2 . Medium Lookup & Reference Sales pack ✓ Passed VLOOKUP INDEX MATCH Commission by attainment tier (approximate lookup) Commission is a percentage of bookings, and the percentage depends on attainment. The tier table is in F2:G5 , and Ada's quota and bookings are on her row in A2:D2 . Put her commission in the Commission cell D2 . Medium Logic ✓ Passed IF MEDIAN Compare a score to the middle value You're analyzing a class quiz: the five scores are already entered in A2:A6 , and you want to know whether the first student's score in A2 sits above, below, or right at the middle of the class. Fill the vs Middle cell B2 — return Above , Below , or Equal . Medium Logic ✓ Passed IF Convert a score into a letter grade (nested IF) It's report-card season: exam scores are already entered, and the school system needs a letter grade beside each one. Fill the Letter cell B2 with a grade for the score in A2 : A for scores ≥90, B for ≥80, C for ≥70, otherwise D . Medium Logic ✓ Passed COUNTIF Count invoices by prefix (COUNTIF wildcards) It is month-end close and a supplier export lands with every document type in one column: invoices start with INV- , purchase orders with PO- , returns with RET- . Finance wants only the invoice rows counted. Count how many of A2:A11 are invoices and put the number in Invoice count cell D2 . Medium Lookup & Reference ✓ Passed VLOOKUP IFERROR Default to zero on missing discount (VLOOKUP + IFERROR) You are processing store checkouts. Members get a discount based on their VIP tier in A2:B5 (Bronze 5%, Silver 10%, Gold 15%, Platinum 20%). Non-VIP customers have standard tiers like 'Regular' or 'None' that are NOT in the VIP table, causing a naked VLOOKUP to fail with #N/A . Fill the Discount cells E2:E5 using VLOOKUP wrapped in IFERROR (or IFNA) so missing tiers safely default to 0 . Medium Math Accounting pack ✓ Passed DATE TEXT Due date from payment terms (DATE + TEXT) Invoice date and terms in days are in B2:C2 for row 2. Put the due date in the Due cell D2 as yyyy-mm-dd. Medium Math Sales pack ✓ Passed SUMIF SUM SUMPRODUCT Each product's share of revenue (SUMIF over a locked total) Revenue is logged per order in A2:B5 , so a product appears on several rows. Put the share of total revenue earned by the product on row 2 in the Share cell E2 , as a decimal. Medium Lookup & Reference ✓ Passed VLOOKUP Find tax bracket using approximate match (VLOOKUP) HR needs to compute the withholding tax rate for each employee's annual taxable income. The reference table in columns A–B lists income thresholds in ascending order (0, $30,000, $60,000, $100,000) and their corresponding tax rates. Fill the Tax rate cells F2:F4 using VLOOKUP with approximate match so each employee is assigned the rate matching their income tier in E2:E4 . Medium Math Sales pack ✓ Passed DAYS IF Flag customers who have gone quiet (DAYS + IF) Any customer whose last order in B2:B5 is more than 90 days before the report date in E1 is at risk. Put "At risk" or "Active" for the customer on row 2 in the Status cell E2 . Medium Logic Accounting pack ✓ Passed IF COUNTIF Flag intercompany balances for elimination (COUNTIF) A balance is intercompany when the counterparty in B2:B5 is itself one of the group entities in A2:A5 . Put "Eliminate" or "External" for row 2 in the Treatment cell D2 . Medium Data Cleaning ✓ Passed IFERROR VLOOKUP Handle missing SKUs in a lookup (IFERROR) Customer service forwarded an order list, and one of the SKUs was discontinued — it is not in the price list ( A2:C6 ) anymore, so a plain lookup would blow up with an error in the middle of your report. Fill the Unit price cells F2:F4 so every order row either shows the matched price or the text Not found . Medium Math Sales pack ✓ Passed ABS IFERROR How far off was the forecast, in percent? (ABS + IFERROR) Forecast and actual revenue are in B2:C2 for row 2. Put the size of the miss — actual versus forecast, as a decimal without a sign — in the Variance % cell D2 . Medium Math ✓ Passed COUNTIFS COUNTIF How many orders landed in the period (COUNTIFS) The ops review asks for the number of orders placed inside a custom period, not a month. Dates sit in B2:B6 as ISO text, and the period limits are F1 (from) and F2 (to), both inclusive. Put the count in the Orders cell F3 . Medium Math Accounting pack ✓ Passed ROUND How much VAT is inside a gross amount? (ROUND) The invoice total in B1 already includes VAT at the rate in B2 . Put the VAT portion in the VAT cell D2 , rounded to two decimals. Medium Math ✓ Passed SUMIFS SUMIF Invoiced minus paid, per vendor (two SUMIFS) Two tables, one report: invoices in A2:B4 and payments in D2:E6 , where a vendor can pay in several instalments. For the vendor in A2 , put the outstanding variance (invoiced minus paid) in the Variance cell F2 . Medium Lookup & Reference ✓ Passed XLOOKUP TRIM UPPER INDEX MATCH Look up a scanned SKU that arrived messy (TRIM + UPPER) The barcode scanner writes SKUs with padding and lower case, so E1 holds sku-103 while the price table in A2:C6 stores clean upper-case keys. Return the price for that scanned code in the Price cell E2 , and "Not found" when the code is not in the table. Medium Math ✓ Passed STDEV Monthly volatility with STDEV An ops analyst screen hands you five months of support-ticket volume in B2:B6 and asks a single question: how volatile is it? The standard deviation is the number they want — put it in Volatility cell D2 . Medium Math ✓ Passed DATEDIF Months in role, not days (DATEDIF) HR reports tenure in whole months, so a partial month must not round up. The start and end dates for Ana are in B2 and C2 as ISO text. Put her completed months in the Months in role cell D2 . Medium Text ✓ Passed VALUE MID RIGHT FIND Next PO number from the last code (MID + VALUE) Purchase orders are coded PO-<year>-<serial>, and the next one must continue the serial of the code in A2 rather than restart at 1. The serial is the last four characters. Put the next serial as a number in the Next number cell B2 . Medium Math ✓ Passed SUMIFS IF SUMIF Paid or outstanding? (SUMIFS + IF) Month-end reconciliation: the invoice list is in A2:B5 and the payment log in D2:E8 , where one invoice can be paid in several instalments. For the invoice in A2 , put "Paid" or "Outstanding" in the Status cell G2 . Medium Lookup & Reference ✓ Passed VLOOKUP Pull multiple columns for an employee (VLOOKUP) You are building an employee profile card. Given an employee ID in F2 , pull their Department into G2 and City into H2 from the master employee directory in columns A–D. Lock the table reference with $ so the lookup formula remains stable across columns. Medium Text ✓ Passed MID FIND LEN Pull the ref out of a tracking code (MID + FIND) Every tracking code in A2:A5 packs three fields: a prefix, the invoice ref inside square brackets, and a country suffix. Your job is the middle one only — the ref for the code in A2 , in the Ref cell B2 . Medium Logic Accounting pack ✓ Passed IF Put the invoice in an aging bucket (nested IF) Aging is measured from the invoice date in C2 to the report date in F1 . Put the bucket in the Bucket cell B2 : 0-30, 31-60, 61-90, or 90+. Medium Math Sales pack ✓ Passed RANK.EQ SUMPRODUCT Rank a rep when two reps are tied (RANK.EQ) Revenue per rep is in B2:B5 . Put the rank of the rep on row 3 in the Rank of row 3 cell D2 . Two reps are level, so read carefully what a tie should produce before you write the formula. Medium Math ✓ Passed SUMIFS SUMIF Regional sales with two filters (SUMIFS) You run the numbers for a tea chain. The East-region manager asks: how much Oolong revenue did East actually book? The log is in A2:C11 — Region in A, Product in B, Amount in C — and the requested region is in E1 . Total only the rows that are East AND Oolong, and put the sum in the East Oolong total cell E2 . Medium Data Cleaning ✓ Passed UNIQUE Remove duplicate contacts (UNIQUE) Your marketing sheet collects signups from three forms, so the same contact shows up several times in A2:A7 . The email tool needs a clean list: put a UNIQUE formula in Unique list so the first unique contact lands in C2 — the full deduplicated list then spills straight down. Medium Logic Accounting pack ✓ Passed COUNTIFS IF SUMPRODUCT Same vendor, same amount: paid twice? (COUNTIFS) Payments are in A2:C5 . Put "Duplicate" or "Unique" for row 2 in the Flag cell D2 , where duplicate means the same vendor was paid the same amount more than once. Medium Math Accounting pack ✓ Passed SLN ROUND Straight-line depreciation per year (SLN) Cost, salvage value and useful life are in B1 , B2 and B3 . Put the depreciation charge for one full year in the Yearly depreciation cell D2 , rounded to two decimals. Medium Math Sales pack ✓ Passed SUMIFS SUMPRODUCT Territory rollup: region and segment together (SUMIFS) The forecast sheet needs one cell per territory, and a territory is a region plus a segment. The log is in A2:C7 , the region is F1 and the segment is F2 . Put the total in the Total cell F3 . Medium Lookup & Reference Sales pack ✓ Passed LARGE INDEX MATCH The account at rank N (LARGE + INDEX/MATCH) The board pack lists the top accounts by revenue. Revenue is in B2:B6 with names in A2:A6 , and the rank to show is E1 . Put that account's name in the Account cell E2 . Medium Math ✓ Passed SUMPRODUCT VALUE SUM The total says 0, the column is full (text numbers) The monthly report shows 0 against a column of five invoices in B2:B6 . The amounts came in from an export, and they are stored as text, so SUM sees nothing to add. Put the real total in the Correct total cell E2 . Medium Lookup & Reference Accounting pack ✓ Passed LARGE INDEX MATCH The vendor at rank N by spend (LARGE + INDEX/MATCH) Spend per vendor is in B2:B6 with names in A2:A6 . The rank to report is E1 . Put that vendor's name in the Vendor cell E2 . Medium Math ✓ Passed SUMIFS SUMIF Total only the orders above a threshold (SUMIFS) You are the analyst for a regional sales team. The manager does not want the whole month, only the big deals: every order at or above the threshold in E1 . The log sits in A2:C11 (Rep, Region, Amount) and the total belongs in the Total cell E2 . Medium Math ✓ Passed SUMIF Total sales for one region (SUMIF) The quarterly ledger in A2:B7 mixes four regions into one running list. Regional review is tomorrow and the North team wants their number, not the company total. Put the North-region sales total in the Total sales cell E2 , driven by the region name in D2 . Medium Math ✓ Passed SUMIF Two regions in one formula (SUMIF with an OR list) The order log in A2:C11 mixes four regions, and the review deck needs North and East combined — one number, not two. Put the combined total of those two regions in the North + East total cell E2 . Medium Data Cleaning ✓ Passed SUMIF Vendor spend when the names carry stray spaces (SUMIF) The AP export in A2:C7 was typed by three different people: the same vendor shows up as Acme Corp , Acme Corp , Acme Corp and ACME CORP . Finance wants one number per vendor and will not let you edit the sheet. Put the paid total for the vendor named in E2 into the Paid total cell F2 . Medium Math ✓ Passed SUMPRODUCT AVERAGE Weighted final grade with SUMPRODUCT Your course grade is not a plain average — the final exam counts for more than the homework. The grading sheet in A2:C5 lists each component's score and its weight (weights sum to 1). Compute the final grade in the Final grade cell E2 by pairing each score with its weight in one formula. Medium Math Sales pack ✓ Passed SUMPRODUCT Weighted pipeline for one stage (SUMPRODUCT) A forecast is not the size of the pipeline, it is the size times the probability. Amounts and probabilities are on each row of A2:D6 , and the stage to forecast is F1 . Put the weighted value of that stage in the Weighted cell F2 . Medium Lookup & Reference ✓ Passed IFERROR XLOOKUP XLOOKUP misses without blowing up (IFERROR) Customer service keeps typing SKUs that stopped existing, and every miss splatters #N/A across the price report. Look up the price for the SKU in E2 from the list in A2:C6 , and return the text "Not found" instead of an error. Fill the Price cell F2 — two ways solve this, and one of them doesn't even need IFERROR. Hard 32 Hard Lookup & Reference Sales pack ✓ Passed VLOOKUP IFERROR SUMIF Audit a commission payout against two tables (VLOOKUP + IFERROR) Booking and rate are in B2:C2 , so row 2's commission can be recomputed. What payroll actually paid is in the second table at E2:F4 , and some reps are missing from it. Put the difference (computed − paid) in the Difference cell G2 . Hard Math Sales pack ✓ Passed AVERAGEIF SUMPRODUCT Average deal size without the whale (AVERAGEIF) One 50,000 deal is dragging the average away from what a typical deal looks like. Amounts are in B2:B6 and the exclusion threshold is in E1 . Put the average of the deals *below* that threshold in the Average cell E2 . Hard Text ✓ Passed TEXT UPPER Build a padded invoice number (TEXT + &) The order system stores a store code in B2 and a plain counter in A2 . Finance wants invoice numbers in a fixed shape — NY-0042 — because the PDF template and the barcode both assume four digits. Fill the Invoice number cell D2 . Hard Logic ✓ Passed IF Calculate tiered sales commission (IF) Quarter-end is here and sales ops needs commission run for the team in B2:B5 . The plan has three tiers: 8% on sales of at least 10,000, 5% on sales of at least 5,000, and 3% below that. Fill the Commission cells D2:D5 with each person's payout. Hard Lookup & Reference ✓ Passed INDEX MATCH VLOOKUP Commission from a tier table (approximate INDEX MATCH) Commission is not a flat rate: the tier table in F2:G5 maps a sales floor to a rate, and the rep's total sales are in B2 . Put the commission amount (sales × the rate for their tier) in the Commission cell D2 . Hard Lookup & Reference Accounting pack ✓ Passed VLOOKUP INDEX MATCH ROUND Convert at the rate in force on the invoice date (approximate VLOOKUP) Amounts are in EUR in C2:C2 and the dated rate table is in F2:G4 , sorted oldest first. Convert row 2 to GBP at the rate in force on its invoice date, rounded to two decimals, in the GBP cell D2 . Hard Math ✓ Passed SUMPRODUCT COUNTIFS Count the orders that are big AND fast (SUMPRODUCT) Sourcing wants the orders that cleared 100 units and still shipped inside 5 days. Units are in B2:B6 , days to ship in C2:C6 , and the thresholds are written on the sheet ( E1 , E2 ). Put the count in the Both cell F3 . Hard Math Sales pack ✓ Passed SUMPRODUCT Credit for the rep who only owns part of the deal (SUMPRODUCT) Deals are often shared: the owner takes the credit percentage in D2:D6 , and the rest goes to whoever sourced it. Bookings, owner and credit share are on each row of A2:D6 . Put the total credited bookings for the rep in F2 in the Credit cell G2 . Hard Math Accounting pack ✓ Passed DB ROUND Declining-balance depreciation for one year (DB) The asset's cost, salvage, life and the year to report are in B1:B4 . Put that year's declining-balance charge in the Charge cell D2 , rounded to a whole unit. Hard Math ✓ Passed WORKDAY TEXT Due date from payment terms (WORKDAY + TEXT) Terms say "net 30 working days", so the due date has to skip weekends and come back as a readable date. The issue date is in B2 and the term length in C2 . Put the due date in the Due cell D2 as yyyy-mm-dd. Hard Text ✓ Passed MID FIND Extract the domain from an email (MID + FIND) Marketing wants signups grouped by company domain, but the export only kept full addresses in A2 . Pull everything after the @ into the Domain cell C2 — corp.com for [email protected] . Hard Lookup & Reference ✓ Passed INDIRECT INDEX Fetch a cell whose address is spelled out in two cells (INDIRECT) A dashboard is driven by two control cells: F1 holds a column letter and F2 holds a row number. The table is in A1:D4 . Return the value at that address in the Value cell F3 , so the control cells alone decide what is shown. Hard Lookup & Reference ✓ Passed VLOOKUP MATCH Find a price from part of a product name (VLOOKUP wildcards) The POS export stores long product names — Arabica Coffee Beans 1kg — but the search box only ever holds a keyword. The keyword is in D2 , the price list is A2:B5 . Fill the Price cell F2 so typing Coffee still finds the first product whose name contains it. Hard Lookup & Reference ✓ Passed INDEX MATCH Find who earns a given salary (INDEX + MATCH) Payroll flagged one salary amount for audit, but the query came back as a number — you need the person. The roster ( A2:C6 ) keeps names in the first column and salaries in the last, and the amount to trace sits in E2 . Return the matching employee's name in the Employee cell F2 . Hard Math ✓ Passed SUMPRODUCT How many vendors do not match (SUMPRODUCT over two columns) Before closing the month, count the vendors where the paid amount in C2:C6 is not equal to the invoiced amount in B2:B6 . Put the count in the Mismatched cell E2 . Hard Lookup & Reference ✓ Passed XLOOKUP LOOKUP Latest order per customer, not the first (XLOOKUP) Sales asks for the most recent order amount for the customer in E2 . The log in A2:C7 is in date order, so the newest row for a customer is the last one that mentions them. Put that amount in the Latest amount cell F2 . Hard Lookup & Reference ✓ Passed VLOOKUP MATCH Look up a field that moves (VLOOKUP + MATCH) The monthly HR export keeps reordering its columns, so a hardcoded column number breaks on every refresh. The employee ID sits in E2 and the field you must return is chosen in G2 — Department today, Manager tomorrow. Fill the Result cell F2 so it returns whatever field G2 asks for. Hard Lookup & Reference Sales pack ✓ Passed INDEX MATCH VLOOKUP One cell from a rep × quarter grid (INDEX MATCH twice) The dashboard grid in B2:E5 is reps down the side and quarters across the top. The rep to show is H1 and the quarter is H2 . Put the value in the Result cell H3 . Hard Text ✓ Passed TEXTJOIN IF One cell listing every rep in a region (TEXTJOIN) The handover note needs one line naming the reps in the region in E1 . Reps are in B2:B6 with their region in A2:A6 . Put the names in the Reps cell E2 , separated by a comma and a space. Hard Math Accounting pack ✓ Passed IF AND ROUND DATE Prepaid expense for one month (IF + date window) A prepayment of B1 is amortised evenly over B3 months starting B2 . Report the expense for the month containing F2 , in the Expense cell D2 , rounded to two decimals. Hard Lookup & Reference ✓ Passed SUMIFS INDEX MATCH TRIM UPPER Price by SKU and region, from a scanned key The same SKU sells at a different price per region, so one key is not enough. The price list is in A2:C7 , the scanned SKU is in F1 (with its padding), and the region is in F2 . Put the price in the Price cell F3 . Hard Lookup & Reference ✓ Passed IF VLOOKUP INDEX MATCH Price from whichever list the switch names (IF + lookup) Two price lists live side by side — old in A2:B5 , new in D2:E5 — and a switch cell decides which one to use: H2 says "New" or "Old", the SKU is in H1 . Put the price in the Price cell H3 . Hard Text Accounting pack ✓ Passed MID FIND VALUE TRIM Pull the amount out of a statement line (MID + VALUE) Bank exports arrive as one string: date, reference and amount separated by pipes, as in A2 . Put the amount for the line in A2 in the Amount cell B2 , as a real number. Hard Math Accounting pack ✓ Passed ROUND IFERROR Receivables days outstanding, without dividing by zero (ROUND) Receivables, revenue for the period and the length of the period are in B1:B3 . Put the days-sales-outstanding figure in the AR turnover days cell D2 , to one decimal. Hard Math ✓ Passed SUMIFS Region revenue inside a date window (three criteria) The quarter pack needs revenue for one region inside one date window. Orders are in A2:C9 , the region is in F1 , and the window limits are F2 and F3 . Put the total in the Total cell F4 . Hard Math ✓ Passed SUMIFS SUMIF Running balance per account (expanding SUMIFS) The ledger mixes two accounts in one column: A2:A7 holds the account, B2:B7 the date and C2:C7 the amount. The row for 2026-01-12 needs its balance to date — everything on that same account from the top of the sheet down to that row. Put it in the Running balance cell D7 . Hard Math Sales pack ✓ Passed NETWORKDAYS Sales cycle in working days (NETWORKDAYS) The sales cycle is measured in working days, not calendar days — a deal that closed over a weekend did not take two extra days. First contact and close dates for deal D-401 are in B2 and C2 . Put the working-day length in the Working days cell D2 . Hard Math ✓ Passed SUMIF Top three deals total (SUMIF + LARGE) The deal register in A2:C9 lists eight closed orders at very different sizes. The bonus pool is paid on the three biggest deals only, so the register has to answer 'top three combined' without a helper column and without sorting anything. Put that total in the Top 3 total cell E2 . Hard Math Accounting pack ✓ Passed SUMPRODUCT ROUND Total payroll burden across a team (SUMPRODUCT) Each person's salary and employer burden rate are on their row in A2:C4 . Put the total employer cost — the sum of salary × burden rate — in the Total cell E2 , rounded to two decimals. Hard Lookup & Reference ✓ Passed INDEX MATCH VLOOKUP Two-way lookup: rep and quarter at once (INDEX MATCH) A sales dashboard asks for one number: the result of the rep in F1 in the quarter in F2 . The grid is B2:D4 with rep labels in A2:A4 and quarter labels in B1:D1 . Put the value in the Result cell F3 — one formula that reads both labels, not a different formula per cell. Hard Math ✓ Passed SUMPRODUCT Weighted average of the finals only (SUMPRODUCT) The gradebook in A2:C5 mixes quizzes and finals, each with its own weight. The board wants one number: the **weighted** average of the Final rows alone. Fill the Final grade cell E2 — the quiz weights must not drag it down. Hard Math ✓ Passed DATEDIF Years of service, rounded down (DATEDIF) HR runs a benefits review and needs each person's completed years of service, not a decimal. Hire dates are in B2 and the review date sits in C2 . Fill the Years cell D2 with whole years — someone hired five years and six months ago counts as 5. Practice by function Drilling one function at a time sticks fastest. Tap a problem to solve it, or read the syntax guide first. VLOOKUP 11 problems Function guide → Look up unit price by SKU (VLOOKUP) Audit missing SKUs with ISNA Default to zero on missing discount (VLOOKUP + IFERROR) Find tax bracket using approximate match (VLOOKUP) Handle missing SKUs in a lookup (IFERROR) Pull multiple columns for an employee (VLOOKUP) Commission from a tier table (approximate INDEX MATCH) Find a price from part of a product name (VLOOKUP wildcards) Look up a field that moves (VLOOKUP + MATCH) Price from whichever list the switch names (IF + lookup) Two-way lookup: rep and quarter at once (INDEX MATCH) INDEX 10 problems Function guide → Look up salary by employee ID (XLOOKUP) Look up a scanned SKU that arrived messy (TRIM + UPPER) Commission from a tier table (approximate INDEX MATCH) Fetch a cell whose address is spelled out in two cells (INDIRECT) Find a price from part of a product name (VLOOKUP wildcards) Find who earns a given salary (INDEX + MATCH) Look up a field that moves (VLOOKUP + MATCH) Price by SKU and region, from a scanned key Price from whichever list the switch names (IF + lookup) Two-way lookup: rep and quarter at once (INDEX MATCH) MATCH 10 problems Function guide → Look up salary by employee ID (XLOOKUP) Audit missing SKUs with ISNA Look up a scanned SKU that arrived messy (TRIM + UPPER) Commission from a tier table (approximate INDEX MATCH) Find a price from part of a product name (VLOOKUP wildcards) Find who earns a given salary (INDEX + MATCH) Look up a field that moves (VLOOKUP + MATCH) Price by SKU and region, from a scanned key Price from whichever list the switch names (IF + lookup) Two-way lookup: rep and quarter at once (INDEX MATCH) SUMIF 10 problems Function guide → Ada's share of total revenue (locked denominator) Expense claims over the approval limit (SUMIF) Invoiced minus paid, per vendor (two SUMIFS) Paid or outstanding? (SUMIFS + IF) Total only the orders above a threshold (SUMIFS) Total sales for one region (SUMIF) Two regions in one formula (SUMIF with an OR list) Vendor spend when the names carry stray spaces (SUMIF) Running balance per account (expanding SUMIFS) Top three deals total (SUMIF + LARGE) SUMPRODUCT 9 problems COUNTIF says 2, the sheet shows 4 (hidden spaces) Count invoices by prefix (COUNTIF wildcards) The total says 0, the column is full (text numbers) Vendor spend when the names carry stray spaces (SUMIF) Weighted final grade with SUMPRODUCT Count the orders that are big AND fast (SUMPRODUCT) How many vendors do not match (SUMPRODUCT over two columns) Region revenue inside a date window (three criteria) Weighted average of the finals only (SUMPRODUCT) COUNTIF 8 problems Function guide → Count passing scores (COUNTIF) Count the stations with no reading (blank, not zero) Flag a double-booked order (COUNTIF) Audit missing SKUs with ISNA COUNTIF says 2, the sheet shows 4 (hidden spaces) Check if cell contains specific text (IF + SEARCH) Count invoices by prefix (COUNTIF wildcards) How many orders landed in the period (COUNTIFS) IF 8 problems Function guide → Unit prices that survive bad data (IFERROR) Check if cell contains specific text (IF + SEARCH) Compare a score to the middle value Convert a score into a letter grade (nested IF) Paid or outstanding? (SUMIFS + IF) Calculate tiered sales commission (IF) One cell listing every rep in a region (TEXTJOIN) Price from whichever list the switch names (IF + lookup) SUMIFS 7 problems Function guide → Invoiced minus paid, per vendor (two SUMIFS) Paid or outstanding? (SUMIFS + IF) Regional sales with two filters (SUMIFS) Total only the orders above a threshold (SUMIFS) Price by SKU and region, from a scanned key Region revenue inside a date window (three criteria) Running balance per account (expanding SUMIFS) TRIM 7 problems Capitalize customer names (PROPER) Clean extra spaces from imported names (TRIM) Count product code characters (LEN) COUNTIF says 2, the sheet shows 4 (hidden spaces) Look up a scanned SKU that arrived messy (TRIM + UPPER) Vendor spend when the names carry stray spaces (SUMIF) Price by SKU and region, from a scanned key IFERROR 6 problems Function guide → Unit prices that survive bad data (IFERROR) Average deal size for one region (AVERAGEIF) Default to zero on missing discount (VLOOKUP + IFERROR) Handle missing SKUs in a lookup (IFERROR) Look up a scanned SKU that arrived messy (TRIM + UPPER) XLOOKUP misses without blowing up (IFERROR) SUM 5 problems Ada's share of total revenue (locked denominator) Sum A1 and A2 with a formula The total says 0, the column is full (text numbers) Two regions in one formula (SUMIF with an OR list) Weighted final grade with SUMPRODUCT XLOOKUP 5 problems Function guide → Look up salary by employee ID (XLOOKUP) Look up a scanned SKU that arrived messy (TRIM + UPPER) XLOOKUP misses without blowing up (IFERROR) Find who earns a given salary (INDEX + MATCH) Latest order per customer, not the first (XLOOKUP) FIND 4 problems Check if cell contains specific text (IF + SEARCH) Next PO number from the last code (MID + VALUE) Pull the ref out of a tracking code (MID + FIND) Extract the domain from an email (MID + FIND) MID 4 problems Function guide → Extract the serial from a product code (MID) Next PO number from the last code (MID + VALUE) Pull the ref out of a tracking code (MID + FIND) Extract the domain from an email (MID + FIND) SEARCH 3 problems Extract a category code (LEFT) Extract the serial from a product code (MID) Check if cell contains specific text (IF + SEARCH) TEXT 3 problems Function guide → Show the weekday from a date (TEXT / WEEKDAY) Build a padded invoice number (TEXT + &) Due date from payment terms (WORKDAY + TEXT) UPPER 3 problems Look up a scanned SKU that arrived messy (TRIM + UPPER) Build a padded invoice number (TEXT + &) Price by SKU and region, from a scanned key CONCAT 2 problems Join first and last names (CONCAT) Build a padded invoice number (TEXT + &) COUNTBLANK 2 problems Count the stations with no reading (blank, not zero) Count who actually signed up (COUNTA) COUNTIFS 2 problems Function guide → How many orders landed in the period (COUNTIFS) Count the orders that are big AND fast (SUMPRODUCT) DATEDIF 2 problems Function guide → Months in role, not days (DATEDIF) Years of service, rounded down (DATEDIF) IFNA 2 problems Function guide → Default to zero on missing discount (VLOOKUP + IFERROR) Handle missing SKUs in a lookup (IFERROR) LEFT 2 problems Extract a category code (LEFT) Count invoices by prefix (COUNTIF wildcards) LEN 2 problems Function guide → Count product code characters (LEN) Extract the domain from an email (MID + FIND) RIGHT 2 problems Next PO number from the last code (MID + VALUE) Extract the domain from an email (MID + FIND) ROUND 2 problems Function guide → Round unit prices to whole numbers (ROUND) Year-over-year growth (percent change) TEXTJOIN 2 problems Build a mailing list in one cell (TEXTJOIN) One cell listing every rep in a region (TEXTJOIN) VALUE 2 problems Next PO number from the last code (MID + VALUE) The total says 0, the column is full (text numbers) AVERAGE 1 problems Function guide → Average three exam scores AVERAGEIF 1 problems Average deal size for one region (AVERAGEIF) CHAR 1 problems Clean extra spaces from imported names (TRIM) CHOOSE 1 problems Show the weekday from a date (TEXT / WEEKDAY) COLUMN 1 problems Fetch a cell whose address is spelled out in two cells (INDIRECT) COUNTA 1 problems Count who actually signed up (COUNTA) IFS 1 problems Calculate tiered sales commission (IF) INDIRECT 1 problems Function guide → Fetch a cell whose address is spelled out in two cells (INDIRECT) ISNA 1 problems Function guide → Audit missing SKUs with ISNA ISNUMBER 1 problems Check if cell contains specific text (IF + SEARCH) LARGE 1 problems Top three deals total (SUMIF + LARGE) LOOKUP 1 problems Latest order per customer, not the first (XLOOKUP) MEDIAN 1 problems Function guide → Compare a score to the middle value NETWORKDAYS 1 problems Business days to payment (NETWORKDAYS) PRODUCT 1 problems Function guide → Multiply quantity × unit price (* / PRODUCT) PROPER 1 problems Capitalize customer names (PROPER) ROUNDDOWN 1 problems Function guide → Years of service, rounded down (DATEDIF) ROWS 1 problems How many vendors do not match (SUMPRODUCT over two columns) SQRT 1 problems Monthly volatility with STDEV STDEV 1 problems Function guide → Monthly volatility with STDEV SUBSTITUTE 1 problems Clean extra spaces from imported names (TRIM) UNIQUE 1 problems Remove duplicate contacts (UNIQUE) VAR 1 problems Monthly volatility with STDEV WEEKDAY 1 problems Function guide → Show the weekday from a date (TEXT / WEEKDAY) WORKDAY 1 problems Due date from payment terms (WORKDAY + TEXT) Why practise instead of watching a tutorial Excel is a skill in your hands, not knowledge in your head. Ten VLOOKUP videos later you still hesitate in your own sheet: does the range need $ , is the criterion "North" or D2 , what if the lookup column sits on the left. Those judgements only become automatic once you have typed them, got them wrong and fixed them in a real sheet. Watching: you learn the function's name and the shape of its syntax, then freeze when the data changes. Practising: you write against real data, see #N/A immediately, and remember the fix because you made it yourself. Every problem here covers one recurring situation — repeat it until you can write it without thinking. SheetDo locks the sheet on purpose: it makes you practise the reference, not the answer. Select the right range, get the argument order right, put the criterion in quotes — those three decisions are identical in every real report. How to pick a difficulty The library has three tiers. Not sure which one you are in? Try the middle one; if it passes, move up. Difficulty Who it is for What you practise Suggested pace Easy (32) You have opened Excel and written a formula or two SUM, AVERAGE, multiplication, joining text, basic IF Five in a row, to get the feel of selecting a range Medium (49) You can write VLOOKUP but keep meeting #N/A, and you total things by filtering by hand Exact-match VLOOKUP / XLOOKUP, SUMIF, COUNTIF, IFERROR, MID / LEFT Two or three a day, until hints are no longer needed Hard (32) You are preparing for an Excel interview, or reconcile multi-condition reports every day Nested IF, multi-criteria SUMIFS, SUMPRODUCT weighted averages, array-style criteria Time yourself: keep each one under ten minutes Difficulty is one axis, category the other: 24 lookup and reference, 14 logic, 55 maths, 13 text, 7 data cleaning. For interview prep, go lookup → logic → aggregation — that is the order employers screen in. Three ways to work through the library If you would rather not pick problem by problem, choose a route. Each one is graded and goes in order. First time in Excel: take the problems marked Easy in order. The goal is knowing where a formula goes and what a range refers to. One function at a time: VLOOKUP → XLOOKUP → SUMIF → COUNTIF → IF / IFERROR. Read the guide once, then solve two problems in a row. Interview sprint: the Top 50 track, built from what employers actually screen for. Passing consistently beats volume. The Top 50 interview track→ · All function guides→ · Excel formula cheatsheet→ · Excel shortcut cheatsheet→ The five mistakes that cost the most time What you see What actually happened What to change The formula is right, and it breaks the moment you drag it down A relative reference moved with the row Lock the range: $A$2:$A$100 VLOOKUP returns #N/A The lookup value carries spaces, the data sits left of the lookup column, or the exact-match argument is missing Clean with TRIM first; to look left, use XLOOKUP or INDEX+MATCH SUMIF returns 0 The criterion is not in quotes, or the sum range holds numbers stored as text Write "North" or ">"&D1 ; convert numbers with VALUE The formula looks correct but the result is text The cell is formatted as Text Set the format back to General and re-enter the formula Every extra condition means rewriting the whole thing You only ever use SUMIF From two conditions on, use SUMIFS — and put the sum range first VLOOKUP guide→ · SUMIF / SUMIFS guide→ · COUNTIF guide→ Can I practise in Google Sheets? Yes. VLOOKUP, XLOOKUP, IF, SUMIF and COUNTIF use the same syntax in Excel and in Google Sheets, and every problem here uses the portable form, so what you practise works in both. The differences live in Sheets-only functions such as QUERY and ARRAYFORMULA, and in macros — none of these problems need them. Excel ↔ Google Sheets cheatsheet→ FAQ Where is my practice progress stored? Pass state is saved in your browser localStorage so you can continue on the same device.