Show
PerfectXL Risk EliminatorHow to find (hidden) circular references in your spreadsheet(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references. In this video we’ll show you how PerfectXL detects any circular reference in your spreadsheet. IntroductionMany spreadsheet developers will recognize this message of Excel: Excel detects a circular reference when a chain of calculations visits the same cell more than once. CaseI received a spreadsheet from a colleague once. Everything looked perfectly alright, until I changed one of the input values. The nasty message popped up complaining about circular references. When I called my colleague, he told me I must have messed with his formulas. So how did this happen? First of all, let me explain what circular references are and how Excel handles them. Types of circular referencesSimplistically speaking there are only two types of circular references to discern: Deliberate circular references Intermezzo: Excel’s circular reference detection mechanismExcel is capable of detecting circular reference chains. As soon as you click OK on the Circular reference warning dialog, you can find the first circle Excel detects in the Excel ribbon: There is a snag however. If the current values cause the calculation to take a different path, it may very well be that Excel does not warn about that circular reference anymore. If for example any cell within the circle contains an IF function which uses different cells in its TRUE argument than it uses in its FALSE argument, it will depend on the result of the TEST argument whether or not the actual calculation will form a circle (This technique is used on purpose here). The third and nasty type of circular referenceSo, this means there is actually a third circular reference category: Hidden circular references. Let’s have a look at these three types. Deliberate circular referencesThese are the realm of the experienced Excel developer, the Excel whizz kid who (thinks he) knows what he is doing. Some people deliberately create (sets of) circular reference chains to have a calculation model do iterative calculations. Examples may include models of a chemical facility where (part of) an input stream of a process is a recycled output stream of the same process. Distillation plants often have such streams and if you want to calculate a model of such plant, using a circular reference may be a good way to model your process. If -and only if- you are convinced you really need circular references to solve your problem, meticulously document your model, stating clearly what your intentions are and how the model functions. Especially important: Devise a way to clearly indicate the circular reference chain. Accidental circular referencesMore often, people inadvertently create a circular reference, for example by having a SUM function which includes the cell the SUM function itself resides in. Example: In cell A10, you write this function: =SUM(A1:A10) Of course, this formula is intended to sum the values in the cells above the cell with the sum function, so the argument for the SUM function should be A1:A9. Suppose you would like to record the date someone entered a value into column A. One frequently described method is to create a self-referencing cell with a formula like this one: So as long as cell A1 is empty, the condition in the IF function in cell B1 evaluates TRUE and Excel only calculates the TODAY function and places that result into cell B1. So, whether or not Excel warns you about a circular reference depends on input values! As you can imagine, these hidden circular references make it very hard to detect potential problems in your Excel file. There is a way my colleague could have prevented his spreadsheet model from displaying this annoying surprise message. Finding Circular ReferencesPerfectXL is capable of finding all these types of circular references even before Excel itself highlights them. To find them, analyze your model and click the “Risk Overview” button: So as long as cell A1 is empty, the condition in the IF function in cell B1 evaluates TRUE and Excel only calculates the TODAY function and places that result into cell B1. So, whether or not Excel warns you about a circular reference depends on input values! As you can imagine, these hidden circular references make it very hard to detect potential problems in your Excel file. There is a way my colleague could have prevented his spreadsheet model from displaying this annoying surprise message. When you click it, all structure related issues will appear in this list. Currently, there is only one: a circular reference. Clicking on one of the entries in the subsequently opened table will show more details about the issue: As you can see this is one of those calculation-driven circular reference chains. Depending on the result of the MATCH function, the formula in cell C7 either points to cell C8 or cell C9. Given that cell C8 contains the formula =C7, only if the MATCH function returns 1 as its result will Excel warn you. PerfectXL will warn you regardless of the intermediate results. It finds all potential circular references, even the hidden ones. Text & narration: Jan Karel Pieterse, Microsoft Excel MVP since 2002, JKP Application Development Services
Read more about risks in Excel filesArray functions are past timesLong ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them. Avoid long formulasEven Thomas Jefferson, 19th century president of the United States, remarked: “The most valuable of all talents is that of never using two words when one will do.” Calculate formulas only onceYou might need to run a calculation for inflation, the result of which is the needed in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Do not merge cellsIt is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous! Don't neglect Excel errorsIt’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet. (Hidden) circular references(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references. How to keep formulas readableSometimes a formula can spill over onto 2 or 3 lines making the standard 1 line formula bar too small to read. So how can you make formulas readable? Iterative calculation, friend or foe?Among the infinite number of settings in Excel, one innocent looking option fundamentally changes the way Excel calculates formula results. It goes by the name Iterative calculation. This little known feature has its uses, but it doesn’t come without risk. Keep a formula close to its inputPlace a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else. Never use hard coded numbers in formulasThe use of hard coded numbers is a bad idea. A future user will not know where the number came from, and hard coded numbers don’t change automatically, and thus might be overlooked when a change is made. Prefer INDEX & MATCH over VLOOKUPThe VLOOKUP and the combination of INDEX and MATCH are well known, and to our great frustration, VLOOKUP is used a lot more often. The combination of INDEX and MATCH is less error prone and a lot more efficient. ReportsReporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel. Risk inspectionsOur software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here. Risks & suggestionsTo be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet. Spreadsheet risk detection
Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation. VBA risk detectionExcel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files. How can PerfectXL help you?Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours. How do you remove Microsoft Excel Cannot calculate a formula there is a circular reference?To fix the problem, you can move the formula to another cell. Press Ctrl+X to cut the formula, select another cell, and press Ctrl+V to paste it. You can also try one of these techniques: If you just entered a formula, start with that cell and check to see if you refer to the cell itself.
How do you fix there is a circular reference in an open workbook?How to remove circular references in Excel?. Go to Formulas > Trace Precedents. ... . Select “Trace Dependents” to display the cells that contain the formula referencing the selected cell. ... . To find out exactly how each cell is referencing another in the formula, select “Show Formulas” from the “Formulas” tab.. How do I resolve a circular reference in Excel?Go to the Formulas tab and click on the Error Checking drop-down menu.. Select Circular References from the drop-down menu.. Here Excel will show you all the circular references that are in the worksheet.. Click on whichever circular reference you want and it will take you to that particular cell to solve the issue.. Why does Excel keep saying I have circular references?If you see a circular reference warning in Excel, it usually points to a formula in a cell that, in some way, refers back to itself. As the formula can break its own result, this creates a circular reference.
|