Problem: How to highlight the wrong entries (conditional formatting).

To enter the data correctly we can use many different ways: drop-down lists, protection sheet or even macros. And you can highlight the wrong entries color to warn the user that the entered value is incorrect. 

For example we have the following form: 

And the list of departments 

If the user enters in the column “C” the department which is not contained in this list, then the cell should marked by red to hint about the error.

Select the cell C4:C6 and then on the Ribbon Tab

Home – Conditional Formatting – New Rule -“Use formula to determine which cell to format)” 

Input the following formula   =AND(NOT(ISBLANK(C4)),COUNTIF(F4:F6,C4)=0)

Functions in this article:

·         COUNTIF - formula calculates the number of cells for the value entered in cell C4 within the list of allowed departments. If this number is zero, then entered the departments in is not in the list.

·         ISBLANK function checks whether something in the cell C4.

·         Function AND checks both given conditions.

How to highlight cells with extra spaces (using conditional formatting).

The data entry process involves the possibility of incorrect information input - we're all human and can make mistakes. One such option - extra spaces. Someone puts their by chance, someone - even intentionally. But in any case, even an extra space will be a problem for you at a later time during the entered information.

Additional "charm” that they have not seen, though, if you really want it, can be made visible by using a macro.

How do we avoid this - highlight incorrectly entered data directly in the process of data entry, rapidly signaling an error to the user.

To do this, use the Conditional Formatting:

1. Select the cells where we need to check extra spaces.

2. From the Home tab, click Conditional Formatting - New Rule.

3. Select the type of the rule right to use a formula to determine the formatted cells  and type in the following formula:

=TRIM(A1)<>A1

The TRIM function removes extra spaces from text. If the original contents of the current cell are not "trimmed" by using the TRIM function, then in the cell have extra spaces. Then fill a color input fields that can be selected by clicking on Format (Format).

And here is the result:

How to Hide Duplicated Values (conditional formatting)

We can use Excel conditional formatting to hide the duplicate values and make the list easier to read.

In this example, we will hide repeated values for the same company name.

Select range C2:C11 In Excel 2007, on the Ribbon's Home tab, click Conditional Formatting - New Rule - Use a Formula to Determine

Which Cells to Format Formula Is =C2=C1 Click the Format button and select a font colour to match the cell.