Do you ever find yourself using the same formula, day in and day out, and think, “Wow, I wish Excel just had a function for this.” Turns out, you can create your own functions in Excel to do whatever you want (for the most part)!
For example, in digital advertising, something that gets calculated a lot is CPM, aka cost per mille, aka cost per 1000 ad impressions.
If an advertiser pays $10,000 for 1,000,000 impressions, we can figure out the CPM by:
Total cost / (Total Impressions / 1000)
Total cost * 1000 / Total Impressions
$10,000 * 1000/ 1,000,000 = $10 CPM
The CPM calc is simple enough to write in Excel, but given the number of times I need to perform this calculation, it would be nice to have function for it.
So let’s make one!
User defined functions are created with Visual Basic for Applications, Microsoft Office’s own programming language. Visual Basic is useful to know, but fairly annoying to learn and work with. Luckily, you don’t need to know much to create your own function!
To write our function, we need to open the Visual Basic Editor. How you access the editor will depend on your computer and Excel version: in Excel 2007 and 2010 on a PC, you need to go to the a Developer tab (or add this tab to your ribbon through Excel Options) where you’ll find a Visual Basic button. For Excel 2011 on Mac, which is what I have and am showing in the screenshots in this article, go to Tools > Macro > Visual Basic Editor.
In the editor, create a Module to store the function code. Go to Insert > Module to do so.
And now we have a ... blank box! This is where we’ll write our function.
Your function needs to abide by the following format
Public Function YourFunctionsName(variable1 as <data type>, variable2 as <data type>)
YourFunctionName = do more stuff
Here’s a very simple example, of a function that multiplies two numbers together:
Public Function MULTIPLY(x as Double, y as Double)
MULTIPLY = x * y
Let’s walk through these functions step by step.
- All the code for your function must be between
End Function. Technically, you don’t need the word
Public, but this ensures that there are no restrictions to accessing it, which is fine unless you’re making some top secret function that you’re distributing widely but don’t want people to copy.
- Name your function. Just make sure it’s not the same as any existing Excel functions! (i.e. don’t name it SUM)
- Define the input variables. You need to tell Excel how many and what type of inputs to expect. In the case of MULTIPLY, we need two numbers (defined as
Double, which means they can have decimals), but there may be cases where your function takes text (
String), true/false values (
Boolean), whole numbers (
Integer) etc. A full list of accepted data types can be found here.
- The inside of the function is where you define what you want it to do. The possibilities are pretty endless, although if you really want to get crazy with these you’ll need to learn some Visual Basic.
- For basic functions though, you’ll generally perform some operation on the input variables and return the result. To return the result, just make sure you assign the value to the function name (ex:
MULTIPLY = x * y)
Following those steps, we can create the CPM function:
Public Function CPM(cost as Double, impressions as Double)
CPM = cost * 1000 / impressions
And voila, now we don’t have to type that formula again!
To make sure your function is still there when you close and re-open Excel tomorrow, you need to install it as an Add-In.
- Open a new workbook (or if the one you used to create your function in is blank, that works too, and you can skip to step #4)
- Open the Visual Basic Editor and create a module
- Paste your function code in this module
- Save the workbook as a .xlam file (the Excel Add-In format) in your Excel Add-In Folder. This folder’s location will vary depending on what computer and version of Excel you use. For my Excel 2011, I can store mine in
/Applications/Microsoft Office 2011/Office/Add-Ins
- Enable your add-in by going to Tools > Add-ins. You should see whatever you named your xlam file listed there - check it and press OK
From then on your function will always be there - in any workbook you open, if start typing it’s name in the formula bar or a cell and Excel will recognize it.
And that’s it! Go make all the crazy (or super simple) custom functions your heart desires.