How do i stop excel from showing hashtags?

Summary 

The "hashtag" error, a string of hash or pound characters like ####### is not technically an error, but it looks like one. In most cases it indicates the column width is too narrow to display the value as formatted. However, there are other causes. See below for more information.

Explanation 

The "hashtag" error, a string of hash or pound characters like ####### is not technically an error, but it looks like one. In most cases, it indicates the column width is too narrow to display the value as formatted. You might run into this odd looking result in several situations:

• You apply number formatting [especially dates] to a value
• A cell containing a amount of text in a cell is formatted as a number
• A cell formatted as a date or time contains a negative value

To fix, try increasing the column width first. Drag the column marker to the right until you have doubled or even tripled the width. If the cell displays properly, adjust the width back down as needed, or apply a shorter number format.

If the hash characters persist, even when you make the column much wider, check to see if you have a negative value in the cell, formatted as a date or time. Dates and times must be positive values.

Example

In the worksheet below, Column A contains start dates, and Column B contains a formula that adds 5 days to the start date to get and end date:

Below, the width of column B has been increased, and dates now display correctly:

Related formulas 

About the #N/A error The #N/A error appears when something can't be found or identified. It is often a useful error, because it tells you something important is missing – a product not yet available, an employee name misspelled, a color option that...

About the #DIV/0! error The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You...

About the #REF! error The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where references are...

The #NAME? error occurs when Excel can't recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a matter of correcting spelling or...

The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel. Fixing a #VALUE! error is...

The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. The examples below show formulas that return the #NUM error. In...

The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma [,] or colon [:] between two cell references. Technically, the space character is the "range intersect" operator and the...

The "hashtag" error, a string of hash or pound characters like ####### is not technically an error, but it looks like one. In most cases, it indicates the column width is too narrow to display the value as formatted. You might run into this odd...

About spilling and the #SPILL! error With the introduction of Dynamic Arrays in Excel , formulas that return multiple values " spill " these values directly onto the worksheet. The rectangle that encloses the values is called the " spill range "...

With the introduction of Dynamic Arrays in Excel formulas , there is more emphasis on arrays . The #CALC! error occurs when a formula runs into a calculation error with an array. The #CALC! error is a "new" error in Excel, introduced with dynamic...

Related videos 

In this video, we'll walk through each of the error codes that Excel displays when there's something wrong with a formula. We'll also look at some simple ways to resolve the errors.

It can sometimes be difficult to know what is causing formula errors. In this video, we'll look at how to track down the source of a formula error in Excel.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel video training

Quick, clean, and to the point.

Learn more