It can be disturbing when you are working in Excel and come across an error that you either don’t know how to define or resolve. There are several common types of errors, and one of them is a row of number, pound or hashtags that looks like this: #####.
We’ll try to go through some of the most common causes of this error, as well as ways to locate it and get rid of it when it pops up. If you don’t want to go through this exercise, you can get problem-specific help from Excelchat around the clock.
_600x.jpg)
Learn about the causes of the ##### error in Excel and how to correct it.
Excel ##### Error When Column Isn’t Wide Enough
The most common reason you’ll see a ##### error in Excel is that the contents of the cell don’t fit into the column width. In other words, there isn’t enough space to display your content.
This is a simple enough fix if this is indeed the reason for your error. You can drag the top of the column and made it wider so that all of the content will display. This can also be accomplished by double-clicking on the right edge of the column header, which will cause it to adjust to a size that will accommodate the content.
You can also reduce the size of the contents in your cell so that they fit the width of the column. You don’t have to change the values that are in the cells but can instead reformat the contents so that the content fits.
This can be accomplished from the Home tab. Click the “more” box in the bottom right corner of the Alignment section. A dialogue box will appear — check the box next to Shrink to fit under Text control. Click Ok.
The ##### error can also occur because numbers have too many decimal places; or dates, times, and currency depictions are too long.
If there are too many decimal places with your numbers, you can short this from the Home tab, Number section, and Decrease Decimal button.
If the data format used is too lengthy, go to the Home tab, Number section, and choose Short Date from the drop-down box.
You can also adjust the format of your date, time and currency by clicking on the bottom right box of the Number section from the Home tab. This brings up a dialogue box that allows you to customize the formatting for your selected range of cells.
Excel ##### Error When Dates or Times Are Negative
Another reason that you might get the ##### error in Excel is when the dates or times in your cell are negative values. When you’re using the 1900 date system, you’ll want to verify that all of your dates and times are positive values. Otherwise, you’ll end up with a troublesome error message.
Some of the ways to address this include changing the results to a different format instead of dates and times. You can also make sure that any formulas being used are correctly adding or subtracting dates and times to avoid a negative result.
Excel ##### Error When Cell Contents > 253 Characters
While it’s rare, you can also get a ##### error in Excel if the content of your cell is text or a number, more than 253 characters, and formatted for accounting, times, dates, or numbers. If you have a cell with a long string of characters, check the formatting to see if this will fix the error.
Locating ##### Errors in Excel
Maybe your Excel spreadsheet holds a ton of data, and you are sure where all of your errors are located. You can quickly find them using the “Find” function under the Editing section. Just place several “####” in your Find what section and then hit Find All or Find Next. You can then go through all of the errors to fix them.
Deleting ##### Errors in Excel
Simply deleting all of the ##### errors in your Excel spreadsheet without addressing the cause isn’t a recommended solution. While you can do this by using the Find & Replace option, the integrity of your worksheet is likely to suffer.
The ##### error is a simple enough error to fix in most cases. It is generally a formatting error. The best way to address it is to locate it and then fix the formatting issue that is causing the error to appear. This way, you won’t cause any problems with underlying formulas.
Need Additional Help With Excel Errors?
No one likes having to take time away from work or their studies to deal with an Excel error. If you’ve reached an impasse and need help with anything related to Excel, you’ll be pleased to learn that this available 24/7.
Excelchat has a team of knowledgeable and friendly Excel experts that will walk you through any tutorial of your choice or help you troubleshoot a specific issue. Your first session is always free.
Leave a Comment