Highlight Below and Above Average Values in Excel in C#

This article demonstrates the steps to highlight below and above average values with conditional formatting in Excel using Spire.XLS.

Below screenshot shows the sample file we used for demonstration:

Highlight Below and Above Average Values in Excel in C#

Detail steps:

Step 1: Instantiate a Workbook object and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Highlight cells below average values with SkyBlue color.

//Add conditional format
XlsConditionalFormats format1 = sheet.ConditionalFormats.Add();
//Set the cell range to apply the formatting
format1.AddRange(sheet.Range["B2:B8"]);
//Add below average condition
IConditionalFormat cf1 = format1.AddAverageCondition(AverageType.Below);
//Highlight cells below average values
cf1.BackColor = Color.SkyBlue;

Step 4: Highlight cells above average values with Orange color.

//Add conditional format
XlsConditionalFormats format2 = sheet.ConditionalFormats.Add();
//Set the cell range to apply the formatting
format2.AddRange(sheet.Range["B2:B8"]);
//Add above average condition
IConditionalFormat cf2 = format1.AddAverageCondition(AverageType.Above);
//Highlight cells above average values
cf2.BackColor = Color.Orange;

Step 5: Save the file.

workbook.SaveToFile("AverageCondition.xlsx", ExcelVersion.Version2013);

Screenshot:

Highlight Below and Above Average Values in Excel in C#

Full code:

using System.Drawing;
using Spire.Xls;
using Spire.Xls.Core;
using Spire.Xls.Core.Spreadsheet.Collections;

namespace Average_Condition
{
    class Program
    {
        static void Main(string[] args)
        {
            //Instantiate a Workbook object
            Workbook workbook = new Workbook();
            //Load Excel file
            workbook.LoadFromFile("Input.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Add conditional format
            XlsConditionalFormats format1 = sheet.ConditionalFormats.Add();
            //Set the cell range to apply the formatting
            format1.AddRange(sheet.Range["B2:B8"]);
            //Add below average condition
            IConditionalFormat cf1 = format1.AddAverageCondition(AverageType.Below);
            //Highlight cells below average values
            cf1.BackColor = Color.SkyBlue;

            //Add conditional format
            XlsConditionalFormats format2 = sheet.ConditionalFormats.Add();
            //Set the cell range to apply the formatting
            format2.AddRange(sheet.Range["B2:B8"]);
            //Add above average condition
            IConditionalFormat cf2 = format1.AddAverageCondition(AverageType.Above);
            //Highlight cells above average values
            cf2.BackColor = Color.Orange;
            
            //Save the file
            workbook.SaveToFile("AverageCondition.xlsx", ExcelVersion.Version2013);
        }
    }
}