Exceljet

Quick, clean, and to the point

How to use text formatting in Excel

In this lesson we'll look at the Text format. It may seem strange to format numbers as text, but there are times when it makes sense. The Text format displays numbers exactly as they were entered.

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.

Course 
Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.