How to highlight errors in Excel

Question

I am trying to format cells within an Excel spreadsheet to highlight errors, but it is not including the cells with "#####". How can I format the spreadsheet to highlight these cells?

I currently have a spreadsheet that is populated with Microsoft Forms responses via Power Automate. One aspect of the data is recording the start date & time as well as the end date & time of a "job". I currently have a formula that takes this information and calculates how long the job took to complete in hours & minutes.

This all works great! However, sometimes the responses are input incorrectly and the end date will be entered as a date or time that is before the start date. Obviously Excel doesn't know how to do that math. In those instances, it returns a number of "#". Fixing this is simply a matter of updating the date/time. However, I want to highlight these instances so that they are easier to locate as the spreadsheet has over 500 rows.

I am attempting to apply a conditional formatting rule to highlight these instances so that I can easily locate them and update the information. I tried to highlight cells with errors but it is not highlighting the aforementioned cells. I also tried highlighting cells that contain "#" with the same results.

I have attempted applying the following formulas per the Assist feature to this rule with no luck.

=ISERROR(A1)

A1= "#"

=IFERROR(FIND("#",A1),0)>0

=ISERROR(A1)+A1="#"

Is there another formula that I should be applying to this rule? Is there something I am missing?

Any assistance would be appreciated!

Answer 1

Hi @Alexandra Sansone

Based on my tests, the "########" error cannot be found out via formulas or "Find" and "Goto Special" feature.

But it can be recognized with "Filter" function.

The workaround is filtering the column, then tick the box of "########".

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---