Easy Tips on Conditional Formatting in Microsoft Excel

in software/Tricks by

This article presents the user the step by step guide on setting the conditional formatting on the Microsoft Excel cells. Conditional formatting is a feature in Microsoft Excel which is used to format a cell (or a group of cells) depending upon the value present in it. The value might be entered by the user manually or it can also be updated automatically based on the formula. A simple example of a product’s price will be taken and complete steps for configuring conditional format options are presented.

Conditional formatting Buttons in MS Excel

The conditional formatting option is present under Home tab. The below picture gives a snap shot of Conditional formatting button and the various options present in conditional format.

Conditional Formatting - Easy Tips on Conditional Formatting in Microsoft Excel
Conditional Formatting – Easy Tips on Conditional Formatting in Microsoft Excel

Consider an example where there are three products (Apple, Mango and Orange) and we are interested in seeing whether the price is too high or not compared to the estimated price. The third column is where the user is expected to enter the data (Price of the product). The below figure is the snapshot of the problem statement.

Example - Easy Tips on Conditional Formatting in Microsoft Excel
Example – Easy Tips on Conditional Formatting in Microsoft Excel

If the price entered by the user is less than or equal to the estimated price, the color of the cell should turn green automatically. If the data (Price) entered by the user is more than the expected price, the cells will turn yellow. Any colors can be set and here, in this example, green and yellow colors are assumed arbitrarily.

Click on the input field (cell corresponding to brand apple) conditional formatting button and select greater than option, as shown in figure below:

Selecting Color Formatting - Easy Tips on Conditional Formatting in Microsoft Excel
Selecting Color Formatting – Easy Tips on Conditional Formatting in Microsoft Excel

The following window pops-up. Enter the cell address of the cell in location ‘1’ marked in the image below:

Custom Formatting - Easy Tips on Conditional Formatting in Microsoft Excel
Custom Formatting – Easy Tips on Conditional Formatting in Microsoft Excel

(J3 is the address is present example) which contains the estimated price of product Apple. In the part of the image marked as ‘2’ select custom format. The second window in the bottom of the figure appears. Select ‘Fill’ tab as shown in section ‘3’ and select ‘Yellow’ color and press OK.

Similarly, go to Conditional formatting -> Highlight Cell Rules -> select ‘less than’ and follow the same steps as followed while setting ‘greater than’ rules. This time, since we are setting it for lesser than or equal to we will do a small change in the cell value. Instead of mentioning as ‘=J3’ mention it as ‘=J3+1’. Now select custom rules and under ‘Fill’ tab select Green color and press OK.

Now the cell is ready to be tested. There are two rules running in the back ground. One to set the color to yellow and the other to set the color to Green. Follow the same rules to all the cells (Hint: Use format painting to apply the rules to all other cells). Below picture shows the example working as expected for multiple values.

Complete Example - Easy Tips on Conditional Formatting in Microsoft Excel
Complete Example – Easy Tips on Conditional Formatting in Microsoft Excel

The other options can be explored in a similar way and more and more creativity can be presented using conditional formatting. The colors and the icons effectively make the document interesting and of course smart.

Leave a Reply

Your email address will not be published.

*