night24

[Updated June 2018] I"m a big fan of Excel"s conditional formatting, and often use it to highlight cells on a worksheet. But this week I ran into a conditional formatting nightmare, and had to start from scratch. Fortunately, the fix didn"t take too long, but with complex formatting, things could have been much worse.

[2018年6月更新]我是Excel 条件格式的忠实拥护者 ，经常使用它来突出显示工作表中的单元格。 但是这周我遇到了有条件的格式化梦night，不得不从头开始。 幸运的是，修复并没有花费太长时间，但是使用复杂的格式，情况可能会变得更糟。

(资料图片仅供参考)

分隔列表中的日期 (Separate the Dates in a List)

There are lots of ways to use Excel conditional formatting. It"s useful for alerting people to problems, hiding duplicate values in a column, or even hiding cells" contents, until related cells are filled correctly.

有很多使用Excel条件格式的方法。 这对于提醒人们出现问题，在列中隐藏重复的值，甚至隐藏单元格的内容非常有用，直到正确填充相关单元格为止。

Earlier this year, I posted a tip for separating dates in a list, by using a red border at the start of a date change.

今年早些时候，我发布了一个技巧，用于在列表中分隔日期 ，方法是在日期更改开始时使用红色边框。

更容易阅读 (Easier to Read)

It"s a technique that I use in one of my own workbooks, which I update every morning. The red border really makes the list easier to read, especially if I"m looking at it before my morning coffee!

我每天早上都会更新自己的一本工作簿中使用的这项技术。 红色边框确实使该列表更易于阅读，尤其是如果我在早上喝咖啡之前正在看的时候！

The conditional formatting was set for cells A2:F9, and uses a simple formula to see if the date is equal to the date in the row above.

为单元格A2：F9设置了条件格式，并使用一个简单的公式查看日期是否等于上一行中的日期。

快速复制和粘贴 (Quick Copy and Paste)

In the workbook, I track some daily statistics for my website, and use the same headings every day. So, to save typing time, I copy and paste the headings from the day before.

在工作簿中，我跟踪网站的一些日常统计数据，并每天使用相同的标题。 因此，为节省键入时间，我复制并粘贴了前一天的标题。

For example, in the screen shot above, I could copy the regions and products headings from B2:C4, and paste them into cell B10:C12, for the latest day"s data.

例如，在上面的屏幕快照中，我可以从B2：C4复制区域和产品标题，并将它们粘贴到单元格B10：C12中，以获取最新的日期数据。

The Excel table automatically expands to include the new rows, and fills in the formulas in column F. Awesome!

Excel表将自动展开以包括新行，并在F列中填写公式。太棒了！

And that copy and paste trick is what I"ve been doing every day, for months on end.

这个复制粘贴技巧就是我连续几个月都在做的事情。

线路故障 (Trouble With the Lines)

Suddenly, this week, I started having trouble with the red border between dates – it wasn"t going across the full table.

突然，这个星期，我开始遇到日期之间的红色边框的麻烦–它没有遍及整个表格。

So, I went into the Conditional Formatting Manager, to fix the problem. But, instead of one simple rule, there were hundreds of rules!

因此，我进入条件格式管理器来解决此问题。 但是，不是一个简单的规则，而是数百个规则！

You can see the tiny scroll box at the bottom right in the screen shot below. That should give you some idea of how long that list was.

您可以在以下屏幕截图的右下方看到微小的滚动框。 这应该使您知道该列表有多长时间。

Every time that I copied and pasted within the conditional formatting range, it created another rule for the worksheet. Yikes!

每次我在条件格式范围内复制和粘贴时，它都会为工作表创建另一个规则。 kes！

Let"s take a look at the smaller example, where I just copied and pasted the Region and Product headings. Now, it has two rules, instead of one, after one copy and paste within the table.

让我们看一下较小的示例，在该示例中，我刚刚复制并粘贴了Region和Product标题。 现在，它有两个规则，而不是一个，一个是复制并粘贴到表中。

清理混乱-更新 (Cleaning Up the Mess - UPDATED)

UPDATE - March 2017: I"ve found a quicker way to fix the problem, as you can see in this video. Get the detailed steps on my Conditional Formatting page.

更新-2017年3月：正如您在本视频中所看到的，我找到了解决此问题的更快方法。 在“ 条件格式”页面上获取详细步骤。

演示地址

The previous solution is listed below, in case you"d rather try that.

如果您愿意尝试一下，下面列出了以前的解决方案。

清理混乱-原始解决方案 (Cleaning Up the Mess - ORIGINAL SOLUTION)

I scrolled through the list, and deleted a few of the rules, but quickly realized that it would take far too long to delete all of them. So, to clean up the mess, I cleared the conditional formatting from the worksheet, and set it up again.

我滚动浏览列表，并删除了一些规则，但很快意识到删除所有规则将花费很长时间。 因此，为清理混乱，我从工作表中清除了条件格式，然后再次进行设置。

With all the extra rules gone, the conditional formatting borders were working correctly again.

随着所有其他规则的消失，条件格式设置边框再次正常工作。

避免条件格式化梦Night (Avoid Conditional Formatting Nightmare)

Now, when I want to save some typing time in the daily list, I copy the previous headings, and use the Paste Values button to create the new records. I"ve got a copy of that button on Excel"s Quick Access Toolbar, so it"s easy to click.

现在，当我想在每日列表中节省一些键入时间时，我复制了以前的标题，并使用“粘贴值”按钮来创建新记录。 我在Excel的快速访问工具栏上有该按钮的副本，因此很容易单击。

That adds the text, and the table expands, including the formulas – but the conditional formatting doesn"t create a new rule.

这样就添加了文本，表格也进行了扩展，包括公式-但条件格式不会创建新规则。

Be careful out there -- don"t let this conditional formatting nightmare happen to you!

小心点-不要让这种条件格式化的噩梦发生在您身上！

另一种解决方案 (Another Solution)

Update: Thanks to Khushnood, who suggested leaving 3 blank rows at the bottom of the table, and inserting new rows above that, when adding new data.

更新：感谢Khushnood的建议，他建议在表底部保留3个空白行，并在添加新数据时在其上方插入新行。

Copy and pasting within the table doesn"t seem to produce the duplicate conditional formatting.

在表中进行复制和粘贴似乎不会产生重复的条件格式。

It"s still an extra step though, instead of a simple copy and paste. ________________

但是，这仍然是一个额外的步骤，而不是简单的复制和粘贴。 ________________

night24