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

Despite all the built-in functions provided by Excel, we may need to create a different function to get our job done. Excel lets us create our own functions by using VBA Programming Code. In this article, we will provide a step-by-step methodology to create a custom formula in Excel.


Watch the Video – Create a Custom Formula in Excel



What Is a User Defined Function?

Excel allows us to create our own custom functions using VBA code, known as User Defined Functions (UDF). Let’s create one.


Suppose 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. We’ll create a custom function to calculate the Total Prices.

how to create a custom formula in excel


Example 1 – Creating a Custom Formula to Find the Total Price in Excel

In order to write the VBA code to create a customized formula, we need to open the VBA window.

Step 1 – Enable the Developer Tab

  • Click on the Customized Quick Access Toolbar.
  • From the drop-down, click on the More Commands option.

Finding Total Price Using Custom Formula in Excel

The Excel Options dialog box will appear.

  • In the Excel Options dialog box, go to the Customize Ribbon tab.
  • Check the box of the Developer field.
  • 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.

  • Go to the Developer tab.
  • Click on the Visual Basic option from the Code group.

Step 2 – Write VBA Code to Create the Custom Formula

We are all set to write VBA code to create the custom formula.

Steps:

After clicking the Visual Basic option, the Microsoft Visual Basic for Applications window will open on your worksheet.

  • Go to the Insert tab from the Microsoft Visual Basic for Applications window.
  • Choose the Module option.

Inserting Module to Create Custom Formula in Excel

  • Enter 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 initiate a function named TOTALPRICE.
  • We declare the arguments of the function as number1, and number2.
  • We specify that the value of TOTALPRICE will be the multiplication of number1 and number2.
  • We terminate the function.
  • Click on the Save button.

Step 3 – Apply the Custom Formula

After saving the VBA code, we can apply the newly created function.

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

  • Press ENTER.

Applying Custom Formula to create a custom formula in Excel

As a result, we have the Total Price for the first Item in cell E5.

  • 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

Read More: How to Apply Formula to Entire Column Using Excel VBA


Example 2 – Creating a Custom Formula in Excel for Calculating the Retail Price

Now, let’s consider another example of creating a custom formula in Excel.

Steps:

  • Follow the first step of the previous method to open a new Module window.
  • Enter 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 

  • We initiate a function named RETAILPRICE.
  • We declare the arguments of the function as number1, number2, and number3.
  • We specify the value of TOTALPRICE in terms of number1, number2 and number3.
  • We end the function.
  • Click on the Save button.

  • Apply the formula below in cell F5:
=RETAILPRICE(C5,D5,E5)

Here, cell E5 indicates the first cell of the Divisor column.

  • Press ENTER.

We have the Retail Price for the first Item in cell F5.

  • 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 a Custom Formula Without Using VBA in Excel

We can also create a custom formula without using VBA in Excel 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 the formula will return the current total salary considering the increment.

Steps:

  • Go to Define Name from the Formulas tab.

Command Sequence to Define Custom Formula

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.
  • Insert the following formula in the Refers to section:
=LAMBDA(salary,increment_rate,salary*(1+increment_rate))
  • Click on OK to finish the formula formation process.

Defining Custom Formula

Now, the formula that we made is available for use in the worksheet.

Highlighting For,ula in Excel

  • Apply the following formula to get the total salary considering increment with our custom formula made without VBA:
=Salary_with_Increment(D5,E5)

Here,
D5 = Previous Salary
E5 = Increment Rate

Applying Formula in Excel

  • Use the Fill Handle to AutoFill the custom formula down to the last cell.

AutoFilling Formula in Excel

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.

Download Practice Workbook


Similar Articles


<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo