Calculation Issues

Fix an Excel Circular Reference Error Near YEAR(TODAY())

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

Find and resolve a circular reference error that appears near a formula containing YEAR(TODAY()).

Fix an Excel Circular Reference Error Near YEAR(TODAY())
Product
Microsoft Excel
Device & OS
not provided
Scenario
An Excel workbook shows a circular reference error after a new year begins, near a formula that uses YEAR(TODAY()).
Observed behavior
Excel reports a circular reference, but another formula or worksheet may contain the actual reference loop.
Before you start

Open the affected workbook in Excel and identify the cell where you noticed the error. Check whether its formula is exactly =YEAR(TODAY()) or includes references to other cells.

Solution 1Recommended

Find and remove the circular reference in Excel

Use Excel's circular reference checker to locate cells involved in a formula loop, including cells on other worksheets.

The formula =YEAR(TODAY()) returns the current year and does not refer to a cell, so it does not create a circular reference by itself. A longer formula containing it may still participate in a loop through other references.

1
Open the circular reference list

In Excel, select Formulas > Error Checking > Circular References. Choose a listed cell to navigate to it.

2
Inspect the formula

Select the listed cell and examine its formula in the formula bar. Look for a reference to the cell itself or to another cell that eventually refers back to it.

3
Follow the references

On the Formulas tab, use Trace Precedents and Trace Dependents to investigate related cells. Check formulas on other worksheets and select Formulas > Name Manager to inspect named ranges used by the formula.

4
Break the loop and recheck

Edit the affected formula in the formula bar so its calculation no longer depends on itself. Return to Formulas > Error Checking > Circular References and inspect any remaining listed cells, checking each worksheet as needed.

Find and remove the circular reference in Excel
Intentional circular calculations: Some workbooks deliberately use iterative calculations. Before changing a formula, confirm whether the reference loop is intentional and how the workbook is designed to calculate.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets. Its Excel format support makes it easy to move existing workbooks into WPS, although it does not repair Excel's own error-checking tools.

Open and edit Excel-compatible XLSX and XLS filesReview spreadsheet formulas, including YEAR and TODAYUse a familiar spreadsheet interface for a smoother transitionWork with a free, lightweight Office alternative
QA img-9

Frequently Asked Questions

Does =YEAR(TODAY()) create a circular reference?

No. By itself, =YEAR(TODAY()) returns the current year without referring to any cell. If it is part of a longer formula, check the other references in that formula.

How do I find a circular reference on another worksheet?

Select each worksheet and use Formulas > Error Checking > Circular References. Choose a listed cell, then inspect its formula and any references to other sheets.

Can a named range cause a circular reference?

Yes. A named range can be part of a reference chain that loops back to the formula cell. Open Formulas > Name Manager and inspect names used in the affected formula.

Why did the error appear when the year changed?

The date change may have caused formulas to recalculate or follow a different path, exposing an existing loop. Use Formulas > Error Checking > Circular References to locate the cells involved rather than assuming TODAY is the cause.