Cell Range in Google Sheets: Meaning, Examples, and Use

A1:B5 is an example of a cell range in Google Sheets: it means every cell from A1 through B5, including both endpoints. A range can be one cell, a rectangular block, a whole row or column, or cells identified on another sheet; the exact notation tells Sheets which cells to work with.

What a cell range means

A cell range is a reference to one or more cells in a spreadsheet. Google Sheets uses column letters and row numbers to identify cells: A1 is the cell at the intersection of column A and row 1. A colon between two cell addresses specifies the rectangle spanning them. For example, A1:B5 contains two columns and five rows, for ten cells in total.

The first address in a range does not have to be above or to the left of the second. A reference such as B5:A1 still identifies the rectangular area between those corners. In ordinary use, however, writing the top-left address first makes the range easier to read. A single cell address, such as C7, can also be used where a formula accepts a range; a range does not have to contain multiple cells.

Ranges are useful because they let you select or refer to groups of cells together. You might format a block of figures, calculate a total, count entries, or use the same set of cells as the input to a chart. The range reference identifies the cells; it does not itself calculate anything or change their contents.

How to read range notation

In the standard A1 notation used by Google Sheets, letters represent columns and numbers represent rows. A colon means through, including the cells at both ends. So C2:C8 is a vertical range in one column, while B3:F3 is a horizontal range across five columns. A2:D6 is a larger rectangle that includes every cell in columns A through D and rows 2 through 6.

A1 refers to one cell.
A1:A10 refers to ten cells in column A, from row 1 to row 10.
A:A refers to the entire column A.
1:1 refers to the entire first row.
A2:A refers to column A from row 2 downward, which can be useful when new entries will be added.

Whole-row and whole-column references can be convenient when a data set grows, but they also include cells beyond the data you currently need. A bounded reference such as A2:A500 is more specific. When choosing between the two, consider whether the sheet will expand and whether including many blank cells could make a calculation or spreadsheet harder to manage.

Select a range in the browser

To select a range directly in Google Sheets, click the first cell, hold the mouse button, and drag to the last cell. You can also click a starting cell, hold Shift, and click the ending cell. The selected cells are highlighted, and the reference appears in the name box near the formula bar. The name box is also a quick way to jump to a range: type A1:B5 there and press Enter.

To select a whole column or row, click its letter or number header. For a specific range, check that the highlighted selection matches the address you intended before applying formatting or entering data. This matters when a sheet has frozen rows, hidden columns, or a large amount of information, where it is easy to select an unintended area.

Selection and reference are related but not identical ideas. Selecting A1:B5 highlights those cells for an action, while typing A1:B5 into a formula refers to those cells as inputs. Editing a selected range, such as pasting into it, can replace existing contents, so confirm the selection before making changes.

Use ranges in formulas

Many Google Sheets functions accept a range as an argument. For example, a SUM formula adds the numeric values in its referenced cells. Assuming A2:A5 contains 12, 18, 25, and 15, the formula =SUM(A2:A5) returns 70. The colon tells the function to include all four cells, not just A2 and A5.

Assuming the same values are in A2:A5, the formula =AVERAGE(A2:A5) returns 17.5. AVERAGE uses the numeric entries in that range to calculate the arithmetic mean. In this example, the total is 70 and there are four numbers, so the result is 70 divided by 4.

A range can also be used in functions that look up, count, or summarize data. For example, you can use a range as the set of values to chart or format. If a formula returns an unexpected result, check the referenced address as well as the cell contents: a blank cell, a text value where a number was expected, or an accidentally excluded row can affect the outcome.

Refer to a range on another sheet

A range can point to cells on a different tab in the same spreadsheet. The general pattern is the sheet name, an exclamation mark, and the cell or range address. For example, January!A1:B5 refers to A1:B5 on the tab named January. If the tab name contains spaces, put the name in single quotation marks: ‘Quarter 1’!A1:B5.

This notation is helpful for keeping raw data on one tab and summaries on another. A formula on a Summary tab can use a range from a Data tab, for instance, without copying the underlying values. When writing a cross-sheet reference, make sure the tab name matches exactly; a spelling difference can cause an error. Inserting or renaming sheets can also affect references, so review important formulas after reorganizing a workbook.

Sheet names and cell addresses serve different purposes: the sheet name identifies the tab, while A1:B5 identifies the cells within it. A reference to another spreadsheet file is a separate case and may require a function such as IMPORTRANGE rather than ordinary same-file sheet notation.

Relative and fixed range references

Cell references in formulas are usually relative by default. If you copy a formula that refers to A2:A5 one column to the right, Sheets may adjust the reference to B2:B5. This behavior is useful when a formula is being copied across a table and should use the corresponding column in each location.

Dollar signs make a row or column reference absolute. For example, $A$2:$A$5 keeps both the column and row positions fixed when the formula is copied. A mixed reference such as $A2:A$5 fixes only the indicated parts. This distinction is useful when a formula should always use a particular input cell or fixed data column while still adapting in another direction. When a copied formula gives different results than expected, inspect whether its references shifted.

Named ranges and choosing an address

Instead of repeatedly using an address such as B2:B100, you can give a range a descriptive name, such as MonthlySales. In Google Sheets, select the cells and use Data > Named ranges to create or manage a named range. A meaningful name can make a formula easier to understand, especially in a workbook with several tabs or many calculations.

Choose a range based on the task and the shape of your data. A small, fixed table is usually best represented by a precise rectangular address. A column that receives new records may call for an open-ended reference or a deliberately generous bounded range. A report that should exclude its heading row should start below the heading, rather than including the entire column without checking how the function treats text.

Use a precise range when the data boundaries are known and stable.
Use an open-ended or larger range when new entries are regularly added, and confirm that blanks or extra content will not cause issues.
Use a named range when a reference appears often or its purpose is not obvious from the cell addresses alone.
Check the first and last cells to make sure headers, totals, or unrelated data are not accidentally included.

Common range mistakes

A frequent mistake is confusing a colon with a comma. A1:A5 means all cells from A1 through A5. A comma in a function can separate arguments instead; it does not describe a continuous block in the same way. A second common mistake is leaving out the row or column that contains the last item, especially after adding new data.

Another source of confusion is assuming a range contains only visible or nonblank cells. A1:A20 includes every cell in that interval, including blanks. Similarly, selecting a range does not automatically make a formula apply to it; the formula must refer to the intended address. If a total seems too high or low, review the range boundaries and check for duplicated values, headings, or unrelated numbers within the selection.

Finally, remember that separate blocks are not one continuous rectangle. If you need to add values from A1:A5 and C1:C5, you can provide both ranges as separate arguments to a function that accepts them. Do not write A1:C5 if you intend to exclude column B: that notation includes it.

How Google Sheets ranges compare with Excel

Basic A1 range notation is familiar in both Google Sheets and Microsoft Excel: A1:B5 describes a rectangular group, and A:A describes a column. Many common formulas also use the same syntax. The surrounding features and limits are not identical, however, and some functions or collaboration behaviors differ between the programs. If a spreadsheet will be opened in both, check formulas and named ranges in the destination application rather than assuming every feature transfers exactly.

These instructions describe Google Sheets in a web browser. Excel has its own interface and menus for selecting, formatting, and managing references. For the simple task of identifying a range, though, the key idea remains the same: the cell addresses define which spreadsheet cells are included.

Frequently Asked Questions

What is an example of a cell range in Google Sheets?
A1:B5 is a range containing the cells in columns A and B and rows 1 through 5. It includes ten cells.

Is A1 a cell range?
Yes. A1 refers to one cell, and a single cell can be used as a range reference in formulas and other spreadsheet features.

What does the colon mean in A1:B5?
The colon means the reference includes the cells from the first address through the second, including the cells between them.

How do I select a range quickly?
Enter its address, such as A1:B5, in the name box beside the formula bar and press Enter. You can also drag across the cells or Shift-click the ending cell.

How do I include an entire column?
Use the column letter twice with a colon, as in A:A. To start below a heading and continue down the column, use a reference such as A2:A.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.