Cross-sheet references in Google Sheets - Step-by-Step Guide

Introduction

Cross-sheet references let you use data from one sheet inside another sheet in the same Google Sheets file. This helps you organize data in separate sheets but still connect them with formulas.

When you want to sum sales numbers from a 'January' sheet into a 'Summary' sheet

When you keep a list of products on one sheet and want to pull prices into another sheet

When you track expenses on different sheets by month and want a yearly total on a summary sheet

When you want to compare data from two sheets side by side using formulas

When you want to avoid copying data manually and keep it linked automatically

Steps

Step 1: Click

- the cell where you want the data on the target sheet

The cell is selected and ready for input

Step 2: Type

- the formula bar

The formula bar shows your input

💡 Start with an equal sign (=) to begin a formula

Step 3: Type

- the formula bar

You see the sheet name followed by an exclamation mark and the cell reference

💡 Use the format: SheetName!CellReference, for example: January!B2

Step 4: Press

- Enter key

The cell shows the value from the referenced cell in the other sheet

Step 5: Alternatively, click

- the sheet tab of the source sheet

The sheet switches to the source sheet

💡 You can click the cell you want to reference instead of typing it

Step 6: Click

- the target sheet tab

You return to the target sheet with the formula completed

Step 7: Press

- Enter key

The formula completes and shows the referenced cell's value

Before vs After

Before

Cell A1 on 'Summary' sheet is empty

After

Cell A1 on 'Summary' sheet shows the value from cell B2 on 'January' sheet

Settings Reference

Sheet name

📍 Sheet tab at the bottom of Google Sheets

Identifies which sheet to get data from

Default: Sheet1

Cell reference

📍 Formula bar when typing formula

Specifies the exact cell to pull data from

Default: None

Common Mistakes

Typing the sheet name without the exclamation mark, like JanuaryB2

Google Sheets does not recognize the reference without the exclamation mark separating sheet and cell

Use the format January!B2 with an exclamation mark

Using spaces in sheet names without single quotes, like January Sales!A1

Sheet names with spaces must be enclosed in single quotes to be recognized

Write the reference as 'January Sales'!A1 with single quotes around the sheet name

Summary

Cross-sheet references let you use data from one sheet inside another sheet with formulas.

Use the format SheetName!CellReference to link cells across sheets.

Remember to use single quotes around sheet names that have spaces.

Practice

1. What is the correct way to reference cell B2 from a sheet named Sales in another sheet?

easy

A. =Sales!B2

B. =B2!Sales

C. =Sales:B2

D. =Sheet2.B2

Solution

1. Step 1: Understand cross-sheet reference format

   In Google Sheets, to reference another sheet's cell, use SheetName!Cell.

2. Step 2: Apply the format to the example

   For sheet named Sales and cell B2, the reference is =Sales!B2.

3. Final Answer:

   =Sales!B2 -> Option A

4. Quick Check:

   SheetName!Cell = =Sales!B2 [OK]

Hint: Use SheetName!Cell to link sheets quickly [OK]

Common Mistakes:

• Swapping sheet name and cell reference order
• Using colon instead of exclamation mark
• Adding extra dots or symbols

2. Which of these formulas correctly references cell A1 from a sheet named 2023 Data?

easy

A. ='2023 Data'!A1

B. =2023 Data!A1

C. =2023_Data!A1

D. =2023Data!A1

Solution

1. Step 1: Recognize sheet names with spaces need quotes

   Sheet names with spaces must be enclosed in single quotes when referenced.

2. Step 2: Apply quotes to the sheet name

   The correct reference is ='2023 Data'!A1.

3. Final Answer:

   ='2023 Data'!A1 -> Option A

4. Quick Check:

   Sheet names with spaces need quotes [OK]

Hint: Put single quotes around sheet names with spaces [OK]

Common Mistakes:

• Omitting quotes for sheet names with spaces
• Using underscores instead of spaces without quotes
• Using invalid characters in sheet names

3. Given the formula =SUM('Expenses 2024'!C2:C5) in Sheet1, what does it calculate?

medium

A. Sum of cells C2 to C5 in the current sheet

B. Sum of cells C2 to C5 in the sheet named Expenses2024 (no space)

C. Sum of cells C2 to C5 in all sheets

D. Sum of cells C2 to C5 in the sheet named 'Expenses 2024'

Solution

1. Step 1: Identify the sheet name in the formula

   The formula references the sheet named Expenses 2024 with quotes because of the space.

2. Step 2: Understand the range and function

   It sums cells from C2 to C5 in that specific sheet.

3. Final Answer:

   Sum of cells C2 to C5 in the sheet named 'Expenses 2024' -> Option D

4. Quick Check:

   SUM('Sheet Name'!Range) sums that sheet's range [OK]

Hint: Quotes mean exact sheet name with spaces [OK]

Common Mistakes:

• Assuming sum is from current sheet
• Ignoring quotes and misreading sheet name
• Thinking it sums multiple sheets

4. You wrote the formula =Sales!B2 in Sheet2, but it shows #REF!. What is the likely cause?

medium

A. You used a wrong cell reference format

B. Cell B2 is empty in the Sales sheet

C. The sheet named 'Sales' does not exist

D. You forgot to add quotes around Sales

Solution

1. Step 1: Understand what #REF! means

   #REF! error usually means the reference is invalid or missing.

2. Step 2: Check if the referenced sheet exists

   If the sheet named 'Sales' does not exist, the formula cannot find it, causing #REF!.

3. Final Answer:

   The sheet named 'Sales' does not exist -> Option C

4. Quick Check:

   #REF! = Missing or invalid reference [OK]

Hint: Check if referenced sheet exists to fix #REF! [OK]

Common Mistakes:

• Assuming empty cell causes #REF!
• Adding quotes unnecessarily when no spaces
• Mistaking #REF! for syntax error

5. You want to create a summary sheet that shows the total sales from multiple sheets named Jan, Feb, and Mar. Which formula correctly sums cell D10 from all three sheets?

hard

A. =SUM(Jan:Mar!D10)

B. =Jan!D10 + Feb!D10 + Mar!D10

C. =SUM('Jan','Feb','Mar'!D10)

D. =SUM(Jan!D10:Mar!D10)

Solution

1. Step 1: Understand cross-sheet range syntax

   Google Sheets supports 3D ranges like Jan:Mar!D10 only if the sheets are consecutive tabs named Jan, Feb, Mar in order.

2. Step 2: Sum individual references explicitly

   Adding each sheet's cell individually like =Jan!D10 + Feb!D10 + Mar!D10 works reliably regardless of tab order.

3. Final Answer:

   =Jan!D10 + Feb!D10 + Mar!D10 -> Option B

4. Quick Check:

   Sum separate sheet cells by adding each reference [OK]

Hint: Add each sheet's cell separately to sum across sheets [OK]

Common Mistakes:

• Using invalid 3D range syntax
• Trying to sum sheets with commas inside SUM
• Assuming colon works for non-adjacent sheets