Kutools for Excel 22.00 HOT

300+ Powerful Features You Must Have in Excel

Kutools-for-Excel

Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!

Read More Download Buy now

Office Tab 14.00HOT

Adding Tabbed Interface for Office

Office Tab

It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo

Read More Download Buy now

Kutools for Outlook 13.00NEW

100+ Powerful Features for Outlook

Kutools-for-Outlook

Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!

Read More Download Buy now

Kutools for Word  9.00NEW

100+ Powerful Features for Word

Kutools-for-Word

Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!

Read More Download Buy now

Classic Menu for Office

Bringing Back Your Familiar Menus

Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.

Read More Download Buy now

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:

doc-find-circular-reference1

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


Find out and locate the cell in Status bar

You can find the circular reference in the Status bar.
doc find circular reference3


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!
doc select same difference
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

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:

doc-find-circular-reference2

Step 4: Click the cell address listed besides the Circular References, it selects the cell with circular reference at once.


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.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
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.
    Paul Ritter · 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.
  • To post as a guest, your comment is unpublished.
    Anjan Kumar · 1 years ago
    Locating 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.
    Mantas · 2 years ago
    Thank 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.
    Tony · 2 years ago
    thank 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.
      Sunny · 2 years ago
      Thank you for your supplement!!!
  • To post as a guest, your comment is unpublished.
    Linda · 2 years ago
    Thank 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.
    Hafiz Ghulam Yaseen · 3 years ago
    Thanks for help. This is an increase in my knowledge.
  • To post as a guest, your comment is unpublished.
    John · 3 years ago
    Hi, 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.
    Aneta · 3 years ago
    awesome! Thank you!:-* :-* :-*
  • To post as a guest, your comment is unpublished.
    Steve · 3 years ago
    Short 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.
    Adelani · 3 years ago
    Thank you for saving my entire life and removing that burden of looking for errors.
  • To post as a guest, your comment is unpublished.
    Idris · 3 years ago
    You eased my headache.
    Thank you.
  • To post as a guest, your comment is unpublished.
    Ken · 3 years ago
    Thank you, hard to find the solution but easy to fix. Your guidance was right on.
  • To post as a guest, your comment is unpublished.
    Milena · 3 years ago
    Thank you so much!!!!
  • To post as a guest, your comment is unpublished.
    Martha · 4 years ago
    Thanks a million ;)very helpful & easy to follow instructions.
  • To post as a guest, your comment is unpublished.
    Ankit Singh · 5 years ago
    thanks a lot Admin i am happy with solution
  • To post as a guest, your comment is unpublished.
    Tapa · 5 years ago
    You just saved me a ton of time. Thank-you so much.
  • To post as a guest, your comment is unpublished.
    Hil · 5 years ago
    So easy once you learn it. :roll: Thank you!!
  • To post as a guest, your comment is unpublished.
    Shariff · 5 years ago
    Awesome! 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.
    Alok · 5 years ago
    very nice and quick. thanks.
  • To post as a guest, your comment is unpublished.
    Roxie · 5 years ago
    THANK YOU SO MUCH!!!!!!!!!!!!!!!!!!! This was diving me crazy!!!!!!!!
  • To post as a guest, your comment is unpublished.
    Chuck · 5 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Charles · 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.
      Charles · 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.
    Sam Hall · 5 years ago
    Thanks. 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.
    chidi · 5 years ago
    Thanks for the quick fix
  • To post as a guest, your comment is unpublished.
    rueuth · 5 years ago
    Many Thanks, solved in two minutes!! :lol:
  • To post as a guest, your comment is unpublished.
    Jasper · 5 years ago
    Thanks, clear and simple instruction for an irritating problem :-)
  • To post as a guest, your comment is unpublished.
    Ed · 6 years ago
    Thanks for the help. Saved me hours going through all the sheets
  • To post as a guest, your comment is unpublished.
    dianna · 6 years ago
    GREAT stuff, quick n easy, also easy the stress :lol:
  • To post as a guest, your comment is unpublished.
    SHERY · 6 years ago
    You fixed my HEACACHE!! :-)
  • To post as a guest, your comment is unpublished.
    Nachu · 6 years ago
    Awesome stuff :)
    Saved me :)
  • To post as a guest, your comment is unpublished.
    walee · 6 years ago
    Thank you so much. You saved my life.
  • To post as a guest, your comment is unpublished.
    sys64738 · 6 years ago
    Thank you, this was annoying me greatly
  • To post as a guest, your comment is unpublished.
    rimzan khan · 6 years ago
    U saved me,Thank you
  • To post as a guest, your comment is unpublished.
    Garry · 6 years ago
    Excellent...easy peasy now. Thanks!
  • To post as a guest, your comment is unpublished.
    Mohamed · 6 years ago
    THANK YOU VERY MUCH !!!
  • To post as a guest, your comment is unpublished.
    Kelvin · 6 years ago
    Excellent advice, spreadsheet is a lot tidier now :)
  • To post as a guest, your comment is unpublished.
    acon · 6 years ago
    thanks a lot mate for your advice
  • To post as a guest, your comment is unpublished.
    Chitoxs · 6 years ago
    This is a lovely and useful tip. Thanks
  • To post as a guest, your comment is unpublished.
    girlie · 6 years ago
    thanks 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.
    Fullcircuits · 6 years ago
    Awesome! You saved my day! :lol:
  • To post as a guest, your comment is unpublished.
    David A · 6 years ago
    This 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.
    Vidya · 6 years ago
    Perfectly explained and exactly what I needed! Thank you!
  • To post as a guest, your comment is unpublished.
    Karen · 6 years ago
    Brilliant save me so much time. Tried MS help rubbish unless you knew where it was. :-*
  • To post as a guest, your comment is unpublished.
    Adesh · 6 years ago
    This was exactly the reply I was searching.... Thanks
  • To post as a guest, your comment is unpublished.
    Milind · 6 years ago
    This 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.
    Moe · 6 years ago
    THNAK YOU VERY MUCH!!!!
  • To post as a guest, your comment is unpublished.
    Irina · 6 years ago
    Thanks much! VERY helpful!
  • To post as a guest, your comment is unpublished.
    msulaiman786 · 6 years ago
    THANKS....
    SAVED ME FROM AN ERROR

    :-x
  • To post as a guest, your comment is unpublished.
    Josh · 6 years ago
    :-) Big simile... quick and easy way.. thanks..
  • To post as a guest, your comment is unpublished.
    Paula · 6 years ago
    Just what I needed, and in such a clear, precise manner.
    Thanks =)
  • To post as a guest, your comment is unpublished.
    Prasad · 6 years ago
    Could 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 !!

Feature Tutorials