Using Conditional Formatting to Show Errors

[PipBoy808]

Hi,

Long time reader, first time poster.

I'm trying to use the 'ISERROR' function as part of a conditional formatting formula to show errors in large batches of data. For instance, if I use '=ISERROR($A$1)' and set the Fill to Red, every time there's an error in cell A1, the cell will turn red.

My problem lies in applying this to individual cells within entire columns or even an entire worksheet so as to create a sort of 'error warning system'. How can I apply this conditional formatting to many other individual cells without having to set up formatting rules one at a time? If I type in 'ISERROR($A:$A)' then the whole column goes red if there's a single error in any $A cell. I just want it so that specific cells within a column/worksheet with errors are flagged, thus highlighting mistakes clearly to the user.

I feel like I'm on the cusp of getting it right. It's quite frustrating.

Thanks!

 

[SirJB7]

Hi, PipBo808!

If you select a range, and then click on the CF icon, you can set the CF formula using the top left cell in it, and Excel will properly copy the entered formula to the other formulas in the selected range.

E.g, if you select B2:F10 and you want to CF using each cell value equal zero and the cell at column A not blank and the cell at row 1 not blank, you should use this formula:
=AND($A2<>"",B$1<>"",B2=0)

Note the fixed reference for 1st column using the $ symbol for the column, and the same for 1st row for the row reference.

Hope it helps!

Regards!

 

[r1c1]

To keep it really simple,

1. Select the entire range where errors may occur
2. Go to CF > New rule
3. Write =ISERROR(A1) (relative reference)
4. Set up the color.
5. Click ok and close

Done!

 

[PipBoy808]

The simplest option works best. That's great, now I can create a little 'error warning system'. Thanks!