Visualizing and Presentation Styles in Excel – Conditional Formatting

in Microsoft Excel conditional formatting is one of basic style for visualizing a set of data using colour ranges without changing the data set into a chart or graph. The human mind recognizes the colours of various information. As an example; Green for “OK/good” and Red for “No/bad”. The basic concept of Conditional formatting is to recognize the values from colours and lengths of colour bars.

Style 1 (Color Scales)

Recognize the highest number to the lowest among many cells. Below table contains vehicle counts in 16 hours into vehicle category. We cannot recognize the highest numbers at once.

Pic 1
Pic 2

Steps: Select the range (E2:N17) > Home > Conditional Formatting > Color Scale

Style 2 (Data Bars)

Data Bars gives more attractive style than colour code for this table.
Steps: Select the range (E2:N17) > Home > Conditional Formatting > Data Bars

Pic 3

As an advanced option, the length of the bar can be changed according to user requirement.
Steps: Select the range (E2:N17) > Home > Conditional Formatting > Data Bars > More Rules

Pic 4

Style 3 (Icon Sets)

Data Bars gives more attractive style than the colour code for this table.
Steps: Select the range (E2:N17) > Home > Conditional Formatting > Icon Sets

Pic 5

Style 4 (Highest Cell Rules)

By applying this style, you can easily identify the cells above/ below/ between of particular value from your data set.
Steps: Select the range (E2:N17) > Home > Conditional Formatting > Highest Cell Rules > Greater Than > Value as 100

Pic 6

Then the cells above 100 will changed their background colour by default, or you can give the colours as your preference.

Pic 7

Style 5 (Top Bottom Rules)

This style gives options to highlight the cells that are in the highest range/ bottom range in ascending/ descending order or highest/ bottom by percentage.
Steps: Select the range (E2:N17) > Home > Conditional Formatting > Top Bottom Rules > top 10 items > Value as 10

Pic 8
Pic 9

Advance Option

You can highlight cells based on formula when the formula is true.

Pic 10
Pic 11

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.