Number
The number to format.
DecimalPlacesMinimum
The minimum number of decimal places a number should be formatted with. Must be greater than or equal to 0 and less than or equal to the maximum number of decimal places. If omitted, 0 is assumed.
DecimalPlacesMaximum
The maximum number of decimal places a number should be formatted with. Must be greater than or equal to the minimum number of decimal places. If this parameter is omitted and the DecimalPlacesMinimum parameter is also omitted, this parameter is set to 2. If this parameter is omitted, and the DecimalPlacesMinimum parameter is not omitted, this parameter is set to that parameter. If this parameter is less than the DecimalPlacesMinimum parameter, this parameter is set to that parameter.
SkipGrouping
FALSE to include thousands separators and TRUE to exclude them. If omitted, FALSE is assumed.
IntegerDigitsMinimum
The minimum number of digits the integer part should be formatted with. If this parameter is greater than the number of digits of the integer part, it is padded with zeroes, meaning that 123 formatted with a minimum of five integer digits is rendered as "00123." If omitted, 1 is assumed.
RoundingMode
The mode to use when rounding numbers. These are the supported values:
| Formula | Description |
|---|---|
| RoundingMode.CeilingRoundingMode,Ceiling | Rounds towards positive infinity. For example, 2.1 becomes 3 and -2.1 becomes -2. |
| RoundingMode.FloorRoundingMode,Floor | Rounds towards negative infinity. For example, 2.9 becomes 2 and -2.9 becomes -3. |
| RoundingMode.ExpandRoundingMode,Expand | Rounds away from zero. For example, 2.1 becomes 3 and -2.1 becomes -3. |
| RoundingMode.TruncateRoundingMode,Truncate | Rounds towards zero. For example, 2.9 becomes 2 and -2.9 becomes -2. |
| RoundingMode.HalfCeilingRoundingMode,HalfCeiling | Rounds to the nearest neighbor, rounding towards positive infinity on ties. For intance, 2.5 becomes 3 and -2.5 becomes -2. |
| RoundingMode.HalfFloorRoundingMode,HalfFloor | Rounds to the nearest neighbor, rounding towards negative infinity on ties. For example, 2.5 becomes 2 and -2.5 becomes -3. |
| RoundingMode.HalfExpandRoundingMode,HalfExpand (default) | Rounds to the nearest neighbor. If the value is exactly halfway between two numbers, it rounds away from zero. For example, 2.5 becomes 3 and -2.5 becomes -3. |
| RoundingMode.HalfTruncateRoundingMode,HalfTruncate | Rounds to the nearest neighbor. If the value is exactly halfway between two numbers, it rounds towards zero. For example, 2.5 becomes 2 and -2.5 becomes -2. |
| RoundingMode.HalfEvenRoundingMode,HalfEven | Rounds to the nearest neighbor, rounding to the nearest even number on ties. This mode is known as Banker's Rounding because it is, on average, free of bias. For example, 2.5 becomes 2 and 3.5 becomes 4. |
Locale
The locale to use when doing the conversion.
A locale represents a language and possibly a region. If omitted, the configured locale is used.
Use a value such as Locale.EnglishLocale,English to provide a locale (the full list is below). If your chosen locale is not part of the list, use the LOCALE function instead.
All locales may not be available. If a locale is not available, a fallback locale can be used. To provide such fallback locales, use the LOCALE function.
The LOCALE function accepts so-called Unicode Locale Identifiers. Every value in the list below corresponds to such an identifier.
| Formula | Unicode Locale Identifier | Description |
|---|---|---|
| Locale.ArabicLocale,Arabic | ar | Arabic |
| Locale.BulgarianLocale,Bulgarian | bg | Bulgarian |
| Locale.ChineseCnLocale,ChineseCn | zh-CN | Chinese (simplified), as used in mainland China |
| Locale.ChineseTwLocale,ChineseTw | zh-TW | Chinese (traditional), as used in Taiwan |
| Locale.CroatianLocale,Croatian | hr | Croatian |
| Locale.CzechLocale,Czech | cs | Czech |
| Locale.DanishLocale,Danish | da | Danish |
| Locale.DutchLocale,Dutch | nl | Dutch |
| Locale.DutchBeLocale,DutchBe | nl-BE | Dutch, as used in Belgium |
| Locale.EnglishLocale,English | en | English |
| Locale.EnglishAuLocale,EnglishAu | en-AU | English, as used in Australia |
| Locale.EnglishCaLocale,EnglishCa | en-CA | English, as used in Canada |
| Locale.EnglishInLocale,EnglishIn | en-IN | English, as used in India |
| Locale.EnglishNzLocale,EnglishNz | en-NZ | English, as used in New Zealand |
| Locale.EnglishUkLocale,EnglishUk | en-GB | English, as used in the United Kingdom |
| Locale.EnglishUsLocale,EnglishUs | en-US | English, as used in the United States of America |
| Locale.EnglishZaLocale,EnglishZa | en-ZA | English, as used in South Africa |
| Locale.FinnishLocale,Finnish | fi | Finnish |
| Locale.FrenchLocale,French | fr | French |
| Locale.FrenchBeLocale,FrenchBe | fr-BE | French, as used in Belgium |
| Locale.FrenchCaLocale,FrenchCa | fr-CA | French, as used in Canada |
| Locale.FrenchChLocale,FrenchCh | fr-CH | French, as used in Switzerland |
| Locale.GermanLocale,German | de | German |
| Locale.GermanAtLocale,GermanAt | de-AT | German, as used in Austria |
| Locale.GermanChLocale,GermanCh | de-CH | German, as used in Switzerland |
| Locale.GreekLocale,Greek | el | Greek |
| Locale.HebrewLocale,Hebrew | he | Hebrew |
| Locale.HindiLocale,Hindi | hi | Hindi |
| Locale.HungarianLocale,Hungarian | hu | Hungarian |
| Locale.IndonesianLocale,Indonesian | id | Indonesian |
| Locale.ItalianLocale,Italian | it | Italian |
| Locale.ItalianChLocale,ItalianCh | it-CH | Italian, as used in Switzerland |
| Locale.JapaneseLocale,Japanese | ja | Japanese |
| Locale.KoreanLocale,Korean | ko | Korean |
| Locale.NorwegianLocale,Norwegian | nb | Norwegian (bokmål) |
| Locale.PersianLocale,Persian | fa | Persian |
| Locale.PolishLocale,Polish | pl | Polish |
| Locale.PortugueseLocale,Portuguese | pt | Portuguese |
| Locale.PortugueseBrLocale,PortugueseBr | pt-BR | Portuguese, as used in Brazil |
| Locale.RomanianLocale,Romanian | ro | Romanian |
| Locale.RussianLocale,Russian | ru | Russian |
| Locale.SlovakLocale,Slovak | sk | Slovak |
| Locale.SpanishLocale,Spanish | es | Spanish |
| Locale.SpanishArLocale,SpanishAr | es-AR | Spanish, as used in Argentina |
| Locale.SpanishEsLocale,SpanishEs | es-ES | Spanish, as used in Spain |
| Locale.SpanishMxLocale,SpanishMx | es-MX | Spanish, as used in Mexico |
| Locale.SwedishLocale,Swedish | sv | Swedish |
| Locale.ThaiLocale,Thai | th | Thai |
| Locale.TurkishLocale,Turkish | tr | Turkish |
| Locale.UkrainianLocale,Ukrainian | uk | Ukrainian |
| Locale.VietnameseLocale,Vietnamese | vi | Vietnamese |
Returns
A number formatted as a percentage.
Returns a number as text, representing a percentage. FORMAT.PERCENTAGE (0.726) FORMAT.PERCENTAGE (0,726), returns "72.6%".
FORMAT.PERCENTAGE takes the configured locale into account when converting numbers. For instance, a decimal point is used when the locale is US English and a decimal comma is used when the configured locale is French.
This function is not commonly supported by spreadsheets.
Decimal places
The second and third parameters specify the number of decimal places in the formatted number. If neither parameter is provided, the function uses the minimum number of decimal places needed, up to a maximum of two. If only the second parameter is given, it specifies an exact number of decimal places.
This formula returns "72.6000%", using exactly four decimal places, if the configured locale is US English and "72,6000 %" if the configured locale is German:
If a third parameter is given, the second parameter is interpreted as the minimum number of decimal places that should be used and the third parameter is interpreted as the maximum number of decimal places that should be used.
This formula uses between 2 and 6 decimal places:
While no decimal places are required to format 72%, the minimum is specified as 2, meaning that "72.00%" is returned (for US English).
However, if the first parameter is set to 0.721234567890,72123456789 instead, "72.123457%" is returned. The returned value respects the maximum number of decimal places to use, which is set to 6.
Thousands separators
The fourth parameter may be set to TRUE to prevent thousands separators from being used. This formula returns "2430.00%" (if the configured locale is US English), and not "2,430.00%":
Padding the number with leading zeroes
The fifth parameter, IntegerDigitsMinimum , may be used to pad the number with leading zeroes, if necessary. This formula returns "0023.60%" if the configured locale is US English:
Named parameters
When a large number of parameters are provided to FORMAT.PERCENTAGE, it can be hard to keep track of which values go with which parameters. To make this easier, a parameter name, followed by : , may precede its value.
These formulas are equivalent:
All parameters don't need to be named, but once a parameter has been named, the remaining parameters must also be named.
Named parameters make it possible to provide parameters out-of-order and to omit optional parameters that normally would have been expected to precede a parameter, had it not been named. Omitted optional parameters use default values.
The formula above uses default values for all parameters other than Number and IntegerDigitsMinimum . As such, those optional parameters can be removed from the formula:
Use named parameters when they help make a formula easier to read.
Related functions
- Use FORMAT to format regular numbers.
- Use FORMAT.CURRENCY to format monetary values (-0.75 can be formatted as "($0.75)").
- Use FORMAT.FRACTION to format fractions (0.75 can be formatted as "3/4").
- Use FORMAT.SCIENTIFIC to use scientific notation (15,000 can be formatted as "1.5E4").
- Use FORMAT.ENGINEERING to use engineering notation (15,000 can be formatted as "15E3").
- Use FIXED to round a number to the left of the decimal separator (7,523 can be formatted as "7,500").
Examples
Returns "72.6%" if the configured locale is US English.
Returns the array { "72.6%", "21.3%" }{ "72.6%"; "21.3%" } if the configured locale is US English. The number of decimal places is set to 1. When invoked with an array, FORMAT.PERCENTAGE is invoked once per array element and the results are collected in an array.
Returns "72.6000%" if the configured locale is US English, using exactly four decimal places.
Returns "72.6000%", using exactly four decimal places.
Returns "72.00%" if the configured locale is US English. As both the second and the third parameter are specified, 2 is taken to mean the minimum number of decimal places that should be used, and 6 is taken to mean the maximum number of decimal places that should be used.
Returns "72.123457%" if the configured locale is US English. As both the second and the third parameter are specified, 2 is taken to mean the minimum number of decimal places that should be used, and 6 is taken to mean the maximum number of decimal places that should be used.
Returns "2430.00%" (if the configured locale is US English), and not "2,430.00%". The fourth parameter prevents thousands separators from being used.
Returns "0023.60%" (if the configured locale is US English). The fifth parameter, 4, specifies the minimum number of integer digits (before the decimal separator). This pads the number with leading zeroes, if necessary.