347 647 9001+1 714 797 8196Request a Call
Call Me

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 the Author

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.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 71538