How to: Format Cell Values that are Above or Below the Average
- 3 min to read
This example demonstrates how to apply an "above or below average" conditional formatting rule to a range of cells.
To create a new conditional formatting rule represented by the AverageConditionalFormatting object, access the collection of conditional formats from the Worksheet.ConditionalFormattings property and call the ConditionalFormattingCollection.AddAverageConditionalFormatting method. Pass the following parameters:
- A Range object that defines a range of cells to which the rule is applied.
- A condition specified by one of the ConditionalFormattingAverageCondition enumeration values.
If you use the overloaded method with three parameters, pass the third parameter:
- An integer value representing a number of standard deviations to be included above or below the mean in a range of cells.
- Specify formatting options to be applied to cells if the condition is true using the ISupportsFormatting.Formatting property of the AverageConditionalFormatting object. Set the background color and specify the font attributes.
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-apply-conditional-formatting-to-a-range-of-cells-e4959
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings; // Create the rule highlighting values that are above the average in cells C2 through C15. AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$C$2:$C$15"], ConditionalFormattingAverageCondition.AboveOrEqual); // Specify formatting options to be applied to cells if the condition is true. // Set the background color to yellow. cfRule1.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA); // Set the font color to red. cfRule1.Formatting.Font.Color = Color.Red; // Create the rule highlighting values that are one standard deviation below the mean in cells D2 through D15. AverageConditionalFormatting cfRule2 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$D$2:$D$15"], ConditionalFormattingAverageCondition.BelowOrEqual, 1); // Specify formatting options to be applied to cells if the conditions is true. // Set the background color to light-green. cfRule2.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0x9F, 0xFB, 0x69); // Set the font color to blue-violet. cfRule2.Formatting.Font.Color = Color.BlueViolet;
The image below shows the result (the workbook is opened in Microsoft® Excel®). Cost values above the average in the first quarter are highlighted in yellow with a red font color and cost values one standard deviation below the average in the second quarter are highlighted in light-green with a blue-violet font color.