Conditional formatting rules will call attention to specific values on your reports. In this video tutorial, Adam Wilbert shows how to apply conditional formatting to highlight top performers. Also, learn how to create data bars to visualize the relative size of values in a column of data.
- [Instructor] Conditional formatting rules will call attention to specific values that you'd like to highlight in your reports. Let's create a new report to analyze the Landon Hotel's monthly revenue summary. To do that, I wanna make use of this query that we created early in the course called MonthlySummary, but I'd need to make a few changes to it. Let's right click on it, open up in Design View. I'm gonna come up to the Show Table window and open up the RoomRates table. Just gonna double click to add that in, and close this window. Then, I wanna move the assignment ID all the way to the far right. We'll click at the menu top to highlight it, and then click and drag to move it over.
Finally, we're gonna add in the column called Rate, double click on that, and come down to the Totals row where it says Group By, and change it to Sum. Now let's take a look at the results here, we can see that we have each month broken out by year, and then we can get a total count of the number of visits that we had across that month, as well as the total revenue for that month. Let's go ahead and close this query and save it. And then, with it still selected over here in the navigation pane, I'll come up to the Create tab, and in the Reports group, we'll create a quick report out of it by pressing the Report button.
Next, I wanna add some grouping and sorting, so I'll turn that window on. We're gonna come down and add a sort based off of the year, and then we'll add another sort based off of the month. Now I want this report to be in reverse chronological order, so I'll select Sort by Year, and change it from smallest to largest, to largest to smallest. We'll do the same thing for the month as well. Now we can go ahead and close the Group and Sort window, and finally turn our attention to the conditional formatting. I wanna highlight any month where we had a count of assignment ID, or a total number of check-ins, that was at or above 100.
Let's go ahead and select any of the data values in this column, and come up to the Format tab of the Report Layout Tools, and choose Conditional Formatting. Then we'll add a new rule, and we have the option to either check the values in the current record, or to compare to other records. Let's take a look at this first option to check the values in the current record. Down below, we're gonna edit the rule description. We have the option of either checking against the field value, or writing out an expression. We're gonna use the field value. The next box will allow us to choose what kind of range we're looking for, so we have between, not between, equal to, and so on.
I want greater than or equal to. And the value that we're looking at is the number 100. So when the field value is greater than or equal to 100, we're gonna format it based off of these formatting rules down below. I'm just gonna highlight it with a purple background, so click on the paint bucket icon, and then choose this chip here, the purple three color. Now to give us a preview of what that looks like, we can press OK, Apply, and then OK. So now we can see in our report anywhere where we had a count of Assignment ID over 100, that we get this purple color highlighting.
Next, I wanna visualize my rates here. To do that, I'm gonna come over to the Design tab, and we're gonna open up the Add Existing Fields, and I'm gonna drag out a duplicate copy of the SumOfRate column. We'll see why that is in just a moment. Let's go ahead and close out the field list. Then, I'll select any one of the numerical values in this column. We'll come up to Format, and we'll choose Conditional Formatting again. Once again, I'll press the New Rule button, and this time we're gonna compare to other records. This'll give us a data bar representation of the value within this Total column.
I'm gonna choose the option to show the bar only, which will remove the numerical representation here, which is why we added the duplicate of it. We'll have the number here in this column, and then we'll have the data bar representation in the next column. We have the option to change our bar color with this bottom drop down menu, let's keep a purple theme here. I'll go ahead and choose a purple, maybe this middle one, purple four. That'll give us a preview of what that bar will look like, and I can press OK. I'll press the Apply button, and then OK to apply that conditional formatting rule. Now I like these bars to be pretty wide, so we can see some differences between the different lengths, I'll go ahead and click here at the very end, and drag that to make it a lot wider.
Now the way these data bars fade from a color that you've chosen out to white makes them a little bit hard to see where they actually end. One way that we can mitigate this is by applying a background color to the bars themselves. So I'll come up here to the Shade and Fill, and we'll choose a nice contrasting color, maybe this purple five. Now we can see exactly where that bar ends, and we can see the relative weight that each applies to our rate values here. Finally, I wanna highlight the entire row where we have our count of Assignment IDs at or above 100, not just the individual cells themselves.
To do that, I will select one of these data values here, and I'll press and hold the Control key on my keyboard, and select the other two columns. Then we'll come up to the Conditional Formatting window, we'll apply a new rule, and this time I'm gonna format only the cells where, and I'll change it to Expression Is. This will allow us to make comparison against this other column. I need to make a reference to that column, so over here, I'm gonna type in a square bracket, and the name of the column, which is CountOfAssignmentID, and we'll finish it with a closing bracket, and here, we're gonna say greater than or equal to 100.
So we're gonna format these cells if the CountOfAssignmentID is greater than or equal to 100 for that particular row. Once again, we're gonna choose the same formatting for this particular cell, we're gonna choose, I think it was the color purple three here, and press OK. We'll apply that change, and the color looks like it's the same, so that's good, we'll press OK there. The last thing that I wanna do is remove the boxes from all of our data cells. So let's go ahead and click on this data column, and I'll Shift click all of these other ones. Then, we'll come up to Shape and Outline, and I'll choose Transparent.
That'll get rid of the rectangles around everything. Let's take a look at the results of our changes here in Print Preview mode, switch over to Design tab, and choose Print Preview from the View menu. And here is our finished report with the conditional formatting applied. This is a good point to save our report, so let's go ahead and press the Save icon, and we'll call it Monthly Summary. So whether you're looking to add impact or clarity to your reports, conditional formatting rules are easily applied to numerical fields that you'll wanna call special attention to. You can reference their value, or, using an expression, you can apply the formatting rule to cells based off of other data in the same record.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.