Excel exercises for beginners
This path starts with typing your first formula and ends with your first decisions using IF. You do not need any experience: the first exercise explains what a cell, a cell reference and a formula are, and every later exercise builds on the one before.
Each exercise gives you a small, realistic task and a real spreadsheet to edit. Your formula is checked on the data in front of you and on other data, so a number typed in by hand or a trick that only works today does not pass. When something is wrong, the feedback says what to look at, and hints are there if you are stuck.
What you will be able to do
- Write formulas that use cell references, and copy them down without breaking them.
- Add, average and count a range, and total rows and columns.
- Calculate a percentage discount, a percent change and a share of a total.
- Round a number for display and for real, choosing between ROUND, ROUNDUP and ROUNDDOWN.
- Make a first decision with IF, AND and OR.
- Tidy text and work with dates in simple cases, such as cleaning names and product codes and finding a due date.
Skills covered
- IF
- Absolute references
- Arithmetic
- Cell references
- MAX
- Percentages
- Ranges
- Relative references
- SUM
- Addition
- &
- AND
- AVERAGE
- Comparison operators
Common mistakes to watch for
- Typing the numbers instead of the cells
- A formula with typed numbers is right once and wrong as soon as the data changes. Point the formula at the cells that hold the numbers.
- Forgetting to lock a reference
- When you copy a formula down, every reference moves with it. A rate or a total that must stay put needs dollar signs, which is what absolute references are for.
- Dividing by the wrong value
- Percent change is measured against the starting value, not the new one. Dividing by the wrong figure gives a result that looks plausible and is wrong.
- Using ROUND when the number means boxes
- Rounding to the nearest whole number can leave you one box short. Think about what the number stands for before choosing how to round it.
The exercises, in order
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.