{"id":3744,"date":"2026-06-13T20:18:56","date_gmt":"2026-06-14T02:18:56","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3744"},"modified":"2026-06-13T20:19:41","modified_gmt":"2026-06-14T02:19:41","slug":"using-circular-references-in-excel","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-tips\/using-circular-references-in-excel.html","title":{"rendered":"Using Circular References in Excel for Cool Stuff"},"content":{"rendered":"<p>This article is dedicated to the various ways that you can use Circular References in Excel on purpose, rather than just as an error signal. I'll share some examples of animated models <strong>without VBA<\/strong>, then explain some of the basic building blocks for working with circular references.<\/p>\n<p><strong>IMPORTANT:<\/strong> To USE circular references on purpose, you need to <strong>Enable Iterative Calculation<\/strong>. If you set the <strong>Maximum Iteration<\/strong> to 1, then you can use F9 to step through each iteration. This can be used for animation or just to watch your model solve one step at a time.<\/p>\n<ul>\n<li><a href=\"#examples\">Examples You Can Download<\/a><\/li>\n<li><a href=\"#counter\">Create a Basic Counter<\/a><\/li>\n<li><a href=\"#checkbox\">Use a Checkbox as a Start\/Reset Trigger<\/a><\/li>\n<li><a href=\"#growing-array\">Build a Growing Array<\/a><\/li>\n<li><a href=\"#timer\">Create a Timer<\/a><\/li>\n<li><a href=\"#jacobi\">Jacobi Updates (make new, replace old with new)<\/a><\/li>\n<li><a href=\"#gauss-seidel\">Gauss-Seidel Updates (update in-place)<\/a><\/li>\n<li><a href=\"#loop\">Build in a Loop<\/a><\/li>\n<li><a href=\"#sequence\">Left-to-Right, Top-to-Bottom<\/a><\/li>\n<\/ul>\n<h2 id=\"examples\">Examples You Can Download<\/h2>\n<p>Before diving into the details, you may want to check out these 4 examples:<\/p>\n<h3>Example 1: <a href=\"https:\/\/www.vertex42.com\/edu\/dice-rolling-in-excel.html\">Dice Rolling Simulation<\/a><\/h3>\n<p><video poster=\"https:\/\/cdn.vertex42.com\/videos\/1d6-dice-race-simulation-in-excel_680x680.png\" muted=\"muted\" width=\"680\" height=\"680\" controls=\"\" autoplay=\"\" class=\"imgshadow\"><source src=\"https:\/\/cdn.vertex42.com\/videos\/1d6-dice-race-simulation-in-excel.mp4\" type=\"video\/mp4\">\"This browser does not support the HTML5 video element\"<\/video><\/p>\n<h3> Example 2: <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/game-of-life.html\">Conway's Game of Life<\/a><\/h3>\n<p><a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/game-of-life.html\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/videos\/make-conways-game-of-life-in-excel_480x270.png\" alt=\"Make Conway's Game of Life in Excel\" width=\"480\" height=\"270\" class=\"imgshadow\" style=\"max-width:100%;height:auto\"><\/a><\/p>\n<h3> Example 3: <a href=\"https:\/\/www.vertex42.com\/edu\/four-bar-linkage.html\">Four-Bar Linkage Animation<\/a><\/h3>\n<p><video poster=\"https:\/\/cdn.vertex42.com\/videos\/four-bar-linkage-animation-in-excel.png\" muted=\"muted\" width=\"680\" height=\"680\" controls=\"\" autoplay=\"\"><source src=\"https:\/\/cdn.vertex42.com\/videos\/four-bar-linkage-animation-in-excel.mp4\" type=\"video\/mp4\">\"This browser does not support the HTML5 video element\"<\/video><\/p>\n<h3> Example 4: <a href=\"https:\/\/www.vertex42.com\/edu\/heat-transfer.html\">Heat Transfer Simulation<\/a><\/h3>\n<p><video poster=\"https:\/\/cdn.vertex42.com\/videos\/2d-transient-heat-transfer-in-excel_680x680.png\" muted=\"muted\" width=\"680\" height=\"680\" controls autoplay class=\"imgshadow\"><source src=\"https:\/\/cdn.vertex42.com\/videos\/2d-transient-heat-transfer-in-excel.mp4\" type=\"video\/mp4\">\"This browser does not support the HTML5 video element\"<\/video><\/p>\n<h2 id=\"counter\">Create a Basic Counter or Iterator<\/h2>\n<p>To get started, we will make a Counter that adds 1 or some other increment value each time you press F9.<\/p>\n<p><strong>In cell B1, enter =B1+1.<\/strong> It's as simple as that! B1 is adding 1 to itself each time you press F9.<\/p>\n<p>You can of course add some other increment value in place of 1, such as 0.25 or 0.5 or 10, etc.<\/p>\n<p><strong>IMPORTANT<\/strong>: If you get the circular reference warning\/error message, go to File > Options > Formulas and check the \"Enable Iterative Calculations\" box, and set Maximum Iterations to 1.<\/p>\n<p>Also note that if you are relying on the worksheet adding only a single step each time you press F9, then make sure you aren't using Data Tables or other features that cause Excel to perform additional iterative calculations.<\/p>\n<h2 id=\"checkbox\">Use a Checkbox as a Start (or Reset) Trigger<\/h2>\n<p>Every time I've built a model based on this type of iteration, I've wanted a way to Start and Reset the simulation, animation, etc.<\/p>\n<p><img decoding=\"async\" class=\"center imgshadow\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/checkbox-and-counter-with-circ-refs.png\" alt=\"Excel Checkbox and Counter with Circular References\" width=\"389\" height=\"103\"><\/p>\n<p>An easy way to do that is to <strong>add a Checkbox into cell A1<\/strong> (via Insert > Checkbox) and modify your Counter formula in cell B1 to be <strong>=IF(A1,B1+1,0)<\/strong>. This means \"If the checkbox in cell A1 is checked (i.e. TRUE), the add one to B1, otherwise make it 0\"<\/p>\n<h2 id=\"growing-array\">Build a Growing Array<\/h2>\n<p>The Dice Rolling simulation above shows how you can store random values in a growing array.<\/p>\n<p>To simulate rolling a 6-sided die, we use =RANDBETWEEN(1,6). Put that formula into cell C1.<\/p>\n<p>Keep your checkbox in cell A1 and your Iterator in cell B1.<\/p>\n<p>In cell D1, enter =IF(A1,VSTACK(D1#,C1),C1)<\/p>\n<p><img decoding=\"async\" class=\"center imgshadow\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/growing-array-of-random-numbers.png\" alt=\"Growing Array of Random Numbers\" width=\"461\" height=\"229\"><\/p>\n<p>When you click on your checkbox and press F9, you should see the array in cell D1 start growing.<\/p>\n<p>Why is this method interesting? Because normally if you make an array of random numbers using RANDBETWEEN, the numbers will change every time the worksheet recalculates. Yes, you could generate a set of numbers and then paste just those values somewhere, but for this case we are generating a growing array of newly generated random numbers that will remain as-is until we restart the simulation.<\/p>\n<h2 id=\"timer\">Create a Timer<\/h2>\n<p>The goal here is to create a functioning timer that keeps track of the amount of real time since the start of a simulation. It will update each time you press F9, and will be reset when you uncheck the box in A2.<\/p>\n<p><img decoding=\"async\" class=\"center imgshadow\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/create-a-timer-in-excel-using-circ-refs.png\" alt=\"Create a Timer in Excel Using Circular References\" width=\"487\" height=\"135\"><\/p>\n<p>For this type of timer, we need to use the concept of \"latching\". We want to capture the timestamp ONCE, only immediately after the checkbox is first checked.<\/p>\n<p>When the checkbox is checked, we can use the NOW() function to store the current timestamp.<\/p>\n<p><strong>A2<\/strong> contains a checkbox.<\/p>\n<p><strong>B2<\/strong> will now represent our Start Time. In B2, enter <strong>=IF(A2, IF(B2=\"\",NOW(),B2), \"\")<\/strong>.<\/p>\n<p><strong>C2<\/strong> =IF(A2, NOW()-B2, 0)<\/p>\n<p>C2 is the time difference between NOW() and our starting timestamp that was stored in B2. You can format C2 using the number format \"00:00:00.000\". Although times in Excel are not supposed to be accurate to the millisecond, Excel seems to do pretty well with 20ms increments.<\/p>\n<p>How does this work? First, we start out with B2=\"\". Immediately after the checkbox is checked, B1 is still blank, but the formula will use NOW() to return the current time stamp. Every subsequent recalculation, B1 is no longer blank, so it will continue to return the value in B1 (the original time stamp). This is called an \"Edge-triggered capture\" and we are \"latching the start time\".<\/p>\n<p>Elapsed Time can be calculated in cell C2 as =IF(checked, NOW()-B2, 0)<\/p>\n<h2 id=\"jacobi\">Jacobi-style Array Updates<\/h2>\n<p>A Jacobi-style update or iteration might be summarized as \"use old, replace with new.\"<\/p>\n<p>For this type of update, we might have an array, such as a Sudoku puzzle, <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/game-of-life.html\">Game of Life<\/a> map, or a matrix representing a system of linear equations.<\/p>\n<p>We perform whatever calculations are needed and create a new array (typically of the same size). Then, using a circular reference we replace the old array with the new array. Then those calculations are repeated until some type of convergence level is achieved (or a specific number of steps).<\/p>\n<p>For this type of simulation, you would typically need 3 versions of your array. The first is a SEED array, containing the starting values. The second is your CURRENT array. The third is your calculated NEW array.<\/p>\n<p>The formula for the CURRENT array is =IF(started,_new,_seed).<\/p>\n<p>The SEED array is not necessary if you always start with the same initial values, or it could represent your current best guess at the solution. Your _seed array might just be an array the size of _new that is all 0s or 1s.<\/p>\n<h2 id=\"gauss-seidel\">Gauss-Seidel Updates<\/h2>\n<p>A Gauss-Seidel update might be described as \"update in place, using new values immediately.\" Although for this type of update, we are typically talking about a matrix or an array, our Counter at the beginning of the article is a extremely simple example.<\/p>\n<p>Another very simple 1D example might be a sequence that starts at an increasing larger number each step such as B2=IF(started,SEQUENCE(10,,B2+1),0)<\/p>\n<p>A good example of a 2D grid update is the <a href=\"https:\/\/www.vertex42.com\/edu\/heat-transfer.html\">Steady-State Heat Transfer simulation<\/a> shown in the list of examples above. Here we can actually see a \"Heat Map\" used for real heat!<\/p>\n<h2 id=\"mindist\">Minimum Distance Along a Path<\/h2>\n<p>One type of problem that comes up in Excel Esport challenges is solving a minimum distance along a path. This can be done using circular references and a Gauss-Seidel Update. It is an example of a non-rectangular \"in-place iterative convergence solution.\"<\/p>\n<p><img decoding=\"async\" class=\"center imgshadow\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/minimum-distance-along-path-circ-refs.png\" alt=\"Minimum Distance Along a Path\" width=\"585\" height=\"376\"><\/p>\n<p>In this example, we are calculating the minimum distance to travel from cell B2 to other cells, following a path (avoiding the blue walls). We start with cell B2 containing a fixed value of 0. Enter the following formula into cell C2:<\/p>\n<p>C2=LET(ortho,VSTACK(C1,C3,B2,D2),validate,IF(ISBLANK(ortho),9999,ortho),MIN(validate)+1).<\/p>\n<p>Then copy that cell to the other locations. After a few iterations, we have the solution.<\/p>\n<h2 id=\"loop\">Build in a Loop<\/h2>\n<p>If you don't want to your simulation running away from you, or you want to put your simulation into a loop, then you can define a period for your loop.<\/p>\n<p>Returning to example 1, let's add a value into cell D1 to represent a Period or Total Frames in our loop, such as 5 steps, so D1=5.<\/p>\n<p>Then in cell B1 enter =IF(A1,1+MOD(B1,D1),0)<\/p>\n<p>This will result in cell B1 having this pattern as you press F9: 1,2,3,4,5,1,2,3,4,5,...<\/p>\n<p>I could have used something like this for my <a href=\"https:\/\/www.vertex42.com\/edu\/four-bar-linkage.html\">Four-Bar Linkage<\/a> model where the crank angle varies between 0 and 360 degrees over time. However, the model already uses SIN and COS for the angle which results in the same result for 0 degrees as for 360 or 720 degrees. So, MOD was not needed.<\/p>\n<h2 id=\"sequence\">Left-to-Right, Top-to-Bottom<\/h2>\n<p>One important thing to remember when working with purposeful circular reference calculations is that Excel evaluates the spreadsheet <strong>left-to-right, top-to-bottom<\/strong> each iteration until values converge.<\/p>\n<p>If you experience some weird behavior with your model, especially as you are stepping one iteration at a time, it could very likely be a result of forgetting about the left-to-right, top-to-bottom sequence.<\/p>\n<h2>Conclusion<\/h2>\n<p>There are likely many or even infinite ways of using circular references. These are just some of the examples and building blocks that I have found in my own exploration of Excel. Hope you enjoyed this article.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Use Circular References in Excel on purpose, rather than just as an error signal. Includes some examples of animated models without VBA and explain some of the basic building blocks for working with circular references.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[78],"tags":[],"class_list":{"0":"post-3744","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-tips"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3744","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3744"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3744\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3744"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3744"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3744"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}