Skip to main content
Skip to main content
Microsoft 365

The good kind of circular reference

(Excel MVP Jan Karel Pieterse, our guest blogger, runs JKP Application Development Services, specializing in developing business solutions using Microsoft Excel/VBA and Access/VBA. )

circular referenceThere have been ample articles about troubleshooting unintentional circular references in Excel workbooks. This post focuses on the opposite:  choosing to deliberately work with circular references. 

Circular references aren’t a bad thing in itself:  you can use them to achieve complex calculations that are otherwise impossible to do, but first you must set them up properly.  

 

 

  Circular references can be useful in situations such as the following: 

  •  If you want to perform a calculation for which you need the last result to be a new input value for the calculation. For example, you are creating a calculation model of a chemical facility where (part of) an input stream of a process is a recycled output stream of the same process.
  • If you want a cell to remember the date at which an entry was made into another cell. 

These are the most important things to consider when you create an Excel workbook with circular references:

  • Decide up front whether what you’re trying to calculate actually is of a circular nature. Very often there is a way to rewrite your calculations in such a way that no circular references are needed. The latter is always the preferred method to use.
  • If and only if you are convinced you need circular references to accomplish your goal, first meticulously document your model. Make sure you clearly state what your intentions are and how the model functions.
  • Devise a way to clearly indicate the circular reference chain(s).
  • Design the circles in such a way that you have cells that can be used to temporarily break the circular reference chain.
  • Make sure you can give your circular chain a starting value, possibly using the breaker cells mentioned above.
  • Check for model convergence: does your model produce stable results during the subsequent iterations? 

If this high-level post has triggered your interest in circular references, let me invite you to head over to the article I posted on my website: Working with Circular references in Excel. In it I discuss the elements of using circular references in much more detail.

Regards,

Jan Karel Pieterse
http://www.jkp-ads.com

You may also like these articles

Image for: View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365
• 5 min read

View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more.

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards
• 5 min read

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting
• 6 min read

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…