Tutorial: Create custom functions in Excel

Custom functions enable you to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Users within Excel can access custom functions as they would any native function in Excel, such as SUM(). You can create custom functions that perform simple tasks like calculations or more complex tasks such as streaming real-time data from the web into a worksheet.

In this tutorial, you will:

  • Create a custom function add-in using the Yeoman generator for Office Add-ins.
  • Use a prebuilt custom function to perform a simple calculation.
  • Create a custom function that gets data from the web.
  • Create a custom function that streams real-time data from the web.


  • Node.js (the latest LTS version)

  • 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


    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) or Excel on the web.

Create a custom functions project

To start, create the code project to build your custom function add-in. The Yeoman generator for Office Add-ins will set up your project with some prebuilt custom functions that you can try out. If you've already run the custom functions quick start and generated a project, continue to use that project and skip to this step instead.

  1. Run the following command to create an add-in project using the Yeoman generator.

    yo office


    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 Add-in project
    • Choose a script type: JavaScript
    • What do you want to name your add-in? starcount

    Screenshot of 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.


    You can ignore the next steps guidance that the Yeoman generator provides after the add-in project's been created. The step-by-step instructions within this article provide all of the guidance you'll need to complete this tutorial.

  2. Navigate to the root folder of the project.

    cd starcount
  3. 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.

  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.

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

Try out a prebuilt custom function

The custom functions project that you created contains some prebuilt custom functions, defined within the ./src/functions/functions.js file. The ./manifest.xml file specifies that all custom functions belong to the CONTOSO namespace. You'll use the CONTOSO namespace to access the custom functions in Excel.

Next, try out the ADD custom function by completing the following steps.

  1. In Excel, go to any cell and enter =CONTOSO. Notice that the autocomplete menu shows the list of all functions in the CONTOSO namespace.

  2. Run the CONTOSO.ADD function, with 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 provided and returns the result of 210.

Create a custom function that requests data from the web

Integrating data from the Web is a great way to extend Excel through custom functions. Next you'll create a custom function named getStarCount that shows how many stars a given Github repository possesses.

  1. In the starcount project, find the file ./src/functions/functions.js and open it in your code editor.

  2. In function.js, add the following code.

      * Gets the star count for a given Github repository.
      * @customfunction 
      * @param {string} userName string name of Github user or organization.
      * @param {string} repoName string name of the Github repository.
      * @return {number} number of stars given to a Github repository.
      async function getStarCount(userName, repoName) {
        try {
          //You can change this URL to any web request you want to work with.
          const url = "https://api.github.com/repos/" + userName + "/" + repoName;
          const response = await fetch(url);
          //Expect that status code is in 200-299 range
          if (!response.ok) {
            throw new Error(response.statusText)
            const jsonResponse = await response.json();
            return jsonResponse.watchers_count;
        catch (error) {
          return error;
  3. Run the following command to rebuild the project.

    npm run build
  4. Complete the following steps (for Excel on the web, Windows, or Mac) to re-register the add-in in Excel. You must complete these steps before the new function will be available.

  1. Close Excel and then reopen Excel.

  2. In Excel, choose the Insert tab and then choose the down-arrow located to the right of My Add-ins. Screenshot of the Insert ribbon in Excel on Windows, with the My Add-ins down-arrow highlighted.

  3. In the list of available add-ins, find the Developer Add-ins section and select the starcount add-in to register it. Screenshot of the Insert ribbon in Excel on Windows, with the Excel Custom Functions add-in highlighted in the My Add-ins list.

Create a streaming asynchronous custom function

The getStarCount function returns the number of stars a repository has at a specific moment in time. Custom functions also return data that is continuously changing. These functions are called streaming functions. They must include an invocation parameter which refers to the cell that called the function. The invocation parameter is used to update the contents of the cell at any time.

In the following code sample, notice that there are two functions, currentTime and clock. The currentTime function is a static function that doesn't use streaming. It returns the date as a string. The clock function uses the currentTime function to provide the new time every second to a cell in Excel. It uses invocation.setResult to deliver the time to the Excel cell and invocation.onCanceled to handle function cancellation.

The starcount project already contains the following two functions in the ./src/functions/functions.js file.

 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
function currentTime() {
  return new Date().toLocaleTimeString();
 * Displays the current time once a second
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
  }, 1000);
  invocation.onCanceled = () => {

To try out the functions, type the text =CONTOSO.CLOCK() in cell C1 and press enter. You should see the current date, which streams an update every second. While this clock is just a timer on a loop, you can use the same idea of setting a timer on more complex functions that make web requests for real-time data.

Next steps

Congratulations! You've created a new custom functions project, tried out a prebuilt function, created a custom function that requests data from the web, and created a custom function that streams data. Next, you can modify your project to use a shared runtime, making it easier for your function to interact with the task pane. Follow the steps in the following article.