You can easily create a stopwatch in excel with the help of VBA and simple excel functionalities.

In this blog, you will learn about creating a countdown timers or a stopwatch inside a excel worksheet

Lets dive right in

How to Stopwatch in Excel… Very Useful

Creating a Timer/Stop watch in Excel

Professionals who are used to organizing their lives in Excel at some point wish Excel could include a timer to help keep track of common tasks. This article shows how to create a macro that enables a Timer/Stop watch, suitable for time management within a worksheet.

You can download the Finished File with the code by clicking on the button

Preparing for Creating the Timer

In a New Worksheet, select columns A to F and adjust the columns width to 60 pixels by dragging. (alternatively go to Home ► Format ►Column Width)

Select range C6:E7 ► on the Home Tab in the Alignment group click on “Merge and Center”

Add a thick border to the merged cells.

Hit the shortcut CTRL + 1 ► Click on the Border Tab

Select a Thick Border ► Select a Blue Color ►Click on Outline ► OK

Set the Font to Impact, 28 pt, black color

Apply Time Format from the Number Tab of the Format cell dialog box

Type 0:00:00 in cell C6 (keep an eye on the Formula Bar).

Now we need to bring 3 pictures 9or Shapes) that will be used as buttons for triggering, pausing and stopping the timer. Position them on top of cell C6. Resize and align them.

Rename the worksheet “Timer” but since it’s the first sheet to the left, I can refer to it as Sheets(1). #1 is the index number.

Before we start writing a simple code, we need to understand 2 terms:

DoEvents is an Excel VBA command that temporarily pauses the execution of the macro to refresh the screen and execute any pending events in Excel. It can allow the user to interact with the Excel spreadsheet while the macro is running on the very same workbook!

DATEADD is a date and time function in VBA and this function has an output value as a date, this function takes input as a format of date and adds it to return a new date, the syntax for this function takes three arguments Interval, Number and the Date.

DateAdd (interval, number, date)

Parameters or Arguments

Interval

The time/date interval that you wish to add. It can be one of the following values:

Number

The number of intervals that you wish to add.

Date

The date to which the interval should be added.

Returns

The DATEADD function returns a date value.

Now we are ready to switch to VBA and write the code.

To switch to the Visual Basic Editor, we hit ALT+F11

In the Visual Basic Editor, create a module by clicking on the Insert menu and select Module. Alternatively, you can use the shortcut ALT+I+M

We start by declaring a variable that will be used in multiple subroutines, so we write it at the top of the module. We use the keyword “Dim”, we name it “a” and we set the data type to Boolean (True/False)

Dim a as Boolean

When this variable is set to TRUE, we want the counter to run, while if it is set to FALSE, we want to Stop or Reset the timer.

The First code to start the Timer is the following:

(If you copy and paste customize the Sheet and Cell reference)

Sub StartTimer()

a = True

Do While a

Application.Wait (Now + #12:00:01 AM#)

DoEvents

Sheets(1).Cells(6, “C”) = Format(DateAdd(“s”, 1, Sheets(1).Cells(6, “C”)), “hh:mm:ss”)

Loop

End Sub

What does this code mean?

1. I set the variable “a” to TRUE = Run the Timer
2. I use the statement Do While… Loop to loop over instructions so long as “a” is TRUE
3. I set a wait time of one second before moving to the next line
4. DoEvents ► enables me to see the Timer update in the worksheet and work in Excel while the code is running.
5. Then I am adding one second to the existing value in cell C6 using the DateAdd VBA function and we format it as “hh:mm:ss”
6. Then we repeat: wait a second and Add a second

To Stop the Timer, we create another subroutine that changes the variable to false

Sub PauseTimer()

a = False

End Sub

To Reset the Timer to zero we create a third subroutine that changes the variable to false and sets the value of C6 to 00:00:00 (If you copy and paste customize the Sheet and Cell reference)

Sub ResetTimer()

a = False

Sheets(1).Cells(6, “C”) = “00:00:00”

End Sub

After writing the 3 subroutines, we switch back to Excel ALT +F11

We’ll attach each subroutine to one of the shapes by Right Clicking and from the right click menu select Assign Macro then pick up the corresponding Macro

Now test by clicking on each shape: Start – Stop – Reset.

You can download the Finished File with the code by clicking on the button

Creating a Countdown timer in Excel

On its own, Excel doesn’t have a function to include a countdown timer. However, with Visual Basic for Applications, it’s possible to write a simple code to perform the countdown functionality. This article shows how to create a macro that enables a timer to count down, suitable for time management within a worksheet.

We can create a new sheet and name it CD (Countdown)

Create a setup identical to the one we created for the Timer.

In Cell C6 the number will be 00:15:00 which means will be counting from 15 minutes down. You can adjust the number as needed.

In the visual basic editor, copy the previous codes and create a new module and paste the codes: we’ll make simple modifications to the code as follows:

1. Name the Variable “b”
2. Change the sheet name
3. Change the subroutine names (you can’t use the same exact names)
4. Change the number in the DateAdd function to negative 1 (to decrease the time)
5. Change the Value in the Reset code to “00:15:00”

The 3 codes will read:

Dim b As Boolean

Sub StartCountDown()

b = True

Do While b

Application.Wait (Now + #12:00:01 AM#)

DoEvents

Sheets(“CD”).Cells(6, “C”) = Format(DateAdd(“s”, -1, Sheets(“CD”).Cells(6, “C”)), “hh:mm:ss”)

Loop

End Sub

Sub PauseCountDown()

b = False

End Sub

Sub ResetCountDown()

b = False

Sheets(“CD”).Cells(6, “C”) = “00:15:00”

End Sub

Close the Visual Basic editor and back to Excel, repeat the process of assigning macros to shapes.

Test your 3 countdown macros

Save the file as a macro enabled Excel file .xlsm

Enjoy the functionality of the Timer/ Stop watch and the Countdown timer.

Our friends at extend office also have a great article regarding the same topic how to make a stopwatch in excel. You can visit their article to also learn more about it

With a little bit more doing, you can convert this timer to a creative digital clock, resize it and keep it to the side of your Excel window. Learn how to do that by Watching the tutorial on my YouTube Channel. Make sure you subscribe to my channel to be notified when new tutorials are released.