How To Manage and Move Decimal Places in Excel

Jamie Read more November 5, 2023

_{^{Last updated/edited by Steve Larner on Nov. 5, 2023.}}

How To Manage and Move Decimal Places in Excel

If you’re dealing with a couple of cells in Excel, changing decimal places manually is simple. You simply double-click and add it where you want to move it to, and you’re done. However, when you’re dealing with larger spreadsheets with hundreds of entries, it becomes more challenging. Fortunately, there are a few ways to move decimal places in Excel.

Moving Decimal Places in Excel

For the sake of this tutorial, let’s say you have a column of cells with specific values in them (IGNORE THE “$” IN THE IMAGES), but you want to change the decimal places. So “column A” has “128.21” and you want it to be “1.2821” or “1.28” instead. You can do that in a couple of ways, assuming your dollar amounts begin at cell A2 onwards.

Method 1: Use a Simple Calculation (Cell Divided by Value)

Copy and paste the following formula into cell B2:
=A2/100
Click and hold on the “tiny box” in the lower-right corner of “cell B2,” then drag it down next to the last value you want to change from “column A.”
Excel should now uniquely convert each row in “column A” to the newly formulated output in “column B.”

The above example should shift the decimal places two spaces to the left. You can change 100 for 10 (moves one space to the left) or 1000 (moves three spaces to the left) if you need to move decimal places differently.

Method 2: Use Simple Math

Aside from the basic formula, you can use math to calculate the desired decimal places. Here’s how to do it—IGNORE THE “$” IN IMAGES.

Type “100” into a spare cell and copy it.
Highlight the range of cells in “column A.”
Select “Paste Special.”
Choose “Divide” and click “OK.”
Delete the cell with 100 in it to tidy up.

You end up at the same place but use a slightly different method. Again, you can use 10 or 1000 to shift more decimal places if necessary.

Method 3: Use the Format Tool

Besides using formulas or math, you can also use the “Format” tool to change decimal places in Excel.

Highlight the range of cells in “column A” that you want to change within your spreadsheet.
Select the “Home” ribbon, then click the “Format” dropdown in the “Cells” section.
Select “Format Cells” in the dropdown list.
Click the “Number” tab in the new “format Cells” window and change the “Decimal places” option to the required value (1, 2, 3, 4, etc.)
Select “OK” when done.

The above steps take you to the same place as the other processes, just in a slightly different way.

Method 4: Use Formulas

Besides using simple calculations, simple math, and the Format tool in Excel, you can use formulas to change decimal places. In this example, you’ll see a change of two decimal places to the left, just like the previous examples in the other processes.

Copy and paste the following formula into cell C2:
=LEFT(A2,LEN(A2)-2)&"."&RIGHT((SUBSTITUTE(A2,".00","")),2)
Left-click and hold the “grab box” in the lower-right corner of cell C2 and drag down to the last value in “column A.”

Assuming your column of data still begins at A2, this should add two decimal places for each value in “column A” to “column C” in the same way the other processes do.

How to Automatically Add Decimals to Excel Cells

Sometimes, when you paste a bunch of cells into Excel, it will remove decimals and ruin your data. You can tell Excel to add them as you either enter data or paste it, saving you much time. It’s similar to other ways of shifting a decimal place and uses the “Format Cells” command.

If you’re constantly working with decimals, you can tell Excel to use them as default so it will format them automatically.

Select “File,” then choose “Options.”
Click the “Advanced” option on the left and check the box next to “Automatically Insert a Decimal Point.”
Add the number of decimal places in the “Places” box underneath.
Select “OK” to save the changes.

How to Round Off Decimals in Excel

If you’re dealing with big numbers, you may also like to round them off to just a specified amount of decimal points to make the data easier to read. That also makes a spreadsheet easier to comprehend while still being accurate to however many decimal places you need it to be. Here’s how to do it.

Select “cell B2” and click the “Formulas” tab from the top menu.
Choose “Math and Trig” from the ribbon.
Select the “ROUND” function from the menu.
Enter the cell to round off in the “Number” box.
Type the number of decimal points you’re rounding in the “Num_digits” box.
Select “OK” when finished.
Left-click and hold the “grab box” in the lower-right corner of “cell B” and drag it down your data column to round off all cells from “column A” to “column C.”

As you can see, there are many ways to adjust decimal places in cells when using Microsoft Excel, whether changing current entries or turning on autopilot for new ones. Some work great for smaller spreadsheets and workbooks, while others make things more efficient for large ones.