course bg
EduPristine>Blog>Conditional Formatting in Excel

Conditional Formatting in Excel

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.

How to Use Conditional Formatting?

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.

Using conditional formatting in excel

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

Conditional Formatting option in Excel

3.Click on the dropdown and you shall see various options.

Options available in conditional formatting

4.When you will hover your mouse on any of the options, you will find other sub options.

How to use conditional formatting in Excel

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.

Formatting cells in Excel

7.After following these steps, you will see that your data has been formatted.

Formatted data in an Excel sheet

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 )

How to add new rule in conditional formatting

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.

Using new formatting rule in Excel

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.

formatting of cells in Excel

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.

Preview of new formatting rule in Excel

13. You will see that your cells have been formatted.

Using Conditional Formatting option in Excel

If you have and queries or suggestions, then the comment box is right below, so do write to us your views.

About Author

avatar EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.

Comments

Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

`````````````````````````````````````````````````` Post ID = 71538