Blue Flower

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)

Example 1.

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.

Data before use Conditional Formatting in Excel

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.

How to run the conditional formatting in Excel

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.

How to insert a formula for Conditional Formatting in Excel

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).

How to format the background of the cell that meets the requirement by using conditional formatting in Excel

After formatting cell colors, font colors, etc.. Necessary to apply conditional formatting to our data in a worksheet Excel. Click on the Apply button.

By Accepting of the rules and conditions for Conditional Formatting in Excel

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.

How to highlight the cells that meet the requirement of the conditional formatting in Excel

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)

Example 2.

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).

The dialog box with options to set the rules and conditions for the Conditional Formatting in Excel

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.

Highlight color of all cells that contain a value less than zero in Excel

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.

On this website I will continuously publish various tutorials related to work in Excel 2013. Check back later and see new examples and new tutorials for you and your learning.