If you’re used to using older versions of Excel, the conditional formatting options in Excel 2007, 2010, and 2013 will surprise you. So why would you use conditional formatting? Well, here are some reasons why I like using this Excel function:
1. To make your data more attractive.
2. Make tables clearer at a glance.
3. Identify specific types of numbers to help solve problems.
4. To help you draw conclusions from your data.
5. To visually show the user what is â€œgoodâ€ or â€œbadâ€ using green and red colors.
You can now use conditional formatting to format each cell in a range based on your own criteria (and there are many formatting options to choose from). For example, if you have an income statement and you want to paint all profits over $ 200 green, all profits under $ 200 yellow, and all losses red, then you can use conditional formatting to quickly execute all the work for you.
Conditional Formatting in Excel
Conditional formatting allows you to quickly and easily format large amounts of data while still being able to distinguish between different types of data. You can create rules for formatting options that allow Microsoft Excel to automatically format for you. You really only need to follow three easy steps.
Step 1. Select the cells you want to format.
Step 2. Click the “Conditional Formatting” button in the main menu in the “Styles” section.
Step 3: Choose your rules. At the top there are cell selection rules and top / bottom rules that allow you to compare values. In this example, we have established three rules. First, any value above $ 200 was green.
It is worth noting that you can also use only the Cell Selection Rules section to compare a dataset with another dataset. Everything else will just use one dataset that you have highlighted and compare the values ??with each other. For example, when using the Greater Than rule, I can compare values ??from A1 to A20 with a specific number, or I can compare A1 and A20 with B1 and B20.
The same logic applied to the second and third rules. The second rule was that everything from $ 0 to $ 200 was yellow. The third rule was that anything less than $ 0 was formatted in red. This is what part of the finished table looks like.
If you don’t like these formatting options, Excel has many different new conditional formatting options that you can use. For example, you can insert icons such as colored arrows (icon sets), histograms as in the second example (data bars), or even a range of automatically selected colors as in the last example (color scales). These three parameters only compare values ??from the same dataset. If you choose between A1 and A20, these values ??will only be compared with 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, click the “Conditional Formatting” button and select “Clear Rules”. Then choose whether you want to clear the rules from only the selected cells or from the entire sheet.
Also, if you have created multiple rules, you may forget which rules you applied to which cells. Since you can apply many rules to the same set of cells, this can be very confusing, especially if someone else created the spreadsheet. To see all the rules, click the Conditional Formatting button and then click Manage Rules.
If multiple rules are applied to the same range of cells, the rules are evaluated in order from higher to lower priority. By default, the most recently added rule will have a higher priority. You can change this by clicking the rule and then using the up and down arrow buttons to change the order. Alternatively, you can click the dropdown at the very top and see the rules for the current selection only or for each sheet in the workbook.
There is also a checkbox called Stop If True, and I won’t go into details here, because it’s rather complicated. However, you can read this post from Microsoft which explains it in detail.
New conditional formatting options for Excel 2010
It’s pretty much the same in Excel 2010 when it comes to the conditional formatting that was included in Excel 2007. However, there is one new feature that really makes it that much more powerful.
I mentioned earlier that the Cell Highlighting Rules section allows you to compare one dataset to another dataset in the same spreadsheet. In 2010, you can now link to another sheet in the same workbook. If you try to do this in Excel 2007, it will allow you to select data from another sheet, but it will give an error message when you try to click OK at the end.
In Excel 2010, you can now do this, but it is a little tricky, so I will explain it step by step. Let’s say I have two worksheets and each has data from B2 to B12 for something like profit. If I want to see which values ??B2 to B12 on sheet 1 are greater than the values ??B2 to B12 on sheet 2, I would first select the values ??B2 to B12 on sheet 1 and then click Great Than in the Highlight Cells Rules section.
Now click on the cell reference button I showed above. The field changes and the cursor icon turns into a white cross. Now click on sheet 2 and select ONLY cell B2. DO NOT select the entire range from B2 to B12.
You will see that the field is now set to = Sheet2! $ B $ 2. We will need to change this to = Sheet2! $ B2. Basically, just get rid of the $ before 2. This will keep the column fixed, but allow the row number to change automatically. For some reason, it won’t let you just select the entire range.
Click the cell reference button again, and then click OK. Values ??on sheet 1 that are larger than sheet 2 will now be formatted according to the formatting options you selected.
Hope this all makes sense! When looking at Excel 2013, there doesn’t seem to be any new functionality when it comes to conditional formatting. As a final tip, if you feel like the default rules don’t match what you are trying to achieve, you can select 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 efficient.
While conditional formatting seems relatively simple and straightforward at first glance, it can get quite complex depending on your data and your needs. If you have any questions, do not hesitate to leave comments. Enjoy!