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.
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
To see how Excel can automatically round numbers with the General format, please watch this short video.
Or watch on YouTube: Excel Rounding With Number Formats
_________________
Working with problems that demand precise accounting, but is practical to report as rounded figures (think taxes or risk analyses), I grow tired of having to use funtional rounding to report numbers to the nearest thousand (or hundred).
I’ve learned a lot from your note (thanks be unto you), but lament that there is no simple way to round to hundreds or thousands automatically. Rounding functions change the value of an item, requiring spreadsheets to retain two copies of the same value: one for presentation, and another for propagation in subsequent calculations.
Hi, This article was quite useful, have one query though, #, would get 1,11,54,723 as 11155, but how to get it in 11,155 in Indian format of counting?