Animated Countdown Timer in Excel

How to develop animated Countdown Timer in Excel?

Excel provides a lot of flexibility to modify charts and make a lot of good visualization and utility tool. Countdown timer is one of the best examples of Excel based tool. This tool has been created with the combination of Pie and Doughnut Chart. Animation has been added with VBA code. If you are familiar with basic charts and macro, then you can easily create this beautiful utility tool in 5 minutes.

Animated Countdown Timer tool developed in Excel

Follow the below steps to create this awesome utility tool from scratch.

  1. Open Excel Application
  2. Create a Blank Workbook
  3. Save the Workbook as ‘Countdown Timer.xlsm’
  4. Rename the ‘Sheet1’ to ‘CountDown Timer’
  5. Un-check the ‘Gridlines’ under ‘Show’ group in ‘View’ tab
  6. Prepare input tables for User input, Pie Chart and Doughnut Chart. Follow the below picture to create all the required tables
  7. Enter the values in first two highlighted tables (Doughnut and Pie Chart). Please use the values available in below image and update the table accordingly.
  8. Select the Range C3:C4 in Doughnut Chart table. Go to Insert tab, click on Pie Chart and select Doughnut Chart
  9. Select the Doughnut Chart, go to Format tab and change the Size: Width=5” and Height=3”. Click on + Icon available at the top right corner of the chart and untick Chart Title and Legend. Now, go to Shape fill and select the color as ‘No Fill’.
  10. Select the Filled Axes (Blue color), go to Format Tab -> Click on Shape Fill -> More Fill Colors -> Click on Custom tab in Colors dialog box -> Enter the Color code as Red: 102; Green: 255; Blue:255. Now Click on OK.
  11. Select the ‘Not Filled’ Axes (Orange color), go to Format Tab -> Click on ‘Shape Fill’ -> Select ‘White Background 1, Darker5%’ and set the Transparency to 64% after click on ‘More Fill Colors…’
  12. Right Click on Plot Area then ‘Click on Format Data Series…’. Go to ‘Format Data Series’ pane and change the ‘Doughnut Hole Size’ from 75% to 85%.
  13. Select the Doughnut, go to Format Data Series and select ‘Border’ as ‘No Line’
  14. Select the Range C7:C9 in Pie Chart table. Go to Insert tab, click on Pie Chart and select 2D Pie Chart
  15. Select the Pie Chart, go to Format tab and change the Size: Width=5” and Height=3”. Click on + icon available at the top right corner of the chart and untick Chart Title, Data Labels and Legend
  16. In this Pie Chart, we have three different slices are available. First one is ‘Not filled – 1’ (filled with blue color), second one is ‘Filled’ (filled with orange color) and third one is ‘Not filled – 2’ (filled with gray color). Let us change the color and formatting of each slice.
  17. Select the Pie chart, got to Format tab then click on ‘Shape Outline’ and select ‘No Outline’ remove the lines available in Pie Chart.
  18. Select the First Slice (filled with blue color), right click on it then click on ‘Format Data Point…’, click on ‘Fill & Line’ icon in Format Data Point pane then select the ‘No fill’ option button. Repeat the same steps for Third Slice (filled with gray color).
  19. Post applying ‘No fill’ for first and third slices, chart will look like the below one. We will have only once slice visible (fill with orange color as of now)
  20. Let’s change the color and formatting of second slice as well
  21. Select the second slice, go to ‘Format Data Point’ pane, click on ‘Fill & Line’. Select ‘Gradient fill’ option, Type as ‘Linear’, Angle as 225 Create two different Gradient stops 1st at 0% and 2nd at 53%. Select the color of 1st gradient stop as Red- 102; Green- 255; Blue-255. Select the color of 2nd gradient stop as ‘White Background – 1’ and Transparency as 100%.
  22. Now, select the chart, got to ‘Format Chart Area’ pane. Click on ‘Fill & Line’, select the ‘Solid fill’ option and select color as ‘Black’
  23. Now, right click on Chart and select ‘Send to Back’
  24. Select both the charts (Pie & Doughnut), click on Format tab then click on Align and select ‘Align Center’ & ‘Align middle’
  25. Now, insert a text box and give the reference of cell C4.
  26. Select the text box, go to ‘Home’ tab. Change the font to ‘Calibri’, size to 80, Font color as ‘White’ and ‘Fill Color’ as ‘No Fill’
  27. Select the text box, go to ‘Format’ tab then select ‘No Outline’ under shape styles group.
  28. Enter the formulas in the first two highlighted tables (Doughnut Chart and Pie Chart). These formulas are required to add animation on this countdown timer
  29. Select Pie Chart, Doughnut Chart and Text Box. Make all these objects aligned to center and middle. Move it and place below the cell ‘G2’. Now, Countdown time will look like below one
  30. Go to VBE window, paste the below mentioned VBA Code to add animation under the Sheet1 code window.
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Row = 2 And Target.Column = 8 Then
    
            Range("C3").Value = 0
    
            Dim CurrentTime
    
            Dim i As Integer
    
            Dim j As Integer
    
            j = Range("H2").Value
    
                For i = 1 To j
    
                    CurrentTime = Timer
    
                    Do While Timer < CurrentTime + 1
    
                   
                        DoEvents
               
                    Loop
    
                    Range("C3").Value = i
    
                Next i
            End If
    End Sub
    
    
  31. Switch to Excel and enter any value in cell ‘H2’ to check whether CountDown timer is working and animating as per our expectation or not.
  32. Now, Countdown timer tool is ready.

Please watch the complete tutorial on YouTube

 

Click on download button to download the Excel file.

Click to download
Follow us on social media: