Round Numbers With Excel Formatting

Did you know that Excel limits the number of numbers that appear in a cell, in General format? I discovered that limitation this week, while updating my page on rounding functions in Excel.

Have you run into this limit? It was something that I hadn’t noticed before, and there doesn’t seem to be any setting to adjust this. Fortunately, I did find a way to fix the problem.

These Go to Eleven

What’s the character limit for numbers? Maybe someone at Microsoft is a fan of the movie, This is Spinal Tap, because “these go to eleven”.

In the screen shot below, the full number in cell B2 is shown, because it has 11 characters, including the decimal point. However, the number in cell B3 has an additional 3 characters added, and they don’t appear. Even though there is enough space in the column, the number is rounded, and only 11 characters appear.

roundlongformat01

Not Just Decimals

It’s not just decimals that are rounded. Integers have their formatting changed to Scientific, if they exceed 11 characters.

In the next screen shot, the 11 characters show up correctly in cell B2. In cell B3, I added one more number at the end, and the number was switched to Scientific format. Again, there is plenty of room in that column – more than enough to show all the numbers.

roundlongformat02

More Automatic Formatting

Excel does other types of automatic formatting too, if the cell is in General format.

In the screen shot below, you can see 11-character numbers at their full width, in column B. As the column width is reduced, the numbers are rounded, either by rounding the decimals, or changing to Scientific format.

Finally, when no format fits, number signs (pound signs / hash tags) are shown in the cell.

roundlongformat03

If there are numbers signs in the cell, you can see the actual value by either of these methods:

  • click on the cell, and look in the formula bar
  • point to the cell, and see the number in the pop-up tip

roundlongformat04

Stop the Automatic Formatting

If you don’t want Excel to automatically adjust the number formatting, you can apply a specific format, such as Number, or use a Custom number format. These specific formats will only change to number signs, if the column is too narrow to display them. The formatting won’t automatically change, at other times.

The Custom formats are created and stored in the Format cells dialog box, on the Number tab.

roundcustomformat05

A few examples of custom formats are shown below.

roundcustomformat06

You can even use a custom number format to line up numbers that have varying decimal places. The question mark (?) is a placeholder that adds extra spacing, so the numbers line up at the decimal point.

roundcustomformat07

There are more examples of Custom formats on the Microsoft website: Create a Custom Number Format

Video: Automatic Rounding in Excel

Watch this short video to see how Excel can automatically round numbers with the General format.

_________________

Save

You may also like...

3 Responses

  1. Pawel says:

    Thank you for the guide! I`m glad i could finally find how to stop the automatic formatting after a decent time of Googling. And special thanks for the video! :)

  2. Chris, California says:

    It’s a shame as Microsoft doesn’t appear to address this issue. I need 14 digits to show, without decimals and no exponential forms.

  1. April 20, 2015

    […] Round Numbers With Excel Formatting […]

Leave a Reply to Pawel Cancel reply

Your email address will not be published. Required fields are marked *