Circular references can cause major problems in Excel and other spreadsheets — so much so that most programs will sternly warn you off if you accidentally create one. But there can be occasions when you actually need them.
A circular reference is a formula in a spreadsheet which refers to the cell where the formula is located. For instance, if you type the formula =A1+1 into cell A1, it’s impossible to calculate, since the cell has to use itself as part of the calculation. If you enter this kind of formula, Excel pops up a warning to make sure you don’t inadvertently mess up your results or crash your spreadsheet.
However, as Excel expert Jan Karel Pieterse points out, there may be occasions when you do want a cell to refer to itself. One example is a cell which calculates when another cell had an entry made into it. If you use the circular formula =IF(A1="",TODAY(),B1) in cell B1, B1 will track when changes happen to A1.
While circular references can be useful in this way, they require careful planning if you don’t want to be constantly bombarded with warnings. Hit the post for a detailed discussion of the issues.
Leave a Reply
You must be logged in to post a comment.