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 條件格式的忠實(shí)擁護(hù)者 ，經(jīng)常使用它來(lái)突出顯示工作表中的單元格。 但是這周我遇到了有條件的格式化夢(mèng)night，不得不從頭開(kāi)始。 幸運(yùn)的是，修復(fù)并沒(méi)有花費(fèi)太長(zhǎng)時(shí)間，但是使用復(fù)雜的格式，情況可能會(huì)變得更糟。

(資料圖片)

分隔列表中的日期 (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條件格式的方法。 這對(duì)于提醒人們出現(xiàn)問(wèn)題，在列中隱藏重復(fù)的值，甚至隱藏單元格的內(nèi)容非常有用，直到正確填充相關(guān)單元格為止。

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.

今年早些時(shí)候，我發(fā)布了一個(gè)技巧，用于在列表中分隔日期 ，方法是在日期更改開(kāi)始時(shí)使用紅色邊框。

更容易閱讀 (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!

我每天早上都會(huì)更新自己的一本工作簿中使用的這項(xiàng)技術(shù)。 紅色邊框確實(shí)使該列表更易于閱讀，尤其是如果我在早上喝咖啡之前正在看的時(shí)候！

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設(shè)置了條件格式，并使用一個(gè)簡(jiǎn)單的公式查看日期是否等于上一行中的日期。

快速?gòu)?fù)制和粘貼 (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.

在工作簿中，我跟蹤網(wǎng)站的一些日常統(tǒng)計(jì)數(shù)據(jù)，并每天使用相同的標(biāo)題。 因此，為節(jié)省鍵入時(shí)間，我復(fù)制并粘貼了前一天的標(biāo)題。

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復(fù)制區(qū)域和產(chǎn)品標(biāo)題，并將它們粘貼到單元格B10：C12中，以獲取最新的日期數(shù)據(jù)。

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

Excel表將自動(dòng)展開(kāi)以包括新行，并在F列中填寫(xiě)公式。太棒了！

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

這個(gè)復(fù)制粘貼技巧就是我連續(xù)幾個(gè)月都在做的事情。

線(xiàn)路故障 (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.

突然，這個(gè)星期，我開(kāi)始遇到日期之間的紅色邊框的麻煩–它沒(méi)有遍及整個(gè)表格。

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

因此，我進(jìn)入條件格式管理器來(lái)解決此問(wèn)題。 但是，不是一個(gè)簡(jiǎn)單的規(guī)則，而是數(shù)百個(gè)規(guī)則！

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.

您可以在以下屏幕截圖的右下方看到微小的滾動(dòng)框。 這應(yīng)該使您知道該列表有多長(zhǎng)時(shí)間。

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

每次我在條件格式范圍內(nèi)復(fù)制和粘貼時(shí)，它都會(huì)為工作表創(chuàng)建另一個(gè)規(guī)則。 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.

讓我們看一下較小的示例，在該示例中，我剛剛復(fù)制并粘貼了Region和Product標(biāo)題。 現(xiàn)在，它有兩個(gè)規(guī)則，而不是一個(gè)，一個(gè)是復(fù)制并粘貼到表中。

清理混亂-更新 (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月：正如您在本視頻中所看到的，我找到了解決此問(wèn)題的更快方法。 在“ 條件格式”頁(yè)面上獲取詳細(xì)步驟。

演示地址

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.

我滾動(dòng)瀏覽列表，并刪除了一些規(guī)則，但很快意識(shí)到刪除所有規(guī)則將花費(fèi)很長(zhǎng)時(shí)間。 因此，為清理混亂，我從工作表中清除了條件格式，然后再次進(jìn)行設(shè)置。

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

隨著所有其他規(guī)則的消失，條件格式設(shè)置邊框再次正常工作。

避免條件格式化夢(mèng)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.

現(xiàn)在，當(dāng)我想在每日列表中節(jié)省一些鍵入時(shí)間時(shí)，我復(fù)制了以前的標(biāo)題，并使用“粘貼值”按鈕來(lái)創(chuàng)建新記錄。 我在Excel的快速訪(fǎng)問(wèn)工具欄上有該按鈕的副本，因此很容易單擊。

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

這樣就添加了文本，表格也進(jìn)行了擴(kuò)展，包括公式-但條件格式不會(huì)創(chuàng)建新規(guī)則。

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

小心點(diǎn)-不要讓這種條件格式化的噩夢(mèng)發(fā)生在您身上！

另一種解決方案 (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個(gè)空白行，并在添加新數(shù)據(jù)時(shí)在其上方插入新行。

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

在表中進(jìn)行復(fù)制和粘貼似乎不會(huì)產(chǎn)生重復(fù)的條件格式。

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

但是，這仍然是一個(gè)額外的步驟，而不是簡(jiǎn)單的復(fù)制和粘貼。 ________________

night24