Prevent duplicates using dynamic conditional formatting in excel
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:
- Highlight duplicates
- 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:
- Select the range (A1:A5)
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formula is true" window.
- Type =COUNTIF($A$1:$A1, A1)-1>0
- Click Format button
- Click "Fill" tab
- Select a color
- Click OK!
- Click OK!
How to apply the dynamic conditional formatting area formula in excel 2007:
- Select the range (A1:A5)
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "Manage rules.."
- Click in the window "Apllies to:"
- Type =OFFSET($A$1,0,0,COUNTA($A:$A),1)
- 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
Related posts:
Highlight duplicates using conditional formatting in excel
Highlight the second or more duplicates in two lists using conditional formatting in excel
Excel vba: Create dynamic conditional formatting
Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
Create a dynamic border to your list using excel conditional formatting
Recommended categories
[...] formatting in excel Filed in Excel on May.17, 2009. Email This article to a Friend In a previous post I extended the conditional formatting area automatically when a new row is entered. This gave me an [...]
firstly i want to thank you for providing us this web site with sample excel files.
i was searching for a solution to my need that i want to apply conditional formating to a dynamic named range. this lesson looks like an answer to my need. but as far as i see when you enter a dynamic range name or its formula to "applies to" section, excel converts it to a cell range rather than dynamic range.
this is also valid for the file you provide. i just downloeded the file and checked the CF options. and what is that a cell range,not "OFFSET($A$1,0,0,COUNTA($A:$A),1)"
So is it not possible to use dynamic range at conditional formating ? or how can i do that ?
No, you can´t do that.
First it seems to work but when you add or remove values from the dynamic list it sometimes fail. It is not reliable.
Unfortunately, the only solution I know of is to apply the conditional formatting to a larger cell range.
Negative aspects: larger file size and slower calculations. CF is extremely volatile:
http://www.decisionmodels.com/calcsecretsi.htm