This function converts numbers to formatted text with fixed decimal places and optional comma separators.
Syntax:
FIXED(number, [decimals], [no_commas])
Arguments:
- number (required): The numeric value to convert
- decimals (optional):
- Positive: Rounds to specified decimal places
- Negative: Rounds left of decimal point
- Default: 2 decimal places
- no_commas (optional):
- TRUE: Omits thousands separators
- FALSE/omitted: Includes commas
Key Features:
- Conversion Rules:
- Maximum 15 significant digits
- Supports up to 127 decimal places
- Uses standard rounding (≥0.5 rounds up)
- Comparison to Formatting:
| Feature | FIXED() | Cell Formatting |
| Data Type | Text | Number |
| Calculations | Auto-converts | Direct |
| Display | Fixed width | Dynamic |
Examples:
Assume that you want to ensure that a column with number values that is used for a mail merge in Word is not changed. Use the FIXED() function to convert the values into text in a new column. This column can then be used for the mail merge in Word. Here are some more examples:
=FIXED(12.56) returns 12.56.
=FIXED(1234.56,-1,1) returns 1230.
=FIXED(12.56,0) returns 13.
=FIXED(1234.56,-2,TRUE) returns 1200.
=FIXED(12.46,0) returns 12.
=FIXED(1234.56,-3,0) returns 1,000.
=FIXED(PI(),3) returns 3.142.
=FIXED(1234.56,-4,FALSE) returns 0.
Important Notes:
- For calculations, Excel automatically converts results back to numbers
- Alternative functions:
- TEXT() for custom formats
- DOLLAR() for currency formatting
- Combine with TRIM() to remove extra spaces