Everything You Need to Know About Volatile Functions in Excel

Of the hundreds of Excel functions, there a few that are known as volatile. In this article, we'll look at what volatile functions are, their pros and cons, how to deal with them if they're causing issues in your spreadsheet, and some of the more straightforward examples.

A volatile function is a function that recalculates every time a change is made to an Excel worksheet, or whenever an existing workbook is reopened. Going one step further, when a volatile function is recalculated, any cell dependent on that volatile function is also recalculated, and so is any cell that depends on that dependent—and so on!

In this example, we have used the NOW function, which shows the current date and time (more on this later). Any time a change is made within the spreadsheet, Excel re-evaluates that cell to see whether it needs to be updated with a new date or time.

Whichever volatile function you use, what you see in the cell can change, even if the function's arguments remain the same.

The greatest benefit—and also the primary function—of Excel's volatile functions is that they provide up-to-date information. This is useful if you're projecting a live spreadsheet onto a screen for an audience to see, like in a business meeting. It's also a handy tool if you have an Excel dashboard and want to monitor the situation as time passes, or if you're involved in finance and want to keep track of market changes.

Volatile functions get some bad press in many Excel tutorials and articles, mostly due to the way they impact the spreadsheet's performance. Adding a volatile function to a small spreadsheet is unlikely to result in any major issues. However, in worksheets with sizable data sets and many formulas, a single volatile function can slow the program's performance significantly.

In the worst-case scenario, it can make Excel crash, especially if you're using a low-memory computer, and you have lots of cells depending on the volatile function. Even on a powerful computer, using lots of volatile functions in one spreadsheet can make Excel run slowly.

Even Microsoft itself recommends you reduce the number of volatile functions you use to speed up your spreadsheet. More specifically, it suggests using alternative methods to get the same or similar outcomes, such as INDEX instead of OFFSET, or CHOOSE instead of INDIRECT.

If, as mentioned above, a volatile function is causing performance issues, there are several ways to stop it from calculating automatically each time you make any changes to your workbook. First, we'll look at how to affect the whole workbook's volatile functions, and then we'll look at other ways to turn individual volatile functions into regular functions.

The first option is to change the calculation settings in Excel. This won't change how the functions themselves work, but it will affect when they are calculated. To do this, click the "Calculation Options" button in the Formulas tab on the ribbon, and choose "Manual."

This changed setting instantly applies to all spreadsheets you already have open, as well as all workbooks you open subsequently, until you change it back. Also, cells containing volatile functions that have not updated automatically due to this change will be reformatted with strikethrough (also known as a stale value). This is to remind you that the information in that cell is not up-to-date.

Now, the volatile functions will only be calculated when you click either "Calculate Now" to calculate the entire workbook and all open workbooks, or "Calculate Sheet" to calculate the active sheet. Both options can be found in the Formula tab on the ribbon. You can also use the F9 Excel keyboard shortcut to perform the same action as Calculate Now, or Shift+F9 to perform the same action as Calculate Sheet.

If you have a volatile time or date function in your spreadsheet, meaning it updates automatically with your computer's time and date, you can turn it into a frozen time or date.

To do this, double-click the cell containing the function, place your cursor on the part of the formula containing the time or date function, and press F9. This will then turn the time or date into a serial number, and when you press Enter, it'll convert that serial number into a static time or date.

Remember, though, that this won't update when the time or date changes, and it won't adopt the strikethrough formatting, as it's no longer a volatile function.

Copy and Paste the Cell Value

Another way to turn a volatile function into a standard value is to copy the cell containing the result of the volatile function, and paste the value only in the same cell.

For example, if you have a cell containing a random number generated through a volatile function, select the cell, press Ctrl+C, and then press Ctrl+Shift+V in the same cell. This will paste the volatile random number as a fixed random number, and because the cell is already correctly formatted, the value will appear exactly as it did when it was volatile.

Here are some of Excel's most basic volatile functions:

Excel also has some more advanced volatile functions, such as OFFSET and INDIRECT. To use these, head to Excel, click the "fx" icon next to the formula bar, and use Excel's Insert Function guide to give them a try.

As well as limiting the number of volatile functions you use in Excel to ensure your workbook's performance, there are many other ways to speed up your spreadsheet, including reducing the formatting, simplifying your formulas, and compressing any images.