This tutorial demonstrates how to move decimal places in Excel and Google Sheets.
Custom Decimal Number Format
If you have whole numbers and want to add decimals, you can do it with custom formatting. This includes inserting a decimal point, which is different from increasing or decreasing decimal places to show significant digits. Say you have the following list of numbers in Column B, and want to add a decimal point before the last 2 digits in Column C.
- Select the range of cells where you want to add a decimal point (here, C2:C8), right-click the selected area, and choose Format Cells…
- In the Format Cells window, select Custom category, enter 0″.”00 in the type box, and click OK.
As a result, all numbers in Column C appear to have 2 decimal places.
Move Decimal Places – New Value
A custom number format is a great way to move decimal places without changing a cell’s value, but if you want to change the actual values and not just how they are displayed, you can multiply or divide them by factors of 10.
You could also use VBA to format numbers.
Move Decimal Places in Google Sheets
You can do the same thing in Google Sheets.
- Select the range of cells where you want to add a decimal point (C2:C8), and in the Menu, go to Format > Number > More Formats > Custom number format.
- In the Custom number formats box, enter 0″.”00, and click Apply.
The result is the same as in Excel: All numbers in Column C now have a decimal before the last 2 digits.