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.


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.

how to create a formula using defined names in excel

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.

Multiplying Named Ranges

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.

Creating Named Range

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

how to create a formula using defined names in excel using multiplying operator

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

Using the SUM Function

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

Selecting Define Names option

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

Defining Names in Excel

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

how to create a formula using defined names in excel with the SUM function

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.

Utilizing COUNTA Function

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

how to create a formula using defined names in excel with COUNTA function

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 the range argument that refers to the C5:C13 cells, while the “>1.5” cell indicates the criteria argument that returns the count of the values greater than “$1.50”.
    • Output → 4

how to create a formula using defined names in excel using COUNTIF Function

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


Similar Readings


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 the criteria_range1 argument that refers to the D5:D13 cells, whereas the “>15” indicates the criteria1 argument that represents the condition greater than “15”. Next, the Unit_Price represents the criteria_range2 argument that refers to the C5:C13 cells, whereas the “>1.1” indicates the criteria2 argument that represents the condition greater than “$1.1”.
    • Output → 2

how to create a formula using defined names in excel using COUNTIFS Function

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:

Combining IF and COUNTIF Functions

  • 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 the range argument that refers to the Item column, while the B15 cell indicates the criteria argument that is “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 the logical_test argument which prompts the IF function to return “Unavailable” (value_if_false argument), otherwise, it returns “Available” (value_if_true argument).
    • Output → Unavailable

how to create a formula using defined names in excel with IF and COUNTIF Functions

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 the lookup_value argument that refers to the “Item” “Watermelon”. Following, Item represents the lookup_array argument from where the value is matched. Lastly, 0 is the optional match_type argument which indicates the 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 the array argument which is the “Unit Price”. Lastly, 6 is the row_num argument that indicates the row location.
    • 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 the logical_test argument which prompts the IF function to return 1.6*5 (value_if_true argument) otherwise it returns “” (Blank) (value_if_false argument).
    • Output → $8.00

how to create a formula using defined names in excel using INDEX and MATCH Functions


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.

How to Edit a Defined Name in Excel

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

Selecting a Defined Name

  • 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 Edit a Defined Name in Excel with the Edit option

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.

How to Delete Defined Names in Excel

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

Deleting Defined Name Range

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

How to Delete Defined Names in Excel using the delete option

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.

how to create a formula using defined names in excel practice section


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.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo