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.
Follow the below steps to create this awesome utility tool from scratch.
- Open Excel Application
- Create a Blank Workbook
- Save the Workbook as ‘Countdown Timer.xlsm’
- Rename the ‘Sheet1’ to ‘CountDown Timer’
- Un-check the ‘Gridlines’ under ‘Show’ group in ‘View’ tab
- Prepare input tables for User input, Pie Chart and Doughnut Chart. Follow the below picture to create all the required tables
- 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.
- Select the Range C3:C4 in Doughnut Chart table. Go to Insert tab, click on Pie Chart and select Doughnut Chart
- 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’.
- 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.
- 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…’
- 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%.
- Select the Doughnut, go to Format Data Series and select ‘Border’ as ‘No Line’
- Select the Range C7:C9 in Pie Chart table. Go to Insert tab, click on Pie Chart and select 2D Pie Chart
- 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
- 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.
- Select the Pie chart, got to Format tab then click on ‘Shape Outline’ and select ‘No Outline’ remove the lines available in Pie Chart.
- 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).
- 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)
- Let’s change the color and formatting of second slice as well
- 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%.
- Now, select the chart, got to ‘Format Chart Area’ pane. Click on ‘Fill & Line’, select the ‘Solid fill’ option and select color as ‘Black’
- Now, right click on Chart and select ‘Send to Back’
- Select both the charts (Pie & Doughnut), click on Format tab then click on Align and select ‘Align Center’ & ‘Align middle’
- Now, insert a text box and give the reference of cell C4.
- 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’
- Select the text box, go to ‘Format’ tab then select ‘No Outline’ under shape styles group.
- 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
- 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
- Go to VBE window, paste the below mentioned VBA Code to add animation under the Sheet1 code window.
1<figure><img class="aligncenter wp-image-832 size-full" src="https://thedatalabs.org/wp-content/uploads/2019/04/31.png" alt="" width="859" height="70"></figure>1234567891011121314151617181920212223242526272829303132Option ExplicitPrivate 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 IfEnd Sub
- 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.
- Now, Countdown timer tool is ready.
Please watch the complete tutorial on YouTube
Click on download button to download the Excel file.