Why ### in Excel Occur and How to Fix It

In this article we will learn about why #### (hashtag) occur And How to Fix ### in Excel.

In excel, at times we get #### (hashtag) errors. This error occurs when there is nothing missing in the cell, it's just that there isn't enough space for the new or updated value. As per Microsoft official site a #### error is Excel's way of saying that the cell width isn't enough for the length of the value.

There could be the following reasons for this error to occur.

  1. Length of the new or updated decimal number doesn't have sufficient cell width.
  2. Usually, a long date format doesn't have enough to fit the value.
  3. Negative date or Time value generates this error.

Let’s understand how this error occurred and how to fix them with some examples.

####  Error due to not having enough space for decimal value

The most basic reason for the  hashtag  error is when the formula used in the cell returns a decimal number which cannot fit in the cell width. Generally Excel function returns a decimal number and lack of space for the cell value shows the ###### error. Here, a ####  error suggests that the width of the cell needs to increase or the decimal value can be shortened. See the below example to understand more.

For example, the mathematical PI function returns the irrational value up to 8 to 9 decimal digits. Here is the usage & error occurrence shown below.

Look in the above snapshot and realize the what excel does with the result.

How to fix it! 

You can fix the error for the result. See the below steps to find what do we need to copy.

Shortened the cell value

In order to get the adjustment of the cell value in cell. The option just allows the cell value to replace #### error to actual value.

In the above gif the value is retained. Follow the below explained steps to avoid errors.

  1. Go to the format box as shown in the top right.
  2. Decrease the length of the value via clicking it multiple times till the cell shows the value in place of error.

Increase the width of the cell

In the gif shown below only the formula is used. Follow the below steps to avoid having #### ( hashtag ) error.

Follow these steps to avoid the ##### (hashtag) error

  1. Go to the top right side of the column. In this case which is D column.
  2. Stretch it wide till the cell shows the value as required.

As you can see the #### ( hashtag ) Error can be handled when you use the above mentioned suggestions.

#### ( hashtag ) Error due to Long Date value

Another reason for the #### (hashtag) error is when the long Date value cannot fit in the cell width. Generally Excel long data format and lack of space for the cell value shows the ###### error. Here, a  hashtag) error suggests that the width of the cell needs to be increased or the date value can be shortened. See the below example to understand more.

For example, the long date value in the D3 cell creates the #### (hashtag) error . Here is the usage & error occurrence shown below.

Look at the above snapshot and realize what excel does with the results.

How to fix it! 

You can fix the error for the result. See the steps below to find what do we need to copy.

Shortened the cell value

In order to get the adjustment of the cell value in cell. The option just allows the cell value to replace #### error to actual value.

In the above gif the value is retained. Follow the below explained steps to avoid errors.

  1. Go to the format box as shown in the top right.
  2. Select the option Short Date from the drop down list under format cell option.

 

Increase the width of the cell

In the gif shown below only the formula is used. Follow the below steps to avoid having #### ( hashtag ) error.

Follow these steps to avoid the ##### (hashtag) error.

  1. Go to the top right side of the column. In this case which is D column.
  2. Stretch it wide till the cell shows the value as required.

As you can see the #### (hashtag) Error can be handled when you use the above mentioned suggestions.

 

Hash Marks  Error due to Negative Date and Time value

Another reason for the #### ( hashtag) error is when the input cell value is negative Date or Time value. Generally Excel has predefined date and time format and negative of these values show the ###### (hashtag) error. See the example below to understand more.

Negative Date value

Correct the Date and Time format as per Excel.

Here are some observational notes while handling the #### (hashtag) error.

Notes:

  1. ##### (hashtag) error isn't as dangerous as other errors.
  2. ##### (hashtag) error major reason occurrence is due to Space adjustment of cell width.
  3. Formatting cells according to the requirement helps to avoid or handle this error.

Hope this article about how Why #### ( hashtag ) occur And How to Fix #### ( hashtag ) error in Excel is explanatory. Find more articles on Excel error functions here. Please share your query below in the comment box. We will assist you.

Related Articles

#VALUE Error And How to Fix It in Excel

How to correct a #NUM! Error

Why #NAME? occur and How to fix #NAME? Error

Why #DIV/0! occur and How to fix #DIV/0! Error

Why #N/A occur and How to fix #N/A error

Why #NULL! occur and How to fix #NULL error

Why #REF! occur and How to fix #REF! error

Popular Articles

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Join first and last name in excel

Count cells which match either A or B

Convert Inches To Feet and Inches in Excel 2016

50 Excel Shortcut to Increase Your Productivity

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube