Читать книгу Excel Data Analysis For Dummies - Paul McFedries - Страница 19

You use conditional formatting mostly to highlight numbers greater than or less than some value, or dates occurring within some range. However, you can use conditional formatting also to look for duplicate values in a range. Why would you want to do that? The main reason is that many range or table columns require unique values. For example, a column of student IDs or part numbers shouldn’t have duplicates.

Unfortunately, scanning such numbers and picking out the repeat values is hard. Not to worry! With conditional formatting, you can specify a font, border, and background pattern that ensures that any duplicate cells in a range or table stand out from the other cells. Here’s what you do:

1 Select the range that you want to check for duplicates.

2 Choose Home ⇒ Conditional Formatting.

3 Choose Highlight Cells Rules ⇒ Duplicate Values.The Duplicate Values dialog box appears. The left drop-down list has Duplicate selected by default, as shown in Figure 1-2. However, if you want to highlight all the unique values instead of the duplicates, select Unique from this list.FIGURE 1-2: Use the Duplicate Values rule to highlight worksheet duplicates.

4 In the right drop-down list, select the formatting to apply to the cells with duplicate values.You can create your own format by choosing the Custom Format command. In the Format Cells dialog box, use the Font, Border, and Fill tabs to specify the formatting you want to apply, and then click OK.

5 Click OK.Excel applies the formatting to any cells that have duplicate values in the range.