How to Make an Excel Add-in [Narrated with an Example]

Last updated on July 25th, 2018

So, you mastered VBA (or not) and automated that extremely complicated operation that consumes all your time. Let’s call your invention SuperFunction.

But what would you do if your boss tells you to do the same operation on another piece of input? One very sub standard solution would be to copy the code you created into your new file. Substandard, yes, but a solution anyway.

Moving on, what happens when the frequency of this increases, let’s say your paranoid boss asks you to analyze hourly data and do the same operation on the input every hour?

Would you still prefer copy-pasting your code into every input file? 24 times a day?

Or what if you get moved to another assignment, how’d you ensure your successor can do it hassle-free?

Excel is supposed to make things easy and VBA tries to make them easier. But this is a hell lot of complication.

You might have to create a macro which takes in an input file and performs the process on it.

Again substandard. Time-consuming. Brain wrecking.

A very simple way of doing it would be converting your SuperFunction into an Add-In.

When you do that you will be able to access it as easily as you use SUM or AVERAGE in any file, one any data. Your SuperFunction will blend in so well, you’d hardly even notice.

What Add-in basically does is give Excel additional functionality which is highly customizable.

Read More: Nominal Interest Rate vs. Effective Interest Rate Excel Formulas

SuperSolution

Let’s dive into the process of making your SuperFunction super-duper powerful.

The process is simple:

Step 1. Develop your SuperFunction

Step 2. Save the file as .xlam

Step 3. Go to Options, spot the Add-Ins option. Manage Add-Ins and browse to your .xlam file

Done!

Simple really!

Demo

Let me show you how I achieved it:

I’ll start with a simple example. I was doing my bank statements, trying to figure out the compound interest my

unpaid student loans were generating. Now we all know staring at out bank records is painful; manually calculating

the money that was due from my loans would have been traumatic.

So I Googled the formula (of course) and built a function out of it:

This function would take the principal, rate of interest and time period as inputs and do something in its black box

and give me the output as the compound interest am generating.

After this, I went ahead and saved it as .xlam or Excel Add-In

Click on the image to get a clearer view

Without changing the location where it will be saved, you can go ahead and click OK.

Now you should know that as soon as you save your file as Excel Add-In, it’s sort of inaccessible. By that I mean its gets hidden. That particular workbook is now unreachable. Although it can be easily found for sharing purposes. But is advisable to not to alter it.

Read More: How to Use Excel Objects to Create an Art Portfolio

Moving on, I will add it now. Go to File and navigate to Options. In Options, you will have to click on Add-ins and the Go button next to it.

This will let you browse to the location where you had stored your add-in file (which hopefully you would not have changed from the default).

I select my file and proceed as the screen prompts.

This will add my formula as Add-In.

Now it is accessible from anywhere and everywhere and ready to use.

This is how it works now:

Ok, easy-peasy.

Now let’s look at a little more complicated example:

I used to work at a cell phone company and we received a lot of stats hourly. These stats were essential for quality control and assessment.

Let me show you a sample of what these stats looked like:

These were basically calls that were attempted and calls that were successfully established. We received these inputs from several cell phone towers located in area A which was under my surveillance.

Read More: The FVSCHEDULE Excel Function

We had to report 2 values for every cell phone tower, the success rate and the number of failed calls.

Depending on this data we would grade the cell tower performance using this index

So, I created generated a complex function in a file, saved it as .xlam and added it in my Add-In.

After adding, I used it on my file so:

This is when I used it on all the data.

So there you go.

I was even nice enough to share the method with my colleagues and make lives easier.

So can you !

I mean so you can also create and customize Add-ins. Sharing or not is a whole other perspective.

Joking aside it takes only a few steps to automate the monotonous jobs you do daily with Excel.

Download working file

Download the working file from the link below.