Basic Conditional Formatting in Excel & Access
Conditional formatting allows one to visually highlight or visually emphasize certain values in one’s dataset, based on the fulfilment of a certain criteria. It can be time consuming to go through large data sets and select specific values of interest. However, when using conditional formatting, one can evaluate large data sets and quickly see the values of interest highlighted through the visual format without having to manually go through the data set yourself.
There are many default preset options available with Excel’s conditional formatting feature, and one can also define one’s own rules for conditional formatting using a formula and one’s own custom formatting. The default presets include Data Bars, Color Scales and Icon Sets. You can also apply a single rule or multiple rules to the same set of cells. One can also using the Rule Manager, further define rules , edit rules and stop a rule if true. You can also use Conditional Formatting to detect unique or duplicate values.
Both Excel and Access have conditional formatting options. If you are familiar with conditional formatting in Excel, it should not be difficult to understand how to use conditional formatting in Access. While the conditional formatting option in Access is not as extensive in terms of the preset options available in Excel, it is still a useful tool to use, especially when analysing reports and forms that contain thousands of records. You can also use calculations in conditional formatting in Access through the expression builder. You can also evaluate multiple criteria in Access.
So, let’s get started with a simple example to illustrate how to use conditional formatting in Excel and Access.
Table of Contents
We have a source workbook with one worksheet, their unique EmployeeID of the Salespeople at hand, the names of Salespeople, the amounts of sales they generated for the hypothetical company at hand, an empty column for now called Sales Visual Only and the rating they received from customers who filled in a survey form about the quality of service they received from the salesperson at hand. One reflects a poor rating and ten reflects an excellent rating. The first main indicator of performance is of course, the amount of sales the salesperson generated for the company. The rating received from clients is the second indicator and measures the brand ambassadorship of the salesperson at hand and their customer service. This indicator can also be used as a mitigating factor, if the salesperson at hand received poor sales but a relatively high rating, the company would look at evaluating other reasons for the poor sales and quantify other variables such as not enough marketing of the product on traditional and social media channels in the area.
We would like to use conditional formatting to highlight some of the data in the range based on certain criteria.
The source data range is shown below.
Simple Conditional Formatting Using Data bars
1) The first thing we would like to do, is see all the sales in a visually comparative manner and for this type of analysis we can use data bars, which are part of the preset options that Excel provides. Data bars are horizontal bars that are added to each cell based on its value in comparison to the other cells in the range, that resembles a mini bar chart.
2) So, we want to add data bars to the Sales column. In order to do this we select the cells in the Sales column containing the Sales values. The range C5:C34 in other words, as shown below.
3) Go to Home>Styles>Conditional Formatting>Data Bars> and select one of the Gradient Fills or the Solid Fill for your data bars. In this case, we chose the blue Gradient Fill.
4) Sometimes the data bars, overlapping the actual numbers can be a bit distracting, so one can also view the data bars based on the source range on their own. This technique is often utilized when creating dashboards.
5) In order to do this in the column next to Sales, called Sales Visual Only, in cell D5 enter the following simple formula:
6) Since we used relative referencing we can drag the formula down the rows in the column as shown below.
7) Select the range D5: D34 and then go to Home>Styles>Conditional Formatting>Data Bars>More Rules.
8) In the New Formatting Rule Dialog Box, check the Show Bar Only as shown below. We left the default option of Solid Fill and the color as blue, however, we could have changed these options according to specific preferences or added a border and click Ok.
9) The range in column D should now only contain data bars based on the source values originally in Column C.
Simple Conditional Formatting Using New Rule
1) We now want to conditionally format the Average Rating Received From Clients column and highlight only cells visually that contain a rating greater than 5, using conditional formatting.
2) So, the first thing we do is select the range in E5: E34.
3) Go to Home>Styles>Conditional Formatting>New Rule.
4) In the New Formatting Rule Dialog Box, under Select Rule Type: section choose Format only cells that contain and in the Edit the Rule Description section, instead of between, choose greater than and put the number 5 in the last box as shown below.
5) Then select Format… in order to set a specific format. In the Format Cells Dialog Box, select the Fill tab and choose a yellow fill as shown below.
6) Click Ok and then Ok again and now all the cells that contain a rating greater than 5 should be filled with a yellow fill as shown below.
7) In order to clear the conditional formatting applied from the entire sheet, go to Home>Styles>Conditional Formatting and select Clear Rules>Clear Rules From Entire Sheet, one can also choose the first option if one selects a particular range containing conditional formatting and then Clear Rules from Selected Cells would apply.
Crossover Access Conditional Formatting Tip
1) We have a table containing the same data as in the Excel sheet, in Access as shown below, except the Sales Visual Only Column has been left out.
2) One can apply conditional formatting to Access forms or reports, so we will create a simple form based on the table in order to showcase the conditional formatting options.
3) With the table called SalesPeopleT selected, go to Create> Forms>Form as shown below.
4) A Form based on the Table is created as shown below with all the default options Access provides. This form is shown in Layout View.
5) Press Ctrl-S on your keyboard in order to be prompted to give the form a name, name the form SalesPeopleF and thus the form is saved and click Ok.
6) The newly created saved form should now appear in the Navigation Pane.
7) Now with the form selected and opened go to Home>Views>View, and Select Design View. When using Design View with either tables, forms or reports, you can completely specify all the options you’d like for your table, form or report. Most professional database administrators use Design View to customize and edit their tables, forms, and reports.
8) While in Design View, change the heading of the form to Data Entry Form as shown below, by simply clicking on the control and typing.
9) Now go to Design>Tools and Select Property Sheet.
10) Using the Property Sheet Window, select Form from the drop-down options.
11) Using the Property Sheet change the Default View of the Form from Single Form to Split Form. Change the Allow Datasheet View from No to Yes. For the Split Form Orientation change it from Datasheet on Top to Datasheet on Bottom. Split Forms allow one to the form in two views at once, datasheet and single record form view.
12) Close the Property Sheet and then click on the text box containing the Average Rating Received From Clients as shown below.
13) Then go to Format >Control Formatting> Conditional Formatting.
14) The Conditional Formatting Rules Manager Dialog Box should appear and since the control is selected, the Show Formatting Rules for: should pick up the field of interest, which in this case is Average Rating Received from Clients.
15) Select New Rule.
16) For the Select a rule type: make sure Check values in the current record or use an expression is selected. In the Format only cells where the: section leave the Field Value is option as is, change between to greater than, and enter 5 in the third box. Set the fill to yellow using the fill option as shown below.
17) Click Ok and then Apply and then Ok again.
18) In order to see the effect of the conditional formatting, go to Home>Views>View and select Form View as shown below.
19) Once back in Form View, you will see the effect of applying the conditional formatting, as all the values in Average Rating Received From Clients that are greater than 5 are highlighted with the yellow fill. You can also scroll through the values in datasheet view at the bottom quite easily and also see the conditional formatting applied.
20) Remember to save your work and there you have it.
Download Working File
Conditional Formatting, in both Excel and Access, saves time when one wants to highlight values of interest visually, without having to manually go through a data set. Excel provides many different conditional formatting options and while the options in Access are not as extensive it is still a worthwhile feature to add to one’s forms and reports.
Please feel to comment and tell us if you use Conditional formatting in your Excel spreadsheets or in conjunction with your Access Reports and Forms.
Review Section: Test your Understanding
1) What is the purpose of Conditional Formatting?
2) Are Access conditional formatting options more extensive or less extensive than Excels?
3) Give one example of a preset conditional formatting option in Excel.
4) Setup a sample fruits range in Excel with two columns, FruitID and Fruit Name and highlight using conditional formatting the text Apples.
5) Setup a table in Access called fruits, with two columns namely FruitID and Fruit Name, create a Report based on this table and then highlight using conditional formatting, Apples.