Ever felt limited by the standard functions in Excel? In “How to Create Your Own Excel Function,” you’ll learn that there’s much more you can do. Imagine crafting a function tailored precisely to your needs, simplifying those repetitive tasks.
I’m here to guide you through this exciting journey, showing you step by step how to turn this possibility into reality. Let’s dive in and unlock the full potential of Excel together!
Excel Functions
Excel boasts an extensive array of functions, catering to most needs. While the majority of users might regularly engage with functions such as =SUM, =AVERAGE, and =VLOOKUP, there are others, like =PDURATION or =ATANH, that serve more specialized purposes – the latter computing the inverse hyperbolic tangent of a number, for the analytically curious!
Despite the comprehensive suite of functions provided by Microsoft, users may occasionally find themselves pondering the absence of certain capabilities within Excel. Fortunately, the flexibility of Excel is such that you can create your own Excel Function.
Create Your Own Excel Function
By using Excel Visual Basic it is possible to create your very own Excel functions. These can be customised Functions that perform exactly what YOU want.
They can be basic like a Function that adds some numbers together or applies a custom tax rate to a value when used, or they can be sophisticated with Functions that perform Fuzzy Lookups or use algorithms.
Basic Example
To understand how you can create your own Excel function we can work through the following example. This basic example will show you how to create a custom function in Excel that will convert Miles into Kilometres.
You might need something like this if you work with external data that is sourced from a different country or where records are stored in a different format.
Define the Function
The first step is to define the function, this makes writing the Visual Basic code easier to get the desired result.
A quick Google search shows that there are 1.60934 kilometres to 1 mile. Therefore to convert miles into kilometres the function will need to multiple any mileage value by 1.60934.
- =Mileage Value * 1.60934
With the function defined you can create your own function in Excel.
The Build
In the Excel workbook go to the Visual Basic Editor using either ALT+F11 or click on the Developer Tab in the Ribbon and select Visual Basic (if you don’t have the Developer Tab showing in your Excel Ribbon click here to find out how to unlock it).
In the Visual Basic Editor click on Insert > Module from the drop down menu at the top of the screen, then select the code window so you can begin constructing your Excel function:
To tell Excel you are creating a Function the Visual Basic code always starts with the keyword Function and finish with the keyword End Function.
Function
'The rest of the code and function calculations go in-between
End FunctionThe Function will need a label. It makes sense to choose something descriptive, especially if you are creating multiple functions in Excel over time. For this example it makes sense to call it ConvertMilesToKMs.
All functions require parameters, which serve as inputs where the function’s operations are applied. Each parameter must be named and assigned a specific data type to ensure the function processes the data correctly.
In the context of our example, the parameter is named ‘Miles’ and the data type for that is a Double. Double is chosen over Integer as this data type allows for both whole numbers and fractions, accommodating the calculations for distances that are not whole numbers.
Function ConvertMilesToKMs(Miles As Double) As Double 'The rest of the code and function calculations go in-between End Function
Now the outline of the function is structured the next step is to write the calculation. In this example the aim is that Kilometres are calculated by the formula Miles * 1.60934.
Function ConvertMilesToKMs(Miles As Double) As Double ConvertMilesToKMs = Miles * 1.60934 End Function
Save the file and the function is complete. You have now learned how to create your own Excel function!
To test the function go back to the workbook and try to convert a value using =ConvertMilesToKMs( cellref ) as shown below:
Tips for Creating Your Own Excel Functions
- Define the requirements of your custom Excel function first. Define what the end result should look like and work backwards to define what variables/inputs are needed, their data types and the calculation you need to perform to get the desired result.
- Add notes in your VBA code. When you get used to writing Custom Excel functions you will likely end up writing lots of them so it helps to keep each one well documented.
- Consider saving all your Functions to the Personal Workbook rather than in specific Excel files. In the long run this will make sure your Custom Functions are available every time you use Excel rather than only when you use a specific Excel file.
- Be creative! Creating your own Excel functions can save you a lot of time down the road.
Summary
Creating your own Excel function, as demonstrated in this guide, is a simple yet powerful way to enhance your Excel skills. Even as a beginner or novice, you’ve seen how custom functions can be tailored to specific needs, such as converting miles to kilometres.
Embrace this newfound ability to personalize your Excel experience, and remember, practice makes perfect. As you continue exploring Excel’s capabilities, feel free to revisit and refer to this guide.
Keep experimenting and discovering the vast potential of Excel’s custom functions!
Keep Excelling,
Enhance your Excel journey even further! Discover how to get Crypto Prices in Excel. Our guide not only teaches you to fetch crypto data but also to create bespoke functions, elevating your Excel skills to new heights.
