In a Nutshell
The FIXED function in Microsoft Excel is used to format a number as text, with a fixed number of decimals.
Syntax
The syntax for the FIXED function is as follows:
=FIXED(number, [decimals], [no_commas])
- number: The number you want to format.
- decimals (optional): The number of digits to the right of the decimal point. If omitted, it defaults to 2.
- no_commas (optional): A logical value indicating whether to include commas as the thousands separator. If TRUE, no commas are included; if FALSE or omitted, commas are used.
Examples
Download the sample workbook here and follow along.
Example 1
Basic usage with default settings:=FIXED(1234.567)
This would result in the text string “1,234.57” (rounded to two decimal places by default).
Example 2
Specifying a different number of decimals:
=FIXED(1234.567, 3)
This would result in the text string “1,234.567” with three decimal places.
Example 3
Using commas as the thousands separator:
=FIXED(1234567.89, 2, FALSE)
This would result in the text string “1,234,567.89” with two decimal places and commas as the thousands separator.
1234567.89 is the number you want to format.
2 is the number of decimal places you want to display.
FALSE indicates that you don’t want to omit (i.e. leave out) commas as the thousands separator. There is no need to state FALSE as it is the default value.
So the following would have given the same result:
=FIXED(1234567.89, 2)
Example 4
Omitting decimals and using commas:
=FIXED(9876543, , FALSE)
This would result in the text string “9,876,543” with no decimal places and commas as the thousands separator.
Example 5
Formatting negative numbers:=FIXED(-5678.4321, 2, TRUE)
This would result in the text string “-5678.43” i.e. with two decimal places and no thousands separator.
Example 6
You can use the CONCATENATE function along with the FIXED function to combine formatted numbers or text strings. Here’s an example:
Let’s say you have two numbers, and you want to concatenate them with different formatting using the FIXED function:
=CONCATENATE("Formatted Number 1: ", FIXED(1234.567, 2, TRUE), ", Formatted Number 2: ", FIXED(9876.543, 1, FALSE))
In this example:
FIXED(1234.567, 2, TRUE) formats the first number as “1234.57” with two decimal places and no thousands separator.FIXED(9876.543, 1, FALSE) formats the second number as “9,876.5” i.e. with one decimal place and commas as the thousands separator.
The CONCATENATE function combines these formatted numbers into a single text string.
The result would be a text string like:
Formatted Number 1: 1234.57, Formatted Number 2: 9,876.5
The formula above can be customised based on your specific requirements and the formatting you need for your numbers.
Excel Versions Supported
All versions.
