Referencing other worksheets in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the basic formula to reference cell A1 from a worksheet named 'Sales'?

Use =Sales!A1. This tells Excel to get the value from cell A1 in the 'Sales' worksheet.

beginner

How do you reference a cell from a worksheet with spaces in its name, like 'Monthly Sales'?

Put single quotes around the sheet name: ='Monthly Sales'!A1. The quotes help Excel understand the full sheet name.

intermediate

What happens if you delete or rename a worksheet that a formula references?

The formula will show an error like #REF! because Excel can’t find the sheet or cell anymore.

intermediate

How can you reference the same cell across multiple worksheets in a formula?

Use a 3D reference like =SUM(Sheet1:Sheet3!A1) to add values from cell A1 in all sheets from Sheet1 to Sheet3.

beginner

Why is it useful to reference other worksheets instead of copying data?

Referencing keeps data linked and updated automatically. If the source changes, your formula shows the new value without extra work.

How do you write a formula to get the value from cell B2 in a worksheet named 'Budget'?

A =B2!Budget

B =Budget!B2

C ='Budget'!B2

D =Budget:B2

The correct syntax is =Budget!B2 to reference cell B2 in the 'Budget' sheet.

Which formula correctly references cell C3 in a sheet named 'Year 2024'?

A ='Year 2024'!C3

B =Year_2024!C3

C =Year 2024!C3

D =Year2024!C3

Sheet names with spaces need single quotes: ='Year 2024'!C3.

What error appears if a referenced worksheet is deleted?

A #NAME?

B #VALUE!

C #REF!

D #DIV/0!

Deleting a referenced sheet causes a #REF! error because the reference is broken.

How do you sum cell A1 across sheets named Jan, Feb, and Mar?

A =SUM(Jan:Mar!A1)

B =SUM(Jan,Feb,Mar!A1)

C =SUM(A1!Jan:Mar)

D =SUM(Jan!A1, Feb!A1, Mar!A1)

Use a 3D reference: =SUM(Jan:Mar!A1) sums A1 across all sheets from Jan to Mar.

Why use worksheet references instead of copying data?

A To make the file size bigger

B To prevent errors

C To avoid using formulas

D To keep data updated automatically

Referencing links data so changes update automatically without copying.

Explain how to reference a cell from another worksheet that has spaces in its name.

Think about how Excel handles sheet names with spaces.

Describe what a 3D reference is and give an example.

It sums or calculates across sheets in a range.