Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Conditional formatting lets you format numbers and dates according to their value. People typically use it to highlight numbers that are especially high or especially low, but you can also highlight numbers that fall into a certain range and even find duplicate values. Before we do that, we want to select all of these numbers. So a couple of ways you can do it. Click the first number and press Ctrl+ Shift+End and that will select to the end. If you don't like doing that, you can click the first number, scroll down if you need to, and hold the Shift key down and Shift+Click the last number so it's selected.
Well if you need to select these numbers over and over again, you don't want to go through this. So let's give it a range name. Put your mouse pointer up here in name box and Click and let's call it data, a simple enough, then press Enter and now if you click somewhere to deselect, you could always go up here, click the Down arrow, choose data and immediately select those numbers again. I will just scroll to the top. Now, if you are on the Home tab, go over here to the Styles section, and this is where you can find Conditional Formatting. Click that and there are three kinds of automatic ways of doing it.
Data Bars, Color Scales and Icon Sets. Well, if you choose Data Bars, you have couple of choices of Gradient Fills and Solid Fills, and when you choose these, not even clicking the mouse, you could just roll over these and you see what this does. This automatically compares the numbers with each other. So this one for example, you can see low numbers have short bars, the highest numbers have the longest bars. Maybe you want yellow instead of blue. This is really personal preference, or what you like. So these are Solid Fills instead of Gradients.
Go over here to Color Scales. This will give you instead of one color of varing lights. These will give you different colors. I kind of like this one over here. Blue, White and Red. Not just because it's patriotic but because this clearly shows you that the highest numbers are the darkest blue and that the smallest numbers are white. And if you want the reverse color scheme, you have red, white and blue. These are some very automatic built-in ways of comparing numbers with each other.
Let's also go to Icon Sets and here you have all these icons. Honestly, I would not use some of these but I want to show you here you have some directional arrows. So this one for example, a down red arrow is for the lowest numbers and up green arrow is for the highest numbers. Maybe instead of a scale of three arrows, you want four arrows or five arrows. So for this one for example, an upward green arrow is highest number, lowest numbers are down red arrow and then you have three yellow arrows in the middle.
Maybe you want bars kind of like on your cell phone. So here's a scale of one to four or here's a scale of zero to 4. Again you can look at the numbers and you can see scale of zero is the lowest number and all four bars are the highest number. Now, let's say we click one. I will choose this. Click off it, so you can see it. Let's go and reselect the data area. Scroll to the top if you need to. Let's go back to Conditional Formatting. And let's apply Data Bars. And you see what can happen here is Excel will let you apply one conditional format on top of another.
Now sometimes that's good but other times you might end up with something hideous like this. So here's what you can do is click the Conditional Formatting button and down over here choose Clear Rules. Now you can Clear Rules from Selected Cells. What I am going to choose here is Clear Rules from Entire Sheet. So that wipes out all the conditional formatting. Let's go back and choose the data area, scroll up, go back to Conditional Formatting. So now you can choose one those data bars and those icons won't be in the way, or vice versa.
So if you want just a very quick way of applying formatting to compare your numbers, I think that's your best bet. In the next movie, I am going to show you some conditional formatting where we can take over some more control over the formatting and apply exactly the type of formatting that we want rather than taking the automatic features.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87267 Viewers
80 Video lessons · 136333 Viewers
59 Video lessons · 54990 Viewers
52 Video lessons · 68853 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.