How to Create and Use Named Ranges in Google Sheets Formulas

When building complex formulas in Google Sheets, you frequently reference blocks of data using standard cell coordinates, such as =SUM(B2:B150). While functional, this syntax is prone to human error and makes large spreadsheets difficult to read. If you return to a spreadsheet six months later, it takes significant mental effort to remember exactly what data resides in “B2:B150”. Google Sheets solves this problem with a feature called “Named Ranges,” which allows you to assign a clear, human-readable word (like “Q1_Revenue”) to a specific block of cells. You can then use that word directly in your formulas, resulting in equations like =SUM(Q1_Revenue).

Rules for Naming Ranges

Before creating a named range, you must understand Google’s strict syntax rules for acceptable names:

• Names can only contain letters, numbers, and underscores. You cannot use spaces or punctuation marks (like hyphens or periods).
• Names must begin with a letter, not a number. “Revenue2024” is acceptable; “2024Revenue” is not.
• Names cannot be the same as existing cell references. You cannot name a range “A1” or “R1C1”.
• Names must be between 1 and 250 characters long.

How to Create a Named Range

Creating the range is a simple process that permanently maps your chosen word to a specific grid location.

1. Open your Google Sheet.
2. Use your mouse to highlight the entire block of cells you want to name (for example, the column containing your sales figures from B2 down to B150).
3. Click on the Data menu in the top toolbar.
4. Select Named ranges from the dropdown list. A sidebar will open on the right side of the screen.
5. In the sidebar, click + Add a range.
6. A text box will appear at the top. Type your desired name (e.g., Sales_Data).
7. The cell range box below it should already be populated with the cells you highlighted (e.g., Sheet1!B2:B150). If it is incorrect, you can click the small grid icon to select the correct range.
8. Click the blue Done button.

Using the Named Range in a Formula

Once the range is defined, you can immediately begin using it in any formula, anywhere in the workbook.

1. Click on an empty cell where you want to calculate a total.
2. Type your formula, but replace the standard coordinates with your new name. For example, type: =SUM(Sales_Data)
3. Press Enter. Google Sheets will automatically highlight the referenced block of cells in a matching colour to confirm it understands the reference, and it will calculate the sum perfectly.

This works for all functions, including complex lookups like =VLOOKUP("Product A", Sales_Data, 2, FALSE), making your logic vastly easier to audit.

Editing or Deleting an Existing Named Range

If you add more data to the bottom of your column, you must expand the named range so your formulas include the new numbers.

1. Click Data > Named ranges to open the sidebar.
2. You will see a list of all defined ranges in the workbook. Click the pencil icon (Edit) next to the range you need to update.
3. Change the cell coordinates in the text box (for example, change Sheet1!B2:B150 to Sheet1!B2:B300).
4. Click Done. Every formula in your spreadsheet that uses that named word will instantly recalculate using the new expanded data block.

To completely remove a named range, click the pencil icon and then click the trash can icon next to the name. Note that deleting a range will break any existing formulas that relied on it, returning a #NAME? error.