How-To

How to Move Decimal Places in Excel

By Adam Davidson

Published January 31, 2024

Do you need to change the decimal places in multiple cells in your spreadsheet? Learn how to move decimal places in Excel.

“To the left, to the left,” sang Beyonce, in a song in which she explains how to move the decimal point if you want to divide a number by 100. At least I assume this is what it was about. If you’re working in Excel, however, trying to move decimal places can be a little trickier. If you’ve got values in your spreadsheet and you need to shift the decimal point in one direction or another, you probably don’t want to have to retype every entry manually.

The good news is that there are several ways that you can create new values with the decimal point in the place that you wish, or even replace your original data with new values where the decimal has been moved. Not every method will work the same way, however, so you’ll need to choose wisely. Here’s how to move decimal places in Excel.

How to Move Decimal Places by Multiplying or Dividing

When you multiply or divide a decimal by 10, the digits of the result remain the same; only the decimal point moves. You can use this fact to move the decimal places in your Excel spreadsheet by dividing or multiplying. To move the decimal left, divide by a multiple of 10. Dividing by 10 will move the decimal one place to the left, dividing by 100 will move it two places to the left, and so on. To move the decimal point to the right, multiply by a multiple of 10. Multiplying by 10 will move the decimal point one place to the right, multiplying by 100 will move it two places to the right, and so on.

1. Find the first cell that contains a decimal that you want to move and click in an empty cell in the same row.
2. Type = and then click the cell containing your decimal.
   
3. To move the decimal point to the left, type /.
   
4. To move the decimal point to the right, type *.
   
5. Enter a multiple of 10. To move the decimal point one place, type 10, to move it two places type 100, to move it three places type 1000 and so on. The number of zeroes in your multiple of 10 will be the number of places that the decimal point moves.
   
6. Press Enter and your cell will now show the original value with the decimal place moved by the appropriate number of spaces.
   
7. To apply the same move to other cells, select the cell, and click and hold the square in the bottom right corner of the cell.
   
8. Drag down to apply the formula to as many cells as you wish.
   
9. Your selected cells will now also show the moved decimal point.
   

How to Move Decimal Places in Excel Using Paste

You can also achieve the same effect by using the Multiply and Divide options within the Paste Special feature in Excel. This allows you to copy your cells, multiply or divide them by a multiple of 10, and then paste the result into a new cell. This method will paste directly over the original cells, replacing them with the new values.

1. Select an empty cell and type a multiple of 10. If you want to move your decimal places a single place in either direction, type 10 if you want to move them two places, type 100 if you want to move them three places, type 1000, and so on. The number of zeroes in the number you type will correspond to the number of decimal places that the decimal point moves.
   
2. Copy the cell containing the number you’ve just typed by pressing Ctrl+C on Windows or Cmd+C on Mac, or right-clicking and selecting Copy.
3. Highlight all of the cells containing the data whose decimal places you want to move.
   
4. Right-click your selection and select Paste Special.
   
5. On Excel for Mac, click Paste Special again.
   
6. To move the decimal point to the left, select Divide.
   
7. To move the decimal point to the right, select Multiply.
   
8. Click OK and the decimal point in your cells will move the appropriate number of places.
   
9. You can now delete the cell that contains the multiple of 10 that you initially entered in step 1 if you wish.

How to Move Decimal Places in Excel Using Formatting

It’s also possible to use cell formatting to move decimal places in Excel. The default number settings allow you to specify the number of decimal places that are displayed, but by using a special custom formatting, you can use formatting to move the decimal point within your data, too. However, this method will fix all values at the same number of decimal places, so some of your values may be rounded to the nearest decimal place. It’s important to note that although the cells will display your amended decimals, the actual value of the cell remains unchanged. If you try to use the cell in a formula or calculation, the calculation uses the original value of the cell and not the decimal that is displayed.

1. Select the cells that you want to apply the formatting to.
2. Right-click your selection and select Format Cells.
   
3. In the Category section, select Custom.
   
4. Click in the field under Type and enter 0"."0 to move one decimal place to the left, 0"."00 to move two, and so on.
   
5. You can see what your formatted values will look like in the Sample area.
   
6. When you’re happy with how the Sample looks, click OK.
7. Your cells will now show the formatting you chose. However, the value of the cell will remain unchanged if you use it in a formula.
   

How to Move Decimal Places in Excel Using a Formula

You can also use a formula to shift decimal places in Excel. The formula is quite complex, but you don’t need to understand how it works. There are only a couple of places where you’ll need to edit it to move your decimal point by the appropriate number of places. This formula will take a number without any decimals and place a decimal point in the appropriate position. However, if your number already contains a decimal, this method won’t work. In this example, we’ll be adding a decimal point two digits from the end of the number.

1. Click in an empty cell in the same row as the number you want to add your decimal places to.
2. Enter the following formula, replacing all instances of A1 with the address of the cell containing your original number:

   =LEFT(A1,LEN(A1)-2)&"."&RIGHT((SUBSTITUTE(A1,".00","")),2)

   

3. Press Enter.
4. Your new value including a decimal place will appear.
   
5. You can apply the formula to other cells by clicking and holding the square in the corner of the cell.
   
6. Drag down and the formula will be applied to the cells you select.
   
7. If you want the decimal place in a different position, change each instance of the number 2 to the appropriate number. For example, to add a decimal point three places from the end, the formula would be:

   =LEFT(A1,LEN(A2)-3)&"."&RIGHT((SUBSTITUTE(A1,".00","")),3)

Moving Decimal Places in Excel

There are a number of different ways you can move decimal places in Excel. Some of the methods above will create a new column of data with the decimals moved. Using the paste special method allows you to replace the current data with your new data containing the moved decimals. The formatting method allows you to change your cells so that visually they show the decimal has moved, but the original values are maintained. Using this method, it’s important to remember that if you reference the cell in a formula, the calculation will use the original value of the cell and not the value that is displayed. You’ll need to think carefully about which method is most suitable for your needs.