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.
3 Responses to “Filter one table if the value is in another table (Formula Trick)”
What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.
Good question. You can check for the =0 as countifs result. for example,
=FILTER(orders, COUNTIFS(products, orders[Product])=0)
should work in this case.
PS: I have added this example to the article now.
Hi there!
Could i check if there was a way to return certain fields of the table only?
so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).