How to Use Conditional Formatting to Highlight Cells in Java
Tags: java, excel, conditionalformatting
Published: 2020-04-24 09:13:08

---
title: How to Use Conditional Formatting to Highlight Cells in Java
published: true
description: This article shows you how to highlight the depulicate values, top 3 values, or values below average in Excel in Java
tags: java,excel,conditional formatting
---
When we process data in Excel, we may need to highlight specific information, such as the top 5 values, the values below average, or the duplicate values, etc. In this case, we can use conditional formatting to easily filter out these special values and highlight the cells or rows where they are.

The following sections will show you how to programmatically apply conditional formatting in Excel using Free Spire.XLS for Java through there examples.

##Source document
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/qhfls3mwer0oo410qg4v.png)

##Add Spire.Xls.jar as dependency 

Method 1: Download [Free Spire.XLS for Java](https://www.e-iceblue.com/Download/xls-for-java-free.html) pack, unzip it and you’ll get Spire.Xls.jar file from the “lib” folder. Import the jar file in your project as a dependency.

Method 2:  If you are creating a Maven project, you can easily add the jar dependency by adding the following configurations to the pom.xml.
```java
<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue </groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>
```
##Highlight values below or above average
```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:B13"));
        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:B13"));
        IConditionalFormat cf2 = format1.addAverageCondition(AverageType.Above);
        cf2.setBackColor(Color.yellow);

        //Save the file 
        workbook.saveToFile("BolowOrAboveAverage.xlsx", ExcelVersion.Version2016);
    }
}
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/mkgis8a0op3oh79vsbua.png)

##Hightlight top 3 or bottom 3 values
```java
import com.spire.xls.*;

import java.awt.*;

public class HighlightTopBottom {

    public static void main(String[] args) {

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

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

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

        //Apply conditional formatting to range "B2:B13" to highlight the top 3 values
        ConditionalFormatWrapper format1 = sheet.getCellRange("B2:B13").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.TopBottom);
        format1.getTopBottom().setType(TopBottomType.Top);
        format1.getTopBottom().setRank(3);
        format1.setBackColor(Color.red);

        //Apply conditional formatting to range "B2:B13" to highlight the bottom 3 values
        ConditionalFormatWrapper format2 = sheet.getCellRange("B2:B13").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.TopBottom);
        format2.getTopBottom().setType(TopBottomType.Bottom);
        format2.getTopBottom().setRank(3);
        format2.setBackColor(Color.yellow);

        //Save the document 
        workbook.saveToFile("TopOrBottomValues.xlsx", ExcelVersion.Version2016);
    }
}
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/sex1spin9gap46jmve2r.png)
 
##Highlight duplicate or unique values
```java
import com.spire.xls.*;

import java.awt.*;

public class HighlightDuplicated {
    public static void main(String[] args) {

        //Create a Workbook instance 
        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 duplicated values in the range "A2:A11" with red
        ConditionalFormatWrapper format1 = sheet.getCellRange("B2:B13").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.DuplicateValues);
        format1.setBackColor(Color.red);

        //Use conditional formatting to highlight unique values in the range "A2:A11" with yellow
        ConditionalFormatWrapper format2 = sheet.getCellRange("B2:B13").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.UniqueValues);
        format2.setBackColor(Color.yellow);

        //Save the document
        workbook.saveToFile("DuplicateOrUniqueValues.xlsx", ExcelVersion.Version2016);
    }
}
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ve63dguy0e2m6gmocbpu.png)