Thursday, 19 April 2018 09:14

Highlight Below and Above Average Values in Excel in C#

Written by  support iceblue
Rate this item
(0 votes)

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);
        }
    }
}

Additional Info

  • tutorial_title:
Last modified on Thursday, 12 November 2020 01:57