How to Fix Excel Conditional Formatting Duplicated Rules
40.1 هزار بار بازدید -
7 سال پیش
-
In Excel, you can use
In Excel, you can use the conditional formatting to highlight one or more cells, based on rules.
In some worksheets, the conditional formatting rules are duplicated automatically, when rows are inserted or deleted in the table.
This can cause a huge mess of rules in the workbook, with many duplicates. Your workbook could slow down in some cases.
See how to remove the duplicates quickly, and get back to just the original set of rules.
Visit my Contextures site to download the sample file for this video.
https://www.contextures.com/excelcond...
00:00 Introduction
00:18 The Conditional Formatting Rules
01:06 Insert a New Row
01:59 Fix the Duplicate Rule Problem - Step 1
02:28 Fix the Duplicate Rule Problem - Step 2
02:58 Get the Sample File
'-------------------------------------------------------------------------------
TRANSCRIPT FOR THE VIDEO
In this workbook, I've got a couple conditional formatting rules, and I'm going to show you how those can get duplicated, so you end up with lots more rules than you started out with, and then how you can fix the problem.
So in this table I have two rules.
--One puts a line at the top of a date, if it's different from the date above.
--The other rule changes the price to green, if it's greater than $500 dollars.
We'll take a look at those rules. In the Home tab, go to Conditional Formatting, Manage Rules.
And there you can see the two rules. They're each applied from row 3 to 19 in this table.
--Now this one is just a cell value greater than 500.
--And this one is referring to a couple of cells. So if A2 is different from A3, we're going to put a blue line, at the top of the cell.
And now, I'm going to insert a row, because I forgot to put in one of the records here. So I'll click, Insert. And then just put some data in this row.
So that was a simple change to the table. And now I'm going to go back and look at my conditional formatting rules again. And suddenly I have a new rule, just for the row that I inserted.
So row 11 has its own rule, and the previous rule is still there, going from A3 to E10, and then A12 to E20. So it's skipping this row, because that row has a separate rule.
And if you insert lots of rows, you can end up with hundreds of these rules set up, without even knowing it.
And what I do to fix it is select all the rows, except for the first one. Then go to Conditional Formatting, Clear Rules, From Selected Cells.
Now if I go back in, and Manage Rules...I'll go back to This Worksheet, just so we can see everything. So we're back to those two original rules.
Then, I select the first row. I'll select the first row in the table, this time, instead of the whole worksheet.
And go to the Format Painter, and make sure you're selecting this first row as well, and apply that formatting to all the rows again.
And when we go back to Manage Rules, we still just have those two original rules.
In some worksheets, the conditional formatting rules are duplicated automatically, when rows are inserted or deleted in the table.
This can cause a huge mess of rules in the workbook, with many duplicates. Your workbook could slow down in some cases.
See how to remove the duplicates quickly, and get back to just the original set of rules.
Visit my Contextures site to download the sample file for this video.
https://www.contextures.com/excelcond...
00:00 Introduction
00:18 The Conditional Formatting Rules
01:06 Insert a New Row
01:59 Fix the Duplicate Rule Problem - Step 1
02:28 Fix the Duplicate Rule Problem - Step 2
02:58 Get the Sample File
'-------------------------------------------------------------------------------
TRANSCRIPT FOR THE VIDEO
In this workbook, I've got a couple conditional formatting rules, and I'm going to show you how those can get duplicated, so you end up with lots more rules than you started out with, and then how you can fix the problem.
So in this table I have two rules.
--One puts a line at the top of a date, if it's different from the date above.
--The other rule changes the price to green, if it's greater than $500 dollars.
We'll take a look at those rules. In the Home tab, go to Conditional Formatting, Manage Rules.
And there you can see the two rules. They're each applied from row 3 to 19 in this table.
--Now this one is just a cell value greater than 500.
--And this one is referring to a couple of cells. So if A2 is different from A3, we're going to put a blue line, at the top of the cell.
And now, I'm going to insert a row, because I forgot to put in one of the records here. So I'll click, Insert. And then just put some data in this row.
So that was a simple change to the table. And now I'm going to go back and look at my conditional formatting rules again. And suddenly I have a new rule, just for the row that I inserted.
So row 11 has its own rule, and the previous rule is still there, going from A3 to E10, and then A12 to E20. So it's skipping this row, because that row has a separate rule.
And if you insert lots of rows, you can end up with hundreds of these rules set up, without even knowing it.
And what I do to fix it is select all the rows, except for the first one. Then go to Conditional Formatting, Clear Rules, From Selected Cells.
Now if I go back in, and Manage Rules...I'll go back to This Worksheet, just so we can see everything. So we're back to those two original rules.
Then, I select the first row. I'll select the first row in the table, this time, instead of the whole worksheet.
And go to the Format Painter, and make sure you're selecting this first row as well, and apply that formatting to all the rows again.
And when we go back to Manage Rules, we still just have those two original rules.
7 سال پیش
در تاریخ 1395/12/20 منتشر شده
است.
40,107
بـار بازدید شده