How to Create a Named Range in Excel

A named range in Excel is a cell or range of cells that are given a name, and thus can be called upon by formulas using that name rather than having to specify cells each time. Named ranges can be particularly useful when you know that you will be calling upon the same dataset across a number of formulas.

Creating a named range in Excel

In order to create a named range, you need to highlight the relevant dataset.

With the cells still highlighted, click into the box which currently states ‘A1’ and type the desired name for the named range. In this example, I have referenced to the range as ‘dataset’.

Note: if using this method to identify a large dataset, it is good practice to start the range in cell A1.

Now that the named range has been identified, it can be freely used in place of cell references within formulas. For example, the below VLOOKUP formula uses the newly created ‘dataset’ range in place of specific cell references for the table array input.

Note that when used in formulas, the named range is absolute. This means that when the formula is copied, the cells referred to will remain unchanged.

Editing a named range

If you add a row within the middle of the named range, Excel will automatically incorporate that row as part of the named range. However, if you enter another line of data at the bottom of the dataset, Excel will not automatically pick up the fact that an additional row has been added. When this occurs, you will need to access the Name Manager functionality to edit the range. Name Manager can be found in the ‘Formulas’ tab of the ribbon.

Once name manager has opened, find the relevant named range and click on that row.

Once you have identified the relevant named range, click in the formula bar available within name manager and edit the range as required. If the tab is being used solely as a dataset, it may be easier to set a large range to accomodate any future rows which may be inserted (e.g. I will set $E$4000 as the bottom row). Once you have edited, hit the enter key and the named range will be updated.

Comments (No)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.