What are Add-ins in Excel?
An add-in is an extension that adds more features and options to the existing Microsoft Excel. By providing additional functions to the user, it increases the power of Excel. An add-in needs to be enabled for usage. Once enabled, it activates as Excel is started.
For example, an excel add-in can perform tasks like creating, deleting, and updating the data of a workbook. Moreover, with add-ins, one can add buttons to the Excel ribbon and run custom functions.
The Solver, Data Analysis (Analysis ToolPakAnalysis ToolPakExcel's data analysis toolpak can be used by users to perform data analysis and other important calculations. It can be manually enabled from the addins section of the files tab by clicking on manage addins, and then checking analysis toolpak.), and Analysis ToolPak-VBA are some essential add-ins.
The purposes of activating add-ins are listed as follows:
- To interact with the objects of Excel
- To avail an extended range of functions and buttons
- To facilitate setting up of standard add-ins throughout an organization
- To serve the varied needs of a broad audience
In Excel, several add-ins can be accessed from “add-ins” under the “options” button of the File tab. For more add-ins, one can select from the drop-down of “manage” in the “add-ins” window.
It is possible that some add-ins may be hidden, by default. The unhidden add-ins can be viewed in the Data tab on the Excel ribbonExcel RibbonThe ribbon is an element of the UI (User Interface) which is seen as a strip that consists of buttons or tabs; it is available at the top of the excel sheet. This option was first introduced in the Microsoft Excel 2007.. The same is shown in the following image.
How to Install Add-ins in Excel?
In case Excel is not displaying the add-ins, they need to be installed. The steps to install excel add-ins are listed as follows:
- Click the File tab located at the top left corner of Excel.
- Click “options,” as shown in the following image.
- The “Excel options” window opens. Select “add-ins.”
- At the bottom, there is a box to the right of “manage.” Click the arrow to view the drop-down menu. Select “Excel add-ins” and click “go.”
- The “add-ins” dialog box appears. Select the required checkboxes and click “Ok.” We have selected all four add-ins.
- The “data analysis” and “solver” options appear under the Data tab of the Excel ribbon.
Types of Add-ins in Excel
The types of add-ins are listed as follows:
- Inbuilt add-ins: These are built into the system. They can be unhidden by performing the steps listed under the preceding heading (how to install add-ins in Excel?).
- Downloadable add-ins: These can be downloaded from the Microsoft website (www.office.com).
- Custom add-ins: These are designed to support the basic functionality of Excel. They may be free or chargeable.
The Data Analysis Add-in
The Data Analysis tool pack is used for analyzing data related to statistics, finance, and engineering.
The various tools available under the Data Analysis add-in are shown in the following image.
Create Custom Functions and Install as an Excel Add-in
Generally, an add-in is created with the help of 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 process.. Let us learn to create an add-in (in all Excel files) for a custom functionCustom 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. . For this, first, we create the custom function.
Let us consider some examples.
Example #1–Extract Comments from the Cells of Excel
We want to extract comments from certain cells of Excel. Create an add-in for the same.
The steps for creating an add-in and extracting comments from cells are listed as follows:
Step 1: Open a new workbook.
Step 2: Press the shortcut “ALT+F11” to access the Visual Basic Editor. The following image shows the main screen of Microsoft Visual Basic for Applications.
Step 3: Click “module” under the Insert tab, as shown in the following image.
Step 4: Enter the following code in the “module” window.
Function TakeOutComment(CommentCell As Range) As String
TakeOutComment = CommentCell.Comment.Text
Step 5: Once the code is entered, save the file with the type “Excel add-in.”
Step 6: Open the file containing comments.
Step 7: In the File tab, select “options.” Choose “add-ins.” In the box to the right of “manage,” select “Excel add-ins.” Click “go.”
Click the “browse” option in the “add-ins” dialog box.
Step 8: Select the add-in file that had been saved. Click “Ok.”
We had saved the file with the name “Excel add-in.”
Step 9: The name of the workbook (Excel add-in) that had been saved appears as an add-in, as shown in the following image.
This add-in can be applied as an Excel formula to extract comments.
Step 10: Go to the sheet containing comments. The names of three cities are appearing with comments, as shown in the following image.
Step 11: In cell B1, enter the symbol “equal to” followed by the name of the function. Type “TakeOutComment,” as shown in the following image.
Step 12: Select cell A1 as the reference. This extracts the comment from the mentioned cell.
Since there are no comments in cells A2 and A3, the formula returns “#VALUE!.”
Example #2–Hide Worksheets in Excel
We want to hide Excel worksheets except for the active sheet. Create an add-in and icon on the Excel toolbar for the same.
The steps to hide worksheets (except for the currently active sheet) and thereafter create an add-in and icon are listed as follows:
Step 1: Open a new workbook.
Step 2: In the Visual Basic window, insert a “module” from the Insert tab. The same is shown in the following image.
Step 3: Copy and paste the following code to the module.
Dim As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name <> ActiveSheet.Name Then
Ws.Visible = xlSheetVeryHidden
Step 4: Save this workbook with the type “Excel add-in.”
Step 5: Add this add-in to the new workbook. For this, click “options” under the File tab. Select “add-ins.” In the box to the right of “manage,” select “Excel add-ins.” Click “go.”
In the “add-ins” window, choose “browse.”
Step 6: Select the saved add-in file. Click “Ok.”
We have saved the file with the name “hide all worksheets.”
Step 7: The new add-in “hide all worksheets” appears in the “add-ins” window.
Step 8: Right-click the Excel ribbon and select “customize the ribbon.”
Step 9: The “Excel options” window appears. Click “quick access toolbar.” Under the drop-down of “choose commands from,” select “macrosMacrosMacro in excel is a series of instructions in code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. .”
In the box following this drop-down, choose the name of the macro. Click “add” followed by “Ok.” The tasks of this step are shown with the help of black arrows in the following image.
Step 10: A small icon appears on the toolbar. Clicking this icon hides all worksheets except for the currently active sheet.
Example #3–Unhide the Hidden Sheets of Excel
We want to unhide the sheetsUnhide The SheetsThere are different methods to Unhide Sheets in Excel as per the need to unhide all, all except one, multiple, or a particular worksheet. You can use Right Click, Excel Shortcut Key, or write a VBA code in Excel. that were hidden in the preceding example (example #2). Create an add-in and toolbar icon for the same.
The steps to unhide the sheets and thereafter create an add-in and toolbar icon are listed as follows:
Step 1: Copy and paste the following code to the “module” inserted in Microsoft Visual Basic for Applications.
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Step 2: Save the file as “Excel add-in.” Add this add-in to the sheet.
Right-click the Excel ribbon and choose the option “customize the ribbon.” In “quick access toolbar,” select “macros” under the drop-down of “choose commands from.”
Choose the name of the macro, click “add” and “Ok.” The tasks of this step are shown with the help of black arrows in the following image.
Step 3: Another icon appears on the toolbar. Clicking this icon unhides the hidden worksheets.
The Cautions While Creating Add-ins
The points to be observed while working with add-ins are listed as follows:
- Remember to save the file in the “add-in” extensionExtensionExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code. of Excel.
- Be careful while selecting the add-ins to be inserted by browsing in the “add-ins” window.
Note: It is possible to uninstall the unnecessary add-ins at any point of time.
Frequently Asked Questions
An add-in extends the functions of Excel. It provides more features to the user. It is also possible to create custom functions and insert them as an add-in in Excel.
An add-in can be created, used, and shared with an audience. The add-ins can be found in the “add-ins” window of Excel.
The steps to access the add-ins in Excel are listed as follows:
a. Click “options” in the File tab of Excel. Select “add-ins.”
b. In the box to the right of “manage,” select “Excel add-ins.” Click “go.”
c. The “add-ins” window opens. Click “browse.”
d. Select the required add-in file and click “Ok.”
Note: The add-ins already present in the system can be accessed by browsing. To activate an add-in, select the corresponding checkbox in the “add-ins” window and click “Ok.”
To remove an add-in from the Excel ribbon, it needs to be inactivated. The steps to inactivate an add-in of Excel are listed as follows:
a. In the File tab, click “options” and choose “add-ins.”
b. From the drop-down menu of the “manage” box, select “Excel add-ins.” Click “go.”
c. In the “add-ins” window, deselect the checkboxes of the add-ins to be inactivated. Click “Ok.”
The deselected add-ins are inactivated. Sometimes, one may need to restart Excel after inactivation. This helps remove the add-in from the ribbon.
Note: Inactivation does not remove an add-in from the computer. To remove an inactivated add-in from the computer, it needs to be uninstalled.
The steps to add an add-in to the Excel toolbar are listed as follows:
a. In an Excel workbook, press “Alt+F11” to open the Visual Basic Editor. Enter the code by inserting a “module.”
b. Press “Alt+F11” to return to Excel. Save the file as “Excel add-in” (.xlam).
c. In File, select “options” followed by “add-ins.” Select “Excel add-ins” in the “manage” box and click “go.”
d. Browse this file in the “add-ins” window. Select the required checkbox and click “Ok.”
e. Right-click the ribbon, choose “customize the ribbon.” Click “quick access toolbar.”
f. Select “macros” from the drop-down of “choose commands from.”
g. Choose the required macro, click “add” and “Ok.”
The icon appears on the toolbar. This icon works in all Excel workbooks as the add-in has been enabled.
This has been a step-by-step guide to create, install, and use add-ins in Excel. Here, we also discuss the types of Excel add-ins & creating a custom function. Take a look at these useful functions of Excel–