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**.

## 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.

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**.

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.

- Enter the following code in the newly created
**Module**;

```
Public Function TOTALPRICE(number1, number2)
TOTALPRICE = (number1 * number2)
End Function
```

**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**.

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.

**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
```

**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.

## 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.

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.

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

- 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

- Use the
**Fill Handle**to**AutoFill**the custom formula down to 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.

**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!