Make User Defined Functions Available to all Workbooks Using Excel Add-ins

You might already know how to create user defined functions or macros to achieve your day-to-day tasks in excel. Do you know how to make these precious little features make available to each workbook you open? This is where the option called Excel Add-ins comes into play.

According to Microsoft, an Excel add-in allows you to extend Excel application Functionality across multiple platforms.

You can easily use Add-ins to add user defined functions to Excel global functions list

Step 01: Save the Workbook as a Add-in

Save your Workbook which has the user defined function as a Excel Add-in (*.xlam)

Save as Add-ins

Step 02 : Open the Add-ins dialog box

Go to “File” Menu and select “Options”
Then click “Add-ins”

Alternative method for Excel 2019

  • Under the “Insert” tab, Find “Add-ins” Command group.
  • Click the little down arrow next to”My Add-ins”
  • Click the”Manage Other Add-ins” menu item in the presented drop down menu.
Excel Mange Add-ins

With the Excel Add-ins selected in the Manage drop-down box, click “Go”

Step 03 : Activate the required Add-in

In the Add-ins dialog box select the check box for the name matching with the file name you chose to save the user defined function.As I have saved my user defined function as “function.xlam”, my add-in name is “Function”. It will vary depend on your choice. Then click “Go”

Step 04: Insert your function through Excel functions list

  • Close the dialog box and the workbook
  • Now open a New Workbook or any of the already created Workbooks.
  • Go to Formulas → Insert function.
  • In the Insert function dialog box, select the category to “All”.
  • Now Search for your user defined function identifier. If you have follow all the steps correctly, you should have your new function in the list.

If you were stuck anywhere please drop a message. I will help through.

Leave a Reply

Close Menu