As a list grows, it would sometimes be convienient to highlight possible duplicate values. If you want to prevent duplicates, highlighting them saves you time and energy.

Usually conditional formatting does this job excellent but to manually expand the conditional formatting area every time you type a new row in your list is not efficient. We want this conditional formatting area to grow as your list grows.

So the tasks in this article are:

1. Highlight duplicates
2. Dynamic condititonal formatting

If your list contains only text values
See column A in the above picture

Conditional formatting formula: =COUNTIF($A$1:$A1, A1)-1>0

Conditional formatting area formula: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

If your list contains only numeric values
See column B in the above picture

Conditional formatting formula: =COUNTIF($A$1:$A1, A1)-1>0

Conditional formatting area formula: =OFFSET($A$1,0,0,COUNT($A:$A),1)

How to apply the conditional formatting formula in excel 2007:

1. Select the range (A1:A5)
2. Click "Home" tab on the ribbon
3. Click "Conditional formatting"
4. Click "New rule..."
5. Click "Use a formula to determine which cells to format"
6. Click "Format values where this formula is true" window.
7. Type =COUNTIF($A$1:$A1, A1)-1>0
8. Click Format button
9. Click "Fill" tab
10. Select a color
11. Click OK!
12. Click OK!

How to apply the dynamic conditional formatting area formula in excel 2007:

1. Select the range (A1:A5)
2. Click "Home" tab on the ribbon
3. Click "Conditional formatting"
4. Click "Manage rules.."
5. Click in the window "Apllies to:"
6. Type =OFFSET($A$1,0,0,COUNTA($A:$A),1)
   
7. Click ok!

The result!

Type a new duplicate value below (Row 10)  and see how the conditional formatting area extends to the new row!

Download excel sample file for this tutorial.
prevent-duplicates-using-dynamic-conditional-formatting-in-excel.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this article

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers