How to Insert a Countdown Timer in an Excel Worksheet
A how-to guide to creating a simple VBA macro that adds a single-cell timer to an Excel worksheet. This method uses direct editing rather than macro recording.
On its own, Excel doesn't have a function to include a countdown timer. However, with a programming language called Visual Basic for Applications, it's possible to write a simple macro to perform the countdown function. This article shows how to create a macro that enables a timer to count down up to 12 hours, suitable for time management within a worksheet.
Format the Timer Cell
In a new, open worksheet, navigate to cell A1. This will be the location of the example timer.
Right-click cell A1 and select Format Cells.
Select Time from the Category box and choose one of the hours:minutes:seconds formats. Click OK to set the cell format.
Create the Macro
Access the Visual Basic editing window by pressing ALT-F11. Click Insert from the menu bar and click Module.
Enter the following text in the Module1 window:
Dim gCount As Date
gCount = Now + TimeValue("00:00:01")
Application.OnTime gCount, "EndMessage"
Dim xRng As Range
Set xRng = Application.ActiveSheet.Range("A1")
xRng.Value = xRng.Value - TimeSerial(0, 0, 1)
If xRng.Value <= 0 Then
MsgBox "Countdown complete."
You can copy and paste the code text above, or you can type it, line-by-line, into the Module1 window. The VBA editor applies formatting as needed.
Minimize the VBA window by clicking the - symbol in the upper right corner. Click File on the ribbon and Save As. Select a location to save your file. In the Save As window, give your workbook a name and select Excel Macro-Enabled Workbook (*.xlsm) from the Save as type drop-down list. Click Save and return to the workbook.
Using the Timer
Click on cell A1 and enter 0:3:0, then press Enter. This sets the time for 0 hours, 3 minutes and 0 seconds.
Click the View tab on the ribbon and click Macros. Select Timer from the Macro list and click Run. Your timer cell will begin counting down to zero, when a Countdown Complete dialog opens. Click OK to return to your work.
You don't have to use cell A1. Place your timer anywhere and change the cell reference in line 8 of the timer code from A1 to whichever cell you use.