#### in Excel cell

You open an Excel file and some cells show #### instead of the value. Here is a quick fix.

  • Just make the columns wider

Still not working? Read on…

Even after making the columns wide-enough, sometimes Excel can show ### in cells. This can happen if you have dates in the cells and some of the are negative (or after 31 December year 9,999).

See below demo:

How to fix the problem for negative dates:

Excel cannot process negative dates or dates before year 1900. In most situations, a negative date could be just a formula or data entry error. So go ahead and apply the necessary data adjustments.

Can I show another message if the cell has error?

Sure. You can use IFERROR function in Excel to show an alternative result when the original values is error. To use this:

=IFERROR(your original value or formula, “alternative result”)

For example:

=IFERROR(AVERAGEIFS(A1:A10, B1:B10, “England”), “No result”)

The above formula tries to calculate the average of A1:A10 where B1:B10 is “England”. If there is an error, it will print “No result”.

Learn more about Excel errors:

See these pages to learn more about common errors with Excel.

  • Excel formulas showing as Text and not calculating
  • A handy guide to common Excel errors
  • Use IFERROR to stop errors in your workbook
  • 10 important Excel functions you should learn

The post #### in Excel cell appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/in-excel-cell/

Tags: , ,
Close Menu