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.
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.
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.
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
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.
A few examples of custom formats are shown below.
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.
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.