excel find duplicate values without highlighting

On the Excel Ribbon’s Home tab, click Conditional Formatting.Except for the first row, select all the rows with the same conditional formatting rules.Here are the easy steps to remove the conditional formatting duplicate rules: Now that I’ve found an easy way to fix the problem, I don’t worry about prevention – I just clean things up when necessary. I’ve written about this problem before, and there are a few ways to prevent the problem from occurring. In a big workbook, that could potentially slow down your workbook’s calculation speed.Īnd, you might not even know about those extra rules, unless you go into the Manage Rules dialog box for some reason. If you frequently delete and insert rows, you could end up with many duplicated rules.

The same #REF! error occurs in the conditional formatting formula, so Excel creates a new rule for that formula.įix Conditional Formatting Duplicate Rules If we had the same formula on the worksheet, you’d see a #REF! error. When you delete a row, the row below that loses its reference to that row. It compares the date in the current row, to the date in the row above: The new rule was created because the Top Border rule has a formula that refers to another row. There is a new rule that applies to row 10 only, and it has a #REF! error.The original rule has changed, and excludes the deleted row.Then, go to Conditional Formatting > Manage Rules, to see the results. To see how a new rule is automatically created, delete row 10 in this table. In columns A:E, add a blue top border if the date in column A is different from the date above.In column E, highlight prices that are greater than 500.In this example, there is a small table, with 2 simple conditional formatting rules: It’s designed for data in in an Excel named table. If you need to do this frequently, go to my website to get a macro that removes the duplicate rules. There are written instructions below the video. Watch this video to see how the duplicated rules are created, and an easy way to fix the problem. Video: Conditional Formatting Duplicated Rules Conditional formatting is a great way to highlight specific data, but did you know that it can automatically create new rules on its own? I’ll show you how that happens, and an easy way to fix those conditional formatting duplicated rules.

Excel find duplicate values without highlighting