Tuesday, October 4

How to move decimals in Excel

If you’re dealing with a couple of cells in Excel, changing the decimal places manually is simple. Double click and add it where you want to move it and you’re good to go. When dealing with larger spreadsheets with hundreds of entries, it becomes more challenging. Fortunately, there are a few ways to move decimals in Excel.

I work a lot in Excel although I wish I hadn’t. I have developed some quick techniques to get things done and this is one of them. I won’t pretend I discovered them because I didn’t. Friends who know a lot more about Excel than I do helped me and now it’s my turn to return the favor.

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

Move decimals in Excel

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

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

This should shift the decimal two places. Obviously you can change 100 to 10 or 1000 if you need to move it more than two places. The same will apply to some of these other options as well.

You can also try this way:

  1. Type $100 in a free cell and copy it.
  2. Highlight the range of cells in column A.
  3. Select Paste and Special.
  4. Select Split and press OK.
  5. Delete the cell with $100 to sort.
See also  5 Things To Know Before Playing The New 'Strand' Game, Witch Strandings

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

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

  1. Highlight the range of cells in column A on your spreadsheet.
  2. Select the Home ribbon and Format in the Cells section.
  3. Select Format Cells from the menu.
  4. Select Number in the new window and set Decimal Places to the value you need.
  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 also a formula for it. I never use this method, but you may be more comfortable with the formulas than I am.

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

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

Those are the ways I know to move decimals in Excel. I also have a couple of other tips on decimals.

Automatically add decimals to cells

Sometimes when you paste a bunch of cells in Excel, it will remove the decimals and mess up your data. You can tell Excel to add them as you enter or paste data, which can save you a lot of time. It’s very similar to that final way of changing a decimal place and using the Format Cells command.

  1. Select the data column to which you want to add a decimal point.
  2. Select the Home ribbon and Format in the Cells section.
  3. Select Format Cells from the menu.
  4. Select Number and the decimal places you want to use.
See also  In Nightmare confirms physical edition for March • Console and Dashboard

If you constantly work with decimals, you can tell Excel to use them by default. This is only for accountants or those who only use Excel for decimals as it will constantly format them.

  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 below.
  4. Select OK.

Round decimals in Excel

If you’re dealing with large numbers, you can round them to a couple of decimal points to make the data easier to read. That makes a spreadsheet easier to understand while still being precise in the number of places you need. Here’s how to do it.

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

That is the limit of my knowledge of decimal places in Excel. Do you have more tips on this topic? Share them below if you do!


Leave a Reply

Your email address will not be published.