Does anyone know of any way to identify all volatile cells on a spreadsheet and/or in a workbook?

or, is there some way to see what cells will be re-calculated when another cell is changed?

  • I know there is a link checker in excel. I don't know how to normally access it, but it will popup if you have a circular loop in your sheet. So if Cell A1 refers to the value of B1, B1 refers to the value in C1 and C1 refers to the value of A1, it will detect a loop and come up with this checker that will show arrows and stuff. Not sure if this is what you are looking for, but it might be. – LPChip Aug 28 '19 at 20:16

The group Formula Auditing on the Formulas ribbon contains commands to trace precedents and dependents of a cell. A precedent is a cell that is referenced by the current cell. A dependent is a cell that references (is dependent) on the current cell. When clicking these commands, arrows will appear that lead from the current cell to the precedents/dependents.

It does not differentiate between volatile and non-volatile formulas, though.

For more detailed analysis you may want to use the Excel Add-in called Inquire, or 3rd party tools.

If you want to find all volatile functions, you may want to search the workbook for formulas using RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), and INFO().

For more details on volatile functions refer to http://www.decisionmodels.com/calcsecretsi.htm

  • I've tried Inquire, but there's nothing there for volatile functions. I've added a suggestion to the excel uservoice community to add something. – CBRF23 Aug 29 '19 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.