Edit

Get started developing Excel custom functions

  • Article

With custom functions, developers can add new functions to Excel by defining them in JavaScript or TypeScript as part of an add-in. Excel users can access custom functions just as they would any native function in Excel, such as SUM().

Prerequisites

  • Node.js (the latest LTS version). Visit the Node.js site to download and install the right version for your operating system.

  • The latest version of Yeoman and the Yeoman generator for Office Add-ins. To install these tools globally, run the following command via the command prompt.

    npm install -g yo generator-office

    Note

    Even if you've previously installed the Yeoman generator, we recommend you update your package to the latest version from npm.

  • Office connected to a Microsoft 365 subscription (including Office on the web).

    Note

    If you don't already have Office, you might qualify for a Microsoft 365 E5 developer subscription through the Microsoft 365 Developer Program; for details, see the FAQ. Alternatively, you can sign up for a 1-month free trial or purchase a Microsoft 365 plan.

Build your first custom functions project

To start, you'll use the Yeoman generator to create the custom functions project. This will set up your project with the correct folder structure, source files, and dependencies to begin coding your custom functions.

  1. Run the following command to create an add-in project using the Yeoman generator. A folder that contains the project will be added to the current directory.

    yo office

    Note

    When you run the yo office command, you may receive prompts about the data collection policies of Yeoman and the Office Add-in CLI tools. Use the information that's provided to respond to the prompts as you see fit.

    When prompted, provide the following information to create your add-in project.

    • Choose a project type: Excel Custom Functions using a Shared Runtime
    • Choose a script type: JavaScript
    • What do you want to name your add-in? My custom functions add-in

    The Yeoman Office Add-in generator command line interface prompts for custom functions projects.

    The Yeoman generator will create the project files and install supporting Node components.

  2. The Yeoman generator will give you some instructions in your command line about what to do with the project, but ignore them and continue to follow our instructions. Navigate to the root folder of the project.

    cd "My custom functions add-in"

  3. Build the project.

    npm run build

  4. Start the local web server, which runs in Node.js. You can try out the custom function add-in in Excel. You may be prompted to open the add-in's task pane, although this is optional. You can still run your custom functions without opening your add-in's task pane.

To test your add-in in Excel on Windows or Mac, run the following command. When you run this command, the local web server will start and Excel will open with your add-in loaded.

npm run start:desktop

Note

Office Add-ins should use HTTPS, not HTTP, even while you're developing. If you're prompted to install a certificate after you run one of the following commands, accept the prompt to install the certificate that the Yeoman generator provides. You may also have to run your command prompt or terminal as an administrator for the changes to be made.

Try out a prebuilt custom function

The custom functions project that you created by using the Yeoman generator contains some prebuilt custom functions, defined within the ./src/functions/functions.js file. The ./manifest.xml file in the root directory of the project specifies that all custom functions belong to the CONTOSO namespace.

In your Excel workbook, try out the ADD custom function by completing the following steps.

  1. Select a cell and type =CONTOSO. Notice that the autocomplete menu shows the list of all functions in the CONTOSO namespace.

  2. Run the CONTOSO.ADD function, using numbers 10 and 200 as input parameters, by typing the value =CONTOSO.ADD(10,200) in the cell and pressing enter.

The ADD custom function computes the sum of the two numbers that you specify as input parameters. Typing =CONTOSO.ADD(10,200) should produce the result 210 in the cell after you press enter.

If the CONTOSO namespace isn't available in the autocomplete menu, take the following steps to register the add-in in Excel.

  1. In the Excel ribbon, select Home > Add-ins.

  2. Under the Developer Add-ins section, select My custom functions add-in to register it.

    The My Add-ins dialog that shows active add-ins, with the My custom function add-in button highlighted.

Next steps

Congratulations, you've successfully created a custom function in an Excel add-in! Next, build a more complex add-in with streaming data capability. The following link takes you through the next steps in the Excel add-in with custom functions tutorial.

Excel custom functions add-in tutorial

Troubleshooting

You may encounter issues if you run the quick start multiple times. If the Office cache already has an instance of a function with the same name, your add-in gets an error when it sideloads. You can prevent this by clearing the Office cache before running npm run start.

An error message in Excel titled 'Error installing functions'. It contains the text 'This add-in wasn't installed because a custom function with the same name already exists'.

See also