How to Move Decimal Places to Left or Right in Excel

If you are currently working on a long list of numbers in your Excel file and are looking for ways to move the decimal places in them, either to the left or right, then you’ve come to the right place.

In this article, I’ll show you the quickest ways to transfer the decimal point from one place to another.

This article will serve as a refresher on our basic math. Yes, we will relearn the formulas taught in primary school. The only difference is that we will learn how to do it in Excel.

Moving Decimal Places to Left or Right

When moving decimal places, there are 2 things that you have to consider:

1. the direction of the movement – whether to the left or the right; and
2. the number of places that you’d like to move the decimal point – it can be 1 or 2 decimal places (or even more)

If you need to move the decimal places to the left, divide the number by the power of 10s.

The table below shows the formulas to use. Note that it varies depending on the number of decimal places to move.

If you need to move the decimal places to the right, multiply the number by the power of 10s.

Once you’ve identified the formula, the next thing to do is set it up in Excel. I’ll show you 2 nifty ways to do this.

Add Formula by Dragging the Fill Handle

1. Select the first cell where you will add the formula (e.g., cell B1) and type the equal sign (=).

2. Click the first cell of the column where your first number is located (e.g., cell A1).
Notice that the selected cell’s address will appear.

3. Type slash (/) for the division operator and asterisk (*) for the multiplication operator. Then type the power of 10s to divide the number by (e.g., 10, 100, 1000).

4. Press ENTER. Notice that the cell will display the same number with the decimal place(s) moved either to the left or right.

5. Once you’re happy with the result, the next step is to copy the formula to the remaining cells below it.

To do this, click on the cell with the formula (e.g., cell B1), then hover the mouse to the bottom right of the cell (the one with a small green square). You’ll notice the plus sign (+) appears. That is the Drag Fill Handle. When you drag this handle down, you will automatically fill the cells based on the cell on top.

Drag the plus sign down until you reach the last cell.

You’ll then see the formula added to the remaining cells.

6. If you want to replace your existing numbers with the new decimals (the ones where the decimal points are moved), copy the cells containing the formulas and paste them as values.

To do this, highlight all cells with the formula and copy it (CTRL + C).

Select the first cell with numbers and press CTRL + ALT + V.

The Paste Special menu will appear. Select Values from the options and click OK.

And that’s it! Your current numbers will be replaced with the updated ones.

You can then remove the cells containing the formulas.

Note This

Note that you should paste the numbers as values to ensure that only the resulting values are copied (not the formulas).

The “Paste Special Operation” Option

The next option is pretty cool and has the fewest steps involved.

All you have to do is:

1. Type the divisor or the multiplicand in one cell (e.g., 10, 100, 1000).

2. Select the cell and copy it (CTRL + C).

3. Highlight all the cells containing your numbers. Press CTRL + ALT +V. From the Paste Special menu, select either Multiply or Divide. Then, click OK.

4. And voila! Your numbers are now updated with the decimal points moved to the left or right.

Conclusion

It’s pretty easy to move the decimal points in a number. All you have to do is remember that:

1. To move decimal places to the left, divide.
2. To move decimal places to the right, multiply.
3. The number of zeroes in the formula represents the number of times the decimal point will move.

You can then choose from the options above your approach for adding the formulas in Excel.