Yesterday, in the 30XL30D challenge, we picked an item from a list with the CHOOSE function, and learned that other functions might be a better MATCH when doing a LOOKUP.
For day 6 in the challenge, we’ll examine the FIXED function, which formats a number with decimals and commas, and returns the result as text. That’s not too surprising, I guess, because the FIXED function is from the Text category. But is the FIXED function worth using? You can judge for yourself in the examples shown below.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the FIXED information and example, and if you have other tips or examples, please share them in the comments. Maybe I’m overlooking some awesome power that this function has, and this blog post needs to be FIXED!
Function 06: FIXED
The FIXED function rounds a number to a specified number of decimals, with or without comma separators, and returns the formatted result as text.
How Could You Use FIXED?
The FIXED function can change numbers to text, formatted with a set number of decimals. This could help in limited situations, such as:
- Matching numbers imported as text from another application
- formatting numbers in a text string
The FIXED function has the following syntax:
- Number is the number you want to round and convert to text.
- Decimals is the number of digits to the right of the decimal point.
- If omitted, decimals defaults to 2
- If negative, decimals round to the left of decimal point
- If no_commas is FALSE or omitted, the result includes commas as usual
- If no_commas is TRUE, commas are not included in the result.
The FIXED function changes a number to text, so don’t use it anywhere that you want to keep numbers as numbers. For example, if you want to limit the number of decimal places for a chart’s data, use number formatting in the cells, or use the ROUND function instead.
Example 1: Format Rounded Number
The FIXED function lets you take a number, round it to a specific number of decimals, add commas, and return the result as text. For example, with the number 32187.5689231 in cell C2, you could use the FIXED function to round it to 2 decimal places, and include a comma.
Instead of using FIXED, you could simply format the cell as Number format, with 2 decimal places, and a comma. That would leave the cell value unchanged — only its appearance would be different.
Or, you could use the ROUND function, combined with cell formatting, to return a number that is rounded to two decimal places, and has a comma separator.
Finally, if you really want the result as text, use the TEXT function. Its formatting options are much more flexible, and if you need rounding to the left, you can combine it with the ROUND function.
Example 2: Round Number to the Left
With the FIXED function, you can also round to the left of the decimal point, by using a negative number for the decimal places argument. To remove commas, use TRUE in the no_commas argument. Again, the result is text.
Or, you could use the ROUND function, to return a number that is rounded to the left.
Example 3: Show Number as Thousands of Dollars
The FIXED function has limited formatting options, and only lets you control the number of decimal places, and the commas. You could use FIXED to round a number to the thousands, but can’t add a dollar sign, or hide the zeros.
Instead of using FIXED, you could format the cell with a Custom Number format of $#, that would leave the cell value unchanged — only its appearance would be different.
Or, for a text result, use the TEXT function, which lets you format the number as thousands of dollars.
Download the FIXED Function File
To see the formulas used in today’s examples, you can download the FIXED function sample workbook. The file is zipped, and is in Excel 2007 file format.
Watch the FIXED Video
To see a demonstration of the examples in the FIXED function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Change Numbers to Text With Excel FIXED Function