C#/VB.NET: Highlight Values Above or Below Average in Excel

If you want to find values that are higher or lower than the average, you don't have to calculate the average, and then check those that are higher or lower. Using conditional formatting in Excel, you can automatically highlight those numbers. In this article, you will learn how to highlight values above average or below average with conditional formatting, by using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Highlight Values Above or Below Average in Excel

Below are the steps to highlight values above or below average in Excel using Spire.XLS for .NET.

  • Create a Workbook object.
  • Load an Excel file using Workbook.LoadFromFile() method.
  • Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
  • Add a conditional formatting to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
  • Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
  • Add an average condition using XlsConditionalFormats.AddAverageCondition() method, specify the average type to above and change the background color of the cells that meet the condition to yellow.
  • Add another average condition to change the background color of the below average values to dark gray.
  • Save the workbook to an Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using Spire.Xls.Core;
using Spire.Xls.Core.Spreadsheet.Collections;
using System.Drawing;

namespace HighlightValuesAboveAndBelowAverage
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Load an Excel file
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

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

            //Add a conditional format to the worksheet
            XlsConditionalFormats format = sheet.ConditionalFormats.Add();

            //Set the range where the conditional format will be applied
            format.AddRange(sheet.Range["F2:F14"]);

            //Add a condition to highlight the top 2 ranked values
            IConditionalFormat condition1 = format.AddAverageCondition(AverageType.Above);
            condition1.BackColor = Color.Yellow;

            //Add a condition to highlight the bottom 2 ranked values
            IConditionalFormat condition2 = format.AddAverageCondition(AverageType.Below);
            condition2.BackColor = Color.DarkGray;

            //Get the count of values below average
            sheet.Range["F17"].Formula = "=COUNTIF(F2:F14,\"<\"&AVERAGE(F2:F14))";

            //Get the count of values above average
            sheet.Range["F18"].Formula = "=COUNTIF(F2:F14,\">\"&AVERAGE(F2:F14))";

            //Save the workbook to an Excel file
            workbook.SaveToFile("HighlightValues.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Highlight Values Above or Below Average in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.