The FIXED function takes three arguments, number, decimals, and no_commas. Number is the number to convert. Decimals is the number of digits to which number will be rounded on the right of the decimal point. If decimals is negative, number will be rounded to the left of the decimal point. Decimals is optional and defaults to 2. The no_commas argument is a Boolean that controls whether commas will be added to the result. No_commas is optional and defaults to FALSE. To prevent commas, set no_commas to TRUE. Note: the FIXED function returns text and not a number, so the result cannot be used in a numeric calculation. If the goal is to format a number and retain its numeric property, applying a standard number format is a better option. Video: How to use number formatting.
Examples
In the example shown above, the formula in E5, copied down, is: At each new row, FIXED returns a result based on the number in column B, the decimals in column C, and comma setting in column D. Number is the only required argument. By default, FIXED will round to 2 decimal places and insert commas for thousands: The FIXED function can be useful when you want to concatenate a number with other text. The example below shows how the output from the PI function can be trimmed by FIXED:
FIXED vs. TEXT
The FIXED function is a specialized function to apply Number formatting only. The TEXT function is a generalized function that does the same thing in a more flexible way. TEXT can convert numeric values to many different number formats, including currency, date, time, percentage, and so on.
Notes
The output from FIXED is text. To simply format a number, apply a number format instead. The FIXED function converts a number to text using a number format like: 0.00 or #,##0 The default for decimals is 2. If decimals is negative, number will be rounded to the left of the decimal point. The TEXT function is a more flexible way to achieve the same result.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
