Creating and Using Named Ranges

A named range lets you give a friendly label to a cell or block of cells. Instead of remembering that your sales figures live in B2:B13, you can simply call that area Sales.

Once a range has a name, you can use that name anywhere you would normally type the address. Your formulas read like plain English, and they stop breaking when you move the data around.

• =SUM(B2:B13) becomes =SUM(Sales)
• Names work in both Excel and Google Sheets

Named ranges solve three everyday problems:

• Readability — =Revenue-Costs is instantly clearer than =C2-C3.
• Reliability — the name follows the data, so inserting rows above will not point the formula at the wrong cells.
• Reuse — type one name in many formulas instead of re-typing a long address.

For a tax rate stored in one cell, a name like TaxRate makes every formula self-documenting.

The fastest way in Excel uses the Name Box — the little box to the left of the formula bar that normally shows the active cell address.

• Select the cells you want to name, for example B2:B13.
• Click inside the Name Box.
• Type a name like Sales and press Enter.

That is it. The range is now named and ready to use in any formula across the workbook.

Google Sheets uses a menu instead of the Name Box.

• Select your cells, such as B2:B13.
• Open the Data menu and choose Named ranges.
• Type a name in the side panel and click Done.

The panel also lists every name you have created, so you can edit or delete them later. The result is identical to Excel: you can now write =SUM(Sales).

Both Excel and Sheets enforce a few naming rules. Keep these in mind to avoid errors:

• Start with a letter or underscore, never a number.
• No spaces — use Unit_Price or UnitPrice instead of Unit Price.
• A name cannot look like a cell address, so Q1 is rejected but Quarter1 works.
• Names are not case sensitive: sales and Sales point to the same range.

Imagine a sheet where column B holds monthly sales and you have named B2:B13 as Sales.

Now totals and averages become effortless and easy to read:

• Total sales: =SUM(Sales)
• Average month: =AVERAGE(Sales)
• Best month: =MAX(Sales)

Anyone opening the file understands these formulas at a glance, even without seeing the underlying addresses.

A named range usually has workbook scope, meaning it works from any tab. If your data sits on a sheet called Data, you can write a formula on a Summary tab without typing the sheet name.

Compare the two approaches:

• Without a name: =SUM(Data!B2:B13)
• With a name: =SUM(Sales)

The name hides the sheet reference, so your summary formulas stay short and clean.

Named ranges shine when formulas grow longer. Suppose Revenue and Costs each point to a range of values. A profit margin formula reads like a sentence:

=(SUM(Revenue)-SUM(Costs))/SUM(Revenue)

You could also pair names with conditional functions, such as totaling sales for a region:

• =SUMIF(Region,"East",Sales)

Here Region and Sales are both named ranges of equal length.

Names are not permanent. In Excel, open the Name Manager from the Formulas tab to rename a range, change which cells it covers, or delete it entirely.

In Google Sheets, the same controls live in the Named ranges side panel under the Data menu.

• Changing the cells updates every formula that uses the name.
• Deleting a name turns its formulas into #NAME? errors, so update them first.

If you see #NAME? in a cell, the spreadsheet does not recognize a name you typed. Common causes include:

• A typo — you wrote Sale but the range is Sales.
• The name was deleted or never created.
• A space crept in, like =SUM( Sales ) spelled wrong.

Open the Name Manager to check the exact spelling, then fix the formula. The error clears as soon as the name resolves.

A few habits keep named ranges helpful instead of confusing:

• Use descriptive names like QuarterlySales, not Range1.
• Keep a naming style consistent, such as PascalCase everywhere.
• Name ranges you reuse often, not every single cell.
• Document your names in the Name Manager so teammates understand them.

Well-chosen names turn a cryptic workbook into one that almost explains itself.

You named the range B2:B13 as Sales. Which formula correctly totals it?

You learned that a named range gives a label to one or more cells so formulas read clearly and survive layout changes.

• Create names with the Name Box (Excel) or Data > Named ranges (Sheets).
• Follow the rules: start with a letter, no spaces, not a cell-like name.
• Use names anywhere you would type an address, even across sheets.
• Manage names in the Name Manager; watch for #NAME? typos.

Next you will name fixed values and reusable formulas.