Categories
Microsoft Excel

How To Change Decimal Places in Excel (3 Ways)

When you’re working with numbers or financial information in Microsoft Excel, number formats often default to two decimal places. These decimal points aren’t always necessary and they can cause ###### symbols to appear if your columns aren’t wide enough.

In this article, we’ll explore 3 ways to change decimal places in Excel.

Use Number Format to Set the Number of Decimal Places

Every cell in a new spreadsheet starts out with the general format applied. You don’t have to do anything – it’s the default setting.

In the screenshot (below), you can see what the general format looks like.

Spreadsheet showing numbers with the general format applied.

With the general format, text is left-aligned and numbers are basically displayed the way you type them. If you enter numbers with decimal places, that’s how they’ll be displayed but other numbers might might not have decimal places at all.

To make the numbers in our sample spreadsheet easier to read, we can use the Format Cells dialog box to add a thousands separator, specify the number of decimal places, and ensure that all of the numbers are displayed consistently.

To specify the number format as described, do this:

  1. Drag to select the numbers in B9:F14.
  2. Click the dialog box launcher in the Home > Number group.
    The Format Cells dialog box is display with the Number tab selected.
  3. Select Number from the Category: section.
  4. Enter 2 beside Decimal places: and select the checkbox beside Use 1000 Separator (,).
  5. Click OK.
Format Cells dialog box with the Number tab selected

Change the Number of Decimal Places with Increase / Decrease Decimal

In the previous section, we talked about changing number formats using the Format Cells dialog box. Sometimes having two decimal places in Excel will make the numbers too wide for the column.

Participants who take Microsoft Excel training with me normally see the ###### symbols as an invitation to make the columns in any spreadsheet wider. Sometimes it makes more sense to reduce the number of decimal places – especially when you’re dealing with whole numbers.

Spreadsheet with columns that are too narrow

To reduce the number of decimal places in this worksheet:

  1. Drag to select the numbers in the range B9:F14.
  2. Click the Decrease Decimal button in the Home > Number group twice.
Reduce decimal places with decrease decimal

The Increase Decimal button is located to the left of Decrease Decimal in the Home > Number group. It provides a quick way to increase the number of decimal places in the event you’d like to display numbers with greater precision.

Use Formulas to Move Decimal Points

You can also use formulas to calculate new values and change the number of decimal places in the process.

Decrease decimals with formulas

In this example, I divided each of the values in column B by 10. You can see the calculation in the formula bar.

Basically, I did this:

128.75 / 10 = 12.875

Use multiplication to increase the number of decimal places, use division to decrease decimals.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.