How to Create a Custom Function in Excel

Excel functions are actually shortcuts to more complex calculations. Like a macro is to a series of steps, a function is to a longer equation. While Excel is packed full of functions, you may find that you have a proprietary calculation that you use regularly that would be useful as a function shortcut.

Advanced Excel users who are comfortable with creating and editing macros will find creating custom functions a snap. Even those who aren't that familiar with VBA code can follow the steps below and create useful shortcuts to their calculations. This article will show how to create a very simple custom function (area of a triangle) to demonstrate the steps. You will have to figure out the math on your own!

Key Takeaways

• User-defined functions (UDFs) let you create custom Excel formulas using VBA code for calculations not available in built-in functions
• Your workbook must be saved as a macro-enabled workbook (.xlsm) to store and run custom functions
• Once created, you can use your UDF just like any built-in Excel function by typing its name or selecting it from Insert Function
• Custom functions are saved within the workbook where they're created unless you store them in your Personal Macro Workbook

What Is a User-Defined Function in Excel?

A user-defined function (UDF) is a custom formula you create using Visual Basic for Applications (VBA) to perform calculations that aren't available through Excel's built-in functions. Think of it as building your own Excel function tailored to your specific needs.

Once you create a UDF, it works just like native Excel functions such as SUM or AVERAGE. You can type it directly into a cell, select it from the Insert Function dialog or include it in larger formulas. The difference is that you control exactly what calculation it performs.

UDFs are particularly valuable when you need to perform the same proprietary calculation repeatedly across your work. Instead of rebuilding a complex formula each time or remembering the exact sequence of operations, you create the function once and call it whenever needed. 

When to Create a Custom Function 

A custom function in Excel is the right solution when: 

• You have a proprietary or industry-specific calculation you use repeatedly that would require a complex nested formula 
• You want to simplify a multi-step calculation into a single, easy-to-remember function name 
• Your organization has standard calculations that multiple team members need to apply consistently 
• You find yourself copying and pasting the same complex formula across workbooks 

However, before creating a UDF, check whether a built-in function or combination of functions can accomplish your goal. Built-in functions run faster and don't require macro-enabled files. Create a custom function when built-in options genuinely don't meet your needs. 

Prerequisites 

Before you begin creating your first UDF, make sure you have: 

• Excel for Windows (UDFs created with VBA code work differently on Mac and may have limitations) 
• Basic familiarity with how Excel formulas work 
• A calculation in mind that you want to automate 
• Willingness to work with simple VBA code (this article provides examples you can adapt) 

You don't need to be a programmer. The steps below walk you through the process, and the code structure is straightforward once you understand the pattern.

Save as a Macro-Enabled Workbook 

Because custom functions use VBA, your file must be saved as an .xlsm macro-enabled workbook. This file format is required because standard Excel files (.xlsx) cannot store or run VBA code. 

1. Click File > Save As 
2. Select Excel Macro-Enabled Workbook (*.xlsm) from the dropdown menu below the document title 
3. Click Save 

Activate the Developer Tab 

If you don't see the Developer tab in your ribbon, you will need to reveal it before you continue: 

1. Click the File tab 
2. Click the Options button 
3. In the Customize Ribbon panel, make sure that the Developer tab has a checkmark beside it. 
4. Click OK 

Determine Your Custom Function Variables 

A function creates a calculation based on variables input at the time the function is used. To create your custom function, you will first need to identify the arguments (the input values your function needs to perform its calculation). 

We are going to create a function to quickly calculate the area of a triangle. Our arguments will be the base and the height: Area = Base times Height divided by two. 

Open Visual Basic Editor 

Hit Alt + F11 or click the Visual Basic button on the Developer tab. 

Choose Insert > Module in the VBA editor: 

Type Code 

You will need to be familiar with VBA code to complete this step in your own work. The basic structure of a User-Defined Function is: 

Function FunctionName(arguments) 

FunctionName = calculation 

End Function 

Adding "Public" to Function tells Excel to list your User-Defined Function in the Insert Function dialog box. 

Here is simple sample code for our Triangle Area Function. 

Public Function TRIAREA(number1, number2) 

TRIAREA = (number1 * number2) / 2 

End Function 

Hint! If your variables are very specific – such as in our example, number1 will always be the Base of our triangle, and number2 will always be the Height – you can give them specific names to help you when you are completing the function in your workbook: 

When you are finished writing the code, click the save button or hit Ctrl+S to save your custom function to the workbook. Note that you will only be able to use it in the workbook in which it was saved, it is not a global function. 

Close the VBA editor. 

Use Your User-Defined Function 

Back in your Excel workbook, place your cursor in the place where you wish to insert the custom function: 

1. Click Insert Function on the Formulas tab. 
2. In the Insert Function dialog box, select User Defined from the "select a category" dropdown list. You should see the name you gave your function. In this example we see TRIAREA. 
3. Select the function, then click OK. 
4. Fill out the Function Arguments dialog box and click OK. 

Saving Your UDF for Use in Other Workbooks 

By default, a user-defined function only works in the workbook where you created it. If you want to use your custom function across all your Excel workbooks, you can save it in your Personal Macro Workbook (PERSONAL.XLSB). 

The Personal Macro Workbook is a hidden file that opens automatically whenever you start Excel. Any UDFs stored there become available in every workbook you open. 

To save your UDF in the Personal Macro Workbook: 

1. If you don't already have a PERSONAL.XLSB file, create one by recording any simple macro and selecting "Personal Macro Workbook" as the storage location 
2. Open the Visual Basic Editor (Alt + F11) 
3. In the Project Explorer pane, locate VBAProject (PERSONAL.XLSB) 
4. Right-click and select Insert > Module 
5. Paste or type your UDF code into this module instead of a specific workbook's module 
6. Save and close the VBA editor 

Now your custom function will be available whenever Excel is running. 

UDF Limitations to Know 

Before relying heavily on custom functions, understand these limitations of custom functions in Excel: 

• UDFs can only return a value to the cell containing the formula. They cannot modify other cells, change formatting or perform actions outside the formula cell. 
• Custom functions run slower than built-in functions. For large datasets with thousands of calculations, this performance difference may be noticeable. 
• Workbooks containing UDFs must remain in macro-enabled format (.xlsm). If someone saves your file as a standard .xlsx, the custom functions will stop working. 
• UDFs require macros to be enabled. Recipients of your workbook must allow macros to run, which some organizations restrict for security reasons. 
• Custom functions cannot call Excel commands or change the Excel environment. They are designed purely for calculations. 

These limitations don't diminish the value of UDFs for appropriate use cases. They simply help you decide when a custom function is the right tool. 

Troubleshooting Common UDF Errors 

• If your Excel UDF isn't working as expected, check these common issues: 
• #NAME? error: Excel cannot find your function. Verify the workbook is saved as macro-enabled (.xlsm), check that you spelled the function name correctly and confirm macros are enabled in your security settings (File > Options > Trust Center > Trust Center Settings > Macro Settings). 
• #VALUE! error: The function received the wrong type of input. Check that you're passing numbers to arguments that expect numbers and text to arguments that expect text. 
• Function doesn't appear in Insert Function dialog: Make sure you included "Public" before "Function" in your VBA code. Without this keyword, the function works but won't be listed in the dialog box. 
• Function returns zero or unexpected result: Review your calculation logic in the VBA code. Add the function name followed by the equals sign before your calculation (e.g., TRIAREA = formula). 

What Are the Types of User-Defined Functions in Excel? 

You can categorize Excel UDFs by their input structure: functions with no arguments, single arguments, multiple arguments, array arguments or optional arguments. No-argument functions return a fixed calculation or value. Single and multiple argument functions accept one or more inputs. Array functions can process ranges of cells, and optional argument functions have default values when inputs aren't provided. 

How Do I Enable UDFs in Excel? 

To enable UDFs, you need to save your workbook as a macro-enabled file (.xlsm) and ensure your Excel security settings allow macros to run. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings, then select "Enable all macros" or "Disable all macros except digitally signed macros" depending on your security requirements. 

What Is an Example of a User-Defined Function in Excel? 

A simple UDF example is a function that calculates the area of a triangle: Public Function TRIAREA(Base, Height) followed by TRIAREA = (Base * Height) / 2 and End Function. Once saved in a macro-enabled workbook, you can use =TRIAREA(5, 10) in any cell to calculate the area, which would return 25. 

Can I Use a Custom Function in Other Excel Workbooks? 

Yes, you can use a custom function across all your workbooks by saving it in your Personal Macro Workbook (PERSONAL.XLSB) instead of a specific workbook. This hidden file loads automatically when Excel starts, making any UDFs stored there available in every workbook you open. 

Why Does My UDF Show a #NAME? Error? 

The #NAME? error typically means Excel cannot find your function, usually because you didn't save the workbook as macro-enabled (.xlsm), you misspelled the function name or you disabled macros in your security settings. Check each of these items, and ensure the module containing your function code is properly saved within the workbook. 

What Is the Difference Between a UDF and a Macro in Excel? 

A UDF (user-defined function) returns a value to a cell like a formula, while a macro performs actions like formatting cells or automating tasks. You use a UDF by typing it into a cell as part of a formula. You run a macro by triggering it manually or through a button. UDFs calculate; macros act.