«

»

Conditional Formatting - Data Bars and Icons

The conditional formatting allows you to add Data Bars and the icons in your cells is function of the result of rules.

The Data bars

When you insert a conditional formatting Data Bar, everybody will approve your document. It's an instant success. 👍😎😊

The method to insert Data Bar in your cells is very simple.

  1. Select your range of cells (1 column or more).
  2. Select one of these options in this menu.

Instantly, your cells are partially fill with a bar in function of the value between the min and the max of the selected range. Change some values in the following document to change the size of bars.

As you can see, the fill is proportional to the cell's values. Look at the size of the bars for the values 0, 5, 10 and 20.


The icon sets

In the same way you can insert icons in your cells to easily identify the best and worst values.

  1. Select your range of cells (1 column or more).
  2. Select one of these options in this menu.

You can see, you have a lot of choices, circles, arrows, flags, stars, network pictograms, ... But the most important to notice, it's that you can select

  • 3 icons like the traffic lights
  • 4 icons like the arrows
  • 5 icons (the pie chart or the network)

In function of the accuracy you want to display, you select one of these icon sets.

Now, in our example, we have selected the circles and the result is this one.

The picture shows the default result of the icon set. By default, when you select a set of icons of 3 icons, their colors always represent a third of the data.

In our case, we want

  • Red for the values between 0 and 8 (exclude)
  • Yellow for the values between 8 and 10 (exclude)
  • Green for the values greater or equal than 10

Customize the rules for the icons

By default, icons split your data by equal proportion but you can change these setting

  • Go to the menu Home>Conditional formatting
  • Select the last option Manage rules...
  • In the dialog box, select the option This worksheet (that's better to always select this option)
  • Select your rule in the dialog box
  • Click on the button Edit Rule ...

 

  • Change the setting of the green icon to 10 the yellow to 8.
  • Replace the Type Percent by Number

The new rules look like this 😉

 

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/data-bars-and-icons/


Leave a Reply

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