How to Use FIXED Function in Excel (6 Suitable Examples)

The FIXED function is a built-in function in Microsoft Excel. It is categorized as a String/Text Function. It is one commonly used procedure in Excel. In this tutorial, you will learn every detail of the FIXED function in Excel. This tutorial will be on point with suitable examples and proper illustrations.

How to use FIXED function in Excel

The above screenshot is an overview of the article. It represents a simplified application of the FIXED function in Excel. You’ll learn more about the dataset as well as the methods and procedures under different criteria in the following sections of this article.


Download Practice Workbook

Download this practice workbook.


Introduction to FIXED Function

Function Objective

The Microsoft Excel FIXED function returns a text illustration of a number. It rounds a number to the fixed number of decimals, formats the number in decimal format using a period and commas.

Syntax 

=FIXED(number, [decimals], [no_commas])

Arguments Explanations

Argument Requirement Description
number Required The number you want to round and convert to text.
[decimals] Optional The number of digits to the right of the decimal point.

-If [decimals] is skipped, it takes on the default value of 2;

-If [decimals] is negative, the number will be rounded up to the left of the decimal end.

[no_commas] Optional This is a logical operator that is either TRUE or FALSE.

-If [no_commas] is TRUE, it will show value without a comma.

-If [no_commas] is FALSE, there will be commas in the output.

Returns

The FIXED function returns a value in Text/String format.

Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.


How to Use FIXED Function

The FIXED function gives you value in a text form. Basically, our input is a number in a decimal form. Maybe you need a particular number of values after the decimal point. You can use this to show a specific number of values with or without commas.

Take a look at the following screenshot:

excel fixed function

As you can see, we have some numbers in decimal form. Then, we have used the FIXED function on them. As you can see, the different results came out as we gave different values in the FIXED arguments. You have to just type the formula and insert your cell references or values in it.


6 Suitable Examples of FIXED Function in Excel

In the following section, we will provide you with six simple but effective examples. I hope it will give you a clear idea about the FIXED function in excel. We recommend you learn and apply all these methods to your dataset. It will surely enrich your Excel knowledge.


1. FIXED Function in Excel When ‘[decimal]’ Argument is Positive or Negative

Now, from the introduction, you have already learned that you have to provide a particular value in the position of the decimals. It will give you that number of numbers after the decimal portion.

So, if you give 2 in the decimal position, it will give you a rounded value with 2 decimal points.

=FIXED(123.4576,1) // result: 123.5

=FIXED(123.4576,2) // result: 123.46

=FIXED(123.4576,3) // result: 123.48

Now, what will happen if you give a negative number in the decimals argument?

Basically, if your given decimals are negative, it will round up to the left of the decimal point. But this time, there will be no decimal point.

=FIXED(5432.446,-1)// result: 5,430

=FIXED(5432.446,-2)// result: 5,400

=FIXED(5432.446,-3)// result: 5,000

Here, you can follow these simple steps.

📌 Steps

  • Type the following formula in Cell D5:
=FIXED(B5,C5)

  • After that, press Enter and drag the Fill handle icon.

output of excel fixed function

Now, you can perform the same procedure with negative values like the following screenshot:

output of excel fixed function

As you can see, we have successfully used the FIXED function of Excel.


2. If ‘[decimals]’ Argument is Omitted in Excel

If your decimal points are blank, Excel automatically rounds up the number with 2 decimal points.

📌 Steps

  • First, type the following formula in Cell C5:
=FIXED(B5,,TRUE)

Here, we are giving the no_commas parameter TRUE to remove the commas from the result.

  • Then, press Enter and drag the Fill handle icon.

output of excel fixed function


3. FIXED Function in Excel If the ‘[no_commas]’ Argument is TRUE or FALSE

Previously, we discussed the arguments of the FIXED function in Excel. In the no_commas, we can give it TRUE or FALSE.

If your no_commas argument is TRUE, there will be no commas in the result.

The opposite will happen if you set it to FALSE.

Follow these steps to have a better understanding:

📌 Steps

  • First, type the following in Cell D5:
=FIXED(B5,C5,TRUE)

  • Then, press Enter. After that, drag the Fill handle icon.

Fixed Function in Excel with ‘no_commas’ is TRUE or FALSE

Similarly, if you want commas in the output you can set it to FALSE.

As you can see, we are successful in using the FIXED function both with commas and no commas.


4. If ‘[no_commas]’ Argument is Omitted in Excel

If you omit the no_commas argument from the FIXED function, Excel will take FALSE as a default value.

📌 Steps

  • First, type the following in Cell D5:
=FIXED(B5,C5)

excel fixed function

  • Then, press Enter. After that, drag the Fill handle icon.

output of excel fixed function


5. FIXED Function with both ‘[decimals]’ and ‘[no_commas]’ Arguments are Omitted

Now, you may ask what would happen if I omit both decimals and no_commas argument?

In this case, Excel will do the following:

-if decimals is omitted, it will automatically set the decimal point to 2.

-if no_commas is omitted, it will automatically set the no_comnas to 0 or FALSE.

Follow these steps to have a clear idea.

📌 Steps

  • First, type the following in Cell C5:
=FIXED(B5)

FIXED Function with both ‘decimals’ and ‘no_commas’ is Omitted

  • Then, press Enter. After that, drag the Fill handle icon.

FIXED Function with both ‘decimals’ and ‘no_commas’ is Omitted

As you can see, we have successfully used the FIXED function in Excel with both omitted arguments.


6. Concatenate with FIXED Function Together with CONCAT in Excel

You can mix the CONCAT function with the FIXED function. You can mix it with any function to get your desired output.

The following screenshot will give you a clear idea:

Concatenate FIXED Function in Excel

Here, we used the FIXED function with the CONCATENATE function to get the decimal values with 2 decimal points.

Read More: Excel Text Function Format Codes


💬 Things to Remember

The FIXED function gives us the output in Text/String form. You can change it using the Number Format.

If you give non-numeric arguments, it will show #VALUE! Error.

As the output is in text form, you can not use it for numeric calculations.

Numbers in Microsoft Excel can have up to 15 significant digits, but decimals can be as large as 127.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the FIXED function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo