[Libs-Or] Tech-Talk: Excel/G-Sheets - Finding and Fixing a “Circular Reference”

Wed Mar 12 08:46:11 PDT 2025

Welcome to the latest issue of Tech-Talk!

Having trouble reading this email?
You can view the tech tip<https://www.tech-talk.com/finding-and-fixing-a-circular-reference.html> and the communication tip<https://www.tech-talk.com/talking-resolving-conflict.html> online instead at:
https://www.tech-talk.com/login/oregon

When prompted for a username and password, use ORLIBTECH for both.

Tech-Talk is a paid subscription service for staff of Oregon libraries and is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.
Here's how to fix this error

[https://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

[View through a tunnel with a red button saying View the Tech Tip Video Here and a scenic lake and mountains in the background.]<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9BpAGYbAsJpl6wkMOihREEx-YOk2d6InV_7Mkjr8PypG1cJrNf7QbBFnEZuraLpIT9wk3gKD9cBcGDAdG4bgsdnKCSGTNumF5GijMsE0Sja68a61M6p7O29NjH4eIslKKYMZo--fR2eAq0L_kvAjNkdg74UsJRF18w==&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==>

Photo by Erlend Ekseth<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9BpAGYbAsJpl0PJHkSNAcji_lyNev_xP8cix3MGZSDZFuO6pa7RI6D_QQ4lhvt7gT28nGMQqqFYKByCrWuE351TsDRHRPHxSNdsXyj-tWPCjRlhyAKM_vTYn0ubdDoBcpjM8qVF1FcYUhdm7CGz2tODnVcJiTH3ssz2q2o0anZKFZ8K19pMimtnBh4EpuJm9n7TruAeaS1w6mFjNRXz0OFo=&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==> on Unsplash<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9BpAGYbAsJplEHxW8TT9NxnKtUoyFAVRQ4va3KwnKEiu2GCyj5uy-0I4aIyRpFW4L_pS0cGbVLA8WDzgg_5pZ4TrklpUvBvM4v3R9lz2NryTpTSkatgc1OJeBsRxV5nuc01hZJ6EWoqh8CwOwFseIxwkcz8r4EJlWEBBqTF2Wmlc4XIl6GJ1SXuYvUjMlVKLoV4Kq9RQhyOMswVM7uRu6eJU5TpkIr5IP4thsKfXcUJIfIuyZFwM1gbJXx9KckwxBT8gaitqdHWMMqH8IANsIinUECY0X-NPjQ==&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==>

[https://files.constantcontact.com/ee1208b4001/e25f6bcf-26a8-4e59-91f2-cc248962042d.png]<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9FHDbP0X50v-OGQWiC4RsyBaCG236gzP_AIhMbWn4M4XvcknLyIIIfgGSQLElzeTlomeSDdHdh4rRSRVf1WYhiP7-Of1k2Dx4q4rHP92LQtI&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==>

Webinars for You

NOTE: Webinars begin at 3 pm ET / 2 pm CT / 1 pm MT / 12 Noon PT and are one hour long.

March 12: [AI] Empowering Creativity with AI: 3 Tools to Design Stunning Visuals Why attend? You want a boost in your creativeness.

March 26: [COMMUNICATE] Unlock the Secret to Effective Communication with the Tech-Talk "Magic Form" Why attend? You want to create more focused and effective letters, email notes, presentations.

April 9: [SOCIAL MEDIA] Exploring Bluesky: The Next-Generation Social Media Platform Why attend? Learn more about this new "X" competitor.

April 23: [EXCEL] How to Edit Charts Like a Pro - Basic Techniques Why attend: You use charts in Excel but want to learn more about customizing.

View Webinars and Register Here<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9NFVpWInqLqRKXngBH0dm_3tcThwMIvlM8HU2cDhKpl5hQCNoy2pKEx_75xL2XNbr-kwNTOSERLygCopXRce7juEQRzTRv_iTOszRUbrCyPkhVu9yQPurRQ=&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==>

Excel/G-Sheets - Finding and Fixing a “Circular Reference”

Advanced

When working in Excel (or Google Sheets) have you ever had that dreaded message pop up:

"There are one or more circular references where a formula refers to its own cell either directly or indirectly.”

You see the error note in the box below.

[Spreadsheet with fruit sales data for three months; a circular reference error message is displayed in Microsoft Excel]

Well, if you're not a "power user" this error code can be confusing. Let's break it down so that the next time it pops up you know what to do.

Identify and Fix Circular Errors

First, a circular reference in Excel (or circular dependency in Google Sheets) occurs when a formula cannot be calculated because the specific cell name in which the formula is listed, is also included as part of the formula.

Often we say the circular reference is a formula that refers to its OWN value. This creates an endless loop which can cause inaccurate results.

[Spreadsheet displaying cells A1 with 10, A2 with 15, and A3 with 0. A formula in A3 shows =A1+A2+A3. Red arrow points to cell A3.]

For example, to keep it simple, say you are adding up the numbers in cells A1, A2 and A3, but the formula is in cell A3. BOTH a number that you want to include AND the formula can't be in the same cell.

As in the example, one of the most common types of circular references is just that: you're trying to add a column of cells and you inadvertently include the cell that contains your "total."

How does this happen? Maybe you dragged your cursor down a column to add it up, but included the "total" cell as well.

So… the first step is to identify the error and then fix it. Let's look at this process both in Excel and Google Sheets.

EXCEL – Fix a Circular Reference

As we've said, when you have an error like this, not only does Excel warn you with the "Circular Reference" message, but it highlights the problem cell.

[Excel warning about circular references that may cause incorrect calculations, with OK and Help buttons at the bottom.]

[Spreadsheet with highlighted cells showing produce costs. A red arrow points to Circular References: D6 in the status bar.]

In the status bar in the lower-left corner of the spreadsheet, after you've clicked "OK" in the error box, it displays "Circular References" and the cell affected.

To find and fix the errant cell:

1. Identify the Circular Reference

·     You can click in the cell to visually check it (as in the above.)

·     Or, in the top navigation, go to the Formulas tab, select Error Checking, and then Circular references.

·     It gives you the first cell where a circular reference occurs. TIP: If you fix the first one, repeating the steps above, will show you the next circular reference cell (if there is one).

[Excel interface showing a dropdown menu with Error Checking selecting Circular References at Sheet2!$A$3, highlighted by a red arrow.]

2. Check and Modify the Formula

If the formula refers to its own cell (e.g., =A1+A2 in cell A2), remove or adjust the formula; put it in a different location.

If it's an indirect circular reference (caused by multiple formulas referring back to each other), trace and break the loop. Excel provides tools to visually track formula relationships:

·     Trace Precedents - Shows which cells feed into the selected cell.
·     Trace Dependents - Shows which cells depend on the selected cell.

To use these tools:

·     Click on the cell with the circular reference.
·     Go to Formulas, then Trace Precedents (to see incoming references).
·     Or, Trace Dependents (to see outgoing references).
·     Follow the blue arrows to find the loop and modify your formula.
·     Remove the arrows after reviewing by clicking the Remove Arrows option in the toolbar.

[Spreadsheet showing fruit sales by month with a formula calculating the total in March. Red arrow points to the formula used.]

Three Basic Solutions to Fix a Circular Reference

1.  Change one of the formulas so it doesn’t refer back to its own cell.
2.  Move the location of the formula to another cell ... outside of the formula.
3.  Use a fixed value instead of a formula if appropriate.

GOOGLE SHEETS – Fix a Circular Reference

In Sheets you can have circular references just like Excel when a formula refers back to its own cell, either directly or indirectly. The cell will contain: #REF! and when you hover over the cell it will display a "Circular dependency detected" Error.

[Spreadsheet with fruit sales per month. Marchs total shows #REF! error. Red arrow points to error message about circular dependency.]

1. Identify the Circular Reference

In a cell that contains #REF! look for the "circular reference" error message.

2. Check and Modify the Formula

[Spreadsheet showing a #REF! error in cell B6 with a tooltip suggesting to change the formula or move the formula to another cell.]
·     Click on the problematic cell and manually review your formula to see which cells are being referenced to check for loops. Example: If cell D6 contains =D2:D6, it is trying to use its own value, causing an error.

·     Fix the formula or move it to a new cell.

"What is one thing you want your reader (or audience) to think or do differently?"

As a communicator, that's the question you want to ask yourself when you're preparing an important message: a letter, report, presentation ... a project, program, video.

Register for the "Magic Form" Webinar<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9BpAGYbAsJplEvuY2mFy-hBI7dd8Mq4r3tSPBiVuC9eg8LvimL6-b7X6NTzec2t6O-WB6-2crZnPFGeN3VvkliW6D9CMGzO86PE7PrTmRjqFKmIXnjkJRrmYvzcQWTK_SKFIfpjkdMPKIQ7OzDoepwRl9yD7jVRrn5hm29MBLY9P&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==> on March 26th to discover a communication planning process that produces focused and effective messaging!

JOIN THE "MAGIC FORM" WEBINAR<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9BpAGYbAsJplEvuY2mFy-hBI7dd8Mq4r3tSPBiVuC9eg8LvimL6-b7X6NTzec2t6O-WB6-2crZnPFGeN3VvkliW6D9CMGzO86PE7PrTmRjqFKmIXnjkJRrmYvzcQWTK_SKFIfpjkdMPKIQ7OzDoepwQjfPNiEbDc1HT6hkR01hFT&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==>

[https://files.constantcontact.com/ee1208b4001/e565d7d1-215e-487a-8e78-f332113e7825.jpg]

Communications: Resolving Conflict

Repeat, persistently and nicely!

Wouldn't you agree that when someone is angry, hurt or distressed, asking, "What is it that upsets you?" might be helpful?

When you do inquire, you may not get the quality answer you desire … the first time. If you want a more complete reply or a better answer, ask the question again!  Just do it gently and persistently.

Minimize your comments on what they’ve said until you feel they’ve divulged the real issue so that you have more fully grasped the situation.

You could also rephrase with something like, "So that’s what upsets you?" or "That could be upsetting." (In this case, you're paraphrasing instead of an exact repeat, and you're calmly soliciting more insight.)

[https://files.constantcontact.com/ee1208b4001/0091e766-eacd-41a9-87a5-f6588196ba86.png]<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9P8WoACzxFnZae2_f3GNXrryv_IIPf_KjcK-CD4dysKuEyQSRa_k3XVxFWJWePwP89RKc5sHWyevJDzyp4049mJgak8YmDcQpkcZFzKab6kn&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==>

www.Tech-Talk.com<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9P8WoACzxFnZae2_f3GNXrryv_IIPf_KjcK-CD4dysKuEyQSRa_k3XVxFWJWePwP89RKc5sHWyevJDzyp4049mKHUoCBMHxxkbAGvUFCFjEb&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==>

When was the last time you visited the Tech-Talk online resource? It has all kinds of fun ways to solve issues, discover new ideas, and hone your skills!

[Ask a question]

We're here to help!

Tell us what you are unclear about ... a new technique you'd like to learn ... or some fun tool you've heard about. Use this form!<https://opiayfbab.cc.rs6.net/tn.jsp?f=001kknNO7MULR50-geE0FR4i72XygxSe7UbOeNPiAbKOUrgtg1c0eoR9MexgLavo6qyzbJkDoamndHW803NzDQmbs1FcFmLy49XB4qx5u9nHxQH-q_WZcU6Eus2d0J_yE7KMBKA3fHSeB5w6k1UZQU9ETbjJCgyvRWOzbJnvTJuO3Q=&c=forA63Jd8srgTy4GGF484_giXipjGe3L8_pize2OU_7a3vbplHCO8A==&ch=u4VlAStxN9Akf7M5iCG-jVS4c-2544kGC56NvvMBjkU0hCKlSB5T0w==>

Copyright 1996-2025 Shared Results International. Published weekly. Distribution is limited by license. For information on how to include additional recipients, contact support at tech-talk.com<mailto:support at tech-talk.com> 585-615-7795.

Cheers,
Darci Hanning, MLIS (she/her/hers)
Public Library Consultant / CE Coordinator
Continuing Education Resources: https://slo.oregon.gov/conted/
State Library of Oregon | Library Support and Development Services
971-375-3491 | darci.hanning at slo.oregon.gov<mailto:darci.hanning at slo.oregon.gov> | www.oregon.gov/library<http://www.oregon.gov/library>

[State Library of Oregon (Logo)]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20250312/1a34c6bf/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20250312/1a34c6bf/attachment.png>