The Fixed function in Google Sheets is specifically for formatting numbers with fixed decimal places. It’s a text function so the formatted numbers will be in text format.
Actually, to format a number that using a formula, you can either use the Text function or the Fixed function. In the former function, you should specify a format. But the latter doesn’t require any specific format.
The Syntax of Google Sheets Fixed Function:
FIXED(number, [number_of_places], [suppress_separator])Arguments:
number – The number in question to format.
number_of_places – It’s optional. In this, you can specify the number of decimal places to display. The default number of decimal places is 2.
suppress_separator – This argument is also optional. In this, you can specify whether to exclude/include the 1000 separator. By default, the 1000 separator, if present, will be included. Put 1 to exclude the 1000 separator.
Formula Examples to the Fixed Function in Google Sheets
Fixed Function without Any Optional Arguments
I have the below Fixed formula in cell D3 which copied to the range D3:D8. The formula converts the numbers to text. Also, it keeps the 1000 separators, if any, and rounds the number of places to two.
=fixed(B2)
The following formula will return the above same output.
=fixed(B2,2,0)Format Numbers to Texts and Remove Thousands Separator in Google Sheets
Using Google Sheets Fixed function, you can very easily format numbers and remove thousands separator, if any.
=fixed(B2,2,1)
Additional Tips Related to the Use of Fixed Formula in Google Sheets
Non-Array to Array:
Fixed is a non-array function. You can convert it to an array function as below.
In the last example, I have used Fixed formulas in each row. Use the below formula in cell D2. It will return the results in the range D2:D8.
=ArrayFormula(fixed(B2:B8,2,1))If you see any error, that may be due to existing values in D3:D8. Array Formulas can’t overwrite existing values in cells.
Must Read: Google Sheets Array Formula Example and Usage.
Text Values in Aggregation:
The Fixed formula returns text output because of formatting numbers. In order to use the ‘fixed’ formatted numbers calculations, you can follow this workaround.
=Sum(ArrayFormula(fixed(B2:B8,2,1)*1))Replace the Fixed Function with Text Function:
Here are two example formulas.
Formula 1:
=text(1500,"0.00")Formula 2:
=text(1500,"?,###.00")
