How to highlight wrong data in excel?

Spotting things in a spreadsheet can be much quicker when you use colors that jump off the screen. With conditional formatting in Microsoft Excel, you can make finding blank cells or formula errors easier.

When you have a spreadsheet full of data that you expect to fill every cell, you can easily overlook cells that are left empty. And a lack of data can be just as detrimental as incorrect data. Here’s how to see blank cells by using conditional formatting.

Open the sheet and select the cells where you want to apply the formatting. Head to the Home tab and click “Conditional Formatting” in the Styles group of the Ribbon. Choose “New Rule.”

In the New Formatting Rule window that appears, pick “Format Only Cells That Contain” under Select the Rule Type at the top.

At the bottom, pick “Blanks” in the Format Only Cells With drop-down box. Then, click “Format” to the right of the preview to select how to format the blank cells.

In the Format Cells window, use the tabs at the top for Font, Border, and Fill to choose your formatting. Click “OK.” For our example, we’ll use Fill to color our blank cells bright yellow.

You’ll be back on the New Formatting Rule window, where you’ll see a preview of the formatting for blank cells. If you’re happy with it, click “OK” to apply the conditional formatting.

You should then see any empty cells in the range that you selected highlighted with the formatting that you picked.

RELATED: How to Check If a Cell Is Blank With ISBLANK in Excel

Even though Microsoft Excel does a decent job of shouting errors out to you, they might not be noticeable if you have a large sheet to scroll through. To make sure that you see the errors quickly, conditional formatting can save the day.

You’ll actually follow the same process that you used in the previous section to highlight blanks, but with one difference.

Switch to the Home tab, click “Conditional Formatting,” and then choose “New Rule.”

In the New Formatting Rule window, pick “Format Only Cells That Contain” at the top. But this time, pick “Errors” in the Format Only Cells With drop-down box at the bottom.

Then, click “Format” to choose the formatting. For this example, adjust the Font options to make the cells with errors bold and red. Click “OK” after you pick the formatting and “OK” again to apply the rule.

Now, those errors pop off the screen!

You can use this type of formatting in Microsoft Excel to do many other things as well. Check out how to use conditional formatting to highlight a row based on content or how to create a progress bar in Excel.

If you create formulas in your worksheet, it will be inevitable to appear some error values. Can you highlight all these cells which contain the error values in your worksheet at once? The Conditional Formatting utility in Excel can help you solve this problem.

Highlight all error cells with Conditional Formatting in Excel

Select and highlight all error cells with Kutools for Excel

The following steps will show you how to apply Conditional Formatting to highlight all cells that contain errors.

1.Select a range or the whole worksheet that you want to apply the Conditional Formatting.

2.Then click Home > Conditional Formatting > New Rule, see screenshot:

3. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format option, and then type this formula =ISERROR(A1) (A1 indicates the first cell in your selected range, you can change it as you need) into the Format values where this formula is true text box. See screenshot:

4. Then click Format button, and in the Format Cells dialog box, select one color that you want to use under Fill tab, see screenshot:

5. After choosing the color, then click OK > OK to close the dialogs, and all the error value cells have been highlighted as following screenshot shown:

Note: Conditional Formatting tool is a dynamic function, if you create other error values in your selected range, they will be highlighted as well.

If you want to select and highlight error cells, you can use Kutools for Excel’s Select Cells with Error Value utility, then highlight them with your favorite color.

After free installing Kutools for Excel, please do as below:

1. Select the cells you want to find error cells, click Kutools > Select > Select Cells with Error Value. See screenshot:

2. Then all the error cells are selected, and click Home > Fill Color to select a color to color the error cells. See screenshot:

On the Data tab, in the Data Tools group, click the arrow next to Data Validation, and then click Circle Invalid Data. Excel displays a red circle around any cells that contain invalid data.