How to Define a name for a value in Excel Example

Names in Excel are convenient for referring to the content of cell ranges that are frequently used in formulas. When these ranges change, instead of reformatting all the formulas, you can simply update the value assigned to the name. Let's look at a specific example.



Assigning a Name in Excel - An Example

Suppose we're managing a store and we want to keep track of all the prices for our products, both purchase and sale prices, to calculate our profit. Let's simplify this example by applying a uniform markup of 10% to all items.

Product Prices

As you can see, if the markup changes, we would need to update all the formulas. Of course, you could move the markup value to a separate cell and use absolute references in the formulas. However, using a name is much more convenient, especially when each group of products has its own markup. Such references in formula arguments are easy to read.

Let's assign a name to the markup, starting with a 10% rate:

  1. Go to the "Formulas" tab and select "Defined Names" and then "Define Name".
  2. Enter the name "Markup1," and in the "Refers to" field, simply enter the value 10%, as shown in the image. You can specify the scope of the name (a specific sheet or the entire workbook) as needed. Then click OK.
    3. Define Name
  3. Now we need to apply our name "Markup1" in the formulas.
Markup1 in Formula

When creating names, you should follow these simple rules:

  1. The name must start with a letter or an underscore. It cannot start with a number or a special character.
  2. From the second character onward, you can use letters, numbers, periods, and underscores. Other symbols are not allowed, not even spaces.
  3. Spaces are not allowed.

Now we can easily change the markup, and the formulas will automatically recalculate. For example, let's increase our markup by 2%:

  1. Go to the "Formulas" tab and select "Name Manager."
  2. In the dialog, select "Markup1" and click "Edit." In the "Refers to" field, enter the new value, 12% (or 0.12), and click OK. After pressing Enter, all formulas with the name "Markup1" will recalculate automatically.
Name Manager

download file Download

Note: If formulas are not recalculated, your Excel settings may be set to manually recalculate formulas ("Formulas" > "Calculation Options" > "Manual"). In such cases, you can get the updated prices by pressing F9.

Advantages of Using Names in Excel

When you launch Excel, cell ranges, sheets, rows, columns, and many other elements are immediately available. Names are the exception, as you need to access them using specific tools. Most users shy away from using names and opt for more complicated solutions. It's a shame because names can elegantly solve many Excel tasks.

Names are most frequently used to identify cell ranges and less often to reserve constants and functions. Rationally used names significantly enhance formula comprehension, especially when you haven't worked with a particular document for some time.