Hi. In this tutorial, we’ll go over how to use the Data Bars, Color Scales and Icon Sets options of the Conditional Formatting styling feature in
Excel. Using these can help you display your data in a more visually interesting way. And, because the coloring is based on the value of
the cell data, it can also help you spot trends. And, by the way, if you enjoy this tutorial, don’t forget to Like the video and subscribe to the
channel for even more tips. On my screen is sales data for a couple of quarters. So, if we select the
sales data and, in the Home tab, go to the Conditional Formatting section and choose Data Bars, we see that we have options to
create Data Bars based on values. We have the option of Gradient Fill or Solid Fill, which is what I'm
going to choose for this example. We'll go with this blue Solid Fill.
So, once we apply this, we can see, through the use of color, how the sales numbers rank against one another. Excel is essentially
determining the lowest and highest values in the range and then using color to visually rank them. So, we can step into the formatting
for our Conditional Format to see even more options. So, we can do that in 2 ways; we can choose More Rules here or we can go to
Manage Rules, choose your Rule, and then Edit
your Conditional Formatting Rule. And, one of the options that we have, we can select to show
only the Bar - and this will get rid of the numbers, making our data look more like a Bar Chart.
I'm going to click Apply so you can see what that can look like. So, we can go back into our rule for more options and one thing to notice
here is
how Excel is setting the Minimum and Maximum values. So, right now it's set to Automatic, meaning that it looked at all of our
data and just determined what the lowest and highest values were and created the shading that way. Now, you can change this to set a
specific number to be your lowest value or your highest value. You can also have it be based on Percents, Formula, or Percentile Ranking.
But, I'm going to leave this in Automatic. But one thing to know is - whatever you change the Minimum to, make sure you also change your
Maximum to that same setting. Now, you can change the Bar appearance. Right now we've chosen a Solid Fill, but you can change back to
a Gradient Fill or change your color, if there's a
specific...perhaps like a brand color that you need to choose for your data. And then, if you would like for your cells to have a Border,
and you can also change the direction of your Data Bars. So, right now they're going left to right, but you can always switch them to be
going right to left. I'm going to leave them left to right for this example. Another thing that we can do in here is alter how our data looks if we
have a negative value. So, I can change any of these values to a negative. So, we'll just say a -1,000, just for argument's sake. And you see
how now all of our Data Bars shifted just a little bit and we see sort of a dotted line. And then here, where I typed the negative, there's this
little red bar going to the left. If we go back into our Manage Rules, in that section we were in. We can go into Negative Value and Axis and
change how that can look. So, we can change the Fill color from red to whatever we want to or we can have it apply the same Fill color as
the positive bar. It'll just be facing the other direction. As well as change where the axis setting can start and what the color of that
dotted line will be. But we're just going to leave everything as is because I'm happy with the way it looks now.
Data Bars can help us spot trends a little bit easier than reading the numbers. And, looking at our Data Bars, we can see that sales are
gradually increasing each month with the highest values of our data trending towards June. So, Color Scales are similar to Data
Bars except that they use multiple colors. So let's
clear these rules. And we'll go into our Color Scales. So, here as with the Data Bars, you can see a preview of what your Color Scales
could look like. And, with Color Scales you have the options of either 2- or 3- color Color Scales. For example, this Green-Yellow-Red is
a 3- color one, as are many of these guys up here. But, if we scroll a little lower, you see that we have a White-Red Color Scale with only 2
colors. So, let's choose one of these 3- color ones.
Then, if we go into Manage Rules and we step into the Edit Rules, we can see the options for our Color Scale.
So we can reformat it to a 2-color scale, if we like. We can, as with the Data Bars, change the Number, Percent, Formula or Percentile
that we're basing our scales on. We can change the color of the scale and we can change the midpoint that the scale is using to
make that color switch. If you use a 2-color scale, obviously, the midpoint is automatically
gone and it's just more of a gradual color change from one color to the next.
So, Icon Sets use shapes instead of colors to rank the data from lowest to highest. So once we've set our Icons, going into Manage Rules
and stepping into the Conditional Formatting Rule, we again have more options as to whether or not want to hide
the data and show only the icon, Reverse the Order of the Icon,
or set a specific Minimum, Mid and Maximum for Excel to determine which Icon to show. So, I hope you found this tutorial helpful on how to
use Excel Conditional Formatting Data Bars, Color Scales and Icon Sets. If you enjoyed it, please make
sure to give it a Like and don’t forget to subscribe to this channel. Thanks!