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.