How to Create a Formula Using Defined Names in Excel (7 Ways)

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.

How to Create a Formula Using Defined Names in Excel: 7 Ways

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:

• To begin with, go to the Formulas tab >> click on Define Names.

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.

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 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 a Formula in Excel without Using a Function 

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.

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”.

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.

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.

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.

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

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.

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

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.

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

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

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

Read More: How to Create a Complex Formula in Excel

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.

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.

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.

Download Practice Workbook

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.

Related Articles

• How to Create a Formula in Excel for Multiple Cells
• How to Create a Custom Formula in Excel
• How to Apply Formula in Excel for Alternate Rows
• How to Insert Formula for Entire Column in Excel
• How to Create a Conditional Formula in Excel

<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel