Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Organizations of all kinds - whether they are corporations, charities, or social clubs - need to keep track of how their group is doing. Businesses set sales and profit goals, charities have fund raising targets, and social groups often collect dues to pay for their get-togethers. You can use conditional formats, which change a cell's appearance based on the value it contains, to indicate how your results compared your targets. A conditional format changes a cell's formatting based on the cell's value. So, for example, let's say that the warehouse manager at Two Trees Olive Oil wants to track the amount of storage space used in her warehouse, so she can project whether ongoing production would bring in more cases than she could fit.
She has decided to change her warehouse capacity's text to bold in any cell that contains a value over 80%. So I'm going to apply my conditional formats, and the first thing I will do is I will select the cells to which I want to apply them, and then on the Home tab at the Ribbon, click the Conditional Formatting button and select the type of rule that I want to create. In this case, I want to change a cell's formatting based on its value, so I will point to Highlight Cells Rules. Remember that my goal is to create a format that changes the cell's text whenever the value is greater than 80%.
So I will click greater than, and when I do, I get the New Formatting Rule dialog. I do want to format based on the cell value. I am creating a greater than rule, and I want to change the cell's formatting, if it contains a value greater than 80%. So I can just type in 80%. I don't have to do 0.8, anything like that. I can just type in 80%, and Excel knows how to interpret it. Now I can change the format. I just want the cell's contents to be bold, so if I click the Format with list boxes down arrow, I can click custom format, and in the dialog that appears, I can now change the formatting.
So I want to change it to bold, but I also need to remove the formatting that was part of the style that Excel had selected by default. So I will change the font color to automatic, which is black in this theme, and then on the Fill page, I will change the background color to No Fill. Go back to Font and here is my preview. Click OK and there is my preview again inside the New Formatting Rule dialog box. When I click OK, Excel applies the conditional format, and I will clear the selection so that you can see it more easily.
Every cell that contains a value greater than 80% is now in bold. If you want, you can also assign a second conditional formatting rule to any cell. So, for example, if I wanted to fill the cell with red in the background, if the value it contains is more than 85%, I can select the same cells, click Conditional Formatting and click Highlight Cells Rules > Greater Than, and now I can go through the same steps I did last time to create my new format. So we have cell value greater than 85%, and I will change my formatting by clicking in this list box button and then custom format, and now I want the font to be the automatic color.
In other words, I don't want it to change color. Then I can go to the Fill tab and select red. Click OK. The row looks good. Click OK and Excel applies the two formats, and each cell that contains a value greater than 85% is now in red, and any cell with a value of over 80% is in bold. So you see that 82, 85, 84, 82 - all of those cells are in bold, and anything that meets both the conditions greater than 80% and greater than 85% is formatted with a red background as well.
Conditional formats let you tell at a glance how your organization is performing. Make sure to test the rules you create though; bad information is worse than no information at all.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69914 Viewers
80 Video lessons · 127346 Viewers
52 Video lessons · 62321 Viewers
59 Video lessons · 48011 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.
Your file was successfully uploaded.