Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
With more than 35 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Bob is having some problems getting Excel to display the text within a cell. What is happening is that Excel is displaying a series of # signs instead of the text. He notes that he is not even close to the character limit of the cell.
The answer here depends on what you mean by "the character limit of the cell." Generally, such a statement means that you haven't reached the limit of the text that Excel can store in the cell—approximately 32,000 characters. It is important to keep in mind that what Excel can store and what it can display are two different things, as will shortly be discussed. If, however, by "character limit" you mean that the cell is wider than what is stored in the cell, that is a separate issue.
First things first: Excel can store about 32,000 text characters in a cell, but it can only display up to 255 characters if the cell is formatted as text. If the cell contains more than 255 characters and the cell is formatted as text, then the hash marks are displayed. The solution is to change the format of the cell to general; then the text will display as you expect.
The more common occurrence is to see hash marks displayed when the cell contains a numeric (or date) value. If the cell is too narrow to display the value, then the hash marks are shown. They indicate that an "overflow" condition has occurred and that your value cannot be displayed as you want.
This is particularly common when displaying dates using a format that requires more horizontal space. For instance, if you display a date as "September 22, 2010," that date takes more column width to display than does "9/22/10." The solution is to simply widen the column so that the display doesn't overflow the width.
Dates will also display hash marks if you attempt to display a date value outside the range of dates that Excel can handle (1/1/1900 through 12/31/9999).
You should also note that you might see hash marks appear if you change the size of the font used in a cell. Change the font to a larger size, and Excel may not be able to display the value horizontally. If you can't widen the column then consider making the font smaller so that Excel can make the full value visible.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8444) applies to MS Excel versions: 2007 | 2010
You can find a version of this tip for the older menu interface of Excel here: Hash Marks Displayed Instead of Cell Contents.
Your Data, Your Way! Want the greatest control possible over how your data appears on the page? Excel's custom formats can provide that control, and ExcelTips: Custom Formats can unlock the secrets to creating your own custom formats. Check out ExcelTips: Custom Formats today!