How to Insert a Countdown Timer in an Excel Worksheet

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

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.

Advertisement

Format the Timer Cell

Video of the Day

Step 1

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

Video of the Day

Step 2

Image Credit: Image courtesy of Microsoft

Right-click cell A1 and select Format Cells.

Advertisement

Step 3

Image Credit: Image courtesy of Microsoft

Select Time from the Category box and choose one of the hours:minutes:seconds formats. Click OK to set the cell format.

Advertisement

Create the Macro

Step 1

Image Credit: Image courtesy of Microsoft

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

Advertisement

Advertisement

Step 2

Image Credit: Image courtesy of Microsoft

Enter the following text in the Module1 window:

Advertisement

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

Advertisement

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

Image Credit: Image courtesy of Microsoft

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.

Advertisement

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.

Advertisement

Advertisement

references

Report an Issue

screenshot of the current page

Screenshot loading...