Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to count (identify) and change decimal place in Excel?

This article will show you the way to count and identify the decimal place of a number, and ways to change decimal place in a given range or the whole worksheet.

Supposing we will count and change the decimal places of numbers in the given Column A, see the following screenshot:


Count and identify the decimal place of a number

Change the decimal place in a given range

Change the decimal place in the whole worksheet

Add decimal point in a specific place with Kutools for Excelgood idea3

Calculate age based on birthday (get the age by a given date. There are dozens of usually used formulas in the Formula Helper.

doc calculate age

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial

arrow blue right bubble Count and identify the decimal place of a number

Sometimes there are numbers containing various amount of decimal place, and you may want to count the digitals and show in a cell. You can do it with following steps:

formula Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group
as auto text, and liberate your brain! Click here to know Auto Text     Click here to get free trial

In a blank cell, saying the Cell B2, enter the formula of =IF(A2=INT(A2),0,LEN(MID(A2-INT(A2),FIND(".",A2,1),LEN(A2)-FIND(".",A2,1)))), and press the Enter key.doc-count-change-decimal-places2

Then it returns the decimal place in the Cell B2. In this case, it shows 5. Now you can get the decimal place of other numbers with copying and pasting this formula to corresponding cells.

arrow blue right bubble Change the decimal place in a given range

You can change the decimal place for many numbers in a given range with following steps:

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

Step 1: Select the numbers you will change their decimal place. In this case, select the range of A2:A6.

Step 2: Click the Decrease Decimal button or Increase Decimal button in the Number group under Home tab.

Step 3: Then the decimal place of numbers in the selection are changed and unified to same decimal place. See the following screenshot:


You can also change and unify the decimal place of numbers with Format Cells by right clicking the selection.

Step 1: Select the range that you want to change, and right click.

Step 2: Choose Format Cells from the context menu, and in the Format Cells dialog box, click Number from the Category list, and enter the decimal places that you need. See screenshot:


Step 3: Then click OK. And the decimal places have been changed to what you want.


arrow blue right bubble Change the decimal place in a given range

This method will help you change the default decimal place for the whole worksheet.

Step 1: Click the File > Options in Excel 2010; and click the Office Button > Excel Options in Excel 2007;

Step 2: In the Excel Options dialog box, click the Advanced button in left bar.

Step 3: Go to the Editing options section.

Step 4: Check the Automatically insert a decimal point option, and enter a number in the Places box.

Step 5: Click OK to confirm and save the changes. Now the default decimal place is changed.

Step 6: When you input the number 1234, it will become 12.34 automatically, if the default decimal place is 2.


By the way, if you enter the number of 16.25893, it will shows as 16.25893, but not 16.26.

arrow blue right bubble Add decimal point in a specific place with Kutools for Excel

If you have a list of values, and now you want to add the decimal point in a specific locations, for instance, to put the decimal after second character of each values as below screenshot shown, how can you solve it quickly in Excel?

doc kte 1

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

With Kutools for Excel, you can add any characters to any place of a string in a list by applying its Add Text utility.

After free installing Kutools for Excel, please do as below:

1. Select the values you want to add decimal point in a same location, and click Kutools > Text > Add Text. See screenshot:
doc kte 2

2. In then Add Text dialog, type the decimal point into Text box, and check Specify option, and type the location you want to add text after, here I want to add decimal point after second character, you can view the results in right pane. See screenshot:
doc kte 3

3. Click Ok or Apply, then the decimal point has added into a specific location of each values.

With Add Text utility, you also can add specific character to multiple specific place of a string in once time.
doc kte 4

arrow blue right bubble Add specific character/String to Cell

Kutools for Excel: 300 + functions you must have in Excel, 60-day free trial from here

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Edward · 4 years ago
    How can you change the decimal place for values that are already in the cell? In other words, i'm not trying to change the number of decimal place showing, but want to convert the value of the number in the cell.
    To illustrate:
    Change 1.25 (cell a) to 125 (cell b).
    Is this possible without having to go through each cell to convert?
    • To post as a guest, your comment is unpublished.
      SteveY · 4 years ago
      Run an equation in the adjacent column =A1*100, clicking bottom right corner of the box/dragging, to run down the length of the column.
  • To post as a guest, your comment is unpublished.
    Steve Young · 4 years ago
    The formula for counting the number of decimal places is flawed. For example, 123.75 returns 1.
  • To post as a guest, your comment is unpublished.
    John Robinson · 5 years ago
    Kindly tell me if it is possible to add values with their decimal values separately ie 47.6+49.5=96.11. Does excel have a formula to deal with this one. If anybody knows, It would help me so much. Thanks.