Formula Errors

How to Use a Checkbox to Replace a Review Date in WPS Office Excel Without Circular References

Camila Milosovich Nov 13, 2026 869 views

Question details

The user wants to use a checkbox in WPS Office Excel to display either the original review date or a later calculated review date without causing a circular reference.

How to Use a Checkbox to Replace a Review Date in WPS Office Excel Without Circular References

Product: WPS Office Excel
Device & OS: not provided
Scenario: A worksheet tracks review dates and uses a checkbox to switch the displayed date from the original review date to a later calculated review date.
Observed behavior: When the later date is calculated from the original date and the checkbox changes the original date, WPS Office Excel can create a circular reference.

Before you start

Before changing the workbook, locate the original review date column and decide which helper cells will hold the checkbox-linked TRUE/FALSE value and the calculated later review date. Work in a copy of the workbook first so you can test the checkbox behavior without altering the original data.

Solution 1Recommended

Keep the Original Review Date Separate and Use the Checkbox Only to Choose Which Date to Display

Use this method when you need a checkbox to switch between an unchanged original review date and a later calculated review date without creating a circular reference.

The safest design keeps the original review date as an input cell. A separate cell calculates the later review date from that unchanged input, and the checkbox-linked helper cell only selects which date to display. This prevents the formula from changing the same cell it depends on.

Add helper cells for the checkbox and later date

In WPS Office Excel, open your worksheet and add helper cells for the checkbox-linked TRUE/FALSE value and the calculated later date. Keep the original review date in its own column, such as B2.

Insert and link the checkbox

Click the Developer tab, select Insert > Form Controls > Checkbox, and draw the checkbox. Right-click the checkbox, choose Format Control, and set the Cell link to your helper cell, such as $E$2.

Calculate the later review date from the original date only

In the later review date column, such as C2, enter =B2+120 or your own review interval. This calculates the later date without overwriting the original date in B2.

Display the selected date with an IF formula

In the display date cell, enter =IF($E$2,C2,B2). The checkbox-linked cell returns TRUE when checked and FALSE when unchecked, so the cell shows the later date or the original date.

Test the checkbox in a copy of the workbook

Save a copy of the workbook, then check and uncheck the checkbox to confirm the display date switches correctly and no circular reference warning appears before applying it to the original file.

Keep the Original Review Date Separate and Use the Checkbox Only to Choose Which Date to Display

Circular reference avoided: The checkbox now switches between the original and later review dates while the original date remains unchanged, so the circular reference is prevented.

WPS Office Excel

Use a Checkbox to Switch Review Dates in WPS Office Excel Without Circular References

In WPS Office Excel, keep the original review date in a separate column, link a checkbox to a TRUE/FALSE helper cell, calculate the later date from the original date only, and use an IF formula to display the selected date. This avoids circular references and works with familiar Excel formulas.

1. Open your workbook in WPS Office Excel: Launch WPS Office and open the Excel workbook that contains the review dates.
2. Enable the Developer tab if needed: Click the Menu button, go to Options > Customize Ribbon, and enable the Developer tab to access checkbox form controls.
3. Insert and link a checkbox to a helper cell: On the Developer tab, click Insert > Form Controls > Checkbox, draw the checkbox, then right-click it, choose Format Control, and link it to a TRUE/FALSE helper cell.
4. Enter formulas for the later date and display value: Keep the original review date in its own column, calculate the later date with =OriginalReviewDate+120, and use =IF(CheckboxCell,SecondReviewDate,OriginalReviewDate) to display the selected date.
5. Test the result: Check and uncheck the checkbox to confirm the displayed date changes as expected without circular references.

Prevents circular references by keeping the original review date unchangedSupports familiar checkbox form controls and IF formulasFully compatible with Microsoft Excel .xlsx filesFree and lightweight alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why does my checkbox formula create a circular reference in Excel?

A circular reference occurs when the formula changes the original review date and also depends on it. Keep the original date in a separate cell and calculate the later date from that unchanged cell, then use the checkbox-linked cell only in an IF formula to choose which date to display.

How do I link a checkbox to a cell in WPS Office Excel?

Enable the Developer tab, insert a Form Controls checkbox, right-click it, choose Format Control, and set the Cell link field to a helper cell. The helper cell shows TRUE when checked and FALSE when unchecked.

Can I calculate the later review date from the original date without overwriting it?

Yes. Put the original review date in its own column, such as B2, and enter =B2+120 in another column for the later review date. The original date remains unchanged.

What formula displays the original or later date based on a checkbox?

Use =IF(CheckboxCell,SecondReviewDate,OriginalReviewDate). Replace CheckboxCell with the linked helper cell, SecondReviewDate with the calculated later date cell, and OriginalReviewDate with the original date cell.

How do I test the checkbox formula safely?

Save a copy of the workbook and test the checkbox there. This lets you catch circular references or formula errors before applying the change to the original file.

Excel & Data Problems