Conditional Formatting in Excel
allows us filling our cell background color or shape in a different way to fulfill the requirement that we set up with the help of formula or conditions. I am going to show the first example of how we can format the background color the cells that meet the condition.
How to conditionally format cells and format the cells that contain the value zero (0)
In the example in the pictures below you can see how to format cells for all customers who have not paid. List of customers who have paid a certain date, we can accommodate on another worksheet (I presented on the same worksheet in order to follow these instructions and tutorials where I work). It is preferable to work with a Conditional Formatting in Excelnaming the cell range or data for simplicity formula. So it is necessary in column 'B' format all cells for those customers who have not paid. In the column 'E' are customers who have paid for.
This task can be solved in Excel using Conditional Formatting so that the first mouse selects a range of data cells that we want to apply the rules and conditions for the Conditional Formatting toolbar, then click on the Home command Conditional Formatting from the drop-down menu and choose the command New Rule.
This opens the dialog box (Figure below) where we need to click the mouse on the last option 'Use a formula to determine the required cells to format'. In the 'Edit the Rule Description' type formula. I am here using Excel COUNTIF function to be searched and listed a range of cells $E$2:$E$10 and found whose name is not listed in the column 'E'. COUNTIF function as a result returns a value of '0' or '1'. If the result is '0' then Excel will color the cell that is not in the name of the list (see the sixth image of this tutorial). So the formula is =COUNTIF($E$2:$E$10;B2)=0.
Furthermore follows form the background color or font cells. It depends on what you want. I decided to fill a cell with a background color (see picture below).
After formatting cell colors, font colors, etc.. Necessary to apply conditional formatting to our data in a worksheet Excel. Click on the Apply button.
The final layout solutions on the worksheet have formatted the background of cells that contain a requirement that we set in the formula. So the picture below you can see that all the cells that contain the name of the customer who has not paid colored red. In the present column 'C' you can see the results of the formula that I previously wrote and I set the conditional formatting rules.
In the picture above you can see an extra column and formula respective column. Please do not pay attention to this column. It is unimportant, I have presented only to clarify tutorials.
How to highlight the cell color where the value is less than zero (0)
In this example I want to show you how to apply the Conditional Formatting in Excel 2013 on all cells that contain a value less than zero (0). Look at the picture below and you will see the option "Format only cells that contain" which means that we want all the cells that meet the requirement of formatting a color. The rule for this example is located in the 'Edit the Rule Description'. Cell Value, =less than, =0, Excel will format the background color of the cell that contains a value or a number less than zero (0).
Look at the picture below and notice that Excel formatted color all cells that contain a value less than zero because of the condition set in the conditional formatting as shown above.
Finally summary of this tutorial. About you all depends on your abilities i.e. your knowledge of the formulas and setting conditions to successfully apply the "Conditional Formatting" in Excel 2013.
Example, how to highlight duplicates in one column.