What is a conditional formatting

Reading time: 3 minutes
Last Updated on 29/06/2020 by Frédéric LE GUEN

The conditional formatting tool changes the color of a cell according to its value.

Never change the color manually ⛔

Many of you change the color of the cells manually using the formatting bar. Of course, to change the colour of the headers of a painting or a whole column, it's perfect.

However, if you use a color to highlight an important result, this technique is not good at all.

Because, if you update your data, your highest or lowest value will not be the same and then, the color will not reflect to correct result.

Avoid to change the background color manually

Example without conditional formatting

In the following worksheet, we have the result of five tests and in column H, the average for each student. You want to color in green the highest average and in red the lowest average.

What is the problem to color manually the cells

Now, we add the marks for the sixth course. The average has changed for all the students but the format of the cells is still the same and doesn't match the new result.

The colours are not changing when the values are updated

So, what to do 🤔

To change automatically the colors in function of cell values, it is better to use conditional formatting.

In this example, you can see that now, automatically, the colors reflect the highest or lowest value.

Change the color automatically

Different types of conditional formatting

Since the version of Excel 2007, the conditional formatting tool has been greatly enriched.

Menu Conditional formatting

Insert bars in the cells

With the conditional formatting, you can insert bars in your cells in function of the values in the cells

The proportion of the bars is based on the values of all the cells 1

Add icons in your cells

You can also add icons to highlight the KPI (key performance indicator)

Icons are added in the cells

Create a color scales

If the values in your cells follow a trend, you can create a color scales to highlight the distribution.

Color Scales shows the distribution of the values in the range of cells

Highlight Top / Bottom values

You can also highlight the highest and lowest values automatically

Highligh the top 5 values

Create your custom rule

You can also create your custom rule with a logical test

The custom rule applies to a range of cells

Leave a Reply

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

What is a conditional formatting

Reading time: 3 minutes
Last Updated on 29/06/2020 by Frédéric LE GUEN

The conditional formatting tool changes the color of a cell according to its value.

Never change the color manually ⛔

Many of you change the color of the cells manually using the formatting bar. Of course, to change the colour of the headers of a painting or a whole column, it's perfect.

However, if you use a color to highlight an important result, this technique is not good at all.

Because, if you update your data, your highest or lowest value will not be the same and then, the color will not reflect to correct result.

Avoid to change the background color manually

Example without conditional formatting

In the following worksheet, we have the result of five tests and in column H, the average for each student. You want to color in green the highest average and in red the lowest average.

What is the problem to color manually the cells

Now, we add the marks for the sixth course. The average has changed for all the students but the format of the cells is still the same and doesn't match the new result.

The colours are not changing when the values are updated

So, what to do 🤔

To change automatically the colors in function of cell values, it is better to use conditional formatting.

In this example, you can see that now, automatically, the colors reflect the highest or lowest value.

Change the color automatically

Different types of conditional formatting

Since the version of Excel 2007, the conditional formatting tool has been greatly enriched.

Menu Conditional formatting

Insert bars in the cells

With the conditional formatting, you can insert bars in your cells in function of the values in the cells

The proportion of the bars is based on the values of all the cells 1

Add icons in your cells

You can also add icons to highlight the KPI (key performance indicator)

Icons are added in the cells

Create a color scales

If the values in your cells follow a trend, you can create a color scales to highlight the distribution.

Color Scales shows the distribution of the values in the range of cells

Highlight Top / Bottom values

You can also highlight the highest and lowest values automatically

Highligh the top 5 values

Create your custom rule

You can also create your custom rule with a logical test

The custom rule applies to a range of cells

Leave a Reply

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