Excel shows a warning: “There are one or more circular references where a formula refers to its own cell”. What does it mean? Is it dangerous? How do you fix it?

This guide explains circular references from the ground up. You will learn how to find them, understand why they occur, and fix them — or, in some cases, use them deliberately.

For general troubleshooting, see the Excel troubleshooting guide. For other common problems, read common Excel problems.

What is a circular reference?

A circular reference occurs when a formula refers, directly or indirectly, to its own cell.

Direct circular reference

Simplest example:

Cell B1: =A1+B1

B1 refers to itself. Excel cannot calculate the value — it would need B1 to calculate B1.

Indirect circular reference

Example:

Cell A1: =B1+10
Cell B1: =C1*2
Cell C1: =A1-5

A1 → B1 → C1 → A1. A circle. Excel cannot calculate any of them without the others.

How to tell that you have a circular reference

Excel warns you in three ways:

1. Warning dialog on first detection

When you create a circular reference: – Excel shows a popup: “There are one or more circular references” – If you click OK: Excel tries to help you find it – If you click Cancel: The formula stays but is not calculated

2. Status bar at the bottom left

When a circular reference exists in the workbook: – Excel shows: “Circular References: B1” (or similar) – Click the text → a small menu showing all circular references

3. Message in Formula Auditing

Formulas → Error Checking → Circular References shows a list.

Find circular references step by step

If you have received the warning but cannot see where the problem is:

Method 1: Use error checking

  1. Go to the Formulas tab
  2. Click Error Checking (small exclamation mark)
  3. Select Circular References
  4. A list shows all cells with circular references
  5. Click a cell to jump to it

Method 2: Click the status bar

At the bottom left: click “Circular References: B1”.

Excel shows a list. Click a cell to jump to it.

Method 3: Trace precedents manually

  1. Select the cell that Excel points to
  2. Formulas → Trace Precedents
  3. Excel draws arrows to the cells the formula uses
  4. Go to one of those cells
  5. Trace precedents again
  6. Continue until you see an arrow that leads back to the starting cell

Now you have found the circle.

Why do circular references occur?

Most circular references are mistakes. Common causes:

1. Copied formulas without adjustment

Scenario: You copy a formula downwards but forget to use absolute references.

A1: =SUMMA($B$1:B1)    ← Rätt: ackumulerad summa
A2: =SUMMA($B$1:B2)    ← Rätt
A10: =SUMMA($B$1:B10)  ← Rätt

But if you forget the dollar sign:

A1: =SUMMA(B1:B1)      ← OK
A2: =SUMMA(B2:B2)      ← OK
A10: =SUMMA(B10:B10)   ← OK men inte vad du ville

Or worse — if you refer to the same column:

A1: =SUMMA(A1:B1)      ← Cirkulär: A1 refererar till sig själv

2. Totals that include themselves

Common mistake:

A1: 100
A2: 200
A3: 300
A4: =SUMMA(A1:A4)      ← Cirkulär: A4 inkluderar sig själv

Correct:

A4: =SUMMA(A1:A3)      ← Inkludera inte resultatcellen

3. Complex models with multiple sheets

In large models, circular references can occur between sheets:

Blad1!A1: =Blad2!B1*2
Blad2!B1: =Blad3!C1+10
Blad3!C1: =Blad1!A1-5    ← Cirkeln stängs här

4. Overlapping named ranges

If a named range includes a cell that refers back to the range.

Fix circular references

Most cases are solved by restructuring the calculation.

Strategy 1: Use helper columns

Instead of referring back — create an intermediate calculation.

Before (circular):

A1: 100
B1: =A1*1,25
A1: =B1-25     ← Fel: A1 kan inte referera till B1 som refererar till A1

After (non-circular):

A1: 100
B1: =A1*1,25
C1: =B1-25     ← Nu finns ingen cirkel

Strategy 2: Change the calculation logic

There is often another way to calculate the same thing.

Example: Commission based on the total including commission

Problem: You sell for SEK 1,000 and want to add 10% commission. The total becomes SEK 1,100 and the commission is SEK 110.

A1: 1000               ← Grundpris
B1: =A1*0,1            ← Provision (10% av grundpris)
C1: =A1+B1             ← Totalt

Men om provision ska vara 10% av totalt (inte grundpris):
B1: =C1*0,1            ← Cirkulär: B1 används i C1
C1: =A1+B1

Solution: Mathematical rewrite

A1: 1000               ← Grundpris
B1: =A1*0,1/0,9        ← Provision: räknad direkt
C1: =A1+B1             ← Totalt

Eller enklare:
C1: =A1/0,9            ← Totalt (grundpris / 0,9 = grundpris * 1,111...)
B1: =C1-A1             ← Provision

Strategy 3: Remove the reference altogether

Sometimes it is not needed.

Example:

A1: =SUMMA(B1:B10)
B10: =A1/10            ← Vill ha genomsnitt

Men MEDEL() gör samma sak utan cirkel:
B10: =MEDEL(B1:B9)

When circular references are intentional

In some cases you want circular references. Excel can handle them with iterative calculation.

Use cases

1. Financial models with reinvestment

Profit for the year is partly reinvested, which affects next year’s profit, which is reinvested, and so on.

2. Cyclic calculations

Temperature simulations where heat spreads between cells that affect each other.

3. Goal Seek-like calculations

Find a balance where two factors affect each other.

Enable iterative calculation

  1. File → Options → Formulas
  2. Tick “Enable iterative calculation”
  3. Set:
    • Maximum iterations: 100 (default)
    • Maximum change: 0.001 (when Excel stops iterating)
  4. Click OK

Now Excel calculates the circle repeatedly until the value stabilises.

Example: Reinvestment model

A1: 1000               ← Startvärde
A2: =A1*1,1+B2         ← Nästa års värde (10% tillväxt + återinvestering)
B2: =A2*0,05           ← Återinvestering (5% av A2)

With iterative calculation: – Excel calculates A2 → B2 → A2 → B2 repeatedly – Stops when the change is below 0.001

Without iterative calculation: Circular reference warning. With iterative calculation: Works.

Warnings about iterative calculation

Slower: Each iteration takes time. Complex models can become very slow.

Harder to troubleshoot: If the logic is wrong you get wrong results without any warning.

Can give different results: If you change the starting conditions, the result may converge to different values.

Recommendation: Only use iterative calculation if you understand the mathematics behind it and have deliberately designed the model for it.

Troubleshooting when the circle is hard to find

Problem: Excel says there is a circular reference but you cannot find it

Possible cause 1: The circle is in a hidden sheet

  1. Right-click the tabs at the bottom
  2. Select “Unhide…” if there are hidden sheets
  3. Unhide all sheets and check again

Possible cause 2: The circle involves named ranges

  1. Ctrl+F3 (Name Manager)
  2. Check each named range
  3. Does any name refer back to a cell that uses the name?

Possible cause 3: The circle is indirect via several steps

Use Trace Precedents systematically: 1. Start at the cell Excel points to 2. Trace precedents 3. Go to each precedent 4. Trace their precedents 5. Draw the chain on paper if necessary

Problem: Excel shows “Circular References” but the formula looks right

Check:

1. Does the formula use named ranges? Perhaps the range includes its own cell.

2. Is there conditional formatting or data validation? These can contain formulas that create circles.

3. Are there hidden rows/columns? Right-click the row number → Unhide.

Best practices for avoiding circular references

1. Clear data flow design

Principle: Data flows in one direction.

Input → Beräkningar → Mellanresultat → Slutresultat

Never back. Sketch the structure before you build complex models.

2. Use helper columns generously

Better five simple steps than one complex one that risks a circle.

3. Name ranges and cells

Makes it easier to see the data flow:

=Försäljning*Marginal+Rabatt

Clearer than:

=A1*B5+C3

4. Document calculations

Add comments (right-click → New Note) in complex cells to explain the logic.

5. Test on a smaller scale first

Build a simplified version with a few cells. When the logic works, scale up.

6. Use formula auditing continuously

Trace precedents/dependents while you build to check that the data flow is logical.

When you need help

Circular references in large, complex models can be hard to untangle.

Contact Excel Department if:

  • The circle involves several sheets or workbooks
  • The model is business-critical and you do not dare to experiment
  • You need to redesign the model but preserve its functionality
  • Iterative calculation is needed but performance is too poor

We help you analyse the structure and rebuild it to eliminate circular references or optimise iterative calculations.

First step: Call 010-264 20 20 or email info@exceldepartment.se.

Summary

Circular references: – Occur when a formula refers to its own cell (directly or indirectly) – Usually mistakes that should be fixed – Sometimes intentional (require iterative calculation)

Find them: – Formulas → Error Checking → Circular References – Click the status bar (bottom left) – Use Trace Precedents

Fix them: – Restructure the calculation – Use helper columns – Change the logic to avoid back-references

Intentional use: – Enable iterative calculation (File → Options → Formulas) – Useful for financial models with reinvestment – Be aware of slower performance

Related content

Excel Department Slottsbacken 8, Stockholm Telephone: +46-10-264-20-20 / 010-264 20 20 Email: info@exceldepartment.se

Schema Markup

{
  "@context": "https://schema.org",
  "@type": "HowTo",
  "name": "Cirkulära referenser i Excel: Hitta och åtgärda",
  "description": "Lär dig vad cirkulära referenser är, hur du hittar dem i Excel och hur du åtgärdar dem — eller använder dem avsiktligt.",
  "image": "https://www.exceldepartment.com/images/cirkulara-referenser-excel.jpg",
  "totalTime": "PT10M",
  "step": [
    {
      "@type": "HowToStep",
      "name": "Hitta cirkulära referenser",
      "text": "Gå till Formler → Felkontroll → Cirkulära referenser. Klicka på en cell i listan för att hoppa dit. Eller klicka på statusfältet nere till vänster.",
      "url": "https://www.exceldepartment.com/excel-cirkulara-referenser/#hitta-cirkulära-referenser-steg-för-steg"
    },
    {
      "@type": "HowToStep",
      "name": "Spåra föregångare",
      "text": "Markera cellen, gå till Formler → Spåra föregångare. Följ pilarna för att se vilka celler formeln använder. Gå vidare till föregångarna tills du hittar cirkeln.",
      "url": "https://www.exceldepartment.com/excel-cirkulara-referenser/#hitta-cirkulära-referenser-steg-för-steg"
    },
    {
      "@type": "HowToStep",
      "name": "Åtgärda med hjälpkolumner",
      "text": "Omstrukturera beräkningen genom att lägga till mellanliggande steg i separata kolumner. På så sätt undviker du att referera tillbaka till samma cell.",
      "url": "https://www.exceldepartment.com/excel-cirkulara-referenser/#åtgärda-cirkulära-referenser"
    }
  ],
  "author": {
    "@type": "Person",
    "name": "Lukas Lilja"
  },
  "publisher": {
    "@type": "Organization",
    "name": "Excel Department"
  },
  "datePublished": "2026-01-30",
  "inLanguage": "sv-SE"
}

Need help with this? Our Excel consultants can help – the first consultation is free of charge.

Book a free call or call 010-264 20 20.