Unquestionably, formulas are the lifeblood of an **Excel** spreadsheet. However, formulas can become complex and difficult to understand. In this regard, Excel’s **Defined Names** feature becomes a very handy tool to simplify large formulas. Keeping this in mind, this article shows 7 ways of **how to create a formula using “Defined Names” in Excel**. Moreover, we’ll discuss how to edit and delete **Defined Names **in Excel.

**Table of Contents**hide

## Download Practice Workbook

## 7 Ways to Create a Formula Using Defined Names in Excel

First and foremost, let’s consider the **Fruit Sales** dataset shown in the **B4:D13** cells, which contains the **Item**, **Quantity**, and **Unit Price **respectively. Here, we want to **create a formula** using Excel’s **Defined Names** feature, so without further delay, let’s see each example individually and in detail.

Here, we have used the *Microsoft Excel 365* version, you may use any other version according to your convenience.

__Example 1__: Multiplying Named Ranges

First of all, let’s apply the **Defined Names** to create a simple formula. Simply, we want to multiply the “*Unit Price”* and “*Quantity”* columns to compute the “*Sales”* in *USD*. So, let’s begin.

📌 ** Steps**:

Now, this opens the **Edit Name** window.

- Next, type in the
**Name**, here it is*“Unit_Price”*>> in the**Refers to**field, select the ranges of cells, in this case, the**C5:C13**cells >> click**OK**to create a**Define Name**.

- In a similar style, create a
**Defined Name**for the “*Quantity”*column by selecting the**D5:D13**range of cells.

- Lastly, multiply the “
*Unit Price”*and “*Quantity”*to calculate the*Sales*in*USD*.

`=Unit_Price*Quantity`

Here, the “*Unit_Price”* and the “*Quantity”* refer to the **Defined Names** which represent the **C5:C13** and **D5:D13** cells.

*📃** Note: In the *

*Microsoft Excel 365**version, you can run the*

*Array Formula**by pressing the*

**ENTER**key. But, in the older versions of Excel, you must press**CTRL + SHIFT + ENTER**to use the array formula.**Read More:** **How to Create Array Formula in Excel (10 Suitable Examples)**

__Example 2__: Using the SUM Function

Alternatively, we can use **Defined Names** in Excel functions, hence we do not need to enter the cell references which simplifies formulas and makes them easier to interpret. In the following examples, we’ll explore all the nitty-gritty of how to create a formula using **Defined Names **in Excel, therefore, let’s see them in action.

📌 ** Steps**:

- At the very beginning, move to the
**E5**cell >> enter the formula given below.

`=C5*D5`

In this case, the **C5** and **D5 **cells represent the “*Unit Price”* and “*Quantity”*.

- Not long after, navigate to the
**Formulas**tab >> choose the**Define Name**option.

- Afterward, define a name (“
*Sales”*) for the**E5:E13**cells as shown in the image below.

- Finally, use the
**SUM function**to obtain the*“Total Sales”*which is*“$190.60”*.

**Read More:**** How to Create a Complex Formula in Excel (with Easy Steps)**

__Example 3__: Utilizing COUNTA Function

For one thing, you can utilize the **Defined Names **to name simple formulas in Excel, for instance, a formula to count the non-blank cells in a column. Here, we’ll use the **COUNTA function** which counts the non-empty cells in a range.

📌 ** Steps**:

- In the first place, follow the steps shown in the
**prior method**>> give a suitable name, like*“Count_Items”*>> insert the formula given below.

`=COUNTA('COUNTA Function'!$B4:$B13)-1`

On this occasion, the **‘COUNTA Function’!$B4:$B13)** is the ** value **argument that represents the

**B4:B13**cells in the

*“COUNTA Function”*worksheet.

- In turn, proceed to the
**C15**cell >> enter the**Defined Name***“Count_Items”*and press**ENTER**to return the count of the items.

📃 *Note: **You can bring up the list of Defined Names by pressing the CTRL + F3 keys on your keyboard.*

**Read More:** **How to Create a Custom Formula in Excel (A Step-by-Step Guideline)**

__Example 4__: Employing COUNTIF Function

Conversely, we can use **Defined Names** with the **COUNTIF function** to count only those items that match the given criterion. In this case, we want to count the number of items with *“Unit Price”* exceeding *“$1.5”*.

📌 ** Steps**:

- Initially, go to the
**D15**cell >> insert the following expression.

`=COUNTIF(Unit_Price,">1.5")`

Specifically, the *“Unit_Price”* points to the **Defined Name** which indicates the **C5:C13** cells.

**Formula Breakdown:**

**COUNTIF(Unit_Price,”>1.5″) →**counts the number of cells within a range that meet the given condition. Here, the**Unit_Price**cells represent theargument that refers to the*range***C5:C13**cells, while the**“>1.5”**cell indicates theargument that returns the count of the values greater than*criteria**“$1.50”*.**Output → 4**

**Read More:** **How to Create a Formula in Excel without Using a Function (6 Approaches)**

**Similar Readings**

**How to Create a Formula in Excel for Multiple Sheets (4 Methods)****Create a Formula to Calculate Percentage in Excel****How to Create a Formula in Excel That Will Place the Word Yes (7 Ways)****How to Create a Formula in Excel for Multiple Cells (9 Methods)**

__Example 5__: Applying COUNTIFS Function

Furthermore, we can combine the **COUNTIFS function** with the **Defined Names** to specify multiple criteria and return the instances that match the given condition. For example, we want to obtain those cases where the *“Quantity”* and *“Unit Price”* are greater than *“15”* and *“$1.1”* respectively.

📌 ** Steps**:

- To start with, navigate to the
**D15**cell >> insert the formula into the**Formula Bar**.

`=COUNTIFS(Quantity,">15",Unit_Price,">1.1")`

In this situation, the *“Quantity”* and *“Unit_Price”* indicate the **Defined Names**.

**Formula Breakdown:**

**COUNTIFS(Quantity,”>15″,Unit_Price,”>1.1″) →**counts the number of cells specified by a given set of conditions and criteria. Here, the**Quantity**represents theargument that refers to the*criteria_range1***D5:D13**cells, whereas the**“>15”**indicates theargument that represents the condition greater than*criteria1**“15”*. Next, the**Unit_Price**represents theargument that refers to the*criteria_range2***C5:C13**cells, whereas the**“>1.1”**indicates theargument that represents the condition greater than*criteria2**“$1.1”*.**Output → 2**

**Read More:** **How to Apply Formula to Entire Column Using Excel VBA**

__Example 6__: Combining IF and COUNTIF Functions

Additionally, we can use multiple functions in conjunction with the **Defined Names**. In this scenario, we’ll apply the **IF** and **COUNTIF** functions to check if the name of the fruit is present within the list, if present then the function returns *“Available”*, else it returns *“Unavailable”*.

📌 ** Steps**:

- First, follow the
**steps shown above**to create a**Defined Name**for*“Item”*.

- Second, in the
**C15**cell, enter the equation given below.

`=IF(COUNTIF(Item,B15),"Available","Unavailable")`

For instance, the **B15** cell indicates the fruit *“Kiwi”*.

**Formula Breakdown:**

**COUNTIF(Item,B15) →**here, the**Item**represents theargument that refers to the*range**Item*column, while the**B15**cell indicates theargument that is*criteria**“Kiwi”.*Since*“Kiwi”*is not present the function returns zero which implies**FALSE**.**Output → 0**

**IF(COUNTIF(Item,B15),”Available”,”Unavailable”) →**becomes**IF(0,”Available”,”Unavailable”) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**0**is theargument which prompts the*logical_test***IF**function to return**“Unavailable”**(argument), otherwise, it returns*value_if_false***“Available”**(argument).*value_if_true***Output → Unavailable**

**Read More:** **How to Apply Formula to Entire Column Without Dragging in Excel**

__Example 7__: Using INDEX and MATCH Functions

Last but not least, by incorporating the **Defined Names **within the **INDEX**, and **MATCH** functions, we can make a versatile formula to calculate the *“Total Sales”* based on the selected *“Item”*. In this case, the **COUNTIF** function checks if the condition holds, then the **INDEX-MATCH** functions extract the *“Quantity”* and *“Unit Price” *values, and lastly, the **IF** function returns the results based on the *“Item”*.

📌 ** Steps**:

- First and foremost, enter the
**D16**cell >> type in the following expression

`=IF(COUNTIF(Item, B16),INDEX(Unit_Price, MATCH(B16, Item,0))*INDEX(Quantity, MATCH(B16, Item,0)), "")`

On this occasion, the **B16** cell refers to the *“Item” “Watermelon”*.

**Formula Breakdown:**

**MATCH(B16, Item,0) →**returns the relative position of an item in an array matching the given value. Here,**B16**is theargument that refers to the*lookup_value**“Item” “Watermelon”*. Following,**Item**represents theargument from where the value is matched. Lastly,*lookup_array***0**is the optionalargument which indicates the*match_type***Exact match**criteria.**Output → 6**

**INDEX(Unit_Price, MATCH(B16, Item,0)) →**becomes**=INDEX(Unit_Price, 6) →**returns a value at the intersection of a row and column in a given range. In this expression, the**Unit_Price**is theargument which is the*array**“Unit Price”*. Lastly,**6**is theargument that indicates the row location.*row_num***Output → $1.6**

**IF(COUNTIF(Item, B16),INDEX(Unit_Price, MATCH(B16, Item,0))*INDEX(Quantity, MATCH(B16, Item,0)), “”) →**becomes**IF(1,1.6*5,””) →**Here,**1**is theargument which prompts the*logical_test***IF**function to return**1.6*5**(argument) otherwise it returns*value_if_true***“” (Blank)**(argument).*value_if_false***Output → $8.00**

## How to Edit a Defined Name in Excel

Thus far, we have demonstrated how to create a formula using **Defined Names** in Excel. Now, in the following section, we’ll learn how we can edit them. It’s simple and easy, just follow along.

📌 ** Steps**:

- In the first place, move to the
**Formulas**tab >> choose the**Name Manager**option.

- In turn, select a
**Defined Name**, here we’ve chosen*“Item”*>> hit the**Edit**button.

- Afterward, rename the named range according to your preference, like
*“Fruit_Name”*. Moreover, you can also enter a different range, if you wish.

Eventually, the results should look like the image shown below.

Here, I have skipped some of the relevant **Examples of how to edit a Defined Range**, which you might explore if you want.

## How to Delete Defined Names in Excel

Now, what if you want to delete a **Defined Name**? Then, you’re in luck, because we’ll answer this exact question. So, let’s see it in action.

📌 ** Steps**:

- Initially, jump to the
**Formulas**tab >> select the**Name Manager**option.

- At this time, select the
**Defined Name**, for instance, we’ve chosen*“Fruit_Name”*>> press the**Delete**button.

Lastly, click the **OK** button to delete the **Defined Name**.

Consequently, the output should look like the screenshot shown below.

Admittedly, we’ve skipped some of the **Methods to delete a Define Name in Excel**, which you may go through if you wish.

## Practice Section

Here, we’ve provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

## Conclusion

In short, this tutorial explores all the ins and outs of how to create a formula using **Defined Names **in Excel. Now, we hope all the methods mentioned above will prompt you to apply them in your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section. Or, you can check out our other articles related to Excel functions on **ExcelDemy**.