If you want to add decimal points in Excel cells automatically, here is how you can do that. Although you can use the in-built options to enable this setting, you can also use the Registry Editor and the Local Group Policy Editor to get the same thing done. However, the REGEDIT method and the Excel Options methods are the most useful in this case.
Let’s assume that you want to do some data entry job by entering tons of numbers in countless cells, and all of them come with a decimal point of 2 or 3 or something else. Instead of pressing the period sign all the time to enter the decimal point, you can use this setting to make things happen automatically. After turning it on, a decimal point will be placed automatically as per the predefined setting.
How to automatically add Decimal Points in Excel
To automatically insert decimal points in Excel, follow these steps:
- Open Microsoft Excel on your computer.
- Click on File > Options.
- Go to the Advanced tab.
- Tick the Automatically insert a decimal point checkbox.
- Enter the places and click the OK button.
To know more about these steps, continue reading.
To get started, open Microsoft Excel and click on the File and select Options from the bottom-right corner. Next, switch to the Advanced tab and find the Automatically insert a decimal point option.
Tick the corresponding checkbox and enter the place. It is the place where you want to show the decimal point. By default, it is set to 2. If so, 4567 will become 45.67.
In case you want to make it 4.567, you need to enter 3 and click the OK button to save the change.
Once done, you can enter the numbers in your spreadsheet to find the change.
How to automatically insert decimal points in Excel using Group Policy
To automatically insert decimal points in Excel using Group Policy, follow these steps:
- Press Win+R to open the Run dialog.
- Type gpedit.msc and press the Enter button.
- Go to Excel Options/Advanced in User Configuration.
- Double-click on the Automatically insert a decimal point setting.
- Choose the Enabled option.
- Click the OK button.
- Restart Microsoft Excel.
Let’s check out these steps in detail.
First, you need to open the Local Group Policy Editor. For that, press Win+R to open the Run dialog, type gpedit.msc and press the Enter button.
Next, navigate to the following path:
User Configuration > Administrative Templates > Microsoft Excel 2016 > Excel Options > Advanced
Find the Automatically insert a decimal point setting on the right-hand side and double-click on it.
Choose the Enabled option and click the OK button.
Next, restart Microsoft Excel if it was opened when you turned the setting on.
The only problem with this method is that you cannot set the places as you did in the first method. That said, if you enable this setting, a decimal point will be placed after two digits automatically, and there is no option to change it.
Note: You must download the administrative template for Office to use the Group Policy method.
How to add decimal points automatically in Excel using Registry
To automatically insert decimal points in Excel using Registry, follow these steps:
- Search for regedit in the taskbar search box.
- Click on the search result and click the Yes button.
- Navigate to 0 in HKCU.
- Right-click on 0 > New > Key and name it as excel.
- Right-click on excel > New > Key and set the name as options.
- Right-click on options > New > DWORD (32-bit) Value.
- Name it as autodec.
- Double-click on it and set the value data as per your requirements.
- Click the OK button and restart your computer.
Let’s delve into these steps in detail.
First, you need to open the Registry Editor. For that, search for regedit in the Taskbar search box, click on the individual search result and click the Yes button in the UAC prompt.
Next, follow this path:
HKEY_CURRENT_USER\Software\Policies\Microsoft\office\16.0
Right-click on 16.0 > New > Key and name it as excel. Then, right-click on excel > New > Key and name it as options.
Following that, right-click on options > New > DWORD (32-bit) Value and set the name as autodec.
Next, double-click on it to set the Value data as per your wish. Having said that, if you want to put the decimal point after 3 digits, enter 3 and so on.
Click the OK button and restart your computer.
Read: How to lock cells in Microsoft Excel formula to protect them.
How to insert decimal after the second character in Excel?
To insert a decimal point after the second character in Excel, you need to follow these steps. You can open Excel > File > Options. Then, go to the Advanced tab, and tick the Automatically insert a decimal point checkbox. Next, enter 2 in the Places box and click the OK button.
How to add decimal points automatically in Excel?
To add decimal points automatically in Excel, you can use the GPEDIT on your computer. Open the Local Group Policy Editor, go to Excel Options > Advanced, and double-click on the Automatically insert a decimal point setting. Next, select the Enabled option and click the OK button.
That’s all! Hope this guide helped.
