How to Use Copilot in Excel to Audit Volatile Functions Like NOW and TODAY

Volatile functions like NOW and TODAY recalculate every time Excel recalculates the workbook. This can slow down large spreadsheets and cause unexpected results when you share files with colleagues. Many users struggle to locate every instance of these functions across complex worksheets. Microsoft Copilot in Excel can find and summarize all volatile functions in seconds. This article explains how to use Copilot to audit volatile functions and reduce their impact on workbook performance.

What Are Volatile Functions and Why They Cause Problems

A volatile function in Excel recalculates every time the workbook recalculates, even if none of its input cells have changed. Common volatile functions include NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, and CELL. When you open a workbook, edit any cell, or run a macro, Excel triggers a full recalculation. Every volatile function recalculates at that moment.

In a small workbook with a few volatile functions, the performance impact is minimal. In a large workbook with hundreds of formulas, volatile functions can cause noticeable lag. Each recalculation can take several seconds or more. This becomes a problem when you share files with remote team members or use Excel in real-time collaboration.

NOW and TODAY: The Most Common Volatile Functions

NOW returns the current date and time. TODAY returns only the current date. Both functions pull the system clock value at the moment of recalculation. If you use NOW or TODAY in a formula that feeds other calculations, those dependent cells also recalculate every time. This cascading effect multiplies the performance cost.

Many users insert NOW or TODAY to capture a timestamp for data entry or to calculate age or duration. The problem is that the timestamp changes every time the workbook recalculates. For audit purposes, you need to know exactly where these functions live and how many cells depend on them.

Steps to Audit Volatile Functions Using Copilot

Copilot in Excel for Microsoft 365 can find and summarize volatile functions across your workbook. You do not need to write any formulas or use the Find dialog. The following steps assume you have an active Microsoft 365 Copilot license and the Copilot pane is visible on the right side of Excel.

1. Open the Copilot pane
   Click the Copilot icon on the Home tab of the ribbon. The pane opens on the right side of the Excel window. If the icon is not visible, go to View > Show > Copilot and enable it.
2. Select the range to audit
   Click any cell inside the worksheet you want to scan. Copilot scans the entire active sheet by default. If you only want to audit a specific range, select that range before typing your request.
3. Type your audit request
   In the Copilot text box, type: Find all volatile functions on this sheet. Press Enter or click the Send arrow.
4. Review the results
   Copilot displays a list of cells containing volatile functions. Each result includes the cell address, the function name, and the current value. You can click any cell reference to jump directly to that cell.
5. Export the audit report
   To save the list, click the Copy button in the Copilot pane. Paste the results into a new worksheet or a text file for documentation.

Audit the Entire Workbook in One Request

To scan all sheets at once, type: Find all volatile functions in this workbook. Copilot iterates through every sheet and returns a consolidated list. This request may take longer on workbooks with dozens of sheets. Copilot shows a progress indicator while scanning.

Request a Summary of Volatile Function Impact

For a deeper audit, type: Show me which volatile functions cause the most recalculations. Copilot analyzes the dependency tree and ranks volatile functions by the number of dependent cells. This helps you prioritize which functions to replace first.

Common Issues When Auditing Volatile Functions

Copilot Does Not List All Volatile Functions

Copilot scans formulas in cells but not in named ranges, conditional formatting rules, or data validation formulas. If you have volatile functions inside conditional formatting, you must check those manually. Open Conditional Formatting Rules Manager on the Home tab and review each rule for references to NOW, TODAY, or other volatile functions.

Copilot Returns “No volatile functions found” When You Know They Exist

This usually happens when volatile functions are hidden inside nested formulas or array formulas. Try a more specific request: Find cells containing the TODAY function. If that fails, check whether the worksheet is in manual calculation mode. Go to Formulas > Calculation Options and set it to Automatic. Copilot only scans formulas that are actively calculated.

Volatile Functions in PivotTable Calculated Fields

Copilot does not scan PivotTable calculated fields. To find volatile functions there, click inside the PivotTable, go to PivotTable Analyze > Fields, Items, and Sets > Calculated Field. Review each formula for volatile functions. Replace them with static values if possible.

Copilot Pro vs Copilot for Microsoft 365: Key Differences for Excel Auditing

Item	Copilot Pro	Copilot for Microsoft 365
Availability	Personal Microsoft 365 subscriptions (Family, Personal)	Business and Enterprise plans (Business Basic, Standard, Premium, E3, E5)
Excel audit features	Can find volatile functions on the active sheet only	Can scan entire workbook across all sheets
Dependency analysis	Lists cells but does not show dependent count	Shows dependency tree and ranks by impact
Export options	Copy results manually	Copy results or export to a new sheet
Conditional formatting audit	Not supported	Not supported

Both Copilot Pro and Copilot for Microsoft 365 can identify volatile functions. The business version provides deeper analysis and workbook-wide scanning. For audit purposes, the business version saves more time on large workbooks.

After you identify volatile functions, consider replacing them with static values. For timestamps, use the keyboard shortcut Ctrl+Shift+; to insert the current time and Ctrl+; to insert the current date as static text. For random numbers, use a static random number generator like RANDBETWEEN with a fixed seed, then paste the values over the formulas. This stops all recalculations and stabilizes workbook performance.