What does Pound / Hash Symbol (####) Mean in Excel?

At some point, you might have come across a cell with a number of pound symbols or hash characters (####).

If you’re a beginner and seeing this for the first time, it might come as a surprise (or even a source of panic for you).

You might have checked your formulae over and over for errors, but would not have found any!

The reason you’re confused is that the #### symbol or pound error, does not really signify an error, although it may look like that.

In this tutorial, we are going to look at some of the possible reasons you’re seeing this symbol, along with ways to correct it.

Table of Contents

Possible Reasons you are Seeing the ### Symbol (Pound/Hash Symbol)

The #### appears in a cell for two possible reasons:

Your column might be too narrow to display contents of the cell
Your cell might contain negative date or time values

In both cases, it is possible to work through and get rid of the symbols. You can follow a series of steps to narrow down to the source and correct it.

Here are some things you can try out to solve the problem:

Problem 1: Your Column is too Narrow to Display the Contents of the Cell

Sometimes when the content of a cell is longer than what the column can display, it ends up showing the #### (hash symbols).

One example of this could be the long date format, which often exceeds the column width.

All the solutions for this issue would involve fitting the content of the cell within it.

Solution 1 – Increase the Width of the Column

The quickest and easiest way to fix the problem is to move the mouse cursor to the header where the individual letters appear for each column.

Position your mouse pointer over the right edge of the column, till it turns into a double-ended arrow.

Drag the cursor to the right till all the contents of the cell are visible.

Alternatively, you could simply double click on the right edge of the cell. This will adjust the column width to the size of the widest cell in the column.

Double click on the column header to adjust the width

Solution 2 –Shrink Contents of the Column

If you don’t want to mess with the column width, you can try reducing the size of the contents inside the cell.

For this, just right-click on the cell, and click ‘Format cells’ from the context menu that appears.

Right-click on the cell and then click on Format Cells

Select the ‘Alignment’ tab and check the box next to ‘Shrink to fit’.

This will reduce the size of the cell’s contents to fit the width of the column:

In most cases, this is not the best solution. But if you want to keep the column widths as is, this does the trick.

Solution 3 –Decrease Decimal Places in Numbers

If you have a number or a formula in your cell, that returns a number, then try reducing the number of decimal places.

For this, click on the ‘Decrease decimal’ button from the Home tab (under the Number group).

Solution 4 –Shorten Dates

If the cell contains a date, you can try shortening the date.

For this, click on the dropdown next to ‘Number format’, in the Home tab. From the dropdown menu that appears, click the ‘Short Date’ option.

Also read: Excel Showing Formula Instead Of Result

Problem 2: Your Cell Contains Negative Date or Time Values

Date and Time in Excel can not be negative.

So if you have a cell where you want to show date or time as negative, Excel revolts and shows the content as hash symbols.

It fills the entire cell with the hash sign, so if you adjust the column width, you will still see the hash symbols.

Solution – Verify that Date and Time are Positive Values

The minus sign could have been added by mistake or could be the result of a calculation.

If it was by accident, then, of course, the easiest solution would be to remove the minus sign and change the negative value to positive, provided it does not affect subsequent calculations.

Check Formula to Avoid Negative Date or Time Value as a Result of Computation

If the negative date and/ or time value is a result of a calculation, then check the formula to rule out any errors that may be causing this.

Change Negative Date or Time Values to a Different Number Formats

If the formula is alright, and you do, in fact, expect a negative date or time value, then consider changing the format of the cell to a different format.

There are two ways to do this.

One way is by changing the Excel date system to the 1904 date system.

Do this by navigating to File->Options->Advanced, scrolling down to the ‘When calculating this workbook’ section, and checking the box next to ‘Use 1904 date system’.

Note: By default, Excel uses the 1900 date system (all dates are serial numbers counted from Jan 1st, 1900). In the 1904 date system, all dates are serial numbers counted from Jan 1st, 1904. So changing the date system might end up changing the results of some of your computations involving dates

If you don’t want to mess with the date and time settings, then enclose the formula inside a TEXT function, and enter your date format as a string in the second parameter to the function.

So if your formula is B5-B4, you can change it to =-TEXT(B5-B4,”dd-mm-yy”)

In this tutorial, we discussed some of the reasons you might be seeing a pound error or a series of # symbols in some of your cells.

We also showed you how you can systematically tackle the problem to get rid of the symbols.

We hope this was simple and easy for you to follow.

Other Excel tutorials you may also like: