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.
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:
- Drag to select the numbers in B9:F14.
- Click the dialog box launcher in the Home > Number group.
The Format Cells dialog box is display with the Number tab selected. - Select Number from the Category: section.
- Enter 2 beside Decimal places: and select the checkbox beside Use 1000 Separator (,).
- Click OK.
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.
To reduce the number of decimal places in this worksheet:
- Drag to select the numbers in the range B9:F14.
- Click the Decrease Decimal button in the Home > Number group twice.
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.
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.
