The FIXED function in Excel is used to format a number as text with a fixed number of decimal places and with or without commas separating thousands. This is useful for displaying numbers in a consistent format, especially for financial data or other numerical reports.
Here’s how you can use the FIXED function in Excel:
Syntax
FIXED(number, [decimals], [no_commas])
- number: This is the number you want to format. It is the only required argument.
- decimals: This optional argument specifies the number of digits to the right of the decimal point. If omitted, Excel will default to 2 decimal places.
- no_commas: This optional boolean argument determines whether commas should be used as the thousands separator. If TRUE, no commas will be included. If FALSE or omitted, commas will be included.
Example Usage
- Basic Example:
To format the number `1234.567` as text with two decimal places:
=FIXED(1234.567)
Result: `”1,234.57″`
- Specifying Decimal Places:
To format the number with three decimal places:
=FIXED(1234.567, 3)
Result: `”1,234.567″`
- Removing Commas:
To format the number without commas and with one decimal place:
=FIXED(1234.567, 1, TRUE)
Result: `”1234.6″`
- Using a Cell Reference:
If cell `A1` contains the number `5678.9123`, you can format it to no decimal places with commas:
=FIXED(A1, 0)
Result: `”5,679″`
Notes
- The FIXED function returns a text string, so it is not suitable for calculations unless converted back to a number.
- Be cautious when using FIXED in calculations, as the result is text. You might need to convert it back to a number using other functions like VALUE if further calculations are required.
Using the FIXED function can help ensure that numeric data is presented in a clear and standardized way, making spreadsheets easier to read and interpret.
Unlock Your Potential
Excel
Access
Power BI
