Documentation needed for Range.numberFormat supported formats

[robertmclaws]

Hello! I'm having a really hard time getting my head around the supported number formats... any chance you could document the Range.numberFormat() method with the types of supported formats? For example, I really need a currency format with zero decimals, and there's nothing on how to do that.

Thanks!

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 91eea38b-5448-965d-f938-c3d13db9dccf
• Version Independent ID: 09f81d36-d60d-3b72-467f-c9335c1e91d0
• Content: Excel.Range class - Office Add-ins
• Content Source: docs/docs-ref-autogen/excel/excel/excel.range.yml
• Product: excel
• Technology: add-ins
• GitHub Login: @o365devx
• Microsoft Alias: o365devx

[AlexJerabek]

Hi @robertmclaws,

Thanks for the question. The JavaScript APIs support any number format supported by Excel. This is not a finite list. There are a few helpful articles for Excel in general:

• Format numbers
• Create a custom number format
• Review guidelines for customizing a number format

If you already have the number format ready in an Excel workbook, you can use Script Lab to log the number format, then add that string into your own add-in. Stack Overflow is also a good place to ask these sort of specific "how to" questions.

I'll tag @alison-mk to see if we improve sample here and the section in the corresponding Range article.

[AlexJerabek]

@robertmclaws,
For you particular scenario, does this produce the number format you're looking for?
myRange.numberFormat = [["$#"]];

[robertmclaws]

Hey Alex, thanks for the notes. The last link in your first post is the only one that helps construct number formatting strings.

In regards to why I opened up a ticket here vs asking on Stack Overflow is because displaying and formatting numbers is a VERY large part of dealing with Excel, but the mechanics of doing that programmatically get very little attention in these docs. For example, very little explanation is given for why Range.numberFormatCategories is read-only in the API when it is settable in the UI, or what the different Range.numberFormat outputs would look like by default if you selected a specific Number Format from the dropdown in the UI.

IMO, there needs to be a whole TOC entry at this level on how to deal with formatting, decimal places, how do deal with setting formats in Tables (using getDataBodyRange() instead of the whole Table range, etc. For example, you have a section on Conditional Formatting, but not cell data formatting. Would be easy to rectify.

HTH, and thanks for your time. :)

[alison-mk]

Hi @robertmclaws, thank you for taking the time to provide this detailed feedback! I've recorded it in an internal work item and plan to incorporate your suggestions. I'm not sure of the timeline, so I will leave this issue open for now.

Thanks again,
Alison

Internal tracking info: work item 4700521

[robertmclaws]

You folks are awesome, thanks so much, and hope you have a great holiday!

[LiuXiao617111]

Hi Team, Any progress about this document. this function is very useful but the document is very deficient.
Why Range.numberFormatCategories is read-only?