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 additional 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 most important add-ins are Solver, Data Analysis (Analysis Tool pack), Analysis Tool Pack VBA.
If the add-ins are already unhidden in your excel then you must see all these add-ins in your Data tab.
How to Install Excel Add-Ins?
If your excel is not showing these options, follow the below steps to add add-ins.
- Step 1: Click on the FILE tab that is located at the topmost left corner of the excel.
- Step 2: After clicking on this FILE tab select Options.
- Step 3: Once you click on Options excel will open a separate window. From this window, select Add-Ins.
- Step 4: Find Mange: drop-down list at the bottom of the window and select Add-ins and click on Go…
- Step 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.
- Step 6: Now you can see SOLVER and Data Analysis options under the Data tab in the ribbon.
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 www.office.com
#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 excel using VBA Add-ins later in the post.
Data Analysis Tool Add-in
Under this tool pack, we can any kind of Data Analysis.
If you click on this Data Analysis you will see many types of analysis under this.
Usually, we create Add-Ins by using VBA macros.
How to Create Custom Functions in Excel and Install as an Excel Add-In
Ok 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.
Example #1 – How to Extract Comments from the Cells
In this example, I will show you how to extract comments from the cells.
- Step 1: Open the new workbook.
- Step 2: Press ALT + F11 (shortcut keys in excel to access Visual Basic Editor)
- Step 3: Go to Insert and insert New Module.
- 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
- Step 5: Once the code is entered save the file as Excel Add-in
- 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.
- Step 8: Select the Add-in file that you have saved.
- Step 9: Click Ok. You can see the new Add-in as per your workbook name. (I have named as Excel Add-In)
- 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 3 comments for myself. You can also create your own data.
- Step 12: Go to cell B1 and enter equals and start typing our Function name i.e TakeOutComment.
- Step 13: Select the cell A1 as the reference it will extract the comment from that cell.
In the cells A2 & A3 there are no comments that are why 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.
- Step 3: Copy and paste the below to the module.
Note: There are two macros here. Copy and paste both of them.
Dim As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name <> ActiveSheet.Name Then
Ws.Visible = xlSheetVeryHidden
- 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.
I have saved the file in the name of hiding All Worksheets.
- Step 6: Click on OK. You can see the new Add-in as per your workbook name. (I have named as hiding All Worksheets)
- Step 7: Now Right-click on the ribbon and select Customize Quick Access Toolbar
- 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.
- Step 9: Now you can see the little icon on your toolbar.
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.
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Save the file as Excel Add-in and this add-in to this sheet.
Now you can see another icon.
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 add-in section
- We can uninstall any add-in at any point in time.
- If you search in google you will get many add-ins
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 –