+91-8826547882

5 cool ways to use Conditional Formatting in Excel

If you want to pick out certain conditions met data from the huge list, conditional formatting in Excel can help you on this. Conditional Formatting in excel is very flexible for applying specified formatting to a cell or range only when certain conditions are met. Here are some cool ways to use it.

This article is from basics of Conditional formatting in Excel.

Click Here to download the spreadsheet.

conditional formatting

Highlight Cells Rules:

1)      Greater Than:
If you quickly want to see the greater number among your data than the specified number, you should use this feature.

    • Select the data range you want to format,
    • Click Conditional Formatting icon, Styles Panel under Home Tab,

conditional-formatting

    • Select Greater Than option(under Highlight Cells Rules), enter the number greater than which you want to highlight the cells(here, I have entered 68 for example).

conditional-formatting-image3

2)      Duplicate Values:

Reference to the above example you can also use the conditional formatting to highlight duplicate values.

    • Select the data range.
    • Same click on the Conditional Formatting icon in Styles Panel under Home Tab,
    • Select Duplicate Values option.

conditional-formatting-excel-image4

3) Top/Bottom Rules:

1)      To color bottom or top values use this feature.

    • Select the data range.
    • Choose Top/Bottom rules, click Top 10 Items.
    • Enter number of items you want to highlight(here I have entered 2).

conditional-fromat-in-excel-image5

4) Data Bars:

To see the cool patterns of bars in your data, you can use this feature.

    • Select the Data Range and click Data Bars > Gradient Fill option.

excel-condition-formatting-iamge6

use-of-conditional-formatting-image7




Color Scaling:

By applying color scaling formatting you can quickly identify variances in a range of values with a quick glance.

    • Select the data range.
    • Click the color Scale option in conditional formatting.

formatting-in-excel-image8conditional-formatting-in-excel-2010-image9

6) Icon Sets:

I really like icon sets, they are neat way to format a cell to show informative information in a quick glance.

      • To apply them, click on Icon Sets in conditional Formatting, select more rules.

conditional-formatting-image10

conditional-formatting-image11

August 25, 2013

1 Responses on 5 cool ways to use Conditional Formatting in Excel"

  1. Anshul gupta says:

    This is awesome Lokesh. and very useful information which play a vital role in Graphics reportng part….your thought is very valuable for us please keep share with us with these tips.. thanks.

Leave a Message

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com