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

## 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! ### 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. • Excel Options window opens. Click on Customize Ribbon.
• Now check on the Developer option to create this ribbon. Click OK to proceed. • Your Excel worksheet has now a new ribbon named Developer. • Select the Developer Ribbon. Click on Macros to open the VBA
• Or you can just press “Alt+F11” to do that. ### 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 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. ### 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. • Insert the values into the formula. The final formula is:
`=TOTALPRICE(C4,D4)`
• Where C4 and D4 is the Stock and Unit Price • Press Enter to get the result. • Our custom formula is working perfectly! Now apply the same formula to the rest of the cells to get the final result. • Let’s discuss another example! In this new dataset, we have to find out the Retail Price by creating a custom formula. • 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`````` • 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. • 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 • 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. 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. Reply 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)”.

• Reply 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! 