Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Hash Marks Displayed Instead of Cell Contents.

Hash Marks Displayed Instead of Cell Contents

by Allen Wyatt
(last updated June 17, 2016)

21

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 "August 22, 2013," that date takes more column width to display than does "8/13/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 Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Hash Marks Displayed Instead of Cell Contents.

Author Bio

Allen Wyatt

With more than 50 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. ...

MORE FROM ALLEN

Turning Off Speech Capabilities

Excel can talk to you, reading back whatever you enter into a cell. If you want to turn this capability off, you'll want to ...

Discover More

Sorting Letters and Numbers

Sorting information in a worksheet can be confusing when Excel applies sorting rules of which you are unaware. This is ...

Discover More

Determining If a Date is between Other Dates

Need to figure out if one date is between two other dates? There are a wide variety of formulaic approaches you could use to ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Changing Currency Formatting for a Single Workbook

Currency is formatted differently in different corners of the world. Most formatting uses periods and commas to indicate ...

Discover More

Moving Custom Formats to Number Formatting Categories

Moving your custom formats into a formatting category other than "custom" isn't something you can do in Excel. Here's ...

Discover More

Applying Table Formats

Want to format your data tables in a hurry? It's easy to do if you use the built-in table formatter provided in Excel.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 8?

2017-03-16 23:33:46

Esme Greenfield

Thank you!


2017-01-22 10:48:07

MIchael Armstrong

Of course, if you've got a whole bunch of dates formatted as numbers in that fashion (i.e., yyyymmdd), you can easily convert them to "Excel" dates with a formula. For example, if cell H8 contains 20150401 formatted as GENERAL, the formula: =DATE(LEFT(H8,4),MID(H8,5,2),RIGHT(H8,2)) will display in DATE format as 4/1/2015, or in NUMBER format as 42095.00.


2017-01-22 07:56:27

Barry

@ Ron,

The problem with cell A is that Excel is treating the number (20150401) as a number and not a date (Excel parses entries and tries to interpret their format, if it doesn't match any of Excel's formats then it just treats it as entered). The entry in Cell B is seen by Excel as a date and it converts the entry to 42095, and sets the formatting of the cell to the short date default format.

The hash signs are as a result of the limits on how dates can be formatted. Excel stores dates as a whole number of days from 1st Jan 1900 (1-Jan-1900 = 1, 2-Jan-1900 =2, etc.) so 1-Apr-2015 is stored as 42095. The maximum date that Excel can handle is 31-Dec-9999 which equates to the number 2958465. So the number 20150401 exceeds 2958465 which Excel deems to be an error and displays this error as hash signs filling the cell.


2017-01-21 10:03:06

RON WHYTOCK

Have tried all format options without success.

Both these cells SHOULD SHOW DATE(1 April 2014)

Cell A: 20150401 is formatted as GENERAL. If I format as DATE it changes to a ###############################

Cell B (right below cell A): 2015-04-01 formatted as DATE & no problem

I cannot format A to agree with B

Have tried clearing all formats and all format options
Help! Thank you


2017-01-04 09:36:16

mcs

Thanks for the tip to change the cell format to "general" - saved me a lot of time!


2016-10-16 15:25:06

Bb

Thanks for the info on widening the cells in order to get numbers to print! Saved me!!


2016-10-14 10:56:17

Mataeo

Wow...great tip to simply make the column wider to rid the hashtags! I am now an Excel genius.
Thanks!


2016-09-27 09:03:23

Dhruvang Chauahn

What is Different Signs In EXCEL


2016-09-04 17:54:27

rafael

brilliant, thank a lot!


2016-07-24 05:16:29

RKS

Thanks, This worked, I changed the format from Plain Text to Rich Text in front end fields on form in MS Access, for the number type back end fields.


2016-07-10 21:17:15

Tiffany

I couldnt figure out why my text was displaying ##### and i had it formatted to text vand wrap text.. You just saved me about an hour of troubleshooting. THANKS!!!


2015-07-15 00:54:20

Anasuddeen

Thanks,it is clear and very useful.


2015-05-06 08:13:36

Barry

@Vicki,

There is a little trick that may just work for you. It is based on the fact that Excel tries to "bend" the datatype to do what you want, sometimes.

I've tried this in Excel 2010 but not in other versions of Excel. If you put a number in a cell formatted as text or when entering a number preceding it with an apostrophe the number is technically text and therefore should NOT be available for computational functions BUT Excel will "bend" the datatype and interpret the number as a number. I know this doesn't work with many functions but does work for arithmetic functions (+,-,*,/,^).

For example I have in cells A1 '12345678901234 (note the apostrophe) in cells C1 the formula =A1/2 give the correct result (61728394506172.5).

If you want to use the value in a function then using a "do nothing" arithmetic function first will get it to work (multiply or divide by 1, add or subtract zero).

Using the formula =SUM(A1, A2,....) doesn't work as Excel sees A1 as text but =SUM(A1*1, A2,....) does work as the arithmetic operator forces the text number to be treated as a normal number. Note this won't work if you want to sum a range.

The display of cell A1 will not be ######### but will work like any other text entry, and spill over into the next cell if it is blank or be truncated if not blank.

I can't vouch for this working in all functions - this really would require extensive testing.


2014-11-17 17:50:14

awyatt

Vicki: Sounds like the column that shows the "spill over" is formatted as text. Text automatically spills over to the next column, as long as there is nothing in it. If the column is formatted as anything other than text, then you get the "####" for numeric values that are too wide for the column.

Short story: If you want to keep the numbers as numbers (meaning you want to use them in other formulas and such), then you'll need to widen the column.

-Allen


2014-11-17 17:25:42

Vicki

Hi, can you help me with this problem?
I have a column formatted not to wrap.
I previously entered 12-digit numbers in the cells, which exceed the width of the column.
Those numbers show, bleeding as they should into the next (empty) column.
Now, when I put the same type of numbers in other cells in the same column, hash ### signs are shown.
I have tried copy/pasting the formatting from the cells which show correctly. That does not fix it.
I have tried copy/pasting the entire contents of the cell which shows correctly. It displays correctly in the target cell - until edited, then the hash ### signs reappear.

This is driving me completely nuts. Is there some code just telling excel NEVER to put # in? Who wants the stupid things!

At least, can you please provide a specific solution as to how to show these numbers? Without widening the column? I have a lot of columns and if I widen them I cannot see what I need to see in the spreadsheet


2014-08-30 15:43:58

Barry Kruse

Another thing that sometimes causes this is if the cell has been indented. This is hard to see in a numeric cell because it's right-justified, but it's easy enough to check in the format dialog box.


2014-08-29 17:25:30

BjM

If ###### prints in the cell instead of the displayed value in the app, change your print drivers from PCL to PS.


2014-08-12 09:02:56

IS

I am using Excel 2010. I have a cell that contains only text with just over 300 characters. It had been displaying fine at around 250 characters, but after adding around 60 characters it now displays all #s across the cell.

This is true even when I change the format to "General," the supposed 'fix' I keep seeing everywhere for this problem.

Please help.


2014-06-03 10:27:56

Nosiku

on the general formats for dates and time the date appears as a series of hash totals


2014-04-21 23:32:14

Abd

Thank you very much. I was very fraustrated. Not even MS employees were able to help me. I had lots of dates on my excel file, but if I zoomed out to see more content a page then I would see # # # # marks. Once I zoomed in, then the contents would show. This article was very helpful. All I did was to increase the width of the cells and everything seems to normal. Again, thank you very much.


2013-10-22 21:02:56

Gary

In my case everything displays fine in Excel and print preview, but when it actually prints out it's all just ### in half the cells. Any fix for this?


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.