While working in Excel or making custom worksheets for business analysis, we may need to create a custom formula of our own. Despite all the functions provided by Excel, we may need to create one to get our job done. Excel lets you create your own functions by using the VBA Programming Codes. In this article, we will provide a step-by-step methodology to create a custom formula in Excel.
Watch Video – Create a Custom Formula in Excel
What Exactly a User Defined Function Is?
Excel allows us to create our own custom functions using VBA codes. These custom functions in Excel are known as User Defined Functions (UDF). They allow you to create your own custom functions to do just about any type of operation. In this section, we will take a step-by-step tour of how to create one. Let’s do it!
In this section of the article, we will learn two suitable examples for creating a custom formula in Excel. Let’s say we have the Price List for Items in ABC Store as our dataset. In the dataset, we have the Stock amount and Unit Price of Items in the store. Our goal is to create a custom function to calculate the Total Prices.
Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
1. Creating a Custom Formula to Find Total Price in Excel
Before writing the VBA code, we need to learn how to open the VBA window to create a customized formula. Now, let’s follow the steps mentioned below to do this.
Step 01: Enable Developer Option
- Firstly, click on the Customized Quick Access Toolbar.
- After that, from the drop-down, click on the More Commands option.
Consequently, the Excel Options dialogue box will appear on your worksheet.
- Now, in the Excel Options dialogue box, go to the Customize Ribbon tab.
- Then, check the box of the Developer field.
- After that, click OK.
As a result, the Developer tab will be available in the Ribbon, as shown in the following image.
- Then, go to the Developer tab from Ribbon.
- After that, click on the Visual Basic option from the Code group.
Step 02: Write VBA Codes to Create Custom Formula
Now we will write VBA code to create the custom formula. To do this, let’s follow the steps mentioned in the following section.
After clicking the Visual Basic option, the Microsoft Visual Basic for Applications window will open on your worksheet.
- Now, go to the Insert tab from the Microsoft Visual Basic for Applications window.
- Then, choose the Module option.
- After that, write the following code in the newly created Module.
Public Function TOTALPRICE(number1, number2)
TOTALPRICE = (number1 * number2)
End Function
Code Breakdown
- Firstly, we initiated a function named TOTALPRICE.
- After that, we declared the arguments of the function as number1, and number2.
- Then, we specified that the value of TOTALPRICE will be the multiplication of number1 and number2.
- Finally, we terminated the function.
- After writing the code, click on the Save option.
Step 03: Apply Custom Formula
After saving the VBA code, it’s time to apply the newly created function. Let’s follow the instructions outlined below.
- Firstly, write the following formula in cell E5.
=TOTALPRICE(C5,D5)Here, cell C5 refers to the first cell of the Stock column, and cell D5 represents the first cell of the Unit Price column.
- After that, hit ENTER.
As a result, you will have the Total Price for the first Item in cell E5.
- Lastly, use Excel’s AutoFill feature to get the rest of the outputs as demonstrated in the following picture.
Read More: How to Apply Formula to Entire Column Using Excel VBA
2. Creating a Custom Formula in Excel for Calculating Retail Price
Now, we will see another example of creating a custom formula in Excel. Let’s follow the procedure discussed in the following section.
Steps:
- Firstly, follow the steps mentioned in the first step of the previous method.
- After that, write the following code in the newly created Module.
Public Function RETAILPRICE(number1, number2, number3)
RETAILPRICE = (number1 + number2) / number3
End Function
Code Breakdown
- Firstly, we initiated a function named RETAILPRICE.
- Following that, we declared the arguments of the function as number1, number2, and number3.
- After that, we specified the value of TOTALPRICE in terms of number1, number2 and number3.
- Lastly, we ended the function.
- After writing the code, click on the Save option.
- Now, apply the formula given below in cell F5.
=RETAILPRICE(C5,D5,E5)Here, cell E5 indicates the first cell of the Divisor column.
- Then, press ENTER.
Consequently, you will have the Retail Price for the first Item in cell F5.
- Finally, use Excel’s AutoFill option to get the Retail Prices for the remaining Items as demonstrated in the image below.
How to Create Custom Formula Without Using VBA in Excel
In this section of the article, we will learn how we can create a custom formula without using VBA in Excel. We can create a custom formula with the help of the Lambda Function and the Define Name option.
Consider a situation where we want to calculate the salary of the employees considering increments. We want to create a custom formula where we will define cells containing the salary and increment rate and it will return the current total salary considering increment.
For this, go to Define Name from the Formulas tab.
An Edit Name wizard will appear.
Give a suitable name (i.e. Salary_with_Increment) to the custom formula from the Name section and add a description about the formula in Comment. Now, insert the following formula in the Refers to section.
=LAMBDA(salary,increment_rate,salary*(1+increment_rate))Afterward, click on OK to finish the formula formation process.
Now, you will find the formula that we made in the worksheet.
Now, apply the following formula to have total salary considering increment with the custom formula made without VBA.
=Salary_with_Increment(D5,E5)Here,
D5 = Previous Salary
E5 = Increment Rate
Use Fill Handle to AutoFill the custom formula till the last cell.
Read More: How to Create a Formula in Excel for Multiple Cells
Quick Notes
- You Cannot Record a customized formula like you can an Excel macro.
- Creating a custom formula has more limitations than regular VBA macros. It cannot alter the structure or format of a worksheet or cell.
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to create a custom formula in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.
This worked perfectly. However, how can I get excel to show the formula input preview thread below the cell? For example, when I pull up “=RETAILPRICE(” in a cell, I want to see the formula guide that pops up below “RETAILPRICE(Price1,Price2,Divisor)”.
Dear TAYFOON,
Thank you for your response.
If you want to see the arguments as “RETAILPRICE(Price1,Price2,Divisor)”.
Then follow the instructions below-
1) In the module Place the following code-
Public Function RETAILPRICE(Price1, Price2, Divisor) RETAILPRICE = (Price1 + Price2) / Divisor End Function2) Then go to your worksheet and type “=RETAILPRICE(” and after that press Ctrl+Shift+A to get the arguments just as you want.
Hope you found your solution. Thanks!