How To Move Decimal Places In Excel

If you’re dealing with a couple of cells in Excel, changing decimal places manually is simple. Double click and add it where you want to move it to and you’re done. 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.

I work a lot in Excel even though I wish I didn’t. I have developed a few quick techniques for getting things done and this is one of them. I won’t pretend I figured them out because I didn’t. Friends who know a lot more about Excel than I do helped me out and now it’s my turn to pay it forward.

I use Excel 2016 so these instructions relate to that version. Office 365 or older versions of Excel should be similar if not the same.

Moving decimal places in Excel

For the sake of this tutorial, let’s say you have a column of cells with dollar values in but you want to change it to cents. So column A has $128.21 but you wanted it to be $1.2821 or 1.28 instead. We can do that in a couple of ways. Assuming your dollar amounts begin at cell A2 onwards…

• Add =A2/100 in cell B2 and drag it down the B column until you have converted all amounts in column A.

This should shift the decimal two places. You can obviously change 100 for 10 or 1000 if you need to move it further than two places. The same will apply to some of these other options too.

You could also try this way:

1. Type $100 into a spare cell and copy it.
   
2. Highlight the range of cells in column A.
   
3. Select Paste and Special.
   
4. Select Divide and hit OK.
   
5. 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 you need to.

Or you could use the Format tool to change the decimal places in Excel.

1. Highlight the range of cells in column A in your spreadsheet.
   
2. Select the Home ribbon and Format in the Cells section.
   
3. Select Format Cells in the menu.
   
4. Select Number in the new window and set Decimal Places to the value you require.
   
5. Select OK when done.
   

This takes you to the same place as these others just in a slightly different way.

Of course, this being Excel there is a formula for it too. I never use this method but you might be more comfortable with formulae than I.

Use this formula: =LEFT(A2,LEN(A2)-2)&"."&RIGHT((SUBSTITUTE(A2,".00","")),2)

Assuming your column of data still begins at A2, this should add two decimal places to your data in the same way these others do.

Those are the ways I know of to move decimal places in Excel. I do have a couple of other tips around decimals too.

Automatically add decimals to 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 which can save you a lot of time. It’s very similar to that final way of shifting a decimal place and uses the Format Cells command.

1. Select the data column you want to add a decimal point to.
   
2. Select the Home ribbon and Format in the Cells section.
   
3. Select Format Cells in the menu.
   
4. Select Number and the decimal places you want to use.
   

If you’re constantly working with decimals, you can tell Excel to use them as default. This is only really for accountants or those who only use Excel for decimals though as it will format them constantly.

1. Select File and Options in Excel.
   
2. Select Advanced and check the box next to Automatically Insert a Decimal Point.
   
3. Add the number of places in the radio menu underneath.
   
4. Select OK.
   

Round off decimals in Excel

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

1. Select cell B2 and select Formulas from the top menu.
   
2. Select the Math and Trig option from the ribbon.
   
3. Select the ROUND function from the menu.
   
4. Enter the cell data to round off in the Number box.
   
5. Enter the number of decimal points you’re rounding in the Num_digits box.
   
6. Select Ok when finished.
   
7. Drag cell B” down your data column to round off all the data you select.
   

That’s about the limit of my knowledge of decimal places in Excel. Got any more tips around this subject? Share them below if you do!