·
1 years ago
The instruction under "Zoek en zoek de cel in Circular Reference" was very-very-very helpful !
MS-Help does not give this solution.
MS-Help does not give this solution.
How to find and locate circular reference in Excel quickly?
When you apply a formula in a cell, says Cell C1, and the formula refers back to its own cell directly or indirectly, says =Sum (A1:C1), circular reference happens.
When you reopen this workbook with circular reference again, it pops up a circular reference error message, which warns formulas contains a circular reference and may not calculate correctly. See the following screenshot:
It tells what is the problem, however, it does not say where the error stays in. If you click the OK button, the warning error message will be closed, but it will pop up next time when you reopen the workbook; if you click the Help button, it will bring you to the Help document.
Find out and locate the cell in Status bar
Find out and locate the cell in Circular Reference
Compare two ranges and select and highlight the same or different values in Excel
|
| In Excel, if there are two tables in a sheet or two sheets needed to be compared, generally, you will compare one row by one manually. But if you have Kutools for Excel's Select Same & Different Cells utility, you can quickly find the same rows or different rows, and, also, you can highlight them with background color or font color as you need. Free trial for full-featured 30-days! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by
80%
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Find out and locate the cell in Status bar
You can find the circular reference in the Status bar.
Find out and locate the cell in Circular Reference
Actually, you can find out and locate the cell with circular reference in Excel with following steps:
Step 1: Go to the Formula Auditing group under the Formula tab.
Step 2: Click the Arrow button besides the Error Checking button.
Step 3: Move mouse over the Circular References item in the drop down list, and it shows the cells with circular references. See the following screenshot:
Step 4: Click the cell address listed besides the Circular References, it selects the cell with circular reference at once.
Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10! |
|
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 30-day trial of Office Tab! |
 |
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
· 1 years agoLocating the Cell In Circular Reference was nicely explained with steps........... I fixed the error..... thank you very much
-
To post as a guest, your comment is unpublished.
· 2 years agoThank you! Simple and short explanation, without writing a ton of paragraphs of why finding circular references is important :)
-
To post as a guest, your comment is unpublished.
· 2 years agothank you, I thought it would be even simpler by just clicking on the statement in the status bar. your explanation was spot on.
-
To post as a guest, your comment is unpublished.
· 2 years agoThank you for your supplement!!!
-
-
To post as a guest, your comment is unpublished.
· 2 years agoThank you. I have been looking for the error for two hours and found it in 10 minutes with your help!
-
To post as a guest, your comment is unpublished.
· 2 years agoThanks for help. This is an increase in my knowledge.
-
To post as a guest, your comment is unpublished.
· 2 years agoHi, thanks for the instructions - but the lower entry (Circular references) of the error checking list is grayed out. No luck in making it selectable - ever.
Error checking is not a properly working feature of Excel generally. Had to switch off four of the selections under options, formular, error checking - otherwise these keept demanding changes to cells/formulars, which were fully correct.
A calculate command now takes 5 minutes - then the invisible checking for circular references begins - and takes another 2-3 minutes. Then you get one circular reference listed in the status line. Having studied that formular and tracked it, Excel says that the offset formular is a circular reference - but a direct reference to the resulting address is OK!
Have now spent a weeks time on trying to hunt down these spooks of circular references, which overload my PC entirely (20 minutes to do a save as command).
Trying to get help from Microsoft, I really like these helping words from Microsoft Support:
[i]While you're looking, check for indirect references. They happen when you put a formula in cell A1, and it uses another formula in B1 that in turn refers back to cell A1. [u]If this confuses you, imagine what it does to Excel.[/u][/i]
Poor Excel, bad user(s) ...
If it is only me being unsuccesfull in solving the circular references problems - then I'm happy for all of you ... Otherwise, a little bit of confort to you, keep fighting - there must be a way to manage this degrading of error management in Excel 2013. But using Microsoft Office has opened up another cost account for my job ... -
To post as a guest, your comment is unpublished.
· 2 years agoawesome! Thank you!:-* :-* :-*
-
To post as a guest, your comment is unpublished.
· 3 years agoShort sweet answer. Popped up on Google right under MS support which I routinely skip past. Thanks!!!
-
To post as a guest, your comment is unpublished.
· 3 years agoThank you for saving my entire life and removing that burden of looking for errors.
-
To post as a guest, your comment is unpublished.
· 3 years agoYou eased my headache.
Thank you. -
To post as a guest, your comment is unpublished.
· 3 years agoThank you, hard to find the solution but easy to fix. Your guidance was right on.
-
To post as a guest, your comment is unpublished.
· 3 years agoThank you so much!!!!
-
To post as a guest, your comment is unpublished.
· 4 years agoThanks a million ;)very helpful & easy to follow instructions.
-
To post as a guest, your comment is unpublished.
· 4 years agothanks a lot Admin i am happy with solution
-
To post as a guest, your comment is unpublished.
· 4 years agoYou just saved me a ton of time. Thank-you so much.
-
To post as a guest, your comment is unpublished.
· 4 years agoSo easy once you learn it. :roll: Thank you!!
-
To post as a guest, your comment is unpublished.
· 5 years agoAwesome! So clear it fixed up my problem and saved me tons of time browsing through sheets and sheets looking for it manually!
-
To post as a guest, your comment is unpublished.
· 5 years agovery nice and quick. thanks.
-
To post as a guest, your comment is unpublished.
· 5 years agoTHANK YOU SO MUCH!!!!!!!!!!!!!!!!!!! This was diving me crazy!!!!!!!!
-
To post as a guest, your comment is unpublished.
· 5 years agoI have this same warning popping up when I open a workbook.
However, I have checked each sheet within the workbook and no sheet reports a circular reference. I have even executed the Formula Auditing=>Error Checking individually on each sheet and no sheet reports a circular reference. It's only reported when I open the workbook. Any ideas on how to find out what excel is complaining about on open?-
To post as a guest, your comment is unpublished.
· 5 years ago[quote name="Chuck"]I have this same warning popping up when I open a workbook.
However, I have checked each sheet within the workbook and no sheet reports a circular reference. I have even executed the Formula Auditing=>Error Checking individually on each sheet and no sheet reports a circular reference. It's only reported when I open the workbook. Any ideas on how to find out what excel is complaining about on open?[/quote]
Great expression - thanks
I have the same - circular reference warning on recalc since I copied this expression to a range in my worksheet. -
To post as a guest, your comment is unpublished.
· 5 years ago[quote name="Chuck"]I have this same warning popping up when I open a workbook.
However, I have checked each sheet within the workbook and no sheet reports a circular reference. I have even executed the Formula Auditing=>Error Checking individually on each sheet and no sheet reports a circular reference. It's only reported when I open the workbook. Any ideas on how to find out what excel is complaining about on open?[/quote]
Same here - circular ref warnings started appearing when ever there is a recalculation, just after I added this fab example in a large range on my work sheet.
-
-
To post as a guest, your comment is unpublished.
· 5 years agoThanks. Have been reading that stupid error for weeks and first solution I found was yours and the directions were super simple.
-
To post as a guest, your comment is unpublished.
· 5 years agoThanks for the quick fix
-
To post as a guest, your comment is unpublished.
· 5 years agoMany Thanks, solved in two minutes!! :lol:
-
To post as a guest, your comment is unpublished.
· 5 years agoThanks, clear and simple instruction for an irritating problem :-)
-
To post as a guest, your comment is unpublished.
· 5 years agoThanks for the help. Saved me hours going through all the sheets
-
To post as a guest, your comment is unpublished.
· 5 years agoGREAT stuff, quick n easy, also easy the stress :lol:
-
To post as a guest, your comment is unpublished.
· 5 years agoYou fixed my HEACACHE!! :-)
-
To post as a guest, your comment is unpublished.
· 5 years agoAwesome stuff :)
Saved me :) -
To post as a guest, your comment is unpublished.
· 5 years agoThank you so much. You saved my life.
-
To post as a guest, your comment is unpublished.
· 5 years agoThank you, this was annoying me greatly
-
To post as a guest, your comment is unpublished.
· 5 years agoU saved me,Thank you
-
To post as a guest, your comment is unpublished.
· 5 years agoExcellent...easy peasy now. Thanks!
-
To post as a guest, your comment is unpublished.
· 5 years agoTHANK YOU VERY MUCH !!!
-
To post as a guest, your comment is unpublished.
· 5 years agoExcellent advice, spreadsheet is a lot tidier now :)
-
To post as a guest, your comment is unpublished.
· 5 years agothanks a lot mate for your advice
-
To post as a guest, your comment is unpublished.
· 5 years agoThis is a lovely and useful tip. Thanks
-
To post as a guest, your comment is unpublished.
· 5 years agothanks SO much. i was about to re-do the whole file when i stumbled upon this!
-
To post as a guest, your comment is unpublished.
· 6 years agoAwesome! You saved my day! :lol:
-
To post as a guest, your comment is unpublished.
· 6 years agoThis was excatly what I needed and your tip was wonderful.
Thanks for making this so easy. -
To post as a guest, your comment is unpublished.
· 6 years agoPerfectly explained and exactly what I needed! Thank you!
-
To post as a guest, your comment is unpublished.
· 6 years agoBrilliant save me so much time. Tried MS help rubbish unless you knew where it was. :-*
-
To post as a guest, your comment is unpublished.
· 6 years agoThis was exactly the reply I was searching.... Thanks
-
To post as a guest, your comment is unpublished.
· 6 years agoThis indeed solved my worries, also it saved me from probable and possible disasters that might happen due to circualar reference errors.
thanks a lot to all. -
To post as a guest, your comment is unpublished.
· 6 years agoTHNAK YOU VERY MUCH!!!!
-
To post as a guest, your comment is unpublished.
· 6 years agoThanks much! VERY helpful!
-
To post as a guest, your comment is unpublished.
· 6 years agoTHANKS....
SAVED ME FROM AN ERROR
:-x -
To post as a guest, your comment is unpublished.
· 6 years ago:-) Big simile... quick and easy way.. thanks..
-
To post as a guest, your comment is unpublished.
· 6 years agoJust what I needed, and in such a clear, precise manner.
Thanks =) -
To post as a guest, your comment is unpublished.
· 6 years agoCould not agree more with the comment of "inquirer" - you are my saviour indeed !
It is really stupid of Microsoft to only give the warning in the pop up message and not inform on how to find the circ. reference. I had a big excel file and it was taking several minutes to open each time because of this circ. reference problem.
Thanks a lot !!