Display comparative data visually with data bars

When you need to tell the story of your data at a glance, use data bars. You can apply data bars to numeric fields to visually contrast data among records. For example, in the following report, you can quickly see how marketing for some training courses were more effective by the length of the data bars in the Outcome field. The data in the Outcome field displays a comparison of the projected course registration data with the attendance data after the marketing efforts.

Data bars in a report showing data comparisons.

This article provides an overview of this new conditional formatting option in Microsoft Access 2010, and explains how you can apply them to reports and forms in a client database.

 Note    This feature is not available in web databases.

In this article


Overview

You can apply data bars to controls in forms or reports when the control is bound to a field that is of the numeric data type. The usefulness of data bars in Access is much like the data bars in Microsoft Excel charts that make it visually easier to compare values. The length of a data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. In forms, data bars are most useful in datasheet view or continuous form view.

Applying data bars is easy with the Conditional Formatting Rules Manager. You can customize data bars that specify conditions for the format and preview the effects before you apply them. For example, you can customize the bar values to represent the highest or lowest values, a number, or percent range.

formatting settings for data bars

The following table explains the settings for the lower visual limit or the Shortest Bar options:

   
Option Result
Lowest Value The default setting. Sets the visual limit for the lowest value.
Number Sets the lower bound of the linear scale. All values that are less than or equal to the shortest bar number are length 0.
Percent Displays like the number setting but limits are placed on a lower bound’s percentage for each value as compared with the rest of the records in the record set.

The following table explains the settings for the highest visual limit or the Longest Bar options:

   
Option Result
Highest Value The default setting. Sets the visual limit for the highest value.

Number

Sets the upper bound of the linear scale for the gradient. All items greater than or equal to longest bar number completely fill the control.
Percent Looks much like the number setting except the limits are placed on the upper bound’s percentage for each value as compared with the rest of the records in the record set.

Top of Page Top of Page

Apply data bars to a report

When you want to show a snapshot of trends or data comparison for a large amount of data, such as top selling and bottom selling toys in a holiday sales report formatting your report with data bars can be a good option. For example in a sales report, it would be easier to understand the sales trend for a specific region when you use data bars.

To apply data bars to your report, complete the following steps:

  1. Right-click the report in the Navigation Pane and then click Layout view.
  2. Select the control to which you want to apply the data bars.
  3. On the Format tab, in the Conditional Formatting group, click Conditional Formatting.
  4. In the Conditional Formatting Rules Manager dialog box click New Rule.
  5. In the New Formatting Rule dialog box, under Select a rule type, select Compare to other records.
  6. Under Edit the rule description, specify the rule that will determine when and what data bar format should be applied.

the new formatting rule dialog box

  1. To display the data bars without the numeric values of the field, select the Show Bar only option.

The following illustrations show instances of displaying only the data bars and data bars with the corresponding values.

apply data bars without the numeric value being displayed

data bars displayed with data

 Note    The data bars for a value temporarily disappears when that control goes into edit mode.

  1. Select options for Shortest Bar and Longest Bar options, and then click the Bar color arrow to select a standard or custom bar color.

 Note    Preview displays a gradient view of the bar color.

  1. Click OK twice to close the dialog boxes.
  2. To change the font for the value displayed, complete the following steps:
  1. Select the cell and press Alt + Enter if the property sheet is not already open.
  2. Select the Format tab, click the arrow in Font Name property, and then select a font.
  1. Switch to the Report View.

Top of Page Top of Page

Apply data bars to a form

Applying data bars to values in a form is more useful in a continuous form, split form, or datasheet view. For example to in an Orders form, it would be easier to understand the sales trend for a specific region when you use data bars.

 Note    Data bars are not available in a web database.

displays data bars in a split form view

To apply data bars to a form, complete the following steps:

  1. Right-click the form in the Navigation Pane and then click Layout view.
  2. Select the cell where you want to apply the data bars.
  3. On the Format tab, in the Conditional Formatting group, click Conditional Formatting.
  4. In the Conditional Formatting Rules Manager dialog box click New Rule.
  5. In the New Formatting Rule dialog box, under Select a rule type, select Compare to other records,
  6. Under Edit the rule description, specify the rule that will determine when and what formatting should be applied.

 Note    If you select Show bar only, the field values will not display.

  1. Select a Select a Type and Value for the Shortest Bar and the Longest Bar.
  2. Click the Bar color arrow to select a standard or custom bar color.
  3. Click OK twice and switch to Form View.

Top of Page Top of Page

Additional information

A tip for formatting data bars and some resources on applying other conditional formatting and displaying data visually in Access 2010:

  • To create space between data bars   : Open the form or report in Layout view, select the data bar control, and then apply required Control Padding option.

Using the padding option to create space between controls

Top of Page Top of Page

 
 
Applies to:
Access 2010