Let’s take a look. In column B of our table we have a set of numbers that are displayed as we like, with both leading and trailing zeros. As we’ve seen in an earlier lesson, if we try to enter these numbers in cells that are using the General format, the leading and trailing zeros will be stripped as the values are entered. One solution to this problem is to first format cells in Text format. Now we can enter the numbers in the format we’d like to see displayed, and Excel will display and store each number exactly as entered. Note that Excel aligns values to the left. This indicates that Excel is treating these values as text. You can, if you like, change the horizontal alignment. You may see Excel flag cells with text entered in this way with a warning that says “Number Stored as Text.” You can ignore these errors on a cell by cell basis. Or, you can change your Error Checking options to disable this flag for all cells in the worksheet. Another option for entering numbers as text is to start each number with an apostrophe. This tells Excel explicitly to treat the number as text, and there is no need to switch to Text format. You can leave the cell format set to General. When displaying numbers in Text format, note that Excel may let you use the cell values in simple formulas, but not in common functions like SUM.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
