Slow Excel Calculations? Search for Volatile Functions
There are some functions in Excel called Volatile functions. If you have a large number of these functions in the workbook, they slow down Excel and the calculation process. Formulas become volatile when they use one of these volatile functions—or when they depend on a precedent cell that is volatile. A few hundred volatile formulas are no big deal. But when you have thousands of them, you will notice your computer lagging every time you hit Enter. And when you have tens of thousands of volatile formulas, the recalculation delay will be annoying.
Volatile functions recalculate every time a value is entered in a cell in any open workbook.
Download the Excel File to follow along
There is a number of actions that trigger worksheet volatility:
- Moving or renaming a worksheet
- Clicking a row or column divider
- Selecting an AutoFilter criteria
- Using Goal Seek
- Opening a .CSV file
- Using Conditional Formatting
- VBA statement Application.Volatile
Here is a list of Volatile functions
- CELL
- INDIRECT
- INFO
- NOW
- TODAY
- OFFSET
- RAND
- RANDARRAY
- RANDBETWEEN
In many cases of volatility delay, the problem is usually over-reliance on Conditional Formatting, INDIRECT, OFFSET or RAND. You can almost always use INDEX formulas in place of OFFSET. You can use a VBA macro to put random numbers in a range of cells instead of using RAND, or to build formulas from strings in cells rather than using INDIRECT. You can also use a VBA macro to highlight cells instead of using Conditional Formatting. The VBA code obviously only runs when you decide it needs to.
Let’s explore the volatile functions in detail.
CELL Function
The CELL function returns information about the formatting, location, or contents of a cell. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can use the following formula:
=IF(CELL(“type”,A1)=”v”,A1*2,0)
This formula calculates A1*2 only if cell A1 contains a numeric value and returns 0 if A1 contains text or is blank.
The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. See below for a full list of information available.
Purpose:
Get information about a cell
Return Value:
A text value.
Syntax:
=CELL (info_type, [reference])
Arguments
info_type – The type of information to return about the reference.
reference – [optional] The reference from which to extract information.
The following info_types can be used with the CELL function:
| Info_type | Description |
| address | returns the address of the first cell in reference (as text). |
| col | returns the column number of the first cell in reference. |
| color | returns the value 1 if the first cell in reference is formatted using color for negative values; or zero if not. |
| contents | returns the value of the upper-left cell in reference. Formulas are not returned. Instead, the result of the formula is returned. |
| filename | returns the file name and full path as text. If the worksheet that contains reference has not yet been saved, an empty string is returned. |
| format | returns a code that corresponds to the number format of the cell. See below for a list of number format codes. If the first cell in reference is formatted with color for values < 0, then “-” is appended to the code. If the cell is formatted with parentheses, returns “() – at the end of the code value. |
| parentheses | returns 1 if the first cell in reference is formatted with parentheses and 0 if not. |
| prefix | returns a text value that corresponds to the label prefix – of the cell: a single quotation mark (‘) if the cell text is left-aligned, a double quotation mark (“) if the cell text is right-aligned, a caret (^) if the cell text is centered text, a backslash () if the cell text is fill-aligned, and an empty string if the label prefix is anything else. |
| protect | returns 1 if the first cell in reference is locked or 0 if not. |
| row | returns the row number of the first cell in reference. |
| type | returns a text value that corresponds to the type of data in the first cell in reference: “b” for blank when the cell is empty, “l” for label if the cell contains a text constant, and “v” for value if the cell contains anything else. |
| width | returns the column width of the cell, rounded to the nearest integer. A unit of column width is equal to the width of one character in the default font size. |
The table below shows the text codes returned by CELL when “format” is used for info_type.
| Format code returned | Format code meaning |
| G | General |
| F0 | 0 |
| ,0 | #,##0 |
| F2 | 0 |
| ,2 | #,##0.00 |
| C0 | $#,##0_);($#,##0) |
| C0- | $#,##0_);[Red]($#,##0) |
| C2 | $#,##0.00_);($#,##0.00) |
| C2- | $#,##0.00_);[Red]($#,##0.00) |
| P0 | 0% |
| P2 | 0.00% |
| S2 | 0.00E+00 |
| G | # ?/? or # ??/?? |
| D1 | d-mmm-yy or dd-mmm-yy |
| D2 | d-mmm or dd-mmm |
| D3 | mmm-yy |
| D4 | m/d/yy or m/d/yy h:mm or mm/dd/yy |
| D5 | mm/dd |
| D6 | h:mm:ss AM/PM |
| D7 | h:mm AM/PM |
| D8 | h:mm:ss |
INDIRECT Function
The Excel INDIRECT function returns a valid reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell reference.
Create a reference from text
Return value
A valid worksheet reference.
Syntax
=INDIRECT (ref_text, [a1])
Arguments
ref_text – A reference supplied as text.
a1 – [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.
There are many situations where the ability to create a reference from text is useful, including:
- A formula that needs a variable sheet name
- A fixed reference that will not change even when rows or columns are deleted
- Creating numeric arrays with the ROW function in complex formulas
Note:INDIRECT is a volatile function and can cause performance problems in large or complex worksheets.
Variable worksheet name
In the example shown above, INDIRECT is set up to use a variable sheet name like this:
=INDIRECT(A5&”!A1″)
The formula in B5, copied down, returns the value in cell A1 in 3 sheets using the sheet names entered in column A. In other words, when a different sheet name is entered in column A, a new result is returned. With the same approach, you could allow a user to select a sheet name with a dropdown list, then construct a reference to the selected sheet with INDIRECT.
Generate numeric array
A more advanced use of INDIRECT is to create a numeric array with the ROW function like this:
ROW(INDIRECT(“1:10”)) // create {1;2;3;4;5;6;7;8;9;10}
INFO Function
Description
Returns information about the current operating environment.
Syntax
INFO(type_text)
The INFO function syntax has the following arguments:
Type_text Required. Text that specifies what type of information you want returned.
| Type_text | Returns |
| “directory” | Path of the current directory or folder. |
| “numfile” | Number of active worksheets in the open workbooks. |
| “origin” | Returns the absolute cell reference of the top and leftmost cell visible in the window, based on the current scrolling position, as text prepended with “$A:”. This value is intended for for Lotus 1-2-3 release 3.x compatibility. The actual value returned depends on the current reference style setting. Using D9 as an example, the return value would be:
|
| “osversion” | Current operating system version, as text. |
| “recalc” | Current recalculation mode; returns “Automatic” or “Manual”. |
| release”Version of Microsoft Excel, as text. | |
| “system” | Name of the operating environment: Macintosh = “mac” Windows = “pcdos” |
Examples
NOW Function
Description
Returns the serial number of the current date and time. If the cell format was General before the function was entered, Excel changes the cell format so that it matches the date and time format of your regional settings. You can change the date and time format for the cell by using the commands in the Number group of the Home tab on the Ribbon.
The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
Syntax
NOW()
The NOW function syntax has no arguments.
Remarks
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and June 1, 2021 is serial number 44348 because it is 44347 days after January 1, 1900.
Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. For example, the serial number 0.5 represents the time 12:00 noon.
The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.
Example
I created this exercise on 21 May 2021 at 8:48 PM
TODAY Function
Description
Returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations. If the cell format was General before the function was entered, Excel changes the cell format to Date. If you want to view the serial number, you must change the cell format to General or Number.
The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals. For example, if you know that someone was born in 1963, you might use the following formula to find that person’s age as of this year’s birthday:
= YEAR( TODAY())-1963
This formula uses the TODAY function as an argument for the YEAR function to obtain the current year, and then subtracts 1963, returning the person’s age.
Syntax
TODAY()
The TODAY function syntax has no arguments.
Note: Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and June 1, 2021 is serial number 44348 because it is 44347 days after January 1, 1900.
Examples
OFFSET Function
Description
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
Syntax
OFFSET(reference, rows, cols, [height], [width])
The OFFSET function syntax has the following arguments:
Reference Required. The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
Rows Required. The number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
Cols Required. The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
Height Optional. The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
Width Optional. The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
Remarks
- If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.
- If height or width is omitted, it is assumed to be the same height or width as reference.
OFFSET doesn’t actually move any cells or change the selection; it just returns a reference. OFFSET can be used with any function expecting a reference argument. For example, the formula SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a 3-row by 1-column range that is 1 row below and 2 columns to the right of cell C2.
Examples
RAND Function
Description
RAND returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
Syntax
=RAND()
The RAND function syntax has no arguments.
Remarks
To generate a random real number between a and b, use:
=RAND()*(b-a)+a
If you want to use RAND to generate a random number but don’t want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number. The formula will calculate and leave you with just a value.
Example
RANDBETWEEN Function
Description
Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.
Syntax
RANDBETWEEN(bottom, top)
The RANDBETWEEN function syntax has the following arguments:
Bottom Required. The smallest integer RANDBETWEEN will return.
Top Required. The largest integer RANDBETWEEN will return.
Example
RANDARRAY Function
The RANDARRAY function returns an array of random numbers. You can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.
In the following examples, we created an array that’s 5 rows tall by 3 columns wide. The first returns a random set of values between 0 and 1, which is RANDARRAY’s default behavior. The next returns a series of random decimal values between 1 and 100. Finally, the third example returns a series of random whole numbers between 1 and 100.
Syntax
=RANDARRAY([rows],[columns],[min],[max],[whole_number])
| Argument | Description |
| [rows] Optional | The number of rows to be returned |
| [columns] Optional | The number of columns to be returned |
| [min] Optional | The minimum number you would like returned |
| [max] Optional | The maximum number you would like returned |
| [whole_number] Optional | Return a whole number or a decimal value
|
Notes
- If you don’t input a row or column argument, RANDARRAY will return a single value between 0 and 1.
- If you don’t input a minimum or maximum value argument, RANDARRAY will default to 0 and 1 respectively.
- The minimum number argument must be less than the maximum number, otherwise RANDARRAY will return a #VALUE! error.
- If you don’t input a whole_number argument, RANDARRY will default to FALSE, or decimal value.
- The RANDARRAY function will return an array, which will spill if it’s the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you’re using structured references. For more details, see this article on spilled array behavior.
- RANDARRAY is different from the RAND function in that RAND does not return an array, so RAND would need to be copied to the entire range.
- An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the array for our RANDARRAY formula is range D2:F6,or 5 rows by 3 columns.
- Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.
