How to: Format Cells Using Data Bars

• Jun 07, 2019
• 4 minutes to read

This example demonstrates how to apply a data bar conditional formatting rule.

1. First of all, specify the minimum and maximum thresholds corresponding to the shortest and longest bars, respectively. To do this, use the ConditionalFormattingCollection.CreateValue method, which creates an instance of the ConditionalFormattingValue object. This object provides access to threshold values and their types. The type of the threshold value is determined by one of the ConditionalFormattingValueType enumeration values and can be a number, percent, formula, or percentile. To automatically set the minimum (maximum) threshold to the lowest (highest) value in a range to which the rule will be applied, use the ConditionalFormattingCollection.CreateValue method with the ConditionalFormattingValueType.MinMax enumeration value passed as a parameter. To compare values in a range of cells based on their distance from zero, use the ConditionalFormattingCollection.CreateValue method with the ConditionalFormattingValueType.Auto parameter.
2. To apply a conditional formatting rule represented by the DataBarConditionalFormatting object, access the collection of conditional formats from the Worksheet.ConditionalFormattings property and call the ConditionalFormattingCollection.AddDataBarConditionalFormatting method with the following parameters: the range of cells to which the rule is applied, the minimum and maximum thresholds, and the bar color.
3. Modify the behavior of bars: set the border color using the DataBarConditionalFormatting.BorderColor property and specify whether to use the gradient fill type by setting the DataBarConditionalFormatting.GradientFill property. If this property is set to false, the solid fill type is applied. To change the direction of bars, use the DataBarConditionalFormatting.Direction property.
4. If cells contain negative values, provide settings for the negative bar. Set its fill color from the DataBarConditionalFormatting.NegativeBarColor property and specify the border line color using the DataBarConditionalFormatting.NegativeBarBorderColor property.
5. Provide settings for the axis separating positive and negative bars. Specify the axis position using the DataBarConditionalFormatting.AxisPosition property. Set the axis color from the DataBarConditionalFormatting.AxisColor property.
6. Specify whether to show or hide values of the cells where the rule is applied by setting the DataBarConditionalFormatting.ShowValue property.

To remove the DataBarConditionalFormatting object, use the ConditionalFormattingCollection.Remove, ConditionalFormattingCollection.RemoveAt or ConditionalFormattingCollection.Clear methods.

View Example

ConditionalFormattingAction.vb

Dim conditionalFormattings As ConditionalFormattingCollection = worksheet.ConditionalFormattings
' Set the value corresponding to the shortest bar to the lowest value.
Dim lowBound1 As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax)
' Set the value corresponding to the longest bar to the highest value.
Dim highBound1 As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax)
' Create the rule to compare values in cells E2 through E15 using data bars. 
Dim cfRule1 As DataBarConditionalFormatting = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range("$E$2:$E$15"), lowBound1, highBound1, DXColor.Green)
' Set the positive bar border color to green.
cfRule1.BorderColor = DXColor.Green
' Set the negative bar color to red.
cfRule1.NegativeBarColor = DXColor.Red
' Set the negative bar border color to red.
cfRule1.NegativeBarBorderColor = DXColor.Red
' Set the axis position to display the axis in the middle of the cell.
cfRule1.AxisPosition = ConditionalFormattingDataBarAxisPosition.Middle
' Set the axis color to dark blue.
cfRule1.AxisColor = Color.DarkBlue

' Set the value corresponding to the shortest bar to 0 percent.
Dim lowBound2 As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "0")
' Set the value corresponding to the longest bar to 100 percent.
Dim highBound2 As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "100")
' Create the rule to compare values in cells G2 through G15 using data bars.  
Dim cfRule2 As DataBarConditionalFormatting = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range("$G$2:$G$15"), lowBound2, highBound2, DXColor.SkyBlue)
' Set the data bar border color to sky blue.
cfRule2.BorderColor = DXColor.SkyBlue
' Specify the solid fill type.
cfRule2.GradientFill = False
' Hide values of cells to which the rule is applied.
cfRule2.ShowValue = False

ConditionalFormatting.cs

ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the value corresponding to the shortest bar to the lowest value.
ConditionalFormattingValue lowBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Set the value corresponding to the longest bar to the highest value.
ConditionalFormattingValue highBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Create the rule to compare values in cells E2 through E15 using data bars. 
DataBarConditionalFormatting cfRule1 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$E$2:$E$15"], lowBound1, highBound1, DXColor.Green);
// Set the positive bar border color to green.
cfRule1.BorderColor = DXColor.Green;
// Set the negative bar color to red.
cfRule1.NegativeBarColor = DXColor.Red;
// Set the negative bar border color to red.
cfRule1.NegativeBarBorderColor = DXColor.Red;
// Set the axis position to display the axis in the middle of the cell.
cfRule1.AxisPosition = ConditionalFormattingDataBarAxisPosition.Middle;
// Set the axis color to dark blue.
cfRule1.AxisColor = Color.DarkBlue;

// Set the value corresponding to the shortest bar to 0 percent.
ConditionalFormattingValue lowBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "0");
// Set the value corresponding to the longest bar to 100 percent.
ConditionalFormattingValue highBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "100");
// Create the rule to compare values in cells G2 through G15 using data bars.  
DataBarConditionalFormatting cfRule2 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$G$2:$G$15"], lowBound2, highBound2, DXColor.SkyBlue);
// Set the data bar border color to sky blue.
cfRule2.BorderColor = DXColor.SkyBlue;
// Specify the solid fill type.
cfRule2.GradientFill = false;
// Hide values of cells to which the rule is applied.
cfRule2.ShowValue = false;