How to Use Conditional Formatting to Shade the Specified Data in Excel

Date:2019-8-23 Author:Cordelia

The so-called Conditional Formatting, is a practical feature with which Excel can set the format of the corresponding data. One example is that when you want to highlight some specified data in a spreadsheet, you can use Conditional Formatting to shade them automatically avoiding a lot of manual operations.

First, select the range of cells you want apply the conditional format to.

How to Use Conditional Formatting to Shade the Specified Data in Excel

Find Conditional Formatting in Home tab.

How to Use Conditional Formatting to Shade the Specified Data in Excel

There are many existing rules in the drop-down menu you can directly choose and use. In my case, I want to shade all the data larger than 400 in the Score column of my table. So choose Greater Than… in Highlight Cell Rules.

How to Use Conditional Formatting to Shade the Specified Data in Excel

Type the number 400 in the text box and choose a format color. If you don’t like these color schemes in the drop-down list, hit Custom Format…

How to Use Conditional Formatting to Shade the Specified Data in Excel

Switch to Fill tab and select the Background Color and Pattern Color you like. Click OK to confirm the settings and hit OK again in Greater Than window.

How to Use Conditional Formatting to Shade the Specified Data in Excel

Now the format you customized is applied to all the data greater than 400 in Score column.

How to Use Conditional Formatting to Shade the Specified Data in Excel

If you’re not satisfied with the existing rules, you can create a new one by clicking New Rule… in Conditional Formatting.

How to Use Conditional Formatting to Shade the Specified Data in Excel

There are many rule types in the list. Take the first one as an example, you can set a color for the Lowest Value and another color for the Highest Value. It will create a set of gradient color for you. Click OK to apply it to your worksheet.

How to Use Conditional Formatting to Shade the Specified Data in Excel

Then all the selected data will be shaded with the color from green to blue in ascending order.

How to Use Conditional Formatting to Shade the Specified Data in Excel

This is just a quick start guide for Conditional Formatting. There are much more practical cases you can make use of it and improve your spreadsheet skillfully.

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

One response to “How to Use Conditional Formatting to Shade the Specified Data in Excel”

  1. Hi there colleagues, good paragraph and good arguments commented at this place, I am really enjoying by
    these.

Leave a Reply

Your email address will not be published. Required fields are marked *