How to create a named range in Excel on Mac: step by step in Microsoft 365

A named range is a label that points at one cell or a block of cells. Excel keeps the label, then uses those cells whenever a formula asks for it. Compare =SUM(B2:B400) with =SUM(Q1_Sales). The second formula tells you what it adds up. It also still makes sense when you open the workbook months later.

When a named range saves you time

Naming a range is quick. It pays off in three situations that come up often on a Mac.

• One input that many formulas share, such as a tax rate in a single cell.
• A column of data, such as monthly sales in B2:B400, that totals and charts read from.
• A lookup table, such as a price list, that formulas search again and again.

Editing a defined name updates every use of it in the workbook. If Q1_Sales points at the wrong block, you fix the name once in Name Manager. You do not have to edit each formula by hand.

Check the cells before you name them

Open the workbook and go to the sheet that holds the data. Click the first cell of the range, then drag or Shift-click to the last cell. Scroll to check both edges. A name points at exact cells, so a block one row too short quietly gives every formula that uses it the wrong result. Decide on the range before you name it.

Create one named range with Define Name

Select the cells first, then press ⌘F3. Microsoft's keyboard shortcut list gives this key for the Define Name dialog in Excel for Microsoft 365, 2024 and 2021 for Mac. The dialog has four fields. Fill in the Name field, check Refers to, and click OK.

Click OK when the fields look right. Then test the name in a formula before you move on. A name that works in a test cell is far easier to trust later.

Naming rules Excel enforces

Excel enforces a short list of rules. Some block a name when you create it. Others cause trouble later, when the workbook grows. Learn these rules once and most rejections stop.

• The first character must be a letter, the _ character, or a backslash.
• Later characters can be letters, numbers, periods, or the _ character.
• No spaces. Use _ instead, such as Q1_Sales.
• No cell references, such as Z$100 or R1C1.
• The letters C, c, R, and r are not allowed on their own.
• Up to 255 characters.

Scope affects uniqueness. A name must be unique within its scope, but the same name can exist at different scopes. Names are not case-sensitive. If you create Sales and then SALES in the same workbook, Excel asks you to choose a unique name.

Create several named ranges from labels

Create from Selection saves time when your data already has labels. Excel reads each label and makes a name for the cells beside or below it. Select the labels and the data together. Then press ⇧⌘F3, or go to Formulas > Defined Names > Create from Selection.

Here is a worked example. Row 1 holds month headings from January to December. Column A holds product names. Select the whole block from A1 to M10, open Create from Selection, and tell Excel the labels sit in the top row. Excel creates one name for each month, and each name covers the cells under its heading.

Names created this way have workbook scope, so every sheet can use them. Check the new names in Name Manager afterward. A label with a space, a symbol, or a cell address may not be valid as a name. Fix the label on the sheet, then run the command again.

Use a named range in a formula

To use a name, type = and the first letter or two of the name. Excel shows matching names in a list. Pick the name with the arrow keys or the mouse, then press Return. You can also go to Formulas > Use in Formula and choose the name from the list.

Say Q1_Sales covers B2:B400. The formula =SUM(Q1_Sales) returns the same total as =SUM(B2:B400). The name makes the formula easier to read. If the range changes, you update the name in one place, and every formula that uses it follows.

Edit and delete names in Name Manager

Open Formulas > Defined Names > Name Manager. The window lists every name in the workbook, with its scope and what it refers to. Use the filter options to narrow the list. Microsoft lists Names Scoped To Worksheet, Names Scoped To Workbook, Names With Errors, Names Without Errors, Defined Names, and Table Names as options.

To edit a name, select it and change the Refers to box. Editing updates all uses of the name in the workbook. To delete a name, search your formulas first. Removing a name that formulas still use breaks those formulas.

Name Manager does not display names defined in VBA, and it does not display hidden names. If a formula uses a name you cannot find in the list, check the workbook for VBA code before you assume the name is missing.

Quick method: the Name Box

Microsoft's Define and use names article also describes a faster method. Select a cell or range, click the Name Box at the left end of the formula bar, type the name, and press Return. Microsoft writes these steps for Windows and Web. If your Mac layout differs, use ⌘F3 instead.

Mistakes people make with named ranges

• Including the header row when you use Define Name. The header becomes part of the range, so functions that count cells may count it too. Select data only.
• Setting the scope to one sheet, then wondering why another sheet cannot use the name. Check Scope before you click OK.
• Typing a space in the name. Excel rejects it. Use _ instead, such as Q1_Sales.
• Using a cell address as a name, such as B2 or Z100. Excel does not accept it.
• Assuming Name Manager shows every name. VBA names and hidden names do not appear there.
• Naming ranges Data or Range1. Months later, nobody knows what those cells hold.

Mac keyboard details that trip people up

Function keys cause the most trouble on a Mac. By default, function keys control the computer, not Excel. Excel function-key shortcuts therefore need the fn key, such as fn+F9 for Evaluate formula. If ⌘F3 does nothing, try fn+⌘F3. You can also change the function key setting in Keyboard settings.

Excel 365 for Mac aligns more shortcuts with Windows, and older Mac-only shortcuts still work. A shortcut you learned on Windows may work on your Mac, or it may need fn. Microsoft's Mac shortcut list does not include a shortcut for Name Manager, so use the ribbon path for that one.

Excel for Mac versions and Excel for the web

Microsoft's keyboard shortcut list covers Excel for Microsoft 365 for Mac, Excel 2024 for Mac, and Excel 2021 for Mac. The steps on this page follow those versions. Menu names can change between releases. If your ribbon looks different, find the Defined Names group on the Formulas tab first.

Excel for the web works differently. Microsoft's Define and use names article describes creating a name by typing it in the Name Box. It does not describe Create from Selection on the web. If you work in a browser, start with the Name Box.

What to do after you create names

Name the inputs and ranges that your formulas share first. Keep a short list of names on a sheet called Names, with a one-line comment for each. The comment field holds up to 255 characters, so it can record the data source and the date.

If the next step is summarizing the data, read the pivot table guide for Excel on Mac. It walks through building a summary from the same data you just named.