Volatile functions in Microsoft Excel are useful when you want to display real-time data, as they recalculate whenever a workbook is reopened, or when any other calculations are performed. However, you can fix the results they return, or temporarily prevent them from recalculating.

Examples of volatile functions in Excel include NOW (which returns the current date and time), TODAY (which returns the current date), RAND (which returns a random number between 0 and 1), and RANDBETWEEN (which returns a random number between two values you specify).

Related

Everything You Need to Know About Volatile Functions in Excel

They’ve got nothing to do with chemistry or hostility!

1

Convert a Volatile Function to a Static Value as You Type

One way to stop a volatile function recalculating is to convert it to a fixed value at the point of entry.

In this example, the NOW function is used to return the current date and time:

=NOW()

Related

13 Microsoft Excel Date and Time Functions You Should Know

Why use a calendar or a clock when Excel can track it all for you?

However, two minutes later, when I edited another cell in the worksheet, the time updated.

To make the date and time static from the outset, type the formula, and with Enter mode still activated, press F9 > Enter.

At first, you may see what appears to be a random decimalized number, but in fact, this is a serial number representing the date and time as they were when you pressed Enter.

Related

What Are Date and Time Serial Numbers in Microsoft Excel, and Why Do They Exist?

Date and time calculations would be impossible without them.

Finally, press Ctrl+1 (or right-click the cell and click “Format Cells”) to launch the Format Cells dialog box, click “Custom,” and in the Type field, state how you want the date and time to be displayed.

When you click “OK,” the date and time will appear in the active cell, and they will remain fixed until you change them manually.

In a second example, let’s say you want to generate a random number between 0 and 1. To do this, you type:

=RAND()

into cell A1, and press Enter.

However, because RAND is a volatile function, a new random number is generated each time a calculation is performed elsewhere in the workbook.

Instead, before you press Enter, press F9, and the RAND function will be replaced by a fixed value in both the cell and the formula bar.

2

Retrospectively Convert Cells With Volatile Functions to Static Values

Another way to fix values produced by volatile functions—and probably the method I use the most—is to copy the result and paste it as a value. This is particularly handy if you want to perform this action on cells already containing volatile functions without changing their values, or if you need to fix many cells at the same time.

Imagine you’ve produced a set of random numbers between 50 and 100 using the RANDBETWEEN function:

=randbetween(50,100)

Related

How to Generate Random Numbers in Microsoft Excel

Use a simple function, or get more advanced control with a generator tool.

However, left as they are, these random numbers will continue to regenerate each time a calculation is performed elsewhere in the workbook, or if you close and reopen the file.

So, to fix these values, first select the cells, and press Ctrl+C (or Cmd+C on a Mac).

Next, with those cells still selected, press Ctrl+Shift+V (or Ctrl+Cmd+V on a Mac) to paste the cell values while forcing Excel to ignore the formula used to generate them.

Now, the previously unfixed values have been converted to static numbers.

3

Turn Off Automatic Calculations

Another way to prevent volatile functions from recalculating automatically in Microsoft Excel is to change the calculation settings. Making this alteration won’t change how the functions work, but it will affect how they are calculated and how their result is displayed on your screen.

To do this, click the “Calculation Options” button in the Formulas tab, and click “Manual.”

Now, when a calculation is performed in the workbook, any cells containing volatile functions that have not updated automatically due to this change (also known as a stale value) will be reformatted with strikethrough. This is to remind you that the information in those cells is not up to date.

Now, any volatile functions will only be calculated when:

• You save the workbook manually or press Ctrl+S,
• You click “Calculate Now” or press F9 in Ready mode (this affects all calculations in the entire workbook and all open workbooks), or
• You click “Calculate Sheet” or press Shift+F9 in Ready mode (this affects all calculations in the active sheet only).

Related

How to Manually Calculate Only the Active Worksheet in Excel

If you have large workbooks with a lot of formulas on the worksheets, recalculating the workbooks can take a long time.

Since volatile functions in Excel continually recalculate, using many in one workbook can significantly affect your spreadsheet’s performance. So, using the methods in this guide can help speed up your Excel file. Simplifying your formulas, compressing images, and avoiding too much formatting are other ways to ensure your workbooks run smoothly.