Excel practice · Workbook Structure

Excel named ranges practice exercises

5 graded problems with realistic data, from medium to hard.

Not a function, but a piece of syntax. Gives a cell or range a plain name, so formulas can use that name instead of the raw reference. 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 Named ranges guide first: syntax, a worked example and the mistakes people make.

In this set

  1. Use a named cell in an arithmetic formula
  2. SUM a named range instead of a raw range
  3. Combine IF with a named threshold
  4. AVERAGE a named range
  5. VLOOKUP against a named table

The exercises

1. Use a named cell in an arithmetic formula Medium

The sales tax rate on this sheet has been given the name "TaxRate" in Name Manager, rather than being referenced as a plain cell. Use the name directly to compute the total including tax.

ABCD
1ItemAmountTax rate
2Consulting5000.08
3Total incl. tax?

Enter a formula in B3.

Show a hint

TaxRate is a defined name pointing at D2. Use it exactly like you would use D2 itself: =B2*(1+TaxRate).

A named rate reads better than a bare cell reference. And if that rate ever moves to a different cell, every formula using its name still finds it; a raw D2 reference would silently point at the wrong thing.

2. SUM a named range instead of a raw range Medium

The three months of spend in B2:B4 have been given the name "Sales" in Name Manager. Total them using the name instead of typing the range.

AB
1MonthSpend
2Jan1200
3Feb900
4Mar1500
5Total?

Enter a formula in B5.

Show a hint

=SUM(Sales). No colon, no cell letters, just the name.

SUM(Sales) says what the number IS; SUM(B2:B4) only says where it lives. Naming a range is how formulas start reading like the report they're building.

3. Combine IF with a named threshold Medium

The attendance bonus cutoff has been named "Threshold" in Name Manager. Check whether Tomas qualifies using the name, not the raw cell.

ABCD
1StudentAttendance %BonusThreshold
2Tomas92?90

Enter a formula in C2.

Show a hint

=IF(B2>=Threshold,"Bonus","No Bonus"). Threshold slots into the comparison exactly like a cell reference would.

A named threshold makes the rule self-explanatory at a glance. IF(B2>=Threshold,...) reads like the actual policy, not like an arbitrary cell address.

4. AVERAGE a named range Medium

The four quiz scores in B2:B5 have been given the name "Scores". Find the average using the name.

AB
1StudentScore
2Lea88
3Noah76
4Ines95
5Marco60
6Average?

Enter a formula in B6.

Show a hint

=AVERAGE(Scores).

Once a range has a name, every formula that uses it (SUM, AVERAGE, COUNT, whatever) gets the same readability and the same protection if the range ever needs to move.

5. VLOOKUP against a named table Hard

The SKU/price table in A2:B4 has been named "PriceTable". Look up SKU-22's price using the name as VLOOKUP's table_array.

ABCDE
1SKUPriceSKU:SKU-22
2SKU-143.5Price:?
3SKU-221.2
4SKU-312

Enter a formula in E2.

Show a hint

=VLOOKUP(E1,PriceTable,2,FALSE). PriceTable stands in for A2:B4 exactly.

A named lookup table is what keeps a real workbook's VLOOKUPs from breaking the moment someone inserts a row above the table. The name keeps tracking the table wherever it is.

Solve these in a real grid

Type your formula, get graded instantly. Your first 10 exercises are free, no signup.

Start the Named ranges exercises →

More workbook structure practice

All Excel practice exercises · Excel test prep