如果您习惯使用旧版本的Excel ， Excel(Excel 2007) 2007、2010 和 2013中的条件格式选项会让您大吃一惊。那么你为什么要费心使用条件格式呢？好吧，以下是我喜欢使用Excel(Excel)的这一功能的几个原因：
1. 让您的数据更具视觉吸引力。
2. 让您的电子表格一目了然。
3. 识别某些类型的数字以帮助解决问题(problem solving)。
4. 帮助您从数据中得出结论。
5. 用绿色和红色直观地向用户显示什么是“好”或“坏”。
现在，您可以使用条件格式根据您自己的条件格式化范围内的每个单元格（并且有很多格式选项可供选择）。例如，如果您有一张利润表(profit sheet)，并且希望将所有大于 200 美元的利润用颜色标记为绿色，将所有小于 200 美元的利润用颜色标记为黄色，将所有损失标记为红色，那么您可以使用条件格式快速为您完成所有工作.
Excel 中的条件格式
条件格式化使您能够快速轻松地格式化大量数据，同时仍然能够区分不同类型的数据。您可以为允许Microsoft Excel(Microsoft Excel)为您自动设置格式的格式选项创建规则。你真的只需要遵循三个简单的步骤。
第 1 步：(Step 1:)选择要格式化的单元格。
第 2 步：(Step 2:)单击主页菜单(Home menu)样式部分(Styles section)下的条件格式(Conditional Formatting)按钮。
第 3 步：(Step 3:)选择您的规则。顶部有突出显示单元格规则(Highlight Cells Rules)和Top/Bottom Rules ，可让您与值进行比较。对于这个例子，我们强加了三个规则。首先是任何大于 200 美元的价值都是绿色的。
值得注意的是，只有突出显示单元格规则部分(Highlight Cells Rules section)也可用于将数据集与另一个数据集进行比较。其他所有内容将只使用您突出显示的一个数据集并将值相互比较。例如，当使用大于规则(Greater Than rule)时，我可以将 A1 到A20的值与特定数字进行比较，或者我可以将 A1 到A20与 B1 到B20进行比较。
同样的逻辑也适用于第二条和第三条规则。第二条规则是 0 到 200 美元之间的任何东西都被格式化为黄色。第三条规则是任何小于 $0 的东西都被格式化为红色。这是完成的电子表格的一部分。
如果您不喜欢这些格式选项，Excel有许多不同的新条件格式(Conditional Formatting)选项可供您使用。例如，您可以插入像彩色箭头（图标集(Icon Sets)）这样的图标，像第二个示例中那样的条形图（数据条(Data Bars)），甚至像上一个示例中那样插入一系列自动选择的颜色（色阶(Color Scales)）。这三个选项仅比较来自同一数据集的值。如果您选择 A1 到A20，它只会将这些值相互比较。
如果您后来决定不希望您的单元格被有条件地格式化，您所要做的就是清除格式。为此，请选择条件格式按钮并选择(Conditional Formatting button and select) 清除规则(Clear Rules)。然后，选择是要仅从选定的单元格还是从整个工作表中清除规则。
此外，如果您创建了多个规则，您可能会忘记将哪些规则应用于哪些单元格。由于您可以将许多规则应用于同一组单元格，因此可能会变得非常混乱，尤其是在其他人创建电子表格时。要查看所有规则，请单击条件格式按钮(Conditional Formatting button)，然后单击管理规则。(Manage Rules.)
当您将多个规则应用于同一单元格区域时，这些规则将按优先级从高到低的顺序进行评估。默认情况下，最新添加的规则将具有更高的优先级。您可以通过单击规则然后使用向上和向下箭头按钮更改顺序来更改它。此外，您可以单击最顶部的下拉菜单并查看仅针对当前选择或工作簿中每个工作表的规则。
还有一个叫做Stop If True的复选框，我不会在这里详细介绍，因为它非常复杂。但是，您可以阅读Microsoft的这篇文章，其中详细解释了它。
新的条件格式选项 Excel 2010(New Conditional Formatting Options Excel 2010)
当涉及到Excel 2007中包含的条件格式时，在(Conditional Formatting)Excel 2010中几乎所有内容都相同。但是，有一项新功能确实使它变得更加强大。
之前我提到过“突出显示单元格规则”(Highlight Cells Rules)部分可让您将一组数据与同一电子表格中的另一组数据进行比较。在 2010 年，您现在可以在同一工作簿中引用另一个工作表。如果您尝试在Excel 2007中执行此操作，它将允许您从另一个工作表中选择数据，但当您尝试最后单击“确定”时会给您一条错误消息。(error message)
在Excel 2010中，您现在可以做到这一点，但它有点棘手，所以我将逐步解释它。假设我有两个工作表，每张工作表上都有从 B2 到B12的数据，例如利润。如果我想查看工作表 1中 B2 到(sheet 1)B12中的哪些值大于工作表 2的 B2 到(sheet 2)B12值，我将首先选择工作表 1中的 B2 到(sheet 1)B12值，然后单击“突出显示单元格规则”下的“大于”。(Great Than)
现在单击上面显示的单元格引用按钮(cell reference button)。该框将发生变化，光标图标(cursor icon)将变为白色十字。现在继续并单击工作表 2(sheet 2)并 仅(ONLY)选择 单元格 B2。不要选择B2到(NOT)B12 的整个范围。
您会看到该框现在的值为=Sheet2!$B$2。我们需要将其更改为=Sheet2!$B2。基本上，只需去掉 2 之前的$ 。这将保持列固定，但允许行号(row number)自动更改。无论出于何种原因，它都不会让您选择整个范围。
再次单击(Click)单元格引用按钮(cell reference button)，然后单击确定。现在，表 1(sheet 1)中大于表 2(sheet 2)的值将根据您选择的格式选项进行格式化。
希望(Hopefully)这一切都说得通！查看Excel 2013时，在条件格式方面似乎没有任何新功能。最后一个提示，如果您觉得默认规则与您想要完成的不匹配，您可以单击“ 新建规则”(New Rule) 选项并(option and start)从头开始。创建新规则的好处在于，您可以使用公式来确定要格式化的单元格，这非常强大。
尽管条件格式在表面上看起来相对容易和简单，但根据您的数据和需求，它可能会变得相当复杂。如果您有任何问题，请随时发表评论。享受！
Format Cells using Conditional Formatting in Excel
If you are used to using older versions of Excеl, the conditional formatting optionѕ in Excel 2007, 2010, and 2013 will amaze you. So why would you want to bother using conditional formatting? Well, here are а couple of reaѕons why I love using this feature of Excel:
1. To make your data more visually appealing.
2. To make your spreadsheets easier to understand at a glance.
3. To identify certain types of numbers for help in problem solving.
4. To assist you in drawing conclusions from your data.
5. To visually display to the user what is “good” or “bad” by using green and red.
Now, you can use conditional formatting to format every cell in a range based on your own criteria (and there are a lot of formatting options to choose from). For example, if you have a profit sheet and you want to color code all profits greater than $200 as green and all profits less than $200 as yellow and all losses as red, then you can use conditional formatting to quickly do all the work for you.
Conditional Formatting in Excel
Conditional formatting enables you to format significant amounts of data quickly and easily – while still being able to distinguish different types of data. You can create rules for the formatting options that will allow Microsoft Excel to auto-format for you. You really only have to follow three simple steps.
Step 1: Select the cells you want to format.
Step 2: Click the Conditional Formatting button under the Home menu, Styles section.
Step 3: Select your rules. There are Highlight Cells Rules and Top/Bottom Rules at the top that let you do comparisons with values. For this example, we imposed three rules. The first was that any value greater than $200 was green.
It’s worth noting that only the Highlight Cells Rules section can also be used to compare a dataset to another dataset. Everything else will just use the one dataset that you have highlighted and compare the values against each other. For example, when using the Greater Than rule, I can compare values from A1 to A20 against a specific number or I can compare A1 to A20 against B1 to B20.
The same logic was applied to the second and third rules. The second rule was that anything between $0 and $200 was formatted yellow. The third rule was that anything less than $0 was formatted red. Here is what a portion of the finished spreadsheet looks like.
If you do not like these formatting options, Excel has many different new Conditional Formatting options that you can use from. For example, you can insert icons like colored arrows (Icon Sets), bar charts like in the second example (Data Bars), or even a range of automatically selected colors like in the last example (Color Scales). These three options only compare values from the same dataset. If you select A1 to A20, it’ll only compare those values against each other.
If you later decide that you don’t want your cells to be conditionally formatted, all you have to do is clear the formatting. To do this, select the Conditional Formatting button and select Clear Rules. Then, select whether you want to clear the rules from only the selected cells or from the entire worksheet.
Also, if you have created several rules, you might forget what rules you have applied to what cells. Since you can apply many rules to the same set of cells, it can become quite confusing especially if someone else created the spreadsheet. To see all the rules, click on the Conditional Formatting button and then click on Manage Rules.
When you have more than one rule applied to the same range of cells, the rules are evaluated in order from higher precedence to lower precedence. By default, the newest rule added will have the higher precedence. You can change that by clicking on the rule and then using the up and down arrow buttons to change the order. Also, you can click the dropdown at the very top and see the rules for only the current selection or for each sheet in the workbook.
There is also a checkbox called Stop If True, which I won’t go into detail here because it’s quite complicated. However, you can read this post from Microsoft that explain it in great detail.
New Conditional Formatting Options Excel 2010
Just about everything is the same in Excel 2010 when it comes to Conditional Formatting that was included in Excel 2007. However, there is one new feature that really makes it much more powerful.
Earlier I had mentioned that the Highlight Cells Rules section lets you compare one set of data to another set of data on the same spreadsheet. In 2010, you can now reference another worksheet in the same workbook. If you try to do this in Excel 2007, it will let you select the data from another worksheet, but will give you an error message when you try to click OK at the end.
In Excel 2010, you can now do this, but it’s a bit tricky so I’m going to explain it step by step. Let’s say I have two worksheets and on each sheet I have data from B2 to B12 for something like profit. If I want to see which values in B2 to B12 from sheet 1 are greater than the B2 to B12 values of sheet 2, I would first select the B2 to B12 values in sheet 1 and then click on Great Than under Highlight Cells Rules.
Now click on the cell reference button that I have shown above. The box will change and the cursor icon will become a white cross. Now go ahead and click on sheet 2 and select ONLY cell B2. Do NOT select the entire range of B2 to B12.
You’ll see that the box now has a value of =Sheet2!$B$2. We’re going to need to change this to =Sheet2!$B2. Basically, just get rid of the $ that comes before the 2. This will keep the column fixed, but allow the row number to change automatically. For whatever reason, it won’t just let you select the entire range.
Click the cell reference button again and then click OK. Now the values in sheet 1 that are greater than sheet 2 will be formatted according to the formatting options you chose.
Hopefully, that all makes sense! When looking at Excel 2013, there doesn’t seem to be any new features when it comes to conditional formatting. As a last tip, if you feel that the default rules don’t match what you are trying to accomplish, you can click the New Rule option and start from scratch. What’s great about creating a new rule is that you can use a formula to determine which cells to format, which is very powerful.
Even though conditional formatting looks relatively easy and simple on the surface, it can become quite complex depending on your data and your needs. If you have any questions, feel free to post a comment. Enjoy!
