Applying conditional formatting rules in Microsoft Access can help you highlight values in your report if the rule or rules are met. For example, if you would like to apply red font to all negative numbers in your data, conditional formatting allows you to do that. Watch this video to learn how conditional formatting rules work, how to set them, and how to use the compare records function to give you a better understanding of what conditional formatting to apply.
- View Offline
Conditional formatting rules will call attention to specific values that you would like to highlight in your report. Let's create a new report to analyze the No Obstacles company's customer spend levels. I have created a new query here, called Customer Spend and if I double-click on it to run it, we'll see those records. We have go the FirstName and LastName of our customers, the Region and State that they are from and then overall spend level that they have contributed to the company's bottom line. By switching to the Design View we can see how that was constructed. We have got four tables; Customers, Orders, States and Products.
This is a totals query and down here this is how I am calculating the TotalSpend. I am taking the price of each product, multiplying it by the quantity that each person has ordered and then I am adding that up across all of the orders for each customer. Let's close this out and we'll create a report based off of this data. I'll go up to the CREATE tab and press the Report button, make sure that the query is still highlighted in the Navigation pane. Press Report to get a basic report. Now I am going to go ahead and adjust these field widths to shrink everything down on to one page. I'll make sure that this FirstName is highlighted and I'll reduce its Width and we'll do the same thing with some of these others, LastName, Region and State.
Next, I am going to add an additional TotalSpend column here to the right side and I'll show you why I am going to do this in just a moment. But for now press, Add Existing Fields, that will show us all the fields from our query and I'll take total spend and I'll double-click on it again, to add a second duplicate column here. Now I can close my field list. Now let's say we want to highlight our top customers. I am going to click on this first TotalSpend column here and go up to the FORMAT tab and choose Conditional Formatting. Here we can create a new rule for how these values will be formatted. I am going to say New Rule, I am going to choose the Field Value is, here in the next box, we can choose how want to specify the value.
We can specify a range between or not between. We can say equal to a specific value or not equal to a specific value. I can choose more than or less than a value or I can say greater than or equal to or less than or equal to. I am going to say greater than or equal to and in the next box over, I'll type 10,000. This will highlight all of our customers that have spent more than $10,000 with the company. We can adjust the formatting down here. I am going to change it to a bold font and I'll change the background color to this green color right here, Green 2.
We'll get a preview of what that looks like over here. Go ahead and say OK and press Apply. And back here in our report, you will see that some of our values got highlighted. Let's go ahead and say OK. Now let's create a second conditional format for this second column. I'll click over here in this TotalSpend value, go up to Conditional Formatting and I'll say New Rule. This time I am going to choose Compare to other records. This will create a data bar whose width is determined by their overall spent. I am going to choose Show Bar only, that will remove the number from this field.
I can choose a Bar color down here. I am going to choose this dark green color here. Here we'll determine how our lowest values and our highest values will be determined. We can either use the absolute values that appear in our data or we can specify our own low value and high value or low percent and high percent. I am going to leave it set to Lowest value and Highest value. Let's go ahead and say OK and then Apply, then we'll press OK to close the Conditional Formatting Rules Manager. You can see the data bars appear here in the background.
In Access, the Data bars fade from the color we selected out to white. I find that this makes them a little bit difficult to read, but if we add a color in the background, for instance I'll go up to Shape Fill and choose this Dark Gray color right here, we get a better distinction of where that data bar ends and I can scroll through my values and see who's contributed the most and who's contributed the least. Now let's do one more conditional formatting. Here we have highlighted the cell where our customer spent more than $10,000. What if we wanted to highlight the entire row of that customer? Well, we can do that too.
Let's go ahead and select these rows here, I'll start with the State, then I'll Shift+click on one of the customers' names. That will select all four columns. I'll go back up to Conditional Formatting. I'll say New Rule, and this time instead of choosing Field Value is here, I am going to choose Expression is. That will allow me to type in a formula. For the formula I am going to make reference to the original field which is TotalSpend. I'll do that with a square bracket, type TotalSpend with no space, closing square bracket and then we'll put in the same value, greater than 10,000.
I'll set my conditional formatting to the same bold and the same green color. We'll say OK and then Apply and you will see that the entire row now gets the same formatting all the way across. Now let's get rid of those lines. I'll select all the cells here, go to Shape Outline and say Transparent and now we can scroll back up to the top, switch to the HOME tab and we'll take a look at it in Print Preview mode. Now we have got some conditional formatting rules that have been applied to our data.
Whether you're looking to add impact or clarity, conditional formatting rules are easily applied to numerical data fields that you want to call special attention to. You can reference their value or using the expression, you can apply the formatting rule to cells based off of other data cells in the same record.
- Understanding table structures and relationships
- Setting primary and foreign keys
- Establishing relationships and maintaining referential integrity
- Sorting and filtering data
- Building queries with constraints and criteria
- Editing table data with queries
- Generating forms from tables
- Adding form controls
- Creating reports with totals and labels
- Embedding macros in buttons
- Repairing your database
- Protecting databases with passwords