Calculation Issues

How to Prevent Excel NOW() Function from Updating Other Rows

Partner Editor Oct 9, 2026 870 views

Question details

The user needs to insert a static timestamp in Excel that records the exact time of data entry without updating automatically when other cells are modified.

How to Prevent the Excel NOW Function from Updating Other Rows

Product: Excel
Device & OS: not provided
Scenario: Setting up a data entry tracker where entering data in one column (e.g., column E) generates a permanent timestamp in an adjacent column.
Observed behavior: The NOW() formula is volatile, meaning it recalculates and changes all previously recorded timestamps in existing rows whenever any cell in the worksheet is edited.

Before you start

Verify whether your data is stored in a structured Excel Table (Insert > Table), as structured tables can automatically fill formulas down columns and may alter how circular reference formulas evaluate across existing rows.

Solution 1Recommended

Use Iterative Calculation with a Circular Reference Formula

This method forces Excel to evaluate the formula on itself, locking in the timestamp the moment the target cell is filled, preventing future recalculations.

By default, Excel blocks circular references (formulas that refer to their own cell). By enabling Iterative Calculation, you can safely use a circular reference to check if a timestamp already exists before applying the NOW() function.

Enable Iterative Calculation

Go to File > Options > Formulas. Under the 'Calculation options' section, check the box for 'Enable iterative calculation' and click OK.

Enter the Circular Formula

Assuming your data entry is in E2 and your timestamp is in F2, select cell F2 and enter the formula: =IF(E2<>"", IF(F2="", NOW(), F2), "")

Format as Date and Time

Right-click cell F2, select 'Format Cells', go to the 'Number' tab, choose 'Custom', and enter 'm/d/yyyy h:mm AM/PM'. Click OK.

Apply to Other Rows

Click the fill handle at the bottom-right corner of cell F2 and drag it down to apply the formula to the rest of the column.

Use Iterative Calculation with a Circular Reference Formula

Formula Logic: The formula translates to: If E2 is not empty, check if F2 is empty. If F2 is empty, insert the current time (NOW). If F2 already has a time, keep the existing time (F2), effectively freezing the timestamp.

Solution 2

Use a VBA Worksheet_Change Event

VBA completely removes the need for formulas by hardcoding the exact date and time into the cell as text/values whenever an edit is made.

Solution 3

Insert Static Timestamps Manually

If you only need to record timestamps occasionally, using keyboard shortcuts is the safest way to guarantee the time will never update.

Work Smarter with WPS Spreadsheet

Create Static Timestamps Effortlessly in WPS Office

WPS Spreadsheet fully supports iterative calculations, advanced formulas, and VBA macros, allowing you to build robust data entry trackers without worrying about accidental data changes.

1. Open your Workbook: Launch WPS Spreadsheet and open the file where you need to track timestamps.
2. Enable Iterative Calculation: Click 'Menu' in the top left, select 'Options', navigate to the 'Calculation' tab, and check 'Enable iterative calculation'.
3. Enter the Formula: Type your circular reference IF formula into the target cell and format it as a Date/Time value.

Fully compatible with Microsoft Excel (.xlsx) formats and circular reference formulas.Built-in support for VBA macros to automate complex data entry tasks.Free to use with a lightweight installation and lightning-fast performance.Familiar interface ensures zero learning curve when switching from other office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the NOW() function keep changing?

The NOW() function is classified as a 'volatile' function in spreadsheet software. This means it is designed to automatically recalculate and fetch the current system time whenever any cell in the entire workbook is modified or calculated.

Can I use the TODAY() function instead of NOW() for a static date?

No, the TODAY() function behaves identically to the NOW() function regarding recalculations. It is also a volatile function and will update to the current date whenever the workbook is opened or edited on a new day.

Why does my circular reference formula show 0 or an error?

If your circular reference formula returns a 0 or triggers a warning prompt, it means Iterative Calculation has not been enabled in your settings. You must enable it via File > Options > Formulas for the circular logic to function properly.

Will iterative calculation slow down my workbook?

If used sparingly for timestamp formulas, it will not noticeably affect performance. However, if you have thousands of complex circular formulas, it may cause slight delays during data entry as the software processes the iterations.

Excel & Data Problems