Get started developing Excel custom functions

With custom functions, developers can now 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 (version 8.0.0 or later)

  • 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.

  • Excel on Windows (version 1904 or later, connected to Office 365 subscription) or Excel on the web
  • Excel custom functions are supported in Office on Mac (connected to Office 365 subscription) and an update to this tutorial is forthcoming.

Note

Excel custom functions are not supported in Office 2019 (one-time purchase).

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.

Important

Spaces are not currently permitted in the add-in project name or anywhere in the folder path where you create your add-in project. If your add-in's project name or folder path contains spaces, the local web server won't start when you run npm start or npm run start:web. This limitation is temporary and will be eliminated when the underlying issue is resolved in the Yeoman generator for Office Add-ins.

  1. In a folder of your choice, run the following command and then answer the prompts as follows.

    yo office
    
    • Choose a project type: Excel Custom Functions Add-in project
    • Choose a script type: JavaScript
    • What do you want to name your add-in? starcount

    Yeoman generator for Office Add-ins prompts for custom functions

    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 starcount
    
  3. Build the project.

    npm run build
    

    Note

    Office Add-ins should use HTTPS, not HTTP, even when you are developing. If you are prompted to install a certificate after you run npm run build, accept the prompt to install the certificate that the Yeoman generator provides.

  4. Start the local web server, which runs in Node.js. You can try out the custom function add-in in Excel on the web or Windows. 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, 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

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.

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.

See also