Preview
How Excel Processes Large Numbers
12 Digit number with General formatting applied to cell below:
When a cell has a certain formatting applied to it, that can often change the way the number is displayed. For example, if a cell has General formatting applied to it, the cell will take up to an 11-digit number. If a 12-digit number is entered with General formatting applied to that cell, that number will be displayed in scientific notation (FIGURE 1). The E+11 in scientific notation means that the number’s size is 11 decimal places to the right of 1.23457. Remember that the scientific notation style of representing the number is for cell display purposes only. The actual 12-digit number still remains in the formula bar, as shown below (FIGURE 2).
One of the key points in this section to remember is that General formatting is the Excel default format for all cells in worksheets.
15 and 16 Digit numbers with Number formatting applied to cell below:
If a cell has Number formatting applied to it, the cell will take up to a 15-digit number (FIGURE 3). If a 16-digit number is entered, that 16th digit will be truncated to a zero (FIGURE 4).
16 Digit number with Text formatting applied to cell below:
Note: An alternate method of entering a 16-digit number is by first entering an apostrophe before entering the number (FIGURE 5). Excel will treat this cell as a Text-formatted cell. You will see an error appear with a menu of choices on how to deal with this error. If you only have a few numbers to process as text, this could be an appropriate choice. If you have many numbers to format as text, see FIGURE 7 below for guidance.
Since text formatted cells cannot be used in formulas, Excel’s limit for using numbers in formulas is 15 digits of accuracy, which is more than enough accuracy for the majority of Excel users (FIGURE 6).
Dealing with Yellow Error Symbols (FIGURE 7)
If you have quite a few cells that require a 16-digit number or higher and do not want to deal with this yellow error symbol each time, you can override this error by going into the Excel Options / Formula category and change the error-processing rule. Uncheck the box for the rule:
“Numbers formatted as text or preceded by an apostrophe.” Then select the OK button and you will not get the errors anymore.
END OF LESSON
Leave a Reply