Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Custom m Formula in Excel (2 Practical Examples)

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.


Download Practice Workbook


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!


2 Suitable Examples to Create a Custom Formula in Excel

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.

how to create a custom formula in excel

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.


Example 01: Finding Total Price Using Custom Formula

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.

Finding Total Price Using Custom Formula in Excel

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.

Enable Developer Option to create a custom formula in Excel

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.

Inserting Module to Create Custom Formula in Excel

  • After that, write the following code in the newly created Module.
Public Function TOTALPRICE(number1, number2)
TOTALPRICE = (number1 * number2)
End Function

Writing VBA Codes to Create Custom Formula in Excel

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.

Applying Custom Formula to create a custom formula in Excel

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.

Final output of method 1 to create a custom formula in Excel


Example 02: Calculating Retail Price Applying Custom Formula

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

Calculating Retail Price Applying Custom Formula in Excel

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.

Final output of method 2 to create a custom formula in Excel


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. To do this, we will use an Add-in from Microsoft. Let’s say, we have the Employee List of ABC Company as our dataset. In the dataset, we have the First Name, and the Last Name of the employees. Our goal is to create an Email for employees using their First Name, and Last Name. Now, let’s follow the steps outlined in the following section.

How to Create Custom Formula Without Using VBA in Excel

Steps:

  • Firstly, go to the Developer tab from Ribbon.
  • Following that, click on the Add-ins option from the Add-ins group.

As a result, the Office Add-ins dialogue box will appear on your worksheet.

  • Now, go to the Store tab in the Office Add-ins dialogue box.
  • After that, type in Advanced Formula in the search box.
  • Then, click on the Search option.
  • Now, from the search results, choose the first option as marked in the following image and click on the Add option.

Using Add-in to Create Custom Formula Without Using VBA in Excel

  • Subsequently, a pop-up window will appear, and click on the Continue option.

Consequently, the Advanced Formula Environment dialogue box will open on your worksheet.

  • Now, go to the Names tab in the Advanced Formula Environment dialogue box.
  • After that, choose the Functions option.
  • Then, click on the Add option.

Editing Advanced Formula Environment dialogue box to Create Custom Formula Without Using VBA in Excel

As a result, the following fields will appear inside the Advanced Formula Environment dialogue box.

  • Afterward, in the Function name field, specify the function’s name. In this case, we used the email_generator as our function name.
  • After that, type in the function’s arguments in the Arguments field, and separate the arguments with a comma. Here, we typed text1, text2.
  • Then, click on the Add option.
  • In the Function definition field, define the function with the help of various Excel functions. Here, we used the CONCAT function to generate emails for employees.
  • Finally, click on the Done option.

  • After that, click on the Save option as shown in the image below.

Saving new function to Create Custom Formula Without Using VBA in Excel

  • Now, use the following formula in cell D5.
=email_generator(B5,C5)

Here, cell B5 indicates the first cell of the First Name column, and cell C5 refers to the first cell of the Last Name column.

  • Following that, hit ENTER.

  • Consequently, you will have the Email for the first employee in cell D5.

  • Lastly, use Excel’s AutoFill feature to get the Emails for the rest of the employees as shown in the following image.

Final output of method 3 to Create Custom Formula Without Using VBA in Excel

Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)


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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.


Similar Articles

Asikul Himel
2 Comments
  1. 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 Function

      2) 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!

Leave a reply

ExcelDemy
Logo