How to Stop Excel From Rounding Numbers in Your Spreadsheet
Excel is helpful in that it rounds up decimals in your sheet. But you can stop Excel from rounding numbers in just a few clicks.
If you have a Microsoft Excel spreadsheet with figures using decimal places, you may notice that your numbers round up. This happens most frequently when you have many digits to the right of the decimal place.
While this may fine for things like currency where you prefer the amounts round up, it can be detrimental for scientific or similar calculations where that exact number must be shown. Widening the width of the column does not solve the problem. So here, we’ll show you two ways to stop Excel from rounding your numbers.
How to Quickly Increase the Decimal Place for a Cell
If you only have a few cells containing static decimal numbers, you can stop the rounding quickly.
- Select the cell or group of cells with your rounded numbers.
- Go to the Home tab.
- Move to the Number section of the ribbon and click the Increase Decimal You can hit that button as many times as you need to so that the cell displays all digits to the right of the decimal point. You can go even further if you like, which will add zeroes to the end of your number and accommodate future changes you may make.
Right next to the Increase Decimal button is the Decrease Decimal button. So you can use that option too if you prefer to go back to rounded numbers.
How to Set a Specific Decimal Place for Cells
The Increase Decimal button gives you a fast and simple way to make room for your digits. But if you have many cells, an entire sheet, or need different decimal places for various sections, you can set a certain decimal place.
There are five cell format categories in Excel that use decimal points and those are Number, Currency, Accounting, Percentage, and Scientific. Depending on the type of data you’re using, you can select any one of those and set an exact decimal place.
Select the cells you want to format and do one of the following:
- Right-click the group and pick Format Cells.
- Click the drop-down box in the Numbers section of the ribbon and pick More Number Formats at the bottom.
- Launch the Numbers dialog box by clicking the arrow on the bottom right of that section of the ribbon. (Not available in Excel for Mac.)
All of the above actions will display the Format Cells window. When it opens, choose the Number tab. Select the category you want to use on the left and then use the Decimal places box to set the number of digits to follow the decimal point. You can see a preview of the first number in your group of cells as you increase the decimal places in the Sample box.
While you have the Format Cells window open, you can customize other options depending on the format category you choose. For instance, you can pick a 1000 separator for numbers or a symbol for currency.
Click OK when you finish and you’ll see your changes apply immediately.
Adjust the Column Width
If you notice after increasing the decimal places that number symbols (#) display, you’ll need to also increase the width of the column(s). Whenever you have data in a cell that isn’t wide enough to accommodate it, you’ll see this symbol. You can change the column width a few different ways.
Place your cursor on the right side of the column header and then:
- Drag the double-sided arrow to the right and release. You can continue to do this as much as necessary until you see all numbers.
- Double-click when you see the double-sided arrow. This will automatically resize the column to accommodate the longest number.
- Right-click, select Column Width, enter a set point value for the size and click OK.
Get Your Exact Decimal Numbers in Excel
It’s a nice feature to have Excel automatically round numbers, but it’s not always practical. So remember how easy it is to increase the decimal places and stop Excel from rounding your numbers when needed.
For more on using Excel, take a look at how to calculate a percentage change or how to work with columns and rows.