Introduction to cell and range names in Excel

Contents

An easy way to highlight a named range in Excel
How to insert a cell or range name into a formula

Creating and applying formulas in Excel is much easier when they use names instead of cell addresses and ranges. The name acts as an identifier for some element of the workbook. The name can be assigned to cells, ranges, tables, charts, shapes, etc. We will consider only the names assigned to cells, ranges and constants in relation to Excel formulas.

Let’s take a small example. Let’s imagine that we sell high-end cosmetics and receive a fixed percentage of commissions on sales. The figure below shows a table that contains sales volume by month, and cell D2 stores the commission percentage. Our task is to calculate how much we have earned over the past year.

In order to calculate our earnings, it is necessary to sum up the sales volumes for the entire year, and then multiply the result by the commission. Our formula will look like this:

Such a formula will calculate the correct result, but the arguments used in it are not entirely obvious. To make the formula more understandable, you need to assign descriptive names to the areas that contain data. For example, let’s name the range B2:B13 Sales_by_month, and cell B4 name Commission. Now our formula can be written as follows:

As you can see, the new form of writing the formula has become more obvious and easier to understand. You can go even further and create a named constant for the commission value. In this case, there will be no need to select a separate cell for it on the Excel worksheet.

An easy way to highlight a named range in Excel

By assigning names to cells and ranges in Excel, we get another very useful advantage – the ability to quickly select these areas. For example, to select an area that has a name, just click on the field First name and select the required one from the drop-down list.

The range will be highlighted:

How to insert a cell or range name into a formula

There are several ways to insert a name into an Excel formula, you can use any of the following:

Select the named range with the mouse and the name will be inserted into the formula automatically.
Start typing the name of the name manually, and it will appear in the auto-complete list of formulas.
Insert a name from the dropdown list Use in formula, which is on the tab formula.