Exercises

Learn Excel one formula at a time.

97 exercises in 9 modules. Each one is a real spreadsheet: type the formula, press Enter, and see right away whether it works.

The first module is free, no account needed. Create a free account to save your progress.

Foundations

Formulas, the everyday functions, and the $ references that make one formula fill a whole table.

0/12 solved
  1. Work out a line totalIn D2, multiply the unit price in B2 by the quantity in C2.EasyFormulas
  2. Average two exam scoresIn D2, work out the average of the two exam scores in B2 and C2 with + and /: add them, then divide by 2.EasyOrder of operations
  3. Total a monthly budgetIn B8, use SUM to add up all the expenses in B2:B7.EasySUM
  4. Average daily visitorsIn E2, use AVERAGE to find the average number of visitors per day in B2:B8.EasyAVERAGE
  5. Find the temperature rangeIn E2, use MAX and MIN to work out the spread of the week: the highest temperature in B2:B8 minus the lowest.EasyMAX and MIN
  6. Count the real answersSome people answered "n/a" or skipped the question. In E2, use COUNT to count how many people gave a numeric rating in B2:B11.EasyCOUNT and COUNTA
  7. Copy one formula down a columnWrite one formula in D2 that multiplies price by quantity. It will be copied down to D7, so it must work on every row.EasyRelative references
  8. Lock the tax rate with $In C2, work out the sales tax: the price in B2 times the tax rate in F1. The formula will be copied down to C7, and every row must use the rate in F1.EasyAbsolute references ($)
  9. Each region's share of salesIn C2, divide the region's sales by the total in B7. It will be copied down to C6, so make sure every row divides by B7.MediumPercent of total
  10. Growth from last yearIn D2, work out the change from last year (B2) to this year (C2) as a fraction of last year. It will be copied down to D6.MediumPercent change
  11. Price with VAT, to the centIn C2, work out the price including VAT: the net price in B2 times (1 + the VAT rate in F1), rounded to 2 decimals with ROUND. It will be copied down to C6.MediumROUND
  12. Fill a price grid with one formulaEach cell of B2:E7 is a quantity (column A) times a unit price (row 1). Write one formula in B2 that fills the whole grid when copied across and down.HardMixed references

Logic and decisions

Let a formula decide: IF, AND, OR, nested IF, IFS, SWITCH and IFERROR.

0/9 solved
  1. Mark each score Pass or FailIn C2, use IF to return "Pass" when the score in B2 is 60 or more, and "Fail" otherwise.EasyIFPro, Pro only
  2. Pay commission above a thresholdReps earn commission only when sales reach the threshold in F1. In C2, use IF to return the sales in B2 times the rate in F2 when B2 is at least F1, and 0 otherwise. It will be copied down to C8.MediumIFPro, Pro only
  3. Bonus only when both targets are metAn employee gets a bonus when sales (B2) are at least G1 AND the rating (C2) is at least G2. In D2, use IF and AND to return "Bonus" or "No bonus". It will be copied down to D8.MediumANDPro, Pro only
  4. Free shipping for members or big ordersShipping is free (0) when the order total in B2 is 50 or more, OR the customer is a member (C2 is "Yes"). Otherwise it costs 4.99. Write it in D2 with IF and OR; it will be copied down to D8.MediumORPro, Pro only
  5. Discount tiers with nested IFOrders of 1000 or more get 10% off, orders of 500 or more get 5%, smaller orders get 0. In C2, use IF to return the discount rate (0.1, 0.05 or 0) for the order in B2. It will be copied down to C8.MediumNested IFPro, Pro only
  6. Letter grades with IFSIn C2, use IFS to return "A" for 90+, "B" for 80+, "C" for 70+, "D" for 60+, and "F" otherwise, for the score in B2. It will be copied down to C9.MediumIFSPro, Pro only
  7. Turn codes into names with SWITCHIn C2, use SWITCH to turn the region code in B2 into its name: "N" is North, "S" is South, "E" is East, "W" is West, anything else is "Unknown". It will be copied down to C8.MediumSWITCHPro, Pro only
  8. Hide #DIV/0! with IFERRORConversion rate is sales divided by visits. Some channels had no visits yet. In D2, divide C2 by B2, and use IFERROR to return 0 instead of an error. It will be copied down to D7.MediumIFERRORPro, Pro only
  9. Pending until shippedIn D2, use IF to return "Pending" when the shipped date in C2 is empty, and "Shipped" otherwise. It will be copied down to D8.EasyIF with blanksPro, Pro only

Conditional counting and sums

Count, add, average and find the maximum of only the rows that matter, with one or many conditions.

0/11 solved
  1. Count the late deliveriesIn F2, use COUNTIF to count how many deliveries in C2:C11 have the status "Late".EasyCOUNTIFPro, Pro only
  2. Count orders above a thresholdIn F2, use COUNTIF to count the orders in B2:B11 that are at least the threshold in F1.MediumCOUNTIFPro, Pro only
  3. Total one rep's salesIn G2, use SUMIF to add up the amounts in C2:C11 for the rep named in G1.EasySUMIFPro, Pro only
  4. Total with two conditionsIn H3, use SUMIFS to add up the amounts in E2:E13 for the region in H1 and the product in H2.MediumSUMIFSPro, Pro only
  5. Count deals that meet two conditionsIn G3, use COUNTIFS to count the deals in the region in G1 whose value in C2:C12 is at least G2.MediumCOUNTIFSPro, Pro only
  6. Average deal size in one regionIn G2, use AVERAGEIF to average the deal values in C2:C12 for the region in G1.EasyAVERAGEIFPro, Pro only
  7. Average delivery time for one carrier and serviceIn H3, use AVERAGEIFS to average the delivery days in D2:D13 for the carrier in H1 and the service in H2.MediumAVERAGEIFSPro, Pro only
  8. Biggest sale in a regionIn H2, use MAXIFS to return the largest amount in E2:E13 for the region in H1.MediumMAXIFSPro, Pro only
  9. Count products that contain a wordIn F2, use COUNTIF to count the products in A2:A11 whose name contains the word in F1 anywhere.MediumWildcardsPro, Pro only
  10. Total revenue in one formulaIn F2, use SUMPRODUCT to work out total revenue: each price in B2:B8 times its quantity in C2:C8, all added up.MediumSUMPRODUCTPro, Pro only
  11. Revenue for one region, no helper columnIn H2, work out the revenue (price × quantity) for the region in H1 only, using SUMPRODUCT.HardSUMPRODUCTPro, Pro only

Lookups

Pull the right value out of a table: VLOOKUP, HLOOKUP, INDEX with MATCH, and XLOOKUP.

0/10 solved
  1. Look up a product priceIn F2, use VLOOKUP to find the price of the product code in E2, using the table in A2:C7. Ask for an exact match.EasyVLOOKUPPro, Pro only
  2. Commission tiers with an approximate matchIn C2, use VLOOKUP to look up the commission rate for the sales in B2 in the tier table E2:F6 (each row is the rate from that amount up). It will be copied down to C8.MediumVLOOKUP (approximate)Pro, Pro only
  3. Read a row-based table with HLOOKUPIn H2, use HLOOKUP to return the profit for the quarter named in H1, from the table A1:E4. Ask for an exact match.EasyHLOOKUPPro, Pro only
  4. Look up to the left with INDEX and MATCHThe employee ID is in column C and the name is to its left, in column A. In G2, use INDEX and MATCH to return the name of the employee whose ID is in G1. An ID that is not in the list shows #N/A.MediumINDEX and MATCHPro, Pro only
  5. Two-way lookup in a rate gridIn H3, use INDEX and MATCH to return the shipping rate from the grid B2:E6 for the zone in H1 (rows) and the weight class in H2 (columns).HardINDEX and MATCHPro, Pro only
  6. XLOOKUP with a friendly "not found"In G2, use XLOOKUP to return the department of the employee ID in G1. If the ID is not in the list, return "Not found".EasyXLOOKUPPro, Pro only
  7. Return a whole record at onceIn H2, use XLOOKUP to look up the employee ID in G2 and return the whole matching row of A2:D9: name, department, ID and email. The result spills into H2:K2.MediumXLOOKUPPro, Pro only
  8. Find the latest priceThe price log is in date order. In G2, use XLOOKUP to return the most recent price for the product in G1.HardXLOOKUPPro, Pro only
  9. Shipping cost by weight bracketEach row of E2:F6 is the cost from that weight up. In C2, use XLOOKUP to return the shipping cost for the weight in B2. It will be copied down to C8.MediumXLOOKUPPro, Pro only
  10. A lookup that never shows #N/AIn F2, use VLOOKUP to look up the price of the code in E2 in the table A2:B8 (exact match), and IFERROR to return "Not listed" when the code is missing.MediumIFERROR + VLOOKUPPro, Pro only

Cleaning text

Split, join, clean and label text the way real exports need it.

0/13 solved
  1. Join first and last namesIn C2, join the first name in A2 and the last name in B2 with a space between them. It will be copied down to C7.EasyJoining text (&)Pro, Pro only
  2. Clean up messy namesThe names in column A were typed carelessly. In B2, use TRIM to remove the extra spaces and PROPER to capitalise each word. It will be copied down to B7.EasyTRIM and PROPERPro, Pro only
  3. Split a SKU into its partsEvery SKU starts with a 3-letter warehouse code, like NYC-0042. In B2, use LEFT to return the warehouse code. It will be copied down to B7.EasyLEFT and RIGHTPro, Pro only
  4. Show only the last four digitsIn B2, use RIGHT to build a masked card label: the text "**** " followed by the last 4 characters of the card number in A2. It will be copied down to B6.EasyRIGHTPro, Pro only
  5. Pull the year out of an invoice numberInvoice numbers look like INV-2026-00412. In B2, use MID to return the 4-digit year, which starts at character 5. It will be copied down to B7.EasyMIDPro, Pro only
  6. Get the username from an emailIn C2, use LEFT to return the part of the email in B2 before the @. It will be copied down to C7.MediumLEFT and FINDPro, Pro only
  7. Get the domain with TEXTAFTERIn C2, use TEXTAFTER to return the domain of the email in B2: everything after the @. It will be copied down to C7.EasyTEXTAFTERPro, Pro only
  8. First names with TEXTBEFOREIn C2, use TEXTBEFORE to return the first name from the full name in A2. It will be copied down to C7.EasyTEXTBEFOREPro, Pro only
  9. Strip dashes and spaces from phone numbersIn B2, use SUBSTITUTE to remove both the dashes and the spaces from the phone number in A2. It will be copied down to B7.MediumSUBSTITUTEPro, Pro only
  10. One line with every attendeeIn D2, use TEXTJOIN to join the names in B2:B9 into one line separated by a comma and a space, skipping the empty cells.MediumTEXTJOINPro, Pro only
  11. A label with a formatted numberIn D2, build the label "Total: " followed by the total in B8, formatted with TEXT as dollars with a thousands separator and 2 decimals ($#,##0.00).MediumTEXTPro, Pro only
  12. Flag tickets that mention a refundIn C2, use SEARCH to look for the word refund in the message in B2, in any capitalisation: return "Refund" when it is there, and "" (empty) otherwise. It will be copied down to C8.HardSEARCHPro, Pro only
  13. Check that every code is 8 charactersA valid product code has exactly 8 characters. In B2, use LEN and IF to return "OK" when the code in A2 has 8 characters, and "Check" otherwise. It will be copied down to B8.MediumLENPro, Pro only

Dates and deadlines

Dates are numbers: build them, count the days between them, and move them by months or working days.

0/10 solved
  1. Build a date from year, month and dayAn export split each date into three columns. In D2, use DATE to build the date from the year in A2, the month in B2 and the day in C2. It will be copied down to D7.EasyDATEPro, Pro only
  2. How many days late?In D2, work out how many days after the due date (B2) each invoice was paid (C2). Early payments come out negative. It will be copied down to D7.EasyDate arithmeticPro, Pro only
  3. Age in whole yearsIn C2, use DATEDIF to work out how many complete years old each person is on the date in F1. It will be copied down to C7.MediumDATEDIFPro, Pro only
  4. When does each contract end?In D2, use EDATE to return the end date of each contract: the start date in B2 plus the number of months in C2. It will be copied down to D7.EasyEDATEPro, Pro only
  5. Due at the end of next monthInvoices are due on the last day of the month after they are issued. In C2, use EOMONTH to return the due date for the issue date in B2. It will be copied down to C7.MediumEOMONTHPro, Pro only
  6. Working days in each projectIn D2, use NETWORKDAYS to count the working days from the start date in B2 to the end date in C2, skipping the holidays in F2:F4. It will be copied down to D6.MediumNETWORKDAYSPro, Pro only
  7. Delivery date in working daysEach order ships after a number of working days. In D2, use WORKDAY to return the ship date: the order date in B2 plus the working days in C2. It will be copied down to D7.MediumWORKDAYPro, Pro only
  8. Weekend or weekday?In C2, use WEEKDAY and IF to return "Weekend" when the date in A2 is a Saturday or Sunday, and "Weekday" otherwise. It will be copied down to C8.MediumWEEKDAYPro, Pro only
  9. Count orders between two datesIn F3, use COUNTIFS to count the orders in A2:A13 dated from F1 to F2, both dates included.HardCOUNTIFS with datesPro, Pro only
  10. Turn dates into month namesIn C2, use TEXT to return the full month name of the date in A2, like "March". It will be copied down to C7.MediumTEXT with datesPro, Pro only

Statistics that matter at work

Median versus average, top values, rankings and the most common value.

0/6 solved
  1. The typical salary, not the averageThe founder's salary is in the list. In E2, use MEDIAN to return the median salary of B2:B10.EasyMEDIANPro, Pro only
  2. The runner-up scoreIn E2, use LARGE to return the second-highest number of points in B2:B9.EasyLARGEPro, Pro only
  3. Total of the top threeIn E2, add up the three highest scores in B2:B9 in one formula, finding them with LARGE.HardLARGEPro, Pro only
  4. Rank every playerIn C2, use RANK.EQ to rank the points in B2 against all points in B2:B9, highest = 1. It will be copied down to C9.MediumRANK.EQPro, Pro only
  5. The best-selling shoe sizeEach row is one pair sold. In D2, use MODE.SNGL to return the shoe size that appears most often in B2:B15.EasyMODE.SNGLPro, Pro only
  6. How many beat the average?In E2, use COUNTIF and AVERAGE to count the stores in B2:B12 whose sales are above the average of B2:B12.HardCOUNTIF with a calculationPro, Pro only

Modern Excel: dynamic arrays

One formula that returns a whole list: FILTER, SORT, UNIQUE, SEQUENCE, TAKE and LET.

0/12 solved
  1. One formula, a whole column of totalsIn D2, multiply all the prices in B2:B7 by all the quantities in C2:C7 in one formula, and let the results spill down to D7.EasySpillingPro, Pro only
  2. A list of customers without repeatsIn E2, use UNIQUE to list every customer in B2:B13 once.EasyUNIQUEPro, Pro only
  3. How many different customers?In F2, use COUNTA and UNIQUE to count how many different customers appear in B2:B13.MediumCOUNTA + UNIQUEPro, Pro only
  4. A leaderboard that sorts itselfIn D2, use SORT to return the table A2:B9 sorted by points, highest first.EasySORTPro, Pro only
  5. Names in score orderIn D2, use SORTBY to list the player names from A2:A9 in order of their points in B2:B9, highest first.MediumSORTBYPro, Pro only
  6. Show only one regionIn F3, use FILTER to return every row of A2:C13 whose region is the one in G1.EasyFILTERPro, Pro only
  7. Filter by two conditions, with a fallbackIn F4, use FILTER to return the rows of A2:C13 for the region in G1 with an amount of at least G2. If nothing matches, show "No match".MediumFILTERPro, Pro only
  8. Filter for one region or anotherIn E2, use FILTER to list the order numbers in A2:A13 whose region is either G1 or G2.MediumFILTERPro, Pro only
  9. A sorted list of distinct customersIn E2, use SORT and UNIQUE to list every customer in B2:B13 once, sorted A to Z.MediumSORT + UNIQUEPro, Pro only
  10. Top three in one formulaIn D2, use TAKE and SORT to return the three players with the most points: the rows of A2:B9 sorted by points, highest first, keeping only the first 3.HardTAKE + SORTPro, Pro only
  11. Name the parts of a formula with LETReps at or above the target in F1 earn the rate in F2, others the rate in F3. In C2, use LET to name the sales and the rate (pick the rate with IF), then return sales × rate. It will be copied down to C8.HardLETPro, Pro only
  12. A 12-month timeline in one formulaIn A4, use EOMONTH and SEQUENCE to return the last day of each of the 12 months starting with the month of the date in B1.HardSEQUENCEPro, Pro only

Finance and business maths

Margins, growth, loans, savings and investment decisions, the way analysts build them.

0/14 solved
  1. Gross margin per productIn D2, work out the gross margin: price (B2) minus cost (C2), divided by the price. It will be copied down to D7.EasyMarginPro, Pro only
  2. How many units to break even?In B6, work out the break-even volume: fixed costs (B1) divided by the price (B2) minus the variable cost per unit (B3).MediumBreak-evenPro, Pro only
  3. Compound annual growth rateIn E2, work out the CAGR from the start value (B2) to the end value (C2) over the number of years in D2. It will be copied down to E6.MediumCAGRPro, Pro only
  4. Project revenue five years outRevenue for 2026 is in B4. In C4, grow the previous year by the rate in B1. It will be copied right to G4.MediumGrowth projectionPro, Pro only
  5. What will the savings be worth?In B6, use FV to work out the balance after B3 years: starting with B1, depositing B4 every month, at the annual rate in B2 compounded monthly.MediumFVPro, Pro only
  6. Monthly payment on a loanIn B5, use PMT to work out the monthly payment on a loan of B1 at the annual rate in B2 over B3 years. Show it as a positive number.MediumPMTPro, Pro only
  7. How much of the first payment is interest?In B6, use IPMT to work out the interest in payment number B4 of a loan of B1 at the annual rate in B2 over B3 years, paid monthly. Show it as a positive number.HardIPMTPro, Pro only
  8. How long until the card is paid off?In B5, use NPER to work out how many months it takes to pay off the balance in B1 at the annual rate in B2 by paying B3 a month.MediumNPERPro, Pro only
  9. Lump sum or yearly payments?A prize pays B1 at the end of each year for B3 years. In B5, use PV to work out what those payments are worth today at the discount rate in B2, as a positive number.MediumPVPro, Pro only
  10. Is the project worth it? NPVThe year-0 investment is in B4 (negative) and years 1 to 5 are in B5:B9. In B11, work out the NPV at the discount rate in B1.HardNPVPro, Pro only
  11. The return on a project: IRRIn B11, work out the IRR of the cash flows in B4:B9, from the year-0 investment to year 5.HardIRRPro, Pro only
  12. Return on investments made on irregular datesIn E2, use XIRR to work out the annual return of the cash flows in B2:B7, which happened on the dates in A2:A7.HardXIRRPro, Pro only
  13. Portfolio return, weighted by sizeIn F2, use SUMPRODUCT and SUM to work out the portfolio return: each holding’s return in C2:C6 weighted by its value in B2:B6.MediumWeighted averagePro, Pro only
  14. A scenario switch for a modelB1 holds the scenario number (1 Base, 2 Upside, 3 Downside). In F4, use CHOOSE to return the assumption for the chosen scenario from B4:D4. It will be copied down to F6.HardCHOOSEPro, Pro only