How to Insert a Countdown Timer in an Excel Worksheet

By Scott Shpak

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

Step 1

In a new, open worksheet, navigate to cell A1. This will be the location of the example timer.

Step 2

Right-click cell A1 and select Format Cells.

Step 3

Selecting Hours/minutes/seconds Format.

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

Step 1

Opening a VBA Module.

Access the Visual Basic editing window by pressing ALT-F11. Click Insert from the menu bar and click Module.

Step 2

Entering VBA code

Enter the following text in the Module1 window:

Dim gCount As Date
Sub Timer()
gCount = Now + TimeValue("00:00:01")
Application.OnTime gCount, "EndMessage"
End Sub
Sub 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."
Exit Sub
End If
Call Timer
End Sub

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.

Step 3

Saving as a Macro Enabled Workbook

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

Step 1

Click on cell A1 and enter 0:3:0, then press Enter. This sets the time for 0 hours, 3 minutes and 0 seconds.

Step 2

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.

Tip

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.