How Can You Test for Volatility?

May 26, 2022 - by Bill Jelen

Problem: How do you know OFFSET is volatile and INDEX is not?

Strategy: Open a workbook. Make no changes. Close the workbook. Does Excel ask you to Save? If so, then something in the workbook is volatile.

Using this technique allowed Excel MVP Liam Bastick to discover that changing =SUMIF(A2:A100,”Yes”,B2:B100) to =SUMIF(A2:A100,”Yes”,B2) forces the SUMIF to become volatile.

If you use Conditional Formatting to turn cells red, the workbook is volatile. If you instead use a custom number format such as [Red][<6]0;0 you can change the font color to red without causing volatility.

This article is an excerpt from Power Excel With MrExcel

Title photo by Mark Fletcher-Brown on Unsplash