Lamisa Musharrat
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.
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.
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.
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.
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.
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 the row_num argument that indicates the row location. This whole part returns the value of $7.
- 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.
