Quick Links

• Convert a Dynamic Function to a Fixed Value While Typing

• Backwardly Transform Cells Containing Dynamic Functions Into Fixed Values

• Disable Automatic Recalculations

Dynamic functions in Microsoft Excel are beneficial when displaying live data, as they update whenever a workbook is opened or when other computations are executed. Nevertheless, you can stabilize the outcomes they produce or temporarily halt their recalculations.

Examples of dynamic functions in Excel include NOW (which provides the current date and time), TODAY (which provides the current date), RAND (which generates a random number between 0 and 1), and RANDBETWEEN (which generates a random number within a range you define).

Related ##### All You Need to Understand About Dynamic Functions in Excel

They're not related to chemistry or hostility!

Posts 1 Convert a Dynamic Function to a Fixed Value While Typing -------------------------------------------------------------

One approach to stop a dynamic function from recalculating is to transform it into a static value at the moment of input.

For instance, the NOW function is employed here to show the present date and time:

<code>=NOW()</code>

Related ##### 13 Microsoft Excel Date and Time Functions You Should Be Aware Of

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

PostsNonetheless, after two minutes, when I modified another cell in the worksheet, the time changed.

To make the date and time stable from the beginning, enter the formula, and while still in Edit mode, press F9 > Enter.

What Are Date and Time Serial Numbers in Microsoft Excel, and Why Do They Exist? Date and time computations wouldn't be feasible without them.

PostsLastly, press Ctrl 1 (or right-click the cell and choose "Format Cells") to open the Format Cells dialog box, select "Custom," and in the Type field, specify how you wish the date and time to be shown.

Once you click "OK," the date and time will appear in the active cell, remaining unchanged until manually altered.

2 Backwardly Transform Cells Containing Dynamic Functions Into Fixed Values Another method to stabilize values generated by dynamic functions—and likely the technique I utilize most frequently—is to copy the result and paste it as a value. This is especially helpful if you aim to carry out this action on cells already holding dynamic functions without altering their values, or if you need to fix numerous cells simultaneously.

Consider you've created a series of random numbers ranging from 50 to 100 using the RANDBETWEEN function:

<code>=randbetween(50,100)</code>

Related ##### How to Create Random Numbers in Microsoft Excel

Employ a straightforward function, or gain more control with a generator tool.

PostsNevertheless, unless fixed, these random numbers will keep regenerating each time a computation is done elsewhere in the workbook, or if the file is closed and reopened.

Thus, to stabilize these values, first highlight the cells, and press Ctrl C (or Cmd C on a Mac).

Subsequently, with those cells still selected, press Ctrl Shift V (or Ctrl Cmd V on a Mac) to paste the cell values while making Excel disregard the formula used to produce them.

Now, the previously unfixed values have been transformed into static numbers.

3 Disable Automatic Recalculations

Another means to stop dynamic functions from recalculating automatically in Microsoft Excel is to adjust the calculation settings. Changing this setting won't modify how the functions operate, but it will impact how they are computed and how their result is displayed on your screen.

To accomplish this, click the "Calculation Options" button in the Formulas tab, and choose "Manual."

How to Manually Compute Only the Active Worksheet in Excel If your workbooks contain extensive worksheets with numerous formulas, recalculating the workbooks can take considerable time.

Posts---

Given that dynamic functions in Excel consistently recalculate, incorporating many in one workbook can notably influence your spreadsheet's performance. Therefore, employing the techniques outlined in this guide can enhance your Excel file's efficiency. Streamlining your formulas, compressing images, and minimizing excessive formatting are additional ways to ensure your workbooks run efficiently.

The above is the detailed content of 3 Ways to Stop Volatile Functions Recalculating in Microsoft Excel. For more information, please follow other related articles on the PHP Chinese website!