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?

image-1

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:

How to make an Excel Add-in Image 1

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

How to make an Excel Add-in Image 2

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.

How to make an Excel Add-in Image 3

How to make an Excel Add-in Image 4

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).

How to make an Excel Add-in Image 5

How to make an Excel Add-in Image 6

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:

How to make an Excel Add-in Image 7How to make an Excel Add-in Image 8

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:

How to make an Excel Add-in Image 9

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

Success RateFailuresGrade
1000A
90-1000 to 5A
5 to 10B
10 to 15C
>15Alarm
80-900 to 5B
5 to 10C
>10Alarm
70-800 to 5C
>5Alarm
<70Alarm

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

How to make an Excel Add-in Image 10

After adding, I used it on my file so:

How to make an Excel Add-in Image 11

This is when I used it on all the data.

How to make an Excel Add-in Image 12

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.


Hi, my name is Priya and I am a VBA expert. I self-learned this while working to a telecom giant We used to have a lot of data in form of subscriber info services call etc. To handle the same efficiently I came across this very interesting and helpful feature about excel and other office products.

1 Comment
  1. Reply
    TPN October 21, 2016 at 6:55 PM

    How could you do this so that no one could see the code and I could sell it to others?

    Leave a reply