In previous post, we learnt about in-build conditional formatting rules and in this post we will learn how to create custom formatting rules in Excel.
- Introduction To Conditional Formatting In Excel
- How To Apply Conditional Formatting In Excel
- In-built Conditional Formatting Rules In Excel
- How To Create A Conditional Formatting Rule In Excel
- Useful Applications Of Conditional Formatting In Excel
We will also learn how to manage and clear conditional formatting rules.
- Create New Conditional Formatting Rule
- Manage Conditional Formatting Rule
- Clear Conditional Formatting Rule
The new rule option in conditional formatting is used for custom formatting cells based on their values, formatting cells with respect to a specific value, formatting only top or bottom values, formatting values above or below the average value, formatting unique or duplicate values and most importantly for formatting cells as per an excel formula.
Let us consider our product sales database to understand this.
Step 1: We need to select the “New Rule” option. As soon as this option is selected a window appears. In that window select the option “Use a formula to determine which cells to format”.
[Enlarge the image by clicking on it for a better view]
Step 2: Enter the desired formula in the “Format values where this formula is true” text field. Always remember that a formula should start with an equal to(=) sign. In this case we want to highlight all the sales values which are more than the combined average. So we first need to calculate the average of all the values ranging from E5 to E24 and then we need to highlight the values(From E5 to E24) which are more than the calculated average value. In this example the average sales is 41891.95. All the values in column E more than this value should be highlighted. The formula we have entered is:
After writing this formula we have to click on the “Format” button. As soon as we click the “Format” button we will get a wide range of colors to choose from for highlighting our information. We have chosen “Yellow” here.
Step 3: As soon as we select the color and click the “OK button we will get our desired results. All the products which have sales more than the total average sales are highlighted in yellow.
We can also edit our conditional formatting rules after we have applied them. For editing our rule we need to select “Manage Rules” option.
This option allows us to clear the conditional formatting created by us. We can choose to clear the conditional formatting from a range of selected cells, from the entire sheet or from a table.
So we know pretty much about what is conditional formatting and how to use it. In our next post, we will learn about some useful applications of conditional formatting.