Free Microsoft Excel Training Videos

Create custom functions in Excel

If you have to perform a custom calculation on your data and wish to reuse the function frequently in Excel, you don't have to repeatedly enter a long, complex worksheet formula. Instead, you can create your own custom function in Excel to perform the calculations. You can then use the function to create formulas that are easier to enter and maintain.

To create your own custom worksheet functions, you work in Microsoft Visual Basic for Applications (VBA) and create a function macro.
For example, say you have a complex formula for figuring a pay package, where the pay package changes based on several factors such as a house rent allowance or a leave travel allowance. Instead of entering the lengthy formula that takes all of these factors into account every time you want to calculate the pay package, you can create a custom pay package function.

If your company changes the rules for the pay package calculations, you don't have to find and make tedious changes to all of your complex formulas. You only have to make changes in one place, the custom function, to update all the formulas in your workbook.
The following steps show how to create and use a custom function.
To keep the example simple, this function calculates the pay package based on the basic salary.

On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu in the Microsoft Visual Basic window, click Module.
In the Module window, type the code for your function.
For example, a function to calculate the pay package might look like this:

Function paypackage(salary)
'House Rent Allowance (HRA)
'HRA=60% of basic salary,br> 'Leave Travel Allowance (LTA)
'LTA=8.33% of basic salary
paypackage = salary + salary * 0.6 + salary * 0.0833
End Function

The first line in the example above creates a function named paypackage, which performs its calculations on a number or excel cell reference. The function uses a variable, salary, to store the number or value from the cell. In between (lines 2 to 5) we have put some remarks for the ready reference of the user.The sixth line calculates the value of the paypackage function by multiplying the number or cell reference (salary) by 0.60 and 0.0833 to take care of the house rent allowance and leave travel allowance respectively.. The last line ends the function code.
On the File menu, click Close and Return to Microsoft Excel.
On the worksheet, use your function in formulas as you would any worksheet function.
For example, you could use the paypackage function to calculate the total salary package.
A function that you create in this manner is available for use only in the workbook where you create it. If you want to use the function elsewhere, you can copy the code for the function to VBA modules in other workbooks; or, if you're a developer, you can copy the function into a workbook used as a function library and compile that workbook as an add in program.