Video: Highlight data on forms by using conditional formatting

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

One of the best things about Access desktop databases is the ability to store large amounts of data and keep it organized. But sometimes you wind up with a screen full of numbers or text and it can be hard to pick out what’s really important. That’s where conditional formatting can help. You can set rules for each field on a form, and automatically highlight values according to those rules.

Note:  Conditional formatting is not compatible with the Publish to Access Services feature in Access 2010, so the command is not available on the Format tab when you’re working on a web-compatible form. Conditional formatting is also not available with Access web apps. However, for standard client forms, conditional formatting can help data stand out according to rules you set.

In the video

Format fields based on the values they contain (or on the results of an expression)

Compare values across records by using data bars

Format fields based on the values they contain (or on the results of an expression)

  1. In the Navigation Pane, right-click the form that you want to change, and then click Layout View.

  2. Select the column or field to which you want to apply conditional formatting. To apply the same formatting rules to multiple columns or fields, hold down the CTRL key and click each one until they are all selected.

  3. On the Format tab, click Conditional Formatting.

  4. In the Conditional Formatting Rules Manager dialog box, click New Rule.

  5. Enter the criteria for your rule.

    • To format the field(s) based on the values they contain, select Field Value Is from the first list, and then select or enter the criteria you want.

    • To format a field based on a calculation, select Expression Is from the first list, and then type the expression in the box. For more information about expressions, see the article Introduction to expressions.

    • To apply formatting to the selected field only when the field has the focus, select Field Has Focus from the first list.

  6. Select the formatting that you want to apply. You can set the following styles:

    • Bold

    • Italic

    • Underline

    • Background color

    • Font color

    • Enable (this option disables the field so that it cannot be edited, and overrides the Background color and Font color settings).

    Access displays a preview of the formatting as you select options.

  7. Click OK.

  8. To add a new rule to the same field or fields, click New Rule and repeat this procedure from step 5.

    Note:  You can add up to 50 rules per field or group of fields. The rules are evaluated from top to bottom. Once the criteria for a rule have been met, Access applies the formatting for that rule and does not evaluate the rules below it.

  9. When you are finished adding rules, click OK in the Conditional Formatting Rules Manager dialog box.

Top of Page

Compare values across records by using data bars

  1. In the Navigation Pane, right-click the form that you want to change, and then click Layout View.

  2. Select the column or field to which you want to apply conditional formatting. To apply the same formatting rules to multiple columns or fields, hold down the CTRL key and click each one until they are all selected.

  3. On the Format tab, click Conditional Formatting.

  4. In the Conditional Formatting Rules Manager dialog box, click New Rule.

  5. Under Select a rule type, select Compare to other records.

  6. Under Edit the rule description, select the options you want, and then click OK.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×