Highlighting Successive Duplicate Values in Excel

Objective

To create a formula in Excel that identifies and highlights duplicate values that occur in succession within a data array.

Formula Explanation

The formula utilized for this task employs conditional formatting, which allows the identification of successive duplicate values.

Required Formula

To highlight successive duplicate values in cells within a range (e.g., A1:A10), utilize the following formula in the conditional formatting rule:

=A1=A2

Steps to Apply the Formula

1. Select Range: Highlight the range of cells you wish to apply formatting to (e.g., A1:A10).

2. Conditional Formatting:

   • Navigate to the Home tab.
   • Click on Conditional Formatting.
   • Choose New Rule.
   • Select Use a formula to determine which cells to format.

3. Enter the Formula: Input the formula =A1=A2 into the formula box.

4. Set Format:

   • Click on the Format button and select your desired formatting (e.g., fill color).
   • Confirm by clicking OK.

5. Apply Rule: Click OK to apply the rule and see the highlighting for successive duplicate values.

How It Works

• The formula =A1=A2 checks if the value in the current cell (A1) is equal to the value in the cell directly below it (A2).
• If the condition is true (indicating a duplicate), the selected formatting is applied.
• This process continues down the selected range, allowing for the identification of any duplicates that occur one after the other.

Practical Example

Dataset:

Assume the following dataset in range A1:A10:

1
1
2
3
3
3
4
5
5
5

Result:

When the conditional formatting rule as described is applied, the successive duplicates ('1', '3', '5') will be highlighted, as they occur directly next to each other.

Conclusion

By following the outlined steps and utilizing the provided formula, one can effectively highlight successive duplicate values in Excel. This method is fundamental for identifying patterns or issues within datasets, enhancing data analysis capabilities.

For further learning about advanced Excel techniques and data analysis, consider exploring the courses available on the Enterprise DNA Platform.