Get started developing Excel custom functions
If you aren't familiar with Node.js or npm, you should start by setting up your development environment.
npm install -g yo generator-office
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 a Microsoft 365 subscription) or Excel on the web
- Excel custom functions are supported in Office on Mac (connected to a Microsoft 365 subscription) and an update to this tutorial is forthcoming.
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.
Run the following command to create an add-in project using the Yeoman generator:
When you run the
yo officecommand, 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 Add-in project
- Choose a script type:
- What do you want to name your add-in?
The Yeoman generator will create the project files and install supporting Node components.
- Choose a project type:
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.
Build the project.
npm run build
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.
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
In your Excel workbook, try out the
ADD custom function by completing the following steps:
Select a cell and type
=CONTOSO. Notice that the autocomplete menu shows the list of all functions in the
CONTOSO.ADDfunction, using numbers
200as input parameters, by typing the value
=CONTOSO.ADD(10,200)in the cell and pressing enter.
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.
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.