What are Volatile Functions in Excel

Struggling to Excel

Excel uses a three stage process to recalculate cells. It first draws a dependency tree, and then lists a calculation chain and then recalculates the cells. Check this link if you are interested in learning more about the recalculation process. Excel determines if a cell’s value depends on another, and decides the order in which they should be recalculated.

When asked to recalculate, Excel also decides to not recalculate a cell if the value of all of its “precedents” remain unchanged. Declaring a function as Volatile would force excel to recalculate the cell every time it recalculates. Consequently, any cell that is dependent on the volatile cell is also recalculated. Thus, using a large number of volatile functions increases the recalculation time of a spreadsheet.

Volatile functions are mostly functions that do not accept any arguments, for instance Today(), Now(), etc. Another flavour is the functions that use properties of any Visual Basic Object in their calculation: Indirect(), Offset(), etc. You can make a function volatile by adding ‘Application.Volatile’ to your code.

 

 

Published by Ejaz

I specialized in actuarial science in my masters’ program and I am determined to become a qualified actuary. I invest extra time and effort into creating intuitive spreadsheet applications that improve productivity at the workplace. I also enjoy spending my leisure time creating digital art; I publish my designs on my art in this blog named Designecdotes. Most of the people who have dramatically influenced my life were enthusiastic academicians. I believe that the greatest gift in life is having a nurturing mentor, and I would like to be that for someone. I owe it to my future mentees to be the best at what I do, and I consciously direct my endeavors at gathering what I consider essential traits of a seasoned mentor.

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: