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