Do you want to format your data in a way that enhances it, but do not want to have to do it manually? Discover in this article conditional formatting, a formidable and customizable valuation tool of Microsoft Excel!
First, launch your Microsoft Excel application.
Step 1: Where can I find conditional formatting?
First, select the cells you want to format.
Go to the "Home" tab of your application if you are not already there, then head to the "Styles" section.
Click on "Conditional Formatting".
Step 2: Predefined rules
In the menu that has opened, you have multiple options available.
First, the "Highlight Cells Rules" allows you to highlight the cells according to the criteria of your choice.
"Greater Than..." selects all cells with a value greater than a value of your choice, "Less Than..." selects all cells with a value lower than a value of your choice, "Between..." all cells whose value is between two values of your choice, "Equal To..." all cells with the value equal to a value of your choice, "Text That Contains..." all cells with text containing the words or letters of your choice, "A Date Occurring..." cells with dates corresponding to the date of your choice and "Duplicate Values..." cells with a duplicate value in the table you selected.
Clicking on any of these options will open the menu where you can choose the value on which the rule is based as well as the color that will affect the selected cells.
Finally, you can select "More Rules..." to open the "New Formatting Rule" menu, which allows you to customize your formatting rules more precisely, but we will see that menu a little later.
Now, let's look at the "Top/Bottom Rules".
These rules apply only to cells with a numeric value. You can decide to apply the formatting to the values or a percentage of the highest or lower values, or the values below or above average.
Again, click on one of these options to open the menu that will allow you to select the number or percentage of values to be modified and the formatting that will be applied.
Note that although the previous menu indicated that ten values (or percentage of values) would be affected, you can change this value to the one you chose.
Now let's move on to the "Data Bars".
With this rule, cells with a numeric value in your table will now have a bar, more or less long depending on whether its value is small or large.
For example, let's apply a blue gradient to the table we selected.
As you can see, the highest value has the longest bar, and the lowest value has the smallest bar.
Note that the bars are proportional, so if one of your cells has a much higher value than the others, it may be the only one with a visible bar, like this:
Then, the "Color Scales".
These rules will color your cells with a gradient according to their values. Different colors can highlight different values.
For example, if you just want a gradient from smaller to larger values, you can take the first color scales.
But if you wish to be able to easily see the most extreme values and ignore the values in between, you can choose a color with white in the middle.
Finally, let's talk about the "Icon Sets".
These rules allow you to add symbols to your cells, selected depending on whether their values are below, above, or close to the average of your values.
Step 3: Create a custom rule
You have seen the predefined rules offered by Excel, but perhaps they do not match what you want to do.
In this case, click on "New Rule...".
This will open the "New Formatting Rule" menu.
In "Select a Rule Type", you can select what type of rule will affect your cells.
For example, "Format all cells based on their value" will allow you to create rules like those created through the predefined rules "Data Bars", "Color Scales", or "Icon Sets", while the other types of rules will give you results like that of "Highlight Cells Rules" or "Top/Bottom Rules".
You can also use formulas to set up your rules, but we will not see that in detail today.
Once you have selected your rule type, you can go to the "Edit rule description" section.
If you selected "Format all cells by their value", you can select the formatting style, as well as the colors of your formatting, or the icons used as part of an icon set style, as well as the values of the cells that will be affected.
If you have selected another type of rule, you can select the values on which your rule is based and the conditions of the formatting, as well as the format itself (by clicking on "Format...").
Once you have finished your rule, click on "OK" to apply it.
Step 4: Manage your rules
Once your rule(s) are in place, you can change them at any time by clicking on "Manage Rules...".
This will open the "Conditional Formatting Rules Manager".
You can choose to view only the rules of the selected cells, or the rules of a particular worksheet.
In this menu, you can click on "New Rule..." to open the "New Formatting Rule" menu, on "Edit rule..." to change the selected rule, on "Delete rule" to delete the rule or on "Duplicate rule" to create a copy of the selected rule.
You can also change directly in this menu which cells your rule affect.
You can click on "Apply" to have a preview of your changes, and, if satisfied, you can click on "OK" to validate them, or on "Close" if you preferred the appearance of your table before your changes.
To finish this article, if you just want to clear all the rules, you can click on "Clear Rules" in the "Conditional Formatting" list.
Click on "Clear Rules from Selected Cells" if you want to remove formatting rules from your selection only, or "Clear Rules from Entire Sheet" if you wish to clear all rules on your worksheet.
You also have the "Clear Rules from This Table" and "Clear Rules from this PivotTable" options, available if you selected a table or Pivot Table.
You now know all need to about conditional formatting on Excel!
Discover our Excel training courses!
📞 Contact us:
📧 Email: support@dileap.com
Follow us on LinkedIn for the latest scoop!