Add-Ins in excel

Excel Add-Ins (2007, 2010, 2013, 2016)

Add-ins are different extensions of excel which when enabled they activate when the excel is started and a user can use its functions, excel has various different add-ins and they are in the options section of the file tab, the first box shows the enabled add-ins in the system and if the user wants to enable more add-ins we need to click on manage add-ins.

Add-In looks like a new word if you do not have any idea of additional excel features. Excel Add-in is nothing but additional features and options added to your existing Microsoft Excel. Adding different functions to you will help you in terms of new features advantages. I would say Add-in is a kind of custom function that increases the power of Excel.

Some of the Add-ins are readily available in excel but are hidden in default excel. Some of the essential add-ins are Solver, Data Analysis (Analysis Tool pack), Analysis Tool Pack VBA.

If the add-ins are already unhidden in your excel, you must see all these add-ins in your Data tab.

Excel Ad-Ins 1

How to Install Excel Add-Ins?

If your excel is not showing these options, follow the below steps to add add-ins.

  1. Click on the FILE tab located at the top left corner of the excel.

    Excel Ad-Ins 1

  2. After clicking on this FILE tab, select Options.

    Excel Ad-Ins 2

  3. Once you click on Options, excel will open a separate window. From this window, select Add-Ins.

    Excel Ad-Ins 3

  4. Find Mange: drop-down list at the bottom of the window and select Add-ins and click on Go.

    Excel Ad-Ins step 4

  5. After clicking on “Go,” it will show you below the dialogue box. You can select all of the add-ins you want. I have selected all 4.

    Excel Ad-Ins step 5

  6. Now, you can see SOLVER and Data Analysis options under the Data tab in the ribbon.

    Excel Ad-Ins step 6

Types of Add-Ins

#1 – Inbuilt

These are inbuilt add-ins, and you can unhide them by following the above steps.

#2 – Downloadable

We can download many Add-INS from Microsoft’s website

#3 – Custom

These Add-Ins are built by individuals who regularly work on Excel Macros. Some of them are free, and some of them are involved cost to use them. These are designed to support the basic functionality of the excel. We will look at how to create custom functions in excelCustom Functions In ExcelCustom Functions, also known as UDF (User Defined Functions) in Excel, are personalized functions that the users create through VBA programming code to fulfill their particular requirements. read more using VBA Add-ins later in the post.

Data Analysis Tool Add-in

Under this tool pack, we can make any Data Analysis.

Excel Ad-Ins - Analysis Tool Pack

If you click on this Data Analysis, you will see many types of analysis under this.

Excel Ad-Ins - Analysis Tool Pack 1

Usually, we create Add-Ins by using VBA macrosVBA MacrosVBA Macros are the lines of code that instruct the excel to do specific tasks, i.e., once the code is written in Visual Basic Editor (VBE), the user can quickly execute the same task at any time in the workbook. It thus eliminates the repetitive, monotonous tasks and automates the more.

How to Create Custom Functions in Excel and Install as an Excel Add-In

In this article, I will show you a simple custom function that we can create and add that as an Add-In to our all excel files.

You can download this Excel Add-Ins Excel Template here – Excel Add-Ins Excel Template

Example #1 – How to Extract Comments from the Cells

In this example, I will show you how to extract comments from the cells.

Excel Add-Ins - example 1
  • Step 3: Go to Insert and insert New Module.
Excel Add-Ins - example 1-1
  • Step 4: Once you have inserted the Module apply the below code to the module.

Function TakeOutComment(CommentCell As Range) As String

TakeOutComment = CommentCell.Comment.Text

End Function

Excel Add-Ins - example 1-2
  • Step 5: Once the code is entered, save the file as Excel Add-in
Excel Add-Ins - example 1-3
  • Step 6: Now open the file that has comments.
  • Step 7: Go to File > Options > Add-Ins > Excel Add-in > Go and click on Browse option.
Excel Add-Ins - example 1-4
  • Step 8: Select the Add-in file that you have saved.
Excel Add-Ins - example 1-5
  • Step 9: Click on OK. You can see the new Add-in as per your workbook name. (I have named as Excel Add-In)
Excel Add-Ins - example 1-6
  • Step 10: Right now, you will not see this add-in. However, you can apply it as an excel formula and extract comments.
  • Step 11: Now go to the Comment listed sheet. I have created three comments for myself. You can also create your data.
Excel Add-Ins - example 1-7
  • Step 12: Go to cell B1 and enter equals and start typing our Function name, i.e., TakeOutComment.
Excel Add-Ins - example 1-8
  • Step 13: Select the cell A1 as the reference. It will extract the comment from that cell.
Excel Add-Ins - example 1-9

In the cells A2 & A3, there are no comments; that is why the formula returned the value as #VALUE!

Example #2 – How to Hide Worksheets in Excel?

In this example, I will show you how to hide worksheets in excel except for the active sheet and add that as an Add-In to the excel.

  • Step 1: Open a new workbook.
  • Step 2: Go to the Visual Basic window and insert a new Module.
Excel Add-Ins - example 1-1
  • Step 3: Copy and paste the below to the module.

Note: There are two macros here. Please copy and paste both of them.

Sub Hide_All_Worksheets_()
Dim As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name <> ActiveSheet.Name Then

Ws.Visible = xlSheetVeryHidden
End If
Next Ws

End Sub

Excel Add-Ins - example 1-10
  • Step 4: Save this workbook as Excel Add-in.
  • Step 5: Add this Add-In to the new workbook. Go to File > Options > Add-in > Go > Browse.
Excel Add-Ins - example 1-11

I have saved the file in the name of hiding All Worksheets.

example 1-12
  • Step 6: Click on OK. You can see the new Add-in as per your workbook name. (I have named as hiding All Worksheets)
example 1-13
  • Step 7: Now Right-click on the ribbon and select Customize Quick Access Toolbar
example 1-14
  • Step 8: Click on Quick Access Toolbar and select Macro from the first drop down and choose the macro name, then Click on Add Button and Click on OK.
example 1-15
  • Step 9: Now, you can see the little icon on your toolbar.
example 1-16

If you click on that icon, it will hide all the worksheets except the one you are in right now.

Example #3 – How to Unhide those Hidden Sheets?

In this example, I will show you how to unhide those hidden sheets. Follow the same procedure and copy-paste the below code.

Sub UnHide_All_HiddenSheets_()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Next Ws

End Sub

example 1-17

Save the file as Excel Add-in and this add-in to this sheet.

example 1-18

Now you can see another icon.

example 1-19

If you click on this icon, it will Unhide all the hidden sheets.

Things to Remember

  • We need to save the file in the extension of Excel.
  • We can add any Add-Ins by browsing them under the add-in section.
  • We can uninstall any add-in at any point in time.
  • If you search on google, you will get many add-ins.

Recommended Articles

This has been a step by step Guide to Create, Install, and Use Add-Ins in Excel. Here we discuss types of excel add-ins and how to create a custom function and install that as an Add-In, along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion