Excel exercises
Hands-on practice with real Excel formulas and instant, accurate feedback. Work through the exercises in order, or pick the skill you want to practise.
Beginner: Excel Foundations
Cell references, totals, percentages, counting, rounding and your first IF formulas: everything you need before the harder tools. See the learning track
Your First Formula
Know what a cell, a cell reference and a formula are, and write a formula that adds two cells.
Invoice Total with Cell References
Build a formula that starts with =, uses cell references and the + and * operators, and recalculates when the numbers it points to change.
Total, Average and Highest Monthly Spend
Summarise a column of figures with SUM, AVERAGE and MAX, and understand how Excel treats empty cells.
Row and Column Totals in a Sales Table
Add up a row and a column of figures with SUM, and understand that a copied formula shifts its references.
Commission with One Rate Cell
Lock a reference with dollar signs so that a formula can be copied down a column while still pointing at one fixed input cell.
Sale Price After a Percentage Discount
Use a percentage stored in a cell inside a formula, to calculate a discounted price and the amount saved.
Percent Change and Share of Total
Work out a percentage change against the starting value, and a share of a total by dividing by a fixed total cell.
Count Survey Responses with COUNT and COUNTA
Choose between COUNT, COUNTA and COUNTBLANK depending on whether you are counting numbers, filled cells or empty cells.
Rounding Prices and Whole Boxes
Round a value for display and for real, and pick ROUND, ROUNDUP or ROUNDDOWN depending on what the number means.
Has the Sales Target Been Met?
Make a formula choose between two results depending on whether a condition is true, using a comparison and cell references.
Free Shipping and Review Flags with AND and OR
Test two conditions in one IF, and decide whether both must be true (AND) or at least one must be true (OR).
Clean and Combine Names with TRIM and PROPER
Clean imported text with TRIM and PROPER and combine pieces of text into one result.
Split Product Codes with LEFT, RIGHT and LEN
Take characters from the start or end of a text value with LEFT and RIGHT, and measure it with LEN.
Due Dates and Days Overdue
Add days to a date, subtract two dates, and keep a result from going below zero.
Intermediate: Conditions, Lookups and Text
Conditional sums and counts, lookups, tiers, error handling, text and dates: make a sheet answer questions on its own. See the learning track
Total Revenue for One Region
Add up only the values that meet a condition, using a formula that reads the data instead of repeating a number you worked out by hand.
Average Order Value with AVERAGEIF and AVERAGEIFS
Average only the rows that meet one or several conditions, and choose AVERAGEIF or AVERAGEIFS accordingly.
Largest and Smallest Deals with MAXIFS and MINIFS
Find the largest or smallest value among only the rows that meet one or more conditions.
Count Support Tickets with COUNTIF and COUNTIFS
Count rows that meet one or several conditions, and write criteria for 'not equal to' and 'starts with'.
Look Up a Product's Price
Retrieve a value from a table by searching for a key, so the answer follows the key in the cell instead of a fixed row.
Volume Discount Tiers with IFS
Choose between several results with IFS, order the tests correctly, and handle values that sit exactly on a boundary.
Typical Salary: AVERAGE, MEDIAN, LARGE and SMALL
Choose between the average and the median when data has an outlier, and pick the nth largest or smallest value with LARGE and SMALL.
Sales Leaderboard with RANK
Rank values against a fixed list, understand how tied values are ranked, and compare first and second place.
Split Email Addresses into Username and Domain
Extract part of a text by finding a separator, instead of counting characters by hand.
Format Numbers and Dates as Text with TEXT
Convert numbers, percentages and dates to formatted text with TEXT, and join them with other text using &.
Clean Imported Amounts with TRIM, SUBSTITUTE and VALUE
Combine TRIM, SUBSTITUTE and VALUE to turn imported text into numbers that can be summed.
Find an Employee by ID with XLOOKUP
Retrieve values that sit to the left of the lookup column, using XLOOKUP, or INDEX with MATCH, when VLOOKUP cannot.
Handle Missing Prices with IFERROR
Replace an Excel error with a useful result using IFERROR, and understand what IFERROR hides.
Flag Bad Data with ISBLANK and ISNUMBER
Use ISBLANK and ISNUMBER inside nested IF functions to classify data, and count the rows that do not pass.
Employee Tenure in Years and Months with DATEDIF
Measure the time between two dates in complete years and months using DATEDIF.
Subscription Renewal Dates with EDATE and EOMONTH
Move a date forward by a number of calendar months with EDATE, and find the last day of a month with EOMONTH.
Build a Region by Product Sales Summary with SUMIFS
Total values that meet two conditions, and lock the right parts of each reference so a single formula can be copied across a whole table.
Profit and Margin by Product
Turn unit figures into profit and margin, and understand why an overall margin is not the average of the product margins.
Advanced: Lookups, Finance and Analysis
Two-way lookups, loans and investments, weighted figures and distinct counts: the formulas behind real business reports. See the learning track
Look Up a Shipping Rate with INDEX and MATCH
Combine INDEX with two MATCH functions to read one cell of a table from a row label and a column label.
Sales Between Two Dates with SUMIFS
Build comparison criteria from cell values with &, so a report window can change without rewriting the formulas.
Weighted Average Price with SUMPRODUCT
Multiply two columns and add the results in one formula with SUMPRODUCT, and use it to compute a weighted average.
Who Sold the Most? INDEX, MATCH and MAX
Find the largest, second-largest or smallest value, then use MATCH and INDEX to return the label that belongs to it.
Shipping Rate Bands with INDEX and XMATCH
Look a number up in a table of bands by finding the largest lower limit that does not exceed it.
Count Distinct Customers with UNIQUE and FILTER
Remove duplicates with UNIQUE, filter a list with FILTER, and count the result with ROWS to get a single number.
Monthly Loan Payment and Total Interest with PMT
Use PMT with matching rate and period units, and build the total cost of a loan from the monthly payment.
Savings Goal: Future Value with FV
Use FV with a regular payment and a starting balance, matching the rate and period units, and separate deposits from interest.
Is the Project Worth It? Net Present Value with NPV
Discount future cash flows with NPV, treat the initial investment correctly, and turn the result into a decision.