Formatting Issues

How to Highlight Duplicate Excel Values Using Checkboxes

WPS Editor Sep 27, 2026 869 views

Question details

The user wants to apply conditional formatting to highlight duplicate values across lists, but only trigger the highlighting dynamically when a linked checkbox is selected.

How to Highlight Duplicate Excel Values Using Checkboxes

Product: Excel
Device & OS: not provided
Scenario: Comparing a regularly refreshed data table with an expanding list (like a training-completion tracker) using interactive checkboxes to toggle the highlights.
Observed behavior: The goal is to set up a conditional formatting rule that dynamically checks for duplicates based on both the checkbox status (TRUE/FALSE) and the presence of the value in the comparison range.

Before you start

Before setting up the formatting rules, ensure the Developer tab is enabled to insert form control checkboxes. You must also link each checkbox to a specific cell so it outputs a TRUE or FALSE value that your formatting formula can reference.

Solution 1Recommended

Use Conditional Formatting with AND and COUNTIF Functions

Create a custom conditional formatting rule that verifies the linked checkbox cell is TRUE and checks for duplicates using the COUNTIF function.

By combining the AND function with the COUNTIF function, you can instruct Excel to format cells only if two conditions are met: the checkbox is ticked, and the value appears in your comparison list.

Link your checkbox to a cell

Right-click the inserted checkbox, select 'Format Control', go to the 'Control' tab, and set the 'Cell link' to an empty cell (e.g., $N$3). This cell will now show TRUE when the box is checked and FALSE when unchecked.

Select the data range to format

Highlight the column or range of cells where you want the duplicate highlighting to appear (for example, starting from K12 downwards).

Create a new formatting rule

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Apply the custom formula

Choose 'Use a formula to determine which cells to format'. Enter a formula like =AND($N$3=TRUE,COUNTIF($A$3:$A$100,K12)>0). Ensure the checkbox reference ($N$3) and lookup range ($A$3:$A$100) are absolute, while the first cell of your selected target range (K12) is relative.

Set the highlight format

Click the 'Format' button, choose your preferred fill color for the duplicates, and click 'OK' twice to apply the rule.

Use Conditional Formatting with AND and COUNTIF Functions

Test the Checkbox: Toggle the checkbox on and off to verify that the conditional formatting dynamically highlights and un-highlights the duplicate values in your list.

Manage Data Easily with WPS Spreadsheet

Dynamically Highlight Duplicates with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting and form controls, allowing you to easily build interactive checklists and highlight duplicate values seamlessly.

1. Insert a Checkbox: Go to the Developer tab, select 'Insert', choose 'Checkbox', and draw it on your sheet. Right-click to format the control and link it to a cell.
2. Open Conditional Formatting: Highlight your target data, go to the Home tab, and click 'Conditional Formatting' > 'New Rule'.
3. Apply the Formula: Select 'Use a formula', input your =AND($N$3=TRUE,COUNTIF($A$3:$A$100,K12)>0) formula, choose a highlight color, and click OK.

Fully compatible with Microsoft Excel (.xlsx) formats, formulas, and conditional formatting rules.Free, lightweight, and incredibly fast when handling large datasets.Built-in Developer tools for easily adding interactive checkboxes and form controls.Intuitive conditional formatting manager with real-time previews.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my conditional formatting formula working when checking the box?

This usually happens if the checkbox isn't properly linked to a cell, or if the cell references in your formula are incorrect. Ensure the checkbox cell link uses absolute references (like $N$3) and the cell being evaluated in the target range uses a relative reference (like K12).

Can I apply this to multiple columns with different checkboxes?

Yes. You will need to create a separate conditional formatting rule for each column. Make sure each rule references its own specific linked checkbox cell and the appropriate comparison range using the exact same logic.

How do I hide the TRUE/FALSE text from the linked checkbox cell?

You can hide the text by formatting the font color to match the cell's background color (e.g., white text on a white background), or by placing the linked cell on a hidden worksheet where it won't interfere with your data presentation.

What if I want to highlight unique values instead of duplicates?

Change the COUNTIF condition in your formula to equal zero. For example, use =AND($N$3=TRUE,COUNTIF($A$3:$A$100,K12)=0) to highlight items that do not exist in your comparison list.

Document & File Issues