In an attempt to make Excel a smart and more user-friendly tool, there are some in-built features that end up being a little bit of an irritation. Working with decimals, you will likely face such an issue. Let me explain! If you type 10.00 in a cell in Excel, it would automatically convert this to 10 by removing the zeros after the decimal. While this is a smart thing to do, sometimes, you may want all your numbers to show a specific number of digits after the decimal, even if these are zeros. In this tutorial, I will show you how to automatically add decimals to the numbers in Excel.
Format Cells to Show Fixed Number Length after Decimal Point
In some cases, you may want to have consistent numbers, where there are always two or three numbers after the decimal point (as shown below).
While Excel does not allow this by default (as it doesn’t allow only zeros after the decimal number), you can easily change this by modifying the custom number formatting of the cells. Below are the steps to make sure that your numbers always show at least 2 numbers after the decimal point. The above steps would make sure that all the numbers now show two numbers after the decimal point. Even if you have a whole number, a decimal point and two zeros after the decimal point would automatically be added to these numbers. In case there are more numbers after the decimal point, those would be rounded. For example, 91.333 would be changed to 91.33, and 91.338 would be changed to 91.34. Note: While the format cells dialog box gives you more control over how we want your numbers to be displayed, if all you want to do is show two numbers after the decimal point, you can also do that by selecting the number formatting option in the format options drop-down in the Number group in the Home tab
Automatically Add Decimal Point to Numbers While Typing
Another thing that you can do with Excel is automatically inserting a decimal point in the numbers of white you are typing these numbers. For example, if you want to have two numbers after the decimal point in all the numbers that you type, then you can change a setting in Excel so that you can simply type the number in the decimal point would automatically be inserted (you won’t have to manually enter the decimal point yourself). Below are the settings that you need to change to enable this: After this setting is enabled, whenever you enter a number in a cell in Excel, it would automatically insert a decimal point before 2 numbers. For example, if you enter 1 in a cell, it would be converted into .01. If you enter 1234, it would be converted into 12.34 While I don’t recommend this setting to be enabled in general, it’s a good option when you’re manually entering the data and want to save some time. Note that this setting would only affect the numbers that you enter after you have enabled the setting. All the numbers that were already there in the worksheet would remain unchanged Once you are done with the data entry, you can go back to the same Excel options dialog box and disable this setting. So these are two methods that you can use to add decimal places to numbers in Excel. The first method using custom number formatting is what you would need in most cases. But in case you are doing manual data entry and want to save some time by not entering the decimal point (and still getting a consistent result with decimals), you can use the second method. I hope you found this tutorial useful. Other Excel tutorials you may also like:
Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds) How to Remove Comma in Excel (from Text and Numbers) How to Remove Leading Zeros in Excel How to Add Leading Zeros in Excel
