MS Excel, known as Microsoft Excel, is one of the most popular spreadsheet programs that allow users to record vast amounts of data within the cells across many worksheets (or sheets). The program is so powerful that we can implement various functions and formulas to get the calculated results.
Although using formulas in Excel is easy, there may be cases when we may come across some random unexpected codes instead of the desired results. Based on different cases, these codes may vary. However, these specific codes directly inform us that there is something wrong with our formulas, supplied data, formatting, etc. These unexpected results or codes are referred to as errors in the context of Excel, and #### is one such Excel error that can occur in our worksheets.
What is #### Error in Excel?
The #### Error is one of the most common errors we encounter while working on Excel. It is also said to be the easiest to tackle error. In most cases, errors occur in Excel due to wrong formulas or values; however, the #### Error is not formula-based. Instead, this error is more related to formatting.
The #### Error mostly occurs when we apply formulas that may generate long format results, expending over the visible width of the corresponding cell (s). Since the cell width is not wide enough to display the entire results or value, it returns the #### Error. Apart from formulas, if we even enter long format values directly in the cell, it gives #### Error.
The #### Error does not always occur due to insufficient width; there may be other reasons. Another reason that causes #### Error is when we try to enter negative times, dates, etc. It is very well known that these types of values cannot be negative. Besides, we may have formulas that may return dates or times as negative values, causing the #### Error within the sheet.
How to find #### Error in Excel?
Excel has various formula errors that may occur due to different reasons. Likewise, Excel allows users to use specific functions to trace and handle certain formula errors. In most cases, the two functions IFERROR and ISERROR help trace common formula errors in Excel. Additionally, Excel's 'Go to Special' tool can also help find or highlight errors in formulas within the sheet. However, both of these do not apply in the case of the #### Error. The reason is the same as we said above. The #### Error is not a typical formula-based error.
Therefore, we must navigate the sheet manually using the scrollbars to find or locate the #### Error within the Excel worksheet. Once we see #### codes in any cell, we must click on the respective cell to ensure whether it is the desired data or the error. After selecting the corresponding cell, we can check the content from the formula bar.
How to fix #### Error in Excel?
We need to try different methods to fix the respective error based on the type of error. When we come across the #### Error, there are two basic reasons, and we must fix such issues from our worksheet. Therefore, we can fix the #### Error in the following two ways:
By adjusting the cell content to its width
Since the #### Error occurs due to insufficient cell width in most cases, we can fix it by making the column width wider. To make a column wider, we can click and hold the column header's right edge and drag it accordingly to the desired width. In addition, we can double-click the right edge to automatically expand the cell's width to the length of the available data.
This will help us display the cell content in full, eliminating the #### Error from the sheet.
We can also try the following ways to adjust the cell content as per its width:
Example: Consider the following sheet as an example where we have the entry of joining dates for some employees. However, the respective cells display the #### Error.
If we select any date cell in the above sheet and check the formula bar, we notice long format dates in corresponding cells. To fix the #### Error issue, we can either increase the cell width or change the date format to a shorter date to fix the #### Error issue.
If we increase the cell width, the date format will not be changed. So, if we need to use a long-format date strictly for any reason, we can try making the cell width wider.
If we change the date format, the date format will be changed. It will get shorter, as shown below:
In the above sheet, we notice that the month name is changed from the characters to numbers. However, both ways remove the #### Error perfectly. We can choose any method as per our presentation requirements.
By correcting negative dates or times
If our Excel sheet displays #### Error because the corresponding cell (s) contain negative date or time values, we can try the following ways to fix it:
- We must ensure to check dates and times when using the 1900 date system of Excel. Both values, dates, and times must always be positive.
- When applying the formula to subtract dates or times or add time within the sheet, we must double-check them to avoid negative date or time value results.
- Suppose the formula results are negative and formatted as dates or times when they are not exact dates or times. We must change the cell format to a different number format.
Example: Consider the following sheet as an example where we have entry and exit times and calculated their difference. However, the difference cell or result cell displays the #### Error.
If we select the result cell and check the formula bar in the above sheet, we notice that the formula (=A2-B2) is applied there.
This subtraction formula returns the negative result because the entry time (A2) is smaller than the exit time (B2), causing the #### Error. To calculate the difference, we must always subtract smaller values by the larger values, not the larger values by the smaller values.
Therefore, we can change the formula as below to fix the error:
=B2-A2
We removed the #### Error by correcting the formula and retrieved the desired difference results accordingly.
Important Points to Remember
- If the cell contains relatively long data, such as a sentence or paragraph, it is better to wrap the text in the corresponding cell rather than increase its width. Making the cell width too wide will negatively affect the overall presentation of the sheet and make it difficult to read.
- When accessing the Format Cells window using the keyboard shortcut 'Ctrl + 1', we must ensure to press the key '1' from the keyboard area, not from the number pad section.
- It is essential to note that we cannot increase the width for any specific cell individually; we have to make the width of the entire column wider.