Create One Named Range from Multiple Cell Ranges in Excel

Yes. In Excel for Microsoft 365 on Windows, you can create one defined name that refers to several nonadjacent cell ranges: select the areas and name the selection, or use Formulas > Define Name and enter a comma-separated union in the Refers to box. The name then represents all those areas together, although it does not turn them into one continuous block.

What a named range can represent

A named range is a readable name for a cell reference or formula. For example, instead of referring to separate blocks by their addresses each time, you can create a name such as QuarterInputs that points to both blocks. Excel calls the collection of separated areas a multi-area reference. Each individual block is an area, and the defined name refers to their union.

This is useful when the same nonadjacent cells are used repeatedly, such as input cells in different parts of a worksheet or totals from several sections. A single name can make formulas easier to read and can reduce the risk of accidentally omitting one of the intended areas.

The name does not copy, move, or join the cells. The original ranges remain where they are, with any blank rows, columns, or unrelated data between them left untouched. Think of the name as a label for a set of references, not as a new range of physically adjacent cells.

Create a name from selected areas

For a quick setup in Excel for Microsoft 365 on Windows, select the ranges together and assign a name. To select separated areas, drag across the first block, then hold Ctrl while selecting each additional block. Keep Ctrl held down for each additional selection.

Select the first cell block you want to include.
Hold Ctrl and select each other block. Check that every intended area is highlighted.
Click the Name Box at the left end of the formula bar. It normally displays the active cell address, such as A1.
Type a valid name, such as QuarterInputs, and press Enter.

Use the Name Box rather than typing into a worksheet cell: typing there assigns a name to the current selection. Names cannot contain spaces, and a name must not look like a cell address. An underscore is a practical substitute for a space, as in Quarter_Inputs. After creating the name, open Formulas > Name Manager and check the Refers to entry to confirm that it includes every intended area.

On a Mac, use Command while selecting additional nonadjacent areas rather than Ctrl. The ribbon command for defining a name is also available under Formulas. In Excel for the web, menu availability and multi-area selection behavior can differ from the desktop app; if the Name Box method is unavailable or does not preserve all areas, use the desktop version to define and verify the name.

Define the areas in Name Manager

For precise control, particularly when the ranges are on different worksheets, define the name directly. In Excel for Microsoft 365 on Windows, select Formulas > Define Name. In the New Name dialog, enter a name, choose its scope, and set the Refers to box to the complete reference. You can also open Formulas > Name Manager and choose New.

For example, suppose the sheet is named Sales Data and the intended blocks are B2:B4 and D2:D4. The Refers to entry is:

='Sales Data'!$B$2:$B$4,'Sales Data'!$D$2:$D$4

The comma is Excel’s union operator in a reference: it combines the two areas as one multi-area reference. The apostrophes around Sales Data are needed because the sheet name contains a space. Dollar signs make the cell addresses absolute, so the references do not shift if the name is used in a formula copied elsewhere. In the dialog, check that the reference begins with an equals sign, then choose OK.

If the sheet name has no spaces, Excel may display it without apostrophes. You can use the worksheet selector and reference-building controls in the dialog to avoid typing a sheet name incorrectly. When the ranges are on different worksheets, include the correct sheet name for each area, separated by the union operator.

Choose the right name and scope

Choose a short name that describes the purpose of the cells, not just their current position. QuarterInputs is more informative than Range1, especially when the workbook is shared. Names are not case-sensitive, cannot contain spaces, and should not be confused with cell references such as A1. Underscores can separate words. Avoid using a name already assigned elsewhere in the workbook.

Scope determines where a name can be used. A workbook-scoped name is available throughout the workbook and is usually the most useful choice for a set of ranges intended for formulas on multiple sheets. A worksheet-scoped name is limited to the sheet chosen in the dialog. Excel allows a worksheet-scoped name to have the same text as a workbook-scoped name, which can be confusing; check the Scope column in Name Manager if a formula resolves to an unexpected reference.

Before defining the name, decide exactly which cells belong in it. Include data cells rather than headings if the name will be used for calculations. If the areas are meant to grow as new entries are added, consider whether a formatted Excel table would be a better fit: tables expand as data is added and expose named columns. A fixed multi-area name will not automatically include cells added outside its current references.

Use a multi-area name in formulas

Many formulas can use a defined name in place of a cell address. With the Sales Data example above, and numeric values 10, 20, and 30 in B2:B4 and 5, 15, and 25 in D2:D4, use:

=SUM(QuarterInputs)

This returns 105: the first area totals 60 and the second totals 45. The example assumes that QuarterInputs has been defined exactly as shown and that all six cells contain those numbers. Because SUM accepts multiple references, it can add values from both areas without requiring an extra cell for each subtotal.

That behavior does not mean every formula treats a multi-area name as a single ordinary rectangle. A function may expect one contiguous array or a single range and may return an error, use only one area, or behave differently depending on the operation. Test the actual formula with a small, known example. If a function needs one continuous list, it may be clearer to create a helper range that gathers the values or to use a formula designed to handle the intended structure.

When one name is useful—and when it is not

A multi-area name works well when the areas have the same role and should be referenced together. Examples include a few separated input blocks that feed a total, repeated sections on one worksheet, or corresponding cells used by a calculation. Naming the set also makes it easier to update the reference in one place: edit the name in Name Manager rather than revising every formula that uses it.

It is less suitable when the blocks represent different kinds of information, need different calculations, or are likely to change independently. A single name can hide those distinctions from someone reading the workbook. Separate names such as NorthRegionSales and SouthRegionSales may be more understandable than one general name if each region is analyzed separately.

Also distinguish a multi-area reference from a contiguous range. If you name A2:A4 and C2:C4 together, the name does not include B2:B4. It refers only to the two selected areas. This matters if you later use a function that counts cells, returns an array, or expects adjacent data: gaps and area boundaries can affect what the function accepts and returns.

Check, edit, or remove the name

Open Formulas > Name Manager to inspect the name, its scope, and its Refers to reference. Selecting the name makes it easier to review the full reference; the dialog also offers controls to edit or delete it. You can select a defined name from the Name Box to navigate to its referenced cells, although a multi-area name will select separated areas rather than one rectangle.

If you move or delete cells, Excel may adjust a name’s reference, but do not assume that every change preserves the meaning you intended. Review the Refers to entry after reorganizing worksheets or replacing source data. Deleting a defined name does not delete the cells themselves, but formulas that rely on that name can stop working. Before removing one from a shared or complex workbook, search for formulas that use it and consider the effects.

Common problems and fixes

The name is rejected: Remove spaces and punctuation that are not allowed, and make sure it does not resemble a cell reference. Try an underscore between words.
Only one area appears: Open Name Manager and inspect Refers to. Add the missing area with the union operator, or reselect the ranges and verify the result. A missed Ctrl-click is a common cause.
A formula returns an error: Confirm the name’s spelling and scope, then check whether the formula supports a multi-area reference. Try SUM as a simple diagnostic if the areas contain numbers.
The wrong worksheet is referenced: Check the sheet names in Refers to, especially where names are similar or contain spaces. Use Name Manager to correct the reference.
A new row is not included: The defined name may point to fixed addresses. Edit its reference or use a table if the data is expected to expand regularly.

Excel uses commas both as function argument separators in many installations and as the union operator between reference areas. In the Refers to box, the comma between two complete references indicates the union. If a workbook is used with regional settings that change formula separators, use Excel’s own reference-selection tools and verify the saved entry in Name Manager rather than assuming that a typed separator will be interpreted as intended.

Frequently Asked Questions

Can one named range include cells from different worksheets?
Yes. Define the name in Name Manager and include a sheet-qualified reference for each area. Review the Refers to entry carefully so each block points to the intended worksheet.

Does a multi-area name fill the cells between the ranges?
No. It refers only to the selected blocks. If you name A2:A4 and C2:C4 together, the cells in column B are not included.

Can I use a multi-area name in Data Validation?
A comma-separated multi-area reference is not the same as a simple contiguous list source, and Data Validation may not accept it directly. If the dropdown needs one list, place the choices in a single helper range and use that range as the list source.

How do I change which cells the name refers to?
Choose Formulas > Name Manager, select the name, choose Edit, and update Refers to. Confirm the scope and reference before closing the dialog.

Can a named range include whole columns or changing data?
A name can refer to column references, but a fixed address does not necessarily expand to cover new data placed outside it. For expanding records, an Excel table with named columns is often easier to maintain.

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.