Skip to content

Latest commit

 

History

History
103 lines (62 loc) · 5.54 KB

excel-custom-functions-quickstart.md

File metadata and controls

103 lines (62 loc) · 5.54 KB
ms.date description title ms.service ms.localizationpriority
11/16/2022
Developing custom functions in Excel quick start guide.
Custom functions quick start
excel
high

Get started developing Excel custom functions

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

[!includeSet up requirements] [!includeYeoman generator prerequisites]

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

    [!NOTE] If you don't already have Office, you can join the Microsoft 365 developer program to get a free, 90-day renewable Microsoft 365 subscription to use during development.

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. [!includeYeoman generator create project guidance]

    • 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

    :::image type="content" source="../images/yo-office-excel-cf-quickstart.png" alt-text="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.

  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.

Excel on Windows or Mac

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

[!INCLUDE alert use https]

Excel on the web

To test your add-in in Excel on the web, run the following command. When you run this command, the local web server will start. Replace "{url}" with the URL of an Excel document on your OneDrive or a SharePoint library to which you have permissions.

[!INCLUDE npm start:web command syntax]

[!INCLUDE alert use https]

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.

[!includeManually register an add-in]

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.

[!div class="nextstepaction"] 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.

:::image type="content" source="../images/custom-function-already-exists-error.png" alt-text="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