Excel practice · Workbook Structure

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

  1. Apply a tax rate stored on another sheet
  2. Pull a pay rate directly from another sheet
  3. Total a range that lives on another sheet
  4. Compare a value against a threshold kept on another sheet
  5. Look up a value in a table kept on another sheet

The exercises

1. Apply a tax rate stored on another sheet Medium

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

AB
1ItemAmount
2Consulting500
3Total incl. tax?

Sheet Rates

AB
1
2Sales tax0.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.

2. Pull a pay rate directly from another sheet Medium

Jordan logged hours on the "Payroll" sheet. The hourly rate lives on the "Rates" sheet instead. Compute Jordan's pay.

Sheet Payroll

AB
1EmployeeHours
2Jordan40
3Pay?

Sheet Rates

AB
1
2Hourly rate22.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.

3. Total a range that lives on another sheet Medium

The monthly marketing spend is tracked on its own "Marketing Spend" sheet. Total it into this quarter's summary.

Sheet Q1 Summary

AB
1CategoryTotal
2Marketing?

Sheet Marketing Spend

AB
1MonthSpend
2Jan1200
3Feb900
4Mar1500

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.

4. Compare a value against a threshold kept on another sheet Medium

Orders qualify for the bulk rate once quantity reaches the threshold on the "Rates" sheet, currently 10 units. Check this order.

Sheet Orders

ABC
1OrderQtyRate Used
2#88112?

Sheet Rates

AB
1Bulk Threshold (units)10
2Bulk Rate0.9
3Standard Rate1

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.

5. Look up a value in a table kept on another sheet Hard

Player names are on the "Roster" sheet; positions are tracked separately on the "Positions" sheet. Look up Maya's position.

Sheet Roster

AB
1PlayerPosition
2Maya?

Sheet Positions

AB
1PlayerPosition
2MayaForward
3OwenGuard
4PriyaCenter

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 →

More workbook structure practice

All Excel practice exercises · Excel test prep