How to Display a Continuously Updating Clock in Excel
Question details
The user needs a method to display the current time in a spreadsheet that updates continuously without requiring manual recalculations.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a real-time dashboard or tracking document that requires a live digital clock displayed directly on the screen.
- Observed behavior
- The built-in NOW() function only updates when the workbook is manually recalculated or when a cell is actively edited, rather than updating continuously in real-time.
Ensure that the Developer tab is enabled in your ribbon and that your spreadsheet will be saved as a Macro-Enabled Workbook (.xlsm) so the underlying code can execute.
Use VBA Application.OnTime to Refresh the Workbook
Implement a VBA macro to trigger a recalculation at a regular interval (like every minute or second) to create a live updating clock.
Because Excel's native time functions are static until a calculation is triggered, VBA is required to automate this process. The Application.OnTime method schedules a macro to run at a specific future time, creating a continuous loop.
Select the cell where you want the clock to appear, type =NOW(), and press Enter. Apply a time format to the cell by right-clicking it, selecting Format Cells, and choosing your preferred Time format.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor. Alternatively, go to the Developer tab on the ribbon and click on 'Visual Basic'.
Right-click on your workbook name in the left-hand Project window, select 'Insert', and click 'Module'. In the new module, write a subroutine that uses Application.OnTime to call the Calculate method on a recurring schedule.
Double-click on 'ThisWorkbook' in the Project window. Select 'Workbook' from the top-left dropdown and 'Open' from the top-right dropdown. Call your newly created timer macro here so it starts automatically when the file opens.
Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown. If you do not save it in this format, the VBA code will be lost upon closing.
Create Live Updating Clocks in WPS Spreadsheet
WPS Office provides robust support for VBA and Macros. You can easily set up an automated timer to refresh functions like NOW() just as you would in Microsoft Excel, completely free.
- 1. Enter the Time Formula: Open WPS Spreadsheet, select a blank cell, and type =NOW() to generate the current time.
- 2. Access the VBA Editor: Navigate to the Developer tab on the top ribbon and click on the 'VBA Editor' button.
- 3. Insert the Macro Code: Insert a new module in the editor and write your Application.OnTime script to automate the sheet calculation continuously.
- 4. Save Your Work: Save the document as a Macro-Enabled Workbook (.xlsm) to preserve the live clock functionality for future use.
Frequently Asked Questions
Can I make a continuously updating clock without using macros?
No, Excel does not have a built-in feature to continuously update the time natively. Without macros, you will need to manually press the F9 key to recalculate the sheet and update the time displayed by the NOW() function.
Why does my live clock pause when I edit a cell?
The application single-threads its calculation and user interface. Whenever a cell is in 'Edit Mode', all VBA macros and background calculations are temporarily paused until you finish editing and press Enter.
Will leaving a live clock running slow down my workbook?
A timer that recalculates the entire workbook every single second can consume processing power and may cause lag if your workbook contains complex formulas or large datasets. It is often better to update the clock every minute instead to optimize performance.
