HomeGoogle DocsSpreadsheetHow to Use the FIXED Text Function in Google Sheets

How to Use the FIXED Text Function in Google Sheets

Published on

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)
FIXED Function in Google Sheets

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)
Formula to Remove Thousands Separator in Google Sheets

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")

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here