Master range names in Excel – Online tutorial

Range names

Range names are a useful tool to help remember ranges of data, it can be difficult to remember cell references such as A6:A38 whereas remembering “customers” is much easier.  Ranges names are also useful for selecting specific items in a worksheet for printing or presentation changes.

Advantages of using range Names

  • Easily recognisable formula
  • Easy to update named ranges and automatically update formula automatically
  • Reduce the chances of error
  • Automatically expand and contract when cells are added or removed from the range
  • Easier to remember
  • Names ranges can be found quickly using the Go To function

 

How to create a range name

Highlight the cells to be included in the range

Navigate to the box beside the main function box on the toolbar.

Click into the cell and add a name for the range, type the chosen name range, in this example I have selected location1.  Ensure to press enter.

Range names from Heading

You may wish to name the data based on the headings of the data table.  Select the cells in the range that require naming.

Once the data has been selected navigate to the “Formulas” ribbon and click the “Create from Selection”.

range names 5

range names 6

On the pop up box select the “Top Row” option which instructs Excel to use the top row to name the data range. Highlighting the data in column A it can now been seen that the data range has been named as Pizza_Type.

range names 7

Editing Named range

As you work on the worksheet you may discover that it would be beneficial to call the named range something different.  Editing the named range is much easier than you would imagine.

Again navigating to the Defined names area of the Formulas Ribbon and select the “Name Manager”.

range names 8

range names 9

The name manager pop up box displays the named ranges currently active within the worksheet which can all be edited using the “Edit” function.

range names 10

We can then amend the data name as required.

range names 11

As we can see when we now select the data range and the name of the range has been changed.

range names 12

 

Viewing named ranges in the worksheet

A handy function with named ranges to enable you to track named ranges in the worksheet is to zoom out of the worksheet.  When zoomed out below 40% the names of the range names are superimposed on top of the data.

range names 13

Deleting a named ranges

Named ranges can be deleted by again navigating to the Name manager option in the Formula ribbon and selecting delete.

range names 14

Find a named range in a workbook

You can quickly navigate to the different named ranges in the worksheet by selecting the drop down box on the main toolbar.

data ranges 15

An alternative method is to use the Ctrl+G (Go to) option.

range names 16

Naming Constants

Based on the Pizza sales data we are able to add sales tax to the sales data through the use of a named constant.

range names 17

Setting up a named constant and use in formulas

Select cell that will be the named range, in this case B12, change the data range as per the above example: –

range names 18

The named constant can then be used in formula within the worksheet.

range names 20

 

Excel now uses the value in the cell B12 within the formula and can be performed with both simple and complex functions.

range names 21

Summary

Named ranges are an amazing Excel tool and one that you should be using to make life easier and more efficient.  To revisit the main reasons to use range names at the start of the post: –

  • Easily recognisable formula
  • Easy to update named ranges and automatically update formula automatically
  • Reduce the chances of error
  • Automatically expand and contract when cells are added or removed from the range
  • Easier to remember
  • Names ranges can be found quickly using the Go To function

If you are comfortable with range names look how they can be applied for drop down boxes.

Leave a Reply