How to Create and Use an Excel Add-in

An Excel add-in can be really useful when you have to run a macro often in different workbooks.

For example, suppose you want to highlight all the cells that have an error in it, you can easily create an Excel add-in that will highlight errors with a click of a button.

Something as shown below (the macro has been added to the Quick Access Toolbar to run it with a single click):

Create an Excel Add-in - Demo

Similarly, you may want to create a custom Excel function and use it in all the Excel workbooks, instead of copy pasting the code again and again.

If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training.

Creating an Excel Add-in

In this tutorial, you’ll learn how to create an Excel add-in. There are three steps to create an add-in and make it available in the QAT.

  • Write/Record the code in a module.
  • Save as an Excel Add-in.
  • Add the macro to the Quick Access Toolbar.

Write/Record the Code in a Module

In this example, we will use a simple code to highlight all the cells that have error values:

Sub HighlightErrors()
 Selection.SpecialCells(xlCellTypeFormulas, xlErrors).Select
 Selection.Interior.Color = vbRed
End Sub

If you are writing code (or copy-pasting it from somewhere), here are steps:

  • Open an Excel Workbook.
  • Press Alt + F11 to open the VB Editor Window.
  • In the VB Editor, you would see the workbook objects listed in the project explorer. If you can’t see that, go to View –> Project Explorer.Create an Excel Add-in - Project Explorer
  • Right-click on any of the objects in the workbook. Go to Insert option and click on ‘Module’. This will insert a module object.Create an Excel Add-in - Insert Module
  • Double-click on the module and enter the above code (copy-paste it).Create an Excel Add-in - Macro Code in Module Window
  • Press Alt+F11 to go back to the Excel Worksheet.

Note: If you are recording a macro, Excel automatically takes care of inserting a module and putting the code in it.

Now let’s go ahead and create an add-in out of this code.

Also read: How to Create QR Codes in Excel (QR Code Generator)

Save and Install the Add-in

Follow the below steps when you are in the workbook where you have inserted the code.

  • Click the File tab.
  • Click on ‘Save As’.Create an Excel Add-in - Save as
  • In the Save As dialogue box, change the ‘Save as’ type to .xlam. The name you assign to the file would be the name of your add-in. In this example, the file is saved with the name Highlight Errors.
    • You’ll notice that the path of the file where it gets saved automatically changes. You can use the default one or change it if you want.Create an Excel Add-in - Save as drop down
  • Open an Excel workbook and Go to Developer –> Add-ins –> Excel Add-ins.Create an Excel Add-in - add-in
  • In the Add-ins dialogue box, browse and locate the file that you saved, and click OK.Create an Excel Add-in - browse add-in

Now the add-in has been activated.

You may not see any tab or option appear in the ribbon, but the add-in gets activated at this stage and the code is available to be used now.

The next step is to add the macro to the Quick Access Toolbar so that you can run the macro with a single click.

Note: If you are creating an add-in that has a custom function, then you don’t need to go to step 3. By the end of step 2, you’ll have the function available in all the workbooks. Step 3 is for such codes where you want something to happen when you run the code (such as highlight cells with errors).

Also read: How to Remove (Disable) Add-Ins in Excel?

Adding Add-in to Quick Access Toolbar

To do this:

  • Right-click on any of the ribbon tabs and select Customize Quick Access Toolbar.Create an Excel Add-in - Customize Ribbon
  • In the Excel Options dialog box, Select Macros from the Choose commands from the drop-down. You’ll notice that the macro ‘HighlightErrors’ is listed there.Create an Excel Add-in - qat macro select
  • Click on the ‘HighlightErrors’ Macro and click on Add. This will add the macro to the list on the right.Create an Excel Add-in - Add to QAT
  • Click OK. This will add the macro to the Quick Access Toolbar.Create an Excel Add-in - Macro Icon in QAT

Now, to run this code in any workbook, select the dataset and click on the macro icon in the QAT.

Create an Excel Add-in - Demo

This will highlight all the cells with errors in red color. You can also use this macro in any workbook since you have enabled the add-in.

Caution: The changes done by the macro can’t be undone using Control + Z.

You can also create custom functions and then save it as an Excel add-in. Now, when you enable the add-in, the custom functions will be available in all your Excel workbooks.

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

14 thoughts on “How to Create and Use an Excel Add-in”

  1. I did all but my macro didn’t show in the macro list to be added to the QAT. I looked up and one site said macros in add-ins don’t show in macro list! Now what?

    Reply
  2. I am intrigued by the idea of the add in… but how is this different than putting vba code in a personal macro workbook?

    Reply
  3. I want the code in my add-in to run when I double-click any cell on any sheet. How do I capture that event in an add-in without requiring the user to add VBA to their own Workbook module?

    Reply
  4. Sumit,

    This is very good. It was just what I needed. Thanks.

    When I was searching for information on creating add-in modules, I did see couple of links to your website, but I was put off by the name. The orange asshole in the White House is so repulsive that I cannot stand to even see that name. I checked out every other link and only came back here because none of the others were any good.

    I may not be the only one who has this reaction. If you change the name of your site, you may get more traffic. How about something like Excel-Step-by-Step or ExcelUnraveled or ExcelYourWay or Excel4Everyone or EasyExcel or ExcelGuide?

    Anything but the name of that horrible monster.

    Reply
    • I was excited to read your posting. I even made a Facebook page called “I hate Trump Because He __________”. Nobody will ever tell me what he did. Do you know what he did? I’ve been searching for (4) years now. Twitter me: @chernipeski if you know something.

      Reply
  5. Hi sir i want to vba add-in compare two sheets if i have id no list sheet 1 ans sheet 2 also have same id but not same cell serch that id then check all data like name possition etc..if there is any speling mistek or any another typing mistake highlight both sheets…its possibul sir..please can u help me..

    Reply
  6. Is it possible to have the ‘ADD-IN’ as a macro button (for end users to click – more visible) on the excel file versus on the ribbon tab? Thanks

    Reply
  7. Dear Sir,

    Do you know how to modify the file name inside ‘add-ins dialogue box’? It is currently shown as ‘Highlight Errors’. Is there a way to change this name to something else and add a description under this name?

    Reply
  8. This sounds easier to manage than adding macros to Personal.xlsb like I am doing now. I have a couple computers I would like to keep the macros in sync for. Is it possible to store the .add-in file on One Drive and have both copies of Excel access the file? Both computers are set to have off-line access to One Drive, so the add-in file would always be available. That way, I can edit a macro in one location (I have one that needs to be edited every year to point to a new file) and have both computers access the most recent version.

    Reply

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster