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