Excel referencing other sheets practice exercises
5 graded problems with realistic data, from medium to hard.
Not a function, but a piece of syntax. Any formula can reach a cell or range on another sheet in the same workbook. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the Referencing other sheets guide first: syntax, a worked example and the mistakes people make.
In this set
- Apply a tax rate stored on another sheet
- Pull a pay rate directly from another sheet
- Total a range that lives on another sheet
- Compare a value against a threshold kept on another sheet
- Look up a value in a table kept on another sheet
The exercises
This invoice lists one line item. The sales tax rate isn't on this sheet, though. It lives on the "Rates" sheet, kept separate so it can be updated in one place. Check the "Rates" tab, then compute the total including tax.
Sheet Invoice
| A | B | |
|---|---|---|
| 1 | Item | Amount |
| 2 | Consulting | 500 |
| 3 | Total incl. tax | ? |
Sheet Rates
| A | B | |
|---|---|---|
| 1 | ||
| 2 | Sales tax | 0.08 |
Enter a formula in B3.
Show a hint
Reach onto another sheet with SheetName!CellRef. No colon, no quotes needed unless the sheet name has a space: =B2*(1+Rates!B2).
Keeping one shared rate on its own sheet (instead of copy-pasted onto every invoice) means updating it once instead of hunting down every copy.
Jordan logged hours on the "Payroll" sheet. The hourly rate lives on the "Rates" sheet instead. Compute Jordan's pay.
Sheet Payroll
| A | B | |
|---|---|---|
| 1 | Employee | Hours |
| 2 | Jordan | 40 |
| 3 | Pay | ? |
Sheet Rates
| A | B | |
|---|---|---|
| 1 | ||
| 2 | Hourly rate | 22.5 |
Enter a formula in B3.
Show a hint
=B2*Rates!B2. Multiply this sheet's hours by the rate sitting on the other sheet.
A rates/assumptions sheet that every other sheet in the workbook points to is one of the most common real spreadsheet patterns.
The monthly marketing spend is tracked on its own "Marketing Spend" sheet. Total it into this quarter's summary.
Sheet Q1 Summary
| A | B | |
|---|---|---|
| 1 | Category | Total |
| 2 | Marketing | ? |
Sheet Marketing Spend
| A | B | |
|---|---|---|
| 1 | Month | Spend |
| 2 | Jan | 1200 |
| 3 | Feb | 900 |
| 4 | Mar | 1500 |
Enter a formula in B2.
Show a hint
A sheet name with a space needs quotes: =SUM('Marketing Spend'!B2:B4).
A summary tab that totals detail kept on other sheets (rather than duplicating the detail itself) is exactly how most real "dashboard" sheets are built.
Orders qualify for the bulk rate once quantity reaches the threshold on the "Rates" sheet, currently 10 units. Check this order.
Sheet Orders
| A | B | C | |
|---|---|---|---|
| 1 | Order | Qty | Rate Used |
| 2 | #881 | 12 | ? |
Sheet Rates
| A | B | |
|---|---|---|
| 1 | Bulk Threshold (units) | 10 |
| 2 | Bulk Rate | 0.9 |
| 3 | Standard Rate | 1 |
Enter a formula in C2.
Show a hint
IF still works exactly the same way when one side of the comparison lives elsewhere: =IF(B2>=Rates!B2,"Bulk","Standard").
Keeping a threshold like this on a settings/rates sheet (instead of hardcoded into every IF) means changing the bulk cutoff once instead of editing every formula that uses it.
Player names are on the "Roster" sheet; positions are tracked separately on the "Positions" sheet. Look up Maya's position.
Sheet Roster
| A | B | |
|---|---|---|
| 1 | Player | Position |
| 2 | Maya | ? |
Sheet Positions
| A | B | |
|---|---|---|
| 1 | Player | Position |
| 2 | Maya | Forward |
| 3 | Owen | Guard |
| 4 | Priya | Center |
Enter a formula in B2.
Show a hint
VLOOKUP's table_array argument can point at another sheet just like any other range: =VLOOKUP(A2,Positions!A2:B4,2,FALSE).
Splitting a roster from a lookup table of details is normal in real workbooks. VLOOKUP doesn't care which sheet the table lives on.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the Referencing other sheets exercises →