How to Create a Stopwatch in Excel (with Easy Steps)

Step 1 – Display Stopwatch Clock in Excel

  • Select the desired cell range, e.g., B4:C4.
  • Navigate to the Home
  • Choose the Merge and Center option from the Alignment group and select Middle and Center align.
  • From the Font group, select the fill color as Blue Lighter 80%. Select Bold and All Border.

Display Stopwatch Clock

  • From the Home tab, click the Expand icon from the Number group.

Display Stopwatch Clock in Excel

  • The Format Cells will appear.
  • From the Format Cells window, select Custom and select h:mm:ss from the options in the Type box.
  • Click OK.

  • Select B4 cell and enter 0:00:00.
  • Press Enter. A stopwatch will appear on the sheet.

Output of Displaying Stopwatch Clock

Read More: How to Add Command Button Programmatically with Excel VBA 


Step 2 – Establish VBA Macro to Start Stopwatch

  • Go to the Developer tab and click on Visual Basic.

Establish VBA Macro to Start Stopwatch

  • Click Insert and select Module.

Establish VBA Macro to Create a Stopwatch in Excel

  • Enter the following code in the Module Box.
Option Explicit
Public countDown As Date
Sub StartTimer()
countDown = Now + TimeValue("00:00:01")
Range("B4") = Range("B4") + TimeValue("00:00:01")
Application.OnTime countDown, "StartTimer"
End Sub
  • Enter the Procedure Name in the StartTimer. The Stopwatch App will start up when you run this Macro.
  • Press Ctrl + S.

Establish VBA Macro to Create a Stopwatch in Excel


Step 3 – Generate Another Macro to Reset Time-Tracker

  • Open the Module.
  • Enter the following code in the Module Box.
Sub ResetTimer()
Range("B4") = TimeValue("00:00:0")
End Sub
  • ResetTimer is the Procedure Name. This Macro can reset the Clock.
  • Press Ctrl + S.

Generate Another Macro to Reset Stopwatch in Excel


Step 4 – Build Last Macro to STOP Stopwatch in Excel

  • Open the Module.
  • Enter the following code into the Module Box.
Sub StopTimer()
Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
End Sub
  • This Macro is to stop the Time-tracker App.
  • Press Ctrl + S.

Build Last Macro to STOP Stopwatch in Excel


Step 5 – Implement Buttons for Stopwatch

  • Go to the Developer tab.
  • From the Controls group, click on the Insert icon.

Implement Buttons for Stopwatch

  • In Form Controls, choose the following Button symbol.

Implement Buttons for Stopwatch in Excel

  • Insert the rectangle symbol right below cell B4.
  • Choose the StartTimer procedure from the Assign Macro. Click OK.

Implement Buttons to Create a Stopwatch in Excel

  • Click on the button’s text area and click on the Home tab.
  • Choose Middle and Center align from the Alignment group.
  • Click on the B icon and change the font size to 20.

Implement Buttons to Create a Stopwatch in Excel

  • Change the name of the button. We have named it START.

Implement Buttons to Create a Stopwatch in Excel

  • Make another button to stop or pause following the same procedure.
  • Draw this button and place it below Button1.
  • Choose StopTimer option from the Assign Macro window and click OK.

Implement Buttons to Create a Stopwatch in Excel

  • Click on the text area of the button and go to the Home tab.
  • Click the B icon and change the font size to 20.
  • In the Alignment group, choose Middle and Center align.
  • Label the button. We have labelled it STOP.

Implement Buttons to Create a Stopwatch in Excel

  • Make another button to reset.
  • Draw the button and place it below the Button2.
  • The Assign Macro window will appear.
  • In the Assign Macro window, choose ResetTimer from the options. Click OK.

Implement Buttons to Create a Stopwatch in Excel

  • Click the button’s text area and go to the Home tab.
  • Click the B symbol and change the font size to 20.
  • In the Alignment section, choose Middle and Center align.
  • Change the button’s name. We have labelled it RESET.

Implement Buttons to Create a Stopwatch in Excel

Read More: How to Make a Calculate Button in Excel


Step 6 – Check Stopwatch Timer

  • Click the Reset button.
  • Press START.
  • Tap the STOP button when you are done.
  • Click the Reset button to set the Stopwatch Timer back to Zero.
  • Press START.
  • Press STOP when you are done.
  • Tap the RESET button to reset it to zero.

Download Practice Workbook


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

14 Comments
  1. Hi, thanks and its useful. However, when i click on the stop button after i already stop the timer. i hit error. how can i solve this? please advise. thanks

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Sep 10, 2023 at 3:58 PM

      Hello JAMIE

      Your appreciation means a lot to us. Thanks for your nice words!

      You often click the stop button after you have already stopped the timer. That results in an Error. Thank you once again for noticing the issue. Your claim is correct.

      However, I am delighted to inform you that the issue can be avoided with the help of an Error Handler called On Error Resume Next.

      All you need to do is to modify the StopTime sub-procedure to fulfil your requirement.

      Modified Excel VBA Code:

      
      Option Explicit
      Public countDown As Date
      Sub StartTimer()
          countDown = Now + TimeValue("00:00:01")
          Range("B4") = Range("B4") + TimeValue("00:00:01")
          Application.OnTime countDown, "StartTimer"
      End Sub
      Sub ResetTimer()
          Range("B4") = TimeValue("00:00:0")
      End Sub
      Sub StopTimer()
          On Error Resume Next
          Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
      End Sub
      

      Good luck!

      Regards
      Lutfor Rahman Shimanto

  2. Thank you for this useful code.

    How can I add a 2nd instance?

    I simply attemted to copy and rename the macros and target another cell.

    The 2nd instance sarts ans then stops immediately… The reset works.

    Thanks for help.

    Sub StartTimer2()
    countDown = Now + TimeValue(“00:00:01”)
    Range(“F4”) = Range(“F4”) + TimeValue(“00:00:01”)
    Application.OnTime countDown, “StartTimer”
    End Sub
    Sub ResetTimer2()
    Range(“F4”) = TimeValue(“00:00:0″)
    End Sub
    Sub StopTimer2()
    On Error Resume Next
    Application.OnTime EarliestTime:=countDown, Procedure:=”StartTimer”, Schedule:=False
    End Sub

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Oct 15, 2023 at 12:23 PM

      Hello BIC

      Thanks for reaching out and posting an interesting problem. You want to create a 2nd instance of the existing stopwatch code.

      Excel VBA Code (2nd Instance):

      
      Option Explicit
      Public countDown As Date
      Public countDown2 As Date ' Public variable for 2nd instance.
      
      Sub StartTimer()
      countDown = Now + TimeValue("00:00:01")
      Range("B4") = Range("B4") + TimeValue("00:00:01")
      Application.OnTime countDown, "StartTimer"
      End Sub
      Sub ResetTimer()
      Range("B4") = TimeValue("00:00:0")
      End Sub
      Sub StopTimer()
      Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
      End Sub
      
      
      ' Codes to start, reset and stop for 2nd instance.
      Sub StartTimer2()
          Range("F4").NumberFormat = "h:mm:ss"
          countDown2 = Now + TimeValue("00:00:01")
          Range("F4") = Range("F4") + TimeValue("00:00:01")
          Application.OnTime countDown2, "StartTimer2"
      End Sub
      Sub ResetTimer2()
          Range("F4") = TimeValue("00:00:0")
      End Sub
      Sub StopTimer2()
          On Error Resume Next
          Application.OnTime EarliestTime:=countDown2, Procedure:="StartTimer2", Schedule:=False
      End Sub
      

      2nd Instance of stopwatch code
      OUTPUT:

      Hopefully, the idea will fulfil your requirements. Good luck!

      Regards
      Lutfor Rahman Shimanto

  3. Hi,

    Whilst the stop watch is running and I try to switch tabs to another sheet in the workbook it generates a Run-time error ’13’ type mismatch. How can I resolve this?

    Thanks

    Adam

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Nov 12, 2023 at 3:48 PM

      Hello ADAM INGLETON

      Thanks for reaching out and posting an exciting query. You are right about the run-time error “13” Type mismatch. This is because the active sheet contains text value within cell B4.

      I am delighted to inform you that I have developed other improved sub-procedures that have overcome the issue you mentioned by modifying the previous code. To be specific, I have taken a worksheet object to avoid this error.

      Raised Error on Your End:

      Improved Sub-procedures:

      Open the VBA Editor window => Click on Insert followed by Module => Paste the following code in the module => Save.

      
      Option Explicit
      Public countDown As Date
      
      Sub StartTimer()
      
          Dim ws As Worksheet
          Set ws = ThisWorkbook.Sheets("Stopwatch")
      
          countDown = Now + TimeValue("00:00:01")
          ws.Range("B4") = ws.Range("B4") + TimeValue("00:00:01")
          Application.OnTime countDown, "StartTimer"
      
      End Sub
      
      Sub ResetTimer()
      
          Dim ws As Worksheet
          Set ws = ThisWorkbook.Sheets("Stopwatch")
      
          ws.Range("B4") = TimeValue("00:00:0")
      
      End Sub
      
      Sub StopTimer()
          Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
      End Sub
      

      OUTPUT of Applying the Improved Sub-procedures:

      Hopefully, the idea will resolve your problem. Good luck!

      Regards
      Lutfor Rahman Shimanto

  4. Hi Lutfor,

    Thanks for you help with this much appreciated

    Thanks,

    Adam

  5. Good Afternoon,

    Do you know if it is possible to make the clock work in metric time (100 minute clock?)

    Thanks,

    Adam

    • Dear ADAM,

      Thanks for your comment. Unfortunately, Excel’s current time formatting does not support metric time display.

      Regards
      Aniruddah
      Team Exceldemy

  6. Hi, Is it possible to change the colour of the buttons?

    Thanks

    Adam

    • Dear Adam,
      Thank you for your concern. You can not change the color of Excel button but you can use Command Button to change the button color.
      You can follow the steps below to color your command button:
      1. use Developer tab > Insert > ActiveX Controls > Command Button.
      Adding Command button
      2. A command will appear and right click on it.
      3. Choose CommandButton Object > Edit.
      right click on the command button
      Name the button as Start.
      4. Now right click on the command button and choose View Code.
      view code
      5. Then write code below for starting.

      
      Private Sub CommandButton2_Click()
      countDown = Now + TimeValue("00:00:01")
      Range("B4") = Range("B4") + TimeValue("00:00:01")
      Application.OnTime countDown, "StartTimer"
      CommandButton2.BackColor = 13959039
      End Sub
      

      Finally the button color has been added.
      start button colored
      6. Now create a stop buttom and use the code below:

      
      Private Sub CommandButton1_Click()
      Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
      CommandButton1.BackColor = 13959039
      End Sub
      

      7. Write down the code for reset button:

      
      Private Sub CommandButton3_Click()
      Range("B4") = TimeValue("00:00:0")
      CommandButton3.BackColor = 13959039
      End Sub
      

      Finally, you will have colored command button. You can change the color by changing color code 13959039.
      Final result

  7. How to make multiple stop watch on one sheet

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 11, 2024 at 3:20 PM

      Hello RAMI

      Thanks for visiting our blog and sharing your queries. You want to create multiple stopwatches in a sheet. Of course, the requirement is very much achievable.

      I have displayed four stopwatches in the B4, F4, B15, and F15 cells.

      OUTPUT OVERVIEW:

      Follow these steps:

      Press Alt+F11 >> Click on Insert followed by Module >> Insert the following code in the module >> Save.

      
      Option Explicit
      
      Public countDown1 As Date
      Public countDown2 As Date
      Public countDown3 As Date
      Public countDown4 As Date
      
      'For Stopwatch 1:
      Sub StartTimer1()
          countDown1 = Now + TimeValue("00:00:01")
          Range("B4") = Range("B4") + TimeValue("00:00:01")
          Application.OnTime countDown1, "StartTimer1"
      End Sub
      Sub ResetTimer1()
          Range("B4") = TimeValue("00:00:0")
      End Sub
      Sub StopTimer1()
          Application.OnTime EarliestTime:=countDown1, Procedure:="StartTimer1", Schedule:=False
      End Sub
      
      'For Stopwatch 2:
      Sub StartTimer2()
          countDown2 = Now + TimeValue("00:00:01")
          Range("F4") = Range("F4") + TimeValue("00:00:01")
          Application.OnTime countDown2, "StartTimer2"
      End Sub
      Sub ResetTimer2()
          Range("F4") = TimeValue("00:00:0")
      End Sub
      Sub StopTimer2()
          Application.OnTime EarliestTime:=countDown2, Procedure:="StartTimer2", Schedule:=False
      End Sub
      
      'For Stopwatch 3:
      Sub StartTimer3()
          countDown3 = Now + TimeValue("00:00:01")
          Range("B15") = Range("B15") + TimeValue("00:00:01")
          Application.OnTime countDown3, "StartTimer3"
      End Sub
      Sub ResetTimer3()
          Range("B15") = TimeValue("00:00:0")
      End Sub
      Sub StopTimer3()
          Application.OnTime EarliestTime:=countDown3, Procedure:="StartTimer3", Schedule:=False
      End Sub
      
      
      'For Stopwatch 4:
      Sub StartTimer4()
          countDown4 = Now + TimeValue("00:00:01")
          Range("F15") = Range("F15") + TimeValue("00:00:01")
          Application.OnTime countDown4, "StartTimer4"
      End Sub
      Sub ResetTimer4()
          Range("F15") = TimeValue("00:00:0")
      End Sub
      Sub StopTimer4()
          Application.OnTime EarliestTime:=countDown4, Procedure:="StartTimer4", Schedule:=False
      End Sub
      

      I hope you have learned how to create multiple stopwatches in a sheet. I am also attaching the solution workbook for better understanding; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo