Passer au contenu principal

# Add Conditional Formatting to a Line Chart

Explains how to create three conditional formatting rules on a chart with many data series. One rule will highlight above expectations, a second will highlight below expectations, and a third will show a neutral zone for "as planned."

This walkthrough shows you how to create conditional formatting rules on a line chart, where:

1 Total Revenue data series is conditionally formatted to highlight above expectations

2 Product Revenue data series is conditionally formatted to highlight below expectations

3 Service Revenue data series is conditionally formatted to highlight neutrality, "as planned"

This example assumes that a line chart was created using the default version Working Budget and the following two accounts and one calculation:

• Product Revenue (general ledger account)
• Service Revenue (general ledger account)
• Total Revenue (calculation that sums Product Revenue and Service Revenue)

Review Conditional Formatting before starting this walkthrough.

## Create a Rule for Above Expectations

In this step, you will create a conditional formatting rule on the Total Revenue data series to highlight revenue that goes above plan. After completing these steps, you will have a conditional formatting rule and a line chart that looks like:

You can replace the hard coded value with an expression that references a data series in the chart. For example:` [Total Revenue] >= [Total Revenue] * 1.03`, where the right-side expression is a growth rate. You must reference a data series that is in the chart.

#### Build the First Rule

1. In edit mode, navigate to the line chart.
2. Click in the right panel to open the Appearance Settings tab. Expand the Conditional Formatting section.
3. Click Add rule. The Conditional Formatting dialog displays.
4. Define the rule, Click  for syntax rules and list of operators and functions.
1. Provide a description: Above Plan
2. Build the expression: `[Total Revenue] >= 2200000`
3. Pick the highlight color to format data points when the condition is true.
4. Choose the series: Total Revenue
5. Click Save.

## Create a Rule for Below Expectations

In this step, you will create a second conditional formatting rule on the 4100 Product Revenue data series that highlights revenue that goes below plan.You will shorten the data series name that displays as a data label on chart and that is referenced in the rule.

After completing these steps, you will have a conditional formatting rule and line chart that looks like:

#### Build  a Second Rule

1. Click Add rule. The Conditional Formatting dialog displays.
2. Define the rule:
1. Provide a description: Below Plan
2. Build the expression:` [4100 Product Revenue] < 1500000`
3. Pick a highlight color.
4. Choose the data series: 4100 Product Revenue
3. Click Save.

#### Change the Data Series Name

The data series name for product revenue is too long and makes the chart look cluttered with data labels. You can shorten the name of the data series referenced in a conditional formatting rule – without breaking the rule. When you change the name in the data series, the name changes in the rule too.

1. Navigate to the Accounts section in the Data Settings tab.
2. Expand the data series: 4100 Product Revenue:
3. Change the name: Product

The name changes on the chart data label and the conditional formatting rule now references the renamed data series.

## Create a Rule for an As Planned Zone

You can create a conditional formatting rule that evaluates data that falls within a range of values.You will create a rule that will highlight data points that are tracking "as planned."

After completing these steps, you will have a conditional formatting rule and a line chart that looks like:

Do the following:

1. Click Add rule. The Conditional Formatting dialog displays.
2. Define the rule:
1. Provide a description: As Planned
2. Build the expression:` [Services] BETWEEN 825000 AND 900000`
3. Pick a highlight color.
4. Choose the series: Services
3. Click Save.

This completes this walkthrough.

• Cet article vous a été utile ?