Visualizing and Presentation Styles in Excel – Conditional Formatting

Visualizing and Presentation Styles in Excel – Conditional Formatting

10 Benefits of Converting Your Data into Excel Tables - new

Conditional formatting is one of basic style for visualizing a set of data using color ranges without changing the data set into a chart or graph. Human mind recognizes the colors for 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 colors and lengths of color bars.

Style 1 (Color Scales)

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

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

Style 2 (Data Bars)

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

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

Style 3 (Icon Sets)

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

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

Then the cells above 100 will changed their background color by default or you can give the colors as your preference.

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

 

Advance Option

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

 

Leave a Reply

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

%d bloggers like this: