5 cool ways to use Conditional Formatting in Excel

Share Now

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,


    • 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).


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.


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).


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.



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.


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.



Share Now
November 15, 2019

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

  1. 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

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

Social Media

facebook page EC Analytics Consulting   Linkedin Page EC Analytics Consulting   Youtube EC Analytics Consulting


0124- 4601426


EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED