How to Create a Custom Formula in Excel (A Step-by-Step Guideline)

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. Today in this article we will provide a step-by-step article to create a custom formula in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Create a Custom Formula in Excel

Consider an example where you have to make a custom formula to find out the total price of your Items given in the dataset. 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 to create one. Let’s do it!

Enable Developer Option to Open VBA Window in Excel


Step 1: Enable Developer Option to Open VBA Window in Excel

First, we need to learn how to open the VBA window to create a customized formula. Follow these steps to learn!

  • Click on the Customized Quick Access Toolbar From the available options, click on More Commands.

Enable Developer Option to Open VBA Window in Excel

  • Excel Options window opens. Click on Customize Ribbon.
  • Now check on the Developer option to create this ribbon. Click OK to proceed.

Enable Developer Option to Open VBA Window in Excel

  • Your Excel worksheet has now a new ribbon named Developer.

Enable Developer Option to Open VBA Window in Excel

  • Select the Developer Ribbon. Click on Macros to open the VBA
  • Or you can just press “Alt+F11” to do that.

Enable Developer Option to Open VBA Window in Excel


Step 2: Write the VBA Codes to Create a Custom Formula

  • In the VBA window, click on Insert.
  • From the available options, click on Module to create a module. We will write our VBA codes in the module.

Write the VBA Codes to Create a Custom Formula

  • Write down your VBA codes to create a custom formula. To find the Total Price for the given items, the VBA codes are,
Public Function  TOTALPRICE (number1, number 2)
TOTALPRICE = (number1 * number2)
End Function
  • We need to declare the VBA codes as a function. That’s why this code starts with the function declaration and ends with the End Function
  • The formula needs a name. We named it TOTALPRICE
  • We will need some inputs in the function. The inputs are defined within parenthesis after the function name.
  • We need to assign the function some sort of value to return. After completing these criteria, our final syntax is:

TOTALPRICE = (number1 * number2)

  • Close the VBA window and return to the main worksheet.

Write the VBA Codes to Create a Custom Formula


Step 3: Apply the Custom Formula in Excel Spreadsheet

  • After creating the custom formula, now we will apply it to our dataset. Click on Cell E4 and search for our custom formula.
  • When the formula shows up, double-click on it to select.

Apply the Custom Formula in Excel Spreadsheet

  • Insert the values into the formula. The final formula is:
=TOTALPRICE(C4,D4)
  • Where C4 and D4 is the Stock and Unit Price

Apply the Custom Formula in Excel Spreadsheet

  • Press Enter to get the result.

Apply the Custom Formula in Excel Spreadsheet

  • Our custom formula is working perfectly! Now apply the same formula to the rest of the cells to get the final result.

Apply the Custom Formula in Excel Spreadsheet

  • Let’s discuss another example! In this new dataset, we have to find out the Retail Price by creating a custom formula.

Apply the Custom Formula in Excel Spreadsheet

  • Open the VBA window and go to the Module following the procedures we discussed before.
  • Write down the VBA The VBA code for the custom formula is,
Public Function RETAILPRICE(number1, number2,number3)
RETAILPRICE = (number1 + number2)/number3
End Function

Apply the Custom Formula in Excel Spreadsheet

  • Now close the VBA window and go to the main worksheet. In Cell F4, search for our new customized function RETAILPRICE.
  • Double-Click on it when found.

Apply the Custom Formula in Excel Spreadsheet

  • Insert the values into the formula and the final form is:
=RETAILPRICE(C4,D4,E4)
  • Where C4, D4, E4 are the Price1, Price2, and Divisor

Apply the Custom Formula in Excel Spreadsheet

  • Get the result by pressing Enter. Now apply this function to all cells to get the final result.
  • This is how you can create a custom formula in excel and use it.

Apply the Custom Formula in Excel Spreadsheet

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

How to create a custom formula in excel is discussed in this article. We hope this article proves useful to you. Do comment if you have any questions or queries.


Similar Articles

Asikul Himel
1 Comment
  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)”.

Leave a reply

ExcelDemy
Logo