Conditional formatting using colors in Excel is undoubtedly a feature that greatly facilitates the interpretation of spreadsheet.
For example, in which of the images below is the result of the spreadsheet clearer?
Ao develop your spreadsheets, you can paint cell by cell manually, which is a laborious practice and subject to human error, or use conditional formatting. This feature of microsoft excel allows you to create conditions for your cells to be formatted automatically.
We will exemplify the formats below, but the conditions can be given through Ranking, - greater values of a color and a gradient until the smaller ones - Conditional and even through Formulas. In addition, color cell and text formatting, cell status bars, and custom icons can be chosen. See the example below:
From now on, in this post, we'll be teaching you how to use the Conditional Formatting feature in a practical way.
See also other features: Free ebook with conditional formatting tips!
1) How to use the conditional formatting feature?
It is quite simple to use the conditional formatting feature. It is on the "Home Page" tab within the "Styles" sub-tab, as shown in the image below.
To start the practice example, create a list of 15 rows with values from 1 to 10 in excel, as shown in the example below:
The first item in the Highlight Cell Rules menu is to create automatic formatting from pre-set conditions. Its sub items are:
a) It is greater than ... - you set a value and you can choose cell formatting (font, font color, borders, and background color) for all values greater than this value.
b) It is smaller than ... - you set a value and you can choose cell formatting (font, font color, borders, and background color) for all values less than this value.
c) It is between ... - you set two values and you can choose cell formatting (font, font color, borders, and background color) for all values within the range between them.
d) It is the same as ... - you set a value and you can choose cell formatting (font, font color, borders, and background color) for all values equal to it.
e) Text Containing ... - you define a text snippet and you can choose cell formatting (font, font color, borders, and background color) for all occurrences of this snippet. For example, by choosing "larger" in a list, cells with "greater", "greater", "greater", and more will be affected by the chosen formatting.
f) A Date Occurring ... - Same thing as item "equals ...", but with dates.
g) Duplicate values ... - any duplicate values will be affected by the chosen formatting.
When selecting your list and clicking on "Conditional Formatting"> "Highlight Cell Rules"> "Is Greater than ...", you should see the following form or something similar, depending on your version of excel:
When you click on any sub-item, in addition to the "Is greater than ...", the form that will open is very similar in all cases varying only the pre-established rule.
The second sub-item of the conditional formatting menu "First / Last Rules" works the same way. You choose predefined criteria to form formatting conditions, but this time, you'll decide whether the rule will focus on 10 higher values, 10% higher, 10 lower, 10% lower, and above the list average and those below the list average.
The next 3 menu options are those discussed earlier in this post: “Data Bars”, “Color Scales” and “Icon Sets”. Just to show you, again, now that you know how to use Conditional Formatting, I will exemplify the 3 in the table we created. You can choose custom layouts and these are very self-explanatory. In my case, the result is as follows:
The last items in the "Clear Rules" menu, "Manage Rules" and "New Rule" function as a custom rule manager. In this case, you are free to create as many rules as you want and with the conditions you want. This is good because you are not conditioned by the rules pre-established by Excel itself.
To start, click on "New Rule ...":
You should see a frame very similar to this one in the image. Notice that in "Show formatting rules for:" you can switch from "Current Selection" to "This Worksheet" and view all rules created in the worksheet. In the "Applies to" column you will see where the rule in question is being addressed.
When we click on "New Rule", we can see a new frame, this time for creating custom rules from the conditions you think:
In this chart you can repair the following facts:
- "Format all cells based on their respective values" - similar to "Enhancing Cell Rules" we saw earlier, but you can customize the colors and scale format.
- "Format only the first or last values" - similar to "First / Last Rules" that we have already seen in this post, but with customizable values and colors, this time.
- "Format only above or below average" - same as the previous one.
- "Format only unique or duplicate values" - we have also seen this in "Enhancing Cell Rules".
- The only new features are "Format only cells that contain" and "Use a formula to determine which cells to format". We will focus on exemplifying them.
(a) "Format only cells containing"
In this example, you are free to create your own formatting conditions. In the first box you select what will be analyzed in the cell among: Cell Value, Specific Text, Empty, No Empty, With Errors and No Errors. Each choice will condition you to a fill type of the rest of the condition.
For example, when choosing Cell Value, you can select from the following options: Is Between, Is Not Between, Is Equal To, Is Not Equal To, Greater Than, Less Than, Greater Than Equal To Minor Or Equal To. From this choice, simply fill in the reference values. You can choose a value or a cell that contains a value for the condition to make the comparisons in your list and format it.
By clicking on "Format ..." you can customize the colors that will appear for this condition. You can create two similar rules (one at a time), for example, paint cells with a value greater than zero green and paint cells with a value less than zero red. Once created, you can view the rules by clicking on “Conditional Formatting”> “Manage Rules…”.
b) "Use a formula to determine which cells should be formatted"
In this case, you will use a formula to define what will be formatted. Note that these formulas are different from the formulas that you write in the Excel main formulas tab. In conditional formatting, you need to use a signal.
For example, if you select the list we created and use this type of conditional training with the formula "= 5> 1" with the sign ">", all cells in the list will be impacted by the chosen formatting. The reading that Excel does is as follows: it goes from line to line and asking itself: "5 is greater than 1?" Since the answer will always be true, it will format all lines.
To make this formula dynamic, we need to use cell references. For example, let's now type "= $ A1> 5". The result should be as follows:
Note that if you write “= $ A $ 1> 5”, Excel will not format any cells, as you are locking the cell line A1 and it will always be less than 5.
We can also use more complex formulas in this type of formatting. For example, I will change the formula to “= IF ($ A1 = 7; 1; $ A1)> 5”. The new result follows below:
Excel did not apply the formatting to the 7 value as it read my formula and it says that if the value is 7, I want to assign the new 1 value to it. Not being 7, the cell value is retained. Because the new 1 value is smaller than 5, formatting is not applied.
You can also use other cells to define what will be formatted or not. For example, let's say this column represents the grades of students at a college. The average to pass is 6 and I want to format their names in column B from the numbers in column A. I need to select the list of names in column B, go to create rule and in the formula type “= $ A1> 5” . See the result:
Now you already know how to do conditional formatting and can create smart spreadsheets! Congratulations! If you have questions, comment below in this post.
At LUZ Business Spreadsheets, we developed ready-made spreadsheets developed by accredited professionals. Here are some LUZ worksheets where you will find conditional formatting:
1 - Cash flow
Are you in doubt? Get in on LUZ Excel Forum and send your question!