Are you trying to use defined names to create a formula in Excel? Formulas can be vast and complex sometimes, and so to reduce the complexity and length of the formula **Define Name** feature can be used. This article will illustrate with examples** how to create a formula using defined names in Excel**.

Here, we will use the following dataset of a store where stationery items, their quantity, and unit price are given. Then, we will calculate sales for each item, total sales, and several of the total items using the** Define Name feature to create formulas.**

## 📁 Download Excel File

Download this Excel file that we used to create this article so that you can practice.

## Learn to Create a Formula Using Defined Names in Excel with 5 Possible Examples

The five unique approaches that can be used to define names in **Excel** to create formulas are discussed below.

**Example**

### 1. Multiplication Using Named Ranges

As mentioned before, **Defined Names **can be used to create formulas; we can use them in a formula where multiplication is needed. Here, we will be calculating the **Sales **of each item.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, click the
**Formulas**tab from the ribbon, and select the Define Name option from the**Defined names**bar, select the**Define Name**. It will open a window named**Edit Name**.

- Then, type the
**Name**in the specified box, select the Refers to, select the range of cells you want to use in the formula, and press**OK**.**Define Name**will create. Here, we named Unit Price as**Unit_Price**in the**Name box**. Again, don’t forget the underscore! Then for range, we selected cells**D6:D11**.

- Again, create another name, following the same steps. Just insert a new name in the name box; here, we’ve used
**Quantity**as the name, which refers to cell**C6:C11**.

- Select the cell where you want to show your result and enter the formula. Here, we have selected the cell
**E6**, and as we intend to calculate the sales of each item, we multiply**Unit_Price**and**Quantity**.

The formula we have used is shown here.

** =Unit_Price*Quantity**

- Press
**Enter,**and you can see answers in every cell.

**Example**

### 2. Adding Values Using SUM Function

**The SUM function** is one of the most used functions in **Excel**. We can use Defined Names to do SUM more easily for cells combined with the SUM function. The steps are explored below.

Here we want to evaluate the **Total Sales** for that shop.

⬇️⬇️ **STEPS **⬇️⬇️

- First, click
**Formulas**from the**Define Names**ribbon; you can find the**Define Name**option; click that.

- A window will pop out, where you have to insert the name and specify the reference in
**Refers to**the box where the sum will take place. So, we entered the name**Sales**and chose the reference cell**E6:E11**, where all sales data are stored. Finally, press**OK,**and you have created a**Defined Name**.

- Then select a cell where you want to show your result and enter the formula using the
**SUM**. In this case, the specified cell is**H5**, and the formula**SUM(Sales)**is used to obtain the Total Sales.

You can take the formula below.

**=SUM(Sales)**

- Finally, press
**Enter**, and you will see the summation.

**Example**

### 3. Applying COUNTA Function

You may have used **the COUNTA function** to count cells. This function can also count cells while combining them with **Defined Names**. Thus, you can track a large amount of data. Here we are going to count the number of items.

⬇️⬇️ **STEPS **⬇️⬇️

- To start, navigate to the
**Formulas**tab and select the**Defined Name**.

- After that, a window will open where you define the name with a suitable name; we used
**Count_Items,**which refers to the cell**B5:B11**but insert the function**COUNTA**with parentheses, close the parentheses and add a**“-1 ”**at the end. - Finally, press
**OK**.

So the formula we have entered in the refers box is

**=COUNTA(‘Applying COUNTA Function’!$B$5:$B$11)-1**

** **

- Afterward, insert the
**Defined Name**in a specific cell where you want the answer to appear. Here, we have entered**Count_Items**in cell**G5**. - You can find the formula that we have used below.

**=Count_Items**

- Finally, press
**Enter**from your keyboard. And now you can see the result.

**Example**

### 4. Utilizing COUNTIF Function

You can use **the COUNTIF function** in Excel to count cells according to criteria. You may wonder how this is used in **Defined Names**. Let’s find out with the following steps. Suppose we want to count the number of items with a price of less than** $5**.

⬇️⬇️ **STEPS **⬇️⬇️

- We have defined the
**Unit_Price**before in example 1. Please check that, or follow the images below.

- Insert any suitable name in the name section and refers just like below.

- Then, enter the following formula with
**COUNTIF**. We set the criteria as**Unit_Price >$5**.

You can copy the formula below.

**=COUNTIF(Unit_Price,”>$5″)**

- Finally, enter the function with the keyboard command
**Enter**and find the result.

**Example**

### 5. Combination of INDEX and MATCH Functions

**The INDEX function **returns a value, and **the MATCH function** search for an item specified. Here we used both to know what was the **Total Sales** for the Item Eraser.

⬇️⬇️ **STEPS **⬇️⬇️

- First, specify the criteria for which you want to count the number of cells, which is
**Eraser**in this case, and the cell we chose for the criteria was cell**G6**.

- Define the names of Items as done in example 3.
- Enter the following formula in the cell where you want to see your result.

You can find the formula below.

**=IF(COUNTIF(Stationery_Items,G5),INDEX(Unit_Price,MATCH(G5,Stationery_Items,0))*INDEX(Quantity,MATCH(G5,Stationery_Items,0)),””)**

- Now press
**Enter**to see the result.

**🔨 Formula Breakdown**

**IF(COUNTIF(Stationery_Items,G5),INDEX(Unit_Price,MATCH(G5,Stationery_Items,0))*INDEX(Quantity,MATCH(G5,Stationery_Items,0)),””)**

**MATCH(G5, Stationery_Items,0)**returns the relative position of data matching the stated value in an array.**G5**is the**look_up**value argument, which refers to the item ‘Eraser.’ Also, the next Stationary_Item represents the**lookup_array**argument from where the value is matched. And as the optional match_type argument 0 is used, which refers to the**Exact match**, It returns the value 4.**INDEX(Unit_Price, MATCH(G5, Stationery_Items,0))**becomes**INDEX(Unit_Price,4)**which gives a value, in a given range, at the intersection of a row and column. The Unit_Price is the “Unit Price” array argument in this formula. Finally,**4**indicates theargument that indicates the row location. This whole part returns the value of $7.*row_num*- So, the entire formula becomes
**IF(1,$7,” ”)**; here, 1 is for**logical_test**. It prompts the**IF**function to return**7*4**(**value_if_true**argument); otherwise, it returns**“ “ “ (Blank)**(**value_if_false**argument).

So, finally, the output becomes** $42**.

## How to Edit Defined Names in Excel

You can also Edit the Defined Names in Excel. What you have to do is follow these steps.

⬇️⬇️ **STEPS **⬇️⬇️

- Just open the
**Formulas**tab and select the**Name Manager**.

- A window will open named
**Name Manager**.**Select**the defined name you want to edit, and select the option**Edit**from the window.

**Edit Name**window will appear, and from the**Name**box, rename the name or change the refers to as you see suitable. Then press**OK**.

- Again, open the
**Name Manager**, and you will find the renamed name there. Here you can see we have named the**Items**to**Stationary_Items**by following these processes.

## How to Delete Defined Names in Excel

You can also delete a defined name as you can create and edit. The steps mentioned below may help.

⬇️⬇️ **STEPS **⬇️⬇️

- Go to the
**Formulas**and select the**Name Manager**option from there.

- The
**Name Manager**window will show up, and select the name you want to delete. We have selected the**Sales**. Then click**Delete.**

- Then, a dialogue box will ask you if you want to delete this. Select
**Ok**.

- As a result, the selected name is deleted, as you see!

## 📝 Takeaway from This Article

📌 You can learn how to create a formula using defined names.

📌 Defined names were used in different formulas using SUM, COUNTIF, and INDEX to give you an idea.

📌 You can both count a column and calculate using the defined names.

📌 We have illustrated the Editing and Deleting process of the **Defined Name**.

## Conclusion

When you are handling a large amount of data, you can apply this Define Name to work smarter and quicker. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our **Excelden** page to enhance your Excel-related knowledge.