Google Sheets: Reference an Entire Sheet in Formulas

Some people recommend using Apps Script to reference an entire sheet in formulas in Google Sheets, but that’s not necessary.

Before we begin, here’s an important note:
When you reference an entire sheet in a formula, the formula must be placed in a different sheet (tab) within the same file. Otherwise, you’ll encounter a circular reference error.

Table of Contents

What Does It Mean to Reference an Entire Sheet in Google Sheets?

To reference an entire sheet in Google Sheets, you’re typically referring to a range with open-ended rows and columns. This approach allows the formula to automatically include new rows or columns without requiring you to manually update the range.

Google Sheets allows either open-ended columns or open-ended rows by default—but not both simultaneously.

For example:

A:A (or A1:A), A:B, etc. → open-ended rows in specific columns
1:1 (or A1:1), 1:2, etc. → open-ended columns in specific rows

Reference an Entire Sheet – A Partially Dynamic Approach

A simple way to reference an entire sheet is by using open-ended columns with a large fixed row number. For instance:

'Sheet1'!A1:50000

This reference, when used in formulas in Sheet2, pulls a large block of data from Sheet1—from cell A1 down to row 50,000—across all available columns.

Key points:

The column range is open-ended, so it includes all columns starting from column A.
The row limit (50000) is generous enough to cover future data expansion in most cases.
No, this won’t significantly affect performance—Google Sheets only processes cells that actually contain data.

However, this method is not fully dynamic. If your sheet grows beyond 50,000 rows, you’ll need to manually update the range to include additional rows.

A Dynamic Way to Reference an Entire Sheet in Formulas in Google Sheets

To make the reference fully dynamic, you can replace the fixed row number with a formula that automatically detects the number of rows in the referenced sheet.

Here’s how:

=INDIRECT("Sheet1!A1:" & ROWS('Sheet1'!A1:A))

This formula dynamically builds the range by counting the rows in 'Sheet1'!A1:A.

Using INDIRECT is essential here—it converts the text string into an actual range reference that the formula can evaluate.

How to Use the Entire Sheet as a Reference in Common Google Sheets Functions

Let’s explore a few practical examples of how to reference an entire sheet dynamically in formulas in Google Sheets, using functions like SORT, COUNTIF, FILTER, QUERY, and INDEX.

1. Reference an Entire Sheet in SORT

Sort the entire sheet from another tab dynamically:

=SORT(INDIRECT("Sheet1!A1:" & ROWS('Sheet1'!A1:A)), 1, TRUE)

If the first row contains headers and you want to start from the second row:

=SORT(INDIRECT("Sheet1!A2:" & ROWS('Sheet1'!A2:A)), 1, TRUE)

2. Use COUNTIF to Reference an Entire Sheet

To count how many times the word "apple" appears in all cells of Sheet1:

=COUNTIF(INDIRECT("Sheet1!A1:" & ROWS('Sheet1'!A1:A)), "apple")

3. Use FILTER with an Entire Sheet as a Reference

To filter all rows in Sheet1 where column A contains "apple":

=FILTER(INDIRECT("Sheet1!A1:" & ROWS('Sheet1'!A1:A)), 'Sheet1'!A1:A = "apple")

This dynamically includes all rows and filters by the first column’s content.

4. Use QUERY to Reference an Entire Sheet

Achieve the same as above using QUERY:

=QUERY(INDIRECT("Sheet1!A1:" & ROWS('Sheet1'!A1:A)), "SELECT * WHERE A = 'apple'")

This is especially useful for more complex conditions and structured results.

5. Use INDEX with a Dynamic Sheet Reference

To retrieve the value from the second row and third column of Sheet1:

=INDEX(INDIRECT("Sheet1!A1:" & ROWS('Sheet1'!A1:A)), 2, 3)

You can change the row and column numbers (2, 3) as needed.

Final Thoughts

Referencing an entire sheet in formulas in Google Sheets can be useful for dynamic reports, dashboards, and simplified workflows. You can start with a partially dynamic approach using fixed row limits or go fully dynamic using functions like INDIRECT and ROWS to adapt automatically as your data grows.

Additionally, consider using Google Sheets’ structured tables (Insert → Table). Structured references let you select the entire table or specific fields directly—making your formulas cleaner, more readable, and less prone to errors than referencing an entire sheet.

Choose the method that best suits your data structure and performance needs.