Exceljet

Quick, clean, and to the point

How to fix the #### (hashtag) error

Excel formula: How to fix the #### (hashtag) error
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:

#### error example - column too narrow

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

#### error example - column too narrow FIXED

 

Author 
Dave Bruns

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.