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.

**Similar Articles**

- How to Apply Formula in Excel for Alternate Rows
- How to Insert Formula for Entire Column in Excel
- How to Create a Conditional Formula in Excel
- How to Create a Formula Using Defined Names in Excel
- How to Create a Formula in Excel without Using a Function
- How to Create a Complex Formula in Excel

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

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!