Formula Basics: practice exercises
Formulas are what make a spreadsheet more than a table, and these exercises drill the basics until they feel automatic. You write sums, differences and percentages with operators, then move on to relative, absolute and mixed references, copy formulas across rows and columns, link sheets together and track down common errors such as #DIV/0! and #REF!. Each task is checked on the result and on the formula itself, so a typed-in number will not pass.
46 exercises · level: Beginner
Start with the first task Study the course: Formula Basics →
Showing 46 of 46
First Formulas
In D2, calculate the total for bread: price × quantity.
Fill in the totals for the other items (D3:D6).
In D2:D5, calculate the discount amount in dollars: price × discount percentage.
In E2:E5, calculate the sale price.
The price went from H1 to H2. In H3, calculate the percentage change and apply the "%" format.
In D2:D7, calculate the total for each item: price × quantity.
In H1, calculate the order total.
The store gives the school the discount shown in H2. In H3, calculate the amount to pay after the discount.
In H4, find the average item price (column B).
In H5, find the highest price, and in H6 the lowest.
In H7, count how many items are in the order, and in H8 count how many items have a number in the "In stock" column.
References
In C2:C6, convert the prices to euros: price in $ × the rate in F1. Write the formula in C2 and fill it down.
The rate has changed: set F1 to 0.95. The prices should recalculate by themselves.
Fill in the cost table B2:F6: the quantity from column A × the price from row 1. Write one formula in B2, then fill it right and down.
Give cell G1 the name "FXRate".
In C2:C6, convert the prices to euros using the name "FXRate".
Name the range C2:C6 "PricesEUR" and, in E2, add them up using that name.
In C2:C6, calculate the student price: price × (1 − the discount in G1). Lock the discount reference.
In D2:D6, add the one-time fee from G2 to the student price.
In E2:E6, calculate what share of the VIP price (B6) each plan costs.
Fill in the table B11:E14: the monthly price (column A) × the number of months (row 10). Use one formula in B11 for the whole table.
Sheets and Errors
On the "Quarter" sheet, in B2:B5, add up the January, February and March sales.
In C2:C5, find the best month: the maximum across the three sheets.
On the "Order" sheet, in C2:C5, pull the dollar price from the "Price List 2026" sheet (the name has spaces, so it needs apostrophes).
In D2:D5, calculate the amount in euros: quantity × price × the rate in cell B1 of the "Rates" sheet (lock the reference to the rate).
In D6, calculate the order total in euros.
Fix D2 so that it gives the total revenue.
Fix D3: the average price of coffee.
Fix D4: the juice revenue plus $5 of delivery.
Fix D5: the number of units of juice sold.
In D2:D6, calculate the average sale (revenue / units sold). Where there were no sales, show 0 instead of an error.
In E2:E6, write "missing" if the revenue is not filled in, and "OK" otherwise.
In B4, calculate the bonus without a circular reference: bonus = rate × profit / (1 + rate).
In B5, calculate the net profit after the bonus.
In B6, check your answer: multiply the rate by the net profit. You should get exactly the amount in B4.
In B10, calculate the interest on the average balance without a cycle: rate × balance / (1 − rate/2).
In B11, calculate the balance at the end of the month: the starting balance plus the interest.
On the "Summary" sheet, in B2:B5, add up the sales of the three branches.
In C2:C5, find the best branch result for each drug (the maximum across the three sheets).
In D2:D5, pull the price of each drug from the "Prices" sheet.
In E2:E5, calculate the revenue for each drug.
In B7, use one formula to calculate the revenue of the Boston branch: its sales × the prices.
Project P02
In E2:E6, pull the bonus percentage for each position from the lookup table J2:K4.
In F2:F6, calculate the gross pay including the bonus, rounded to the dollar.
In G2:G6, calculate the withholding (the rate is in N2), rounded to the dollar.
In H2:H6, calculate the net pay.
No exercises match these filters.