Create custom functions in Excel - Office Add-ins (original) (raw)

Custom functions enable developers 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 just as they would any native function in Excel, such as SUM().

Note

Custom function is a general term that is interchangeable with user-defined function. Both terms apply to VBA, COM, and Office.js add-ins. The Office Add-ins documentation uses the term custom function when referring to custom functions that use Office JavaScript APIs.

Important

Note that Excel custom functions are available on the following platforms.

• Office on the web
• Office on Windows
  • Microsoft 365 subscription
  • retail perpetual Office 2016 and later
  • volume-licensed perpetual Office 2021 and later
• Office on Mac

Excel custom functions aren't currently supported in the following:

• Office on iPad
• volume-licensed perpetual versions of Office 2019 or earlier on Windows

Note

The unified manifest for Microsoft 365 doesn't currently support custom functions projects. You must use the add-in only manifest for custom functions projects. For more information, see Office Add-ins manifest.

The following animated image shows your workbook calling a function you've created with JavaScript or TypeScript. In this example, the custom function =MYFUNCTION.SPHEREVOLUME calculates the volume of a sphere.

The following code defines the custom function =MYFUNCTION.SPHEREVOLUME.

/**
 * Returns the volume of a sphere.
 * @customfunction
 * @param {number} radius
 */
function sphereVolume(radius) {
  return Math.pow(radius, 3) * 4 * Math.PI / 3;
}

How a custom function is defined in code

If you use the Yeoman generator for Office Add-ins to create an Excel custom functions add-in project, it creates files which control your functions and task pane. We'll concentrate on the files that are important to custom functions.