February 19, 2015
Two similar answer sheets, two same answers but one student gets more marks than the other. Why??? No, the teacher was not partial but the thing was, that one student had highlighted the important points whereas the other had simply written them. Not highlighting the important things cannot only cost you some marks but also some important projects at times. To save you from facing a similar situation we bring to an article on conditional formatting in Excel that will help you to highlight the right things as per your requirements.
1.Select the data that needs the formatting. In the example below, we have selected the cells C2:C13 as we want to know the months in which sales was above 5000 in NewYork.
2.You will find the conditional formatting option at the right hand side of the home tab. Alternatively, you can also use shortcut key : Alt + H + L
3.Click on the dropdown and you shall see various options.
4.When you will hover your mouse on any of the options, you will find other sub options.
5.You can go ahead with any formatting option which you feel is appropriate for your data. As mentioned above, we want to know the months in which sales was above 5000 in NewYork. So we shall select the Highlight Cell Rules and the Greater than sub option.
6.When you select the above mentioned options, a new dialog box shall appear. Since we want to highlight the data above 5000, we mention 5000 and then you can select any format from the options given or you can customize your format through custom format option. In our case we select Light Red fill with Dark Red Text. You can see the preview on the selected data.
7.After following these steps, you will see that your data has been formatted.
8.If you don’t want to use these existing rules then you can always create your own rule. In step 3 instead of using any of the options. Select the option New rule.
9.A new dialog box shall appear with six options. In our example we shall use the 2nd option ( format only cells that contain )
10. In our example we are searching for the cells where the value is in between 4000 to 8000. After filling the data click on the format button.
11. After clicking on the format option, a new dialog box shall appear. It consists of 4 options (Number, Font, Border, Fill) In our example we shall select fill and use the blue color. If you want you can give various fill effects pattern style by clicking on the respective options. Click on OK after filling the appropriate things.
12.You can see the preview of your formatting in the next dialog box and if you think that it is fine, then click on OK.
13. You will see that your cells have been formatted.
If you have and queries or suggestions, then the comment box is right below, so do write to us your views.