Excel uses the countif function to retrieve duplicate values, which are distinguished by different font colors.

Use the = countif (B: C, B2)> 1 function. Use this function in the Excel condition format and mark the font of the function that requires repetition in red.

 

= Countif (B: C, B2)> 1. B Indicates the start search range, C indicates the end search range, and B2 indicates the start position of the Start search range.

 

 

See the link http://www.dzwebs.net/2331.html for details

 

For details, see.

 

How can I find the same value in the numerous irregular data in an Excel table and mark these values with distinctive colors for easy observation!

To solve this problem, you can use two methods: one is VBA and the other is the countif function;

In this example, the countif function is used to facilitate your learning. As for the implementation of VBA, you can stay at the same time.

For example, Score data is displayed in the BC column.

 

Now, the effect we want to achieve is to search for data with the same score from B2 to cn. If there is a duplicate value, mark it as red.

First, select the range of cells for which you want to search for duplicate values. In this example, the range is from B2 to cn. Then, perform the following operations!

 

"Format" → "condition format", as shown in the dialog box!

 

Select "formula" in "condition 1", and then enter the same formula code on the right side, that is, = countif (B: C, B2)> 1. Then, click the format button to display the condition settings, as shown in.

 

, We select the red color, and set the font to bold, click the "OK" button, return to the action dialog box as shown in!

 

The Set dialog box, for example, click "OK" to get the result.

 

Let's see, the same score value and repeated values are marked in red respectively.

The formula code is analyzed as follows:

Formula code: = countif (B: C, B2)> 1

Here, B: C refers to the data range to be compared. Here, B and C are two columns, that is, the data range that meets the conditions for color change; b2 indicates the start cell.

We can sum up a general formula:

Formula code: = countif (data comparison range, starting cell)> 1