Join Aaron Quigley for an in-depth discussion in this video Solution: Write a conditional format, part of Office for Educators.
For this challenge, I'm first going to focus on writing conditional formatting for a single cell and then apply the conditional formatting to the entire section of the grade book that I'd like it to be applied to. Here we have cell F4. I can see that the cell F4 is indeed greater than the total point of this assignment, which is 12. So, I'm going to go and select Cell F4. Under the home section of the ribbon, I'm going to go and click on Conditional Formatting. The first thing I'd like to do is to write the formula that'll highlight this cell, because its value is greater than the total points possible for the assignment. To do that, I'm going to select Highlight Cell Rules and I'm going to click on More Rules.
Here, I'm going to choose to use a formula to determine which cell to format. The formula for this is fairly straightforward. It's going to be equal to, parenthesis, F4 is greater than F2, and a closing parenthesis. Let's click on Format. We're going to go to Fill, and we're just going to give it a background color of this peachy orange color, and we'll click OK. We'll click OK, you can see that the cell has changed to be an orange color because it's a 13. Let's go and test this by typing a 12. 12 is not greater than itself, so there's no color.
11 all the way down would work. Let's go and just type a 15, and it looks like this is formatting correctly. Now, if all I wanted to do was simply apply this formatting to the rest of the page, I can go back to Conditional Formatting. I can go to Manage Rules, now the rule is created. And I can say this applies to, I'm going to select the entire area. So I've gone all the way to the left of my grade book, and I'll go all the way to the bottom of my grade book and now have that entire area selected. I can click back on the cell section. Click Apply, click OK.
And now you'll notice we've got some issues here. The first issue that we see is that, wait a minute, this is a 20, yet the unit test was worth 30. So why is it I've got conditional formatting applying to things that I don't want it? And that has to do with how we wrote our formula. So, let's go ahead and go back into our Conditional Formatting. Let's go to Manage Rules. And let's take a look at the formula itself. I can see this simply by editing this rule. Now here we have it equal that F4 is greater then F2. But what happens when I move down one. All of a sudden it's trying to say is F5 greater than F3.
Or what happens when I move over one. Okay, here we've got G4 is greater than G2. That's okay. As the cell that the formatting has been applied to, it can move around the page. We would also like the column to change with it. We always want row two to be the number we're comparing this inequality to. To make sure that the two stays the same throughout this entire formatting, I'm going to type a dollar sign directly in front of the two, and now I'm going to click OK. Here, I can see it applies to the whole sheet still. I'm going to click Apply. And I'll go ahead and I'll click OK. You'll notice now that the rest of the formatting has properly been updated.
Here, we've got a unit test, which is greater than 30 and it's highlighted. Here we've got week one quiz, which is greater than 55 and it's highlighted. Now, let's go ahead and talk about how to tackle these M's. Once again, I'm going to start with just one cell. Here I will select M under Susan's grades. I would go to conditional formatting. And we're going to go to Highlight Cell Rules, and we're going to say that If a Text Contains. So here, I can choose that if a certain amount of text contains a certain item, then it applies certain amount of formatting. For example, we're going to format cells that contain the text M.
We're going to have a little red fill with dark red text. There are several predefined formats you can select from. For example, I could just say give it a red border. Or I can have it be green filled with green text. But, since this is an indicator that the student has an outstanding assignment, I think red is an appropriate color. I'll go ahead and click OK. And now you can see that M has been changed. Now, the next thing we need to do is apply this formatting to the rest of the page. I'm going to go ahead and click on Conditional Formatting, we're going to manage this rule. And here, I can simply copy where we applied our last rule to using keyboard command Ctrl+C or Cmd+C on the Mac, and paste it underneath our new value.
I'm going to click Apply. Click OK. And now you can see that all of the M's in the document are applying that new formatting. I'll go ahead and change one just to double check. There we go. We've now completely formatted our grade book, utilizing conditional formatting to double check if we've made an input error. And also to help us identify students that have outstanding assignments.
- Creating lesson plan templates
- Creating worksheets with math equations, charts, and graphs
- Grading papers
- Creating a gradebook in Excel
- Creating an animated presentation
- Setting up a school email account in Outlook
- Storing documents online with SkyDrive
- Creating a class website with SharePoint
Skill Level Intermediate
Q: This course was updated on 10/01/2014. What changed?
A: We added a brand new chapter on Office Mix, the PowerPoint plugin that allows educators to record interactive presentations and test students with quizzes.
Q: This course was updated on 10/15/2015. What changed?
A. We added videos for OneNote, OneDrive, and Office Online. OneDrive replaced SkyDrive as the cloud-based file service.