Cell Shows ##### (Excel)


To totally unlock this section you need to Log-in


Login

Sometimes, when you enter a formula into Excel, the cell simply shows #####, instead of the expected value.

Cell Shows ##### (Excel)

Possible Reason No. 1

This problem most commonly occurs because the cell is not wide enough to display the value that it has calculated.

Some cell formats (e.g. decimals) can be shortened if a cell is not wide enough to display the entire number. However, some formats (e.g. dates and times) require the cell to be wide enough to display the entire value. If the cell is not wide enough, it will display a row of hashes.

Solution

This problem is easily solved, by altering the cell width. The easiest way to do this is to drag the bar separating the column headers (as shown in the image on the right), until the cell is wider.

Cell Shows ##### (Excel)

Alternatively, if you double-click on this bar, the cell should automatically re-size to fit the contents.

Possible Reason No. 2

If Excel continues to display a string of # symbols, no matter how wide you make your column, it is likely that Excel is attempting to display the contents of the cell as a date or time, but the cell contains an invalid date or time value.

As dates and times are stored as positive numeric values within Excel, some values (for example negative values) are invalid as dates or times. Excel shows this by filling the cell with # symbols.

Solution

If the cell is intended to contain a date or time, check your formulas. Excel dates and times must be positive numeric values. Note that Excel can not handle negative dates or times.

If the cell is not intended to contain a date or time, change the formatting of the cell.

The quickest way to change a cell's formatting is to select the cell to be formatted and then select the required cell formatting from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):

Cell Shows ##### (Excel)