Highlight Values Below or Above Average in Excel in Java

This article demonstrates how to highlight the values below average or above average, and how to calculate the number of these values respectively using Spire.XLS for Java.

Sample Document

Highlight Values Below or Above Average in Excel in Java

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightBelowAboveAverage {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Use conditional formatting to highlight the values below average in the range "B2:B9"
        XlsConditionalFormats format1 = sheet.getConditionalFormats().add();
        format1.addRange(sheet.getCellRange("B2:B9"));
        IConditionalFormat cf1 = format1.addAverageCondition(AverageType.Below);
        cf1.setBackColor(Color.red);

        //Use conditional formatting to highlight the values above average in the range "B2:B9"
        XlsConditionalFormats format2 = sheet.getConditionalFormats().add();
        format2.addRange(sheet.getCellRange("B2:B9"));
        IConditionalFormat cf2 = format1.addAverageCondition(AverageType.Above);
        cf2.setBackColor(Color.yellow);

        //Get the count of values below average
        sheet.getCellRange("D13").setFormula("=COUNTIF(B2:B9,\"<\"&AVERAGE(B2:B9))");
        
        //Get the count of values above average
        sheet.getCellRange("D14").setFormula("=COUNTIF(B2:B9,\">\"&AVERAGE(B2:B9))");

        //Save the file
        workbook.saveToFile("BolowOrAboveAverage.xlsx", ExcelVersion.Version2016);
    }
}

Output

Highlight Values Below or Above Average in Excel in Java