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

Formatting Page Numbers

Need to format the page numbers you added to your document? Word makes it easy, using the same techniques you use to format ...

Discover More

Managing Comments

If you frequently add comments to cells in a worksheet, Excel provides a variety of tools you can use to manage those ...

Discover More

Checking if a Workbook is Already Open

Knowing of a workbook is already open can be a prerequisite to your macro working correctly. Here's how to check it out.

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Using Copy and Paste for Formatting

Want to copy formatting from one cell and paste it into another cell? It's easy to do if you use the Paste Special feature of ...

Discover More

Matching Formatting when Concatenating

Convert a numeric value to text and you may be surprised by how Excel displays the value. Here's a run-down on exactly what ...

Discover More

Problems with Custom Views

If you get an error when you try to use one of your custom views, it could be due to the protection you have applied to the ...

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. 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 six less than 6?

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.