education, Excel

Excel Named Range

Excel Named Range

Defining names in Excel involves giving a range of cells a unique name, which means that you can easily refer to a specific defined range, e.g. while creating a function. This is especially applicable in the following functions:
as the first argument
MATCH as the second argument
VLOOKUP and HLOOKUP as the second argument.
Formulas containing names instead of ranges are more understandable and easier to use, provided that the names we give will be legible. These names are also used in VBA programming.
In the Formulas card you will find the Defined names section:

How to define name for the range?

• Select the relevant range.
• In the Name Box, enter the unique name of the selected range. Do not use any illegal special characters, e.g. spaces, $, @, #,! or arithmetic operators (+, -, /, *, ^), comparison (<> =) and the text operator (&).
• Accept by pressing the Enter key.

Instead of the Name box you can click Formulas card then Define Name, set up: the name, scope, comment and refers to. In addition, the defined name cannot only refer to a cell or range of cells, but it also contains a formula or a specific value or text.

Create multiple names from the selection

• Select a range of data which contains multiple columns or rows with the labels at the beginning or the end.
• Click Formulas card then Create form Selection or shortcuts[Ctr] + [Shift] + [F3].
• Select options based on what you want to name.

Name Manager

• Click Formulas card then Name Manager [Ctrl] + [F3].
• Create another new name, edit existing name, delete it, filter it and sort it

Insert the defined name into the formula

Simple click [F3] to obtain a list of created names and select the relevant one or start typing existing name and select the relevant one.

You can learn similar tips and tricks in Excel at a comprehensive Training in London.