How to Create Analog Clock in Excel (with Easy Steps)

Today we will show how to create an Analog Clock in Excel. Analog Clocks are clocks that contain hour, minute and second hands and give time in a 12-hour system. Often Excel users need to perform a lot of tasks. An analog clock in their Excel sheet will reduce the time required to look into the manual clock and thus, increase productivity.

How to Create Analog Clock in Excel: Step-by-Step Procedures

In this article, we will discuss exhaustive procedures to create an Analog Clock in Excel. We will go into details of each step and discuss every detail with proper pictures.

Step 1: Setting up Hours, Minutes and Seconds

In the first step, we will insert the necessary formula to create the Analog Clock in Excel. We will use the HOUR, MINUTE, SECOND, SUM, and IF functions to write the formulas.

To begin with, click on the C5 cell and enter the following formula,

=IF((HOUR(NOW())+C10/60+C15/3600)>12,(HOUR(NOW())+C10/60+C15/3600)-12,HOUR(NOW())+C10/60+C15/3600)

Then, press Enter.
As a result, we will get the value that will control the hours in the clock.

Now, enter the Hour Hand Width in the C6 cell.
Next, choose the C7 cell and type,

=12-SUM(C5:C6)

Consequently, we will get the blank space for hours.

After that, select the C10 cell and enter,

=MINUTE(NOW())+(C15/60)

Next, hit Enter.
As a result, we will have the value for the minutes in the clock.

Then, enter the Minute Hand Width in the C11 cell.
Next, choose the C12 cell and type,

=60-SUM(C10:C11)

As a result, we will get blank space for minutes.

Thereafter, select the C15 cell and insert,

=SECOND(NOW())

Then, press the Enter button.

Consequently, we will have the value to control the seconds.
Now, set the Second Hand Width in the C16 cell.
Finally, choose the C17 cell and type,

=60-SUM(C15:C16)

In the process, we will get the value that will control the seconds in the clock.

Step 2: Creating Clock Frame

Here, we will insert the frame of the Analog Clock. We will use the Doughnut and 2-D Pie charts to do that.

Firstly, go to the Insert tab.
Secondly, choose the Doughnut Chart from the Insert Pie or Doughnut Chart option.
As a result, we will have a blank box on the screen.

Now, right-click on the box and select Select Data from the available options.
Consequently, a prompt will be on the screen.

Next, click on Add in the prompt.

In the Edit Series prompt, type the following as the Series values,

={1,1,1,1,1,1,1,1,1,1,1,1}

Then, choose OK.
As a result, a data series will be added to the chart which will set up the position of the hour.

Again, click on Add.

Then, type the same value as the previous one in the Series values We are doing this for the minute hand.
Next, click OK.
This too will add another data series to the existing chart.

Once more click on Add.

This time, enter {1} as the Series value to give shape to the second hand.
Choose OK.

Finally, click another OK to exit the Select Data Source prompt.

As a result, a Doughnut chart will be on the screen.
After that, click on the chart.
Then, go to the Format
From there, set No Outline from the Shape Outline option.

Next, turn off the Chart Title and Legend of the chart after clicking on the plus sign to the top-right corner of the chart.

Now, choose any of the data series and right-click.
From the available options, choose Format Data Series.

Next, change the Doughnut Hole Size to 60% from the Format Data Series tab on the right.

After that, right-click on the data series 2 from the chart.
Then, select Format Data Series.

Next, set the Angle of the first slice to 15%.

Thereafter, turn on the Data Labels for the data series 2.

Now, write 12 in the data label that is at the top middle position.

Then, write down the rest of the labels of the chart as the sequence of an Analog Clock.

Next, remove the fill color of the data series 2 by going to the Format tab and clicking on No Fill from the Shape Fill option.

Now, turn on the data label of the data series 1.

After that, choose the D16 cell.
Then, go to the Insert tab.
From there, select Symbol from the Symbols option.

In the Symbol prompt, write down 25CF as the Character code.
Next, press Insert.

As a result, the character will be inserted in the sheet.
Next, copy the character by pressing Ctrl+C.

Then, paste the character in the data label of the data series 1 by using Ctrl+V.

Do the same for the rest of the data labels.

Now, turn off the fill color of the data series 1.

Consequently, we will have the frame of our Analog Clock.

Read More: How to Make a Clock Chart in Excel

Step 3: Inserting Time Hands

In this step, we will create the hour, minute and second hands of our Analog Clock by using the Pie chart.

Firstly, choose the C5:C7 cell range.
Secondly, go to the Insert tab.
Thirdly, choose the Pie Chart from the Insert Pie or Doughnut Chart option.

As a result, we will have a Pie Chart box on the screen.
Now, turn the Chart Title and Legend off for the chart.

Next, change the outline of the data series one to No Outline.

Also, set the fill color to No Fill.

Do the same for the data series 1.
As a result, we will have the portion of the Hour Hand Width

Step 4: Aligning Time Hand

In this instance, we will adjust the newly created hour, minute and second hands inside the Analog Clock frame.

To begin with, set the fill color of the background of the entire Pie Chart to No Fill.

Next, fit the Hour Hand inside the clock by adjusting the size of the Pie Chart.
Also, turn off the Chart Title and Legend of the Doughnut Chart.

Similarly, insert a Pie Chart for the Minute Hand and Second Hand dataset.
Set the color of the hands different from each other.
Also, set the size of the hands different from each other.
Then, adjust them inside the clock.
Next, click on any of the charts and press Ctrl+A.
Now, go to the Shape Format.
From there, choose Align Center from the Align option.

After that, go to the Shapes option from the Insert tab.
Then, select an oval shape from the available options.

Then, set it at the center of the clock.

Step 5: Applying VBA Code for Starting and Stopping Clock

In the final method, we will insert a start and a stop button to start and stop the clock by connecting the buttons with two VBA codes.

At the start, go to the Developer tab.
From there, choose Visual Basic.
As a result, a new window will be opened.

In the Visual Basic window, choose ThisWorkbook under the VBAProject option.
As a result, a coding module will appear.

In the module, write the following code and save it.

Sub StartClock()
Dim am_ws As Worksheet
Set am_ws = ActiveSheet
am_ws.Range("H5").Value = "Start"
x:
VBA.DoEvents
If am_ws.Range("H5").Value = "Stop" Then Exit Sub
Application.Calculate
GoTo x
End Sub

Sub StopClock()
Dim am_ws As Worksheet
Set am_ws = ActiveSheet
am_ws.Range("H5").Value = "Stop"
End Sub

Next, go to the Insert tab.
Then, select a rectangle shape from the Shapes option.

Then, insert the shape below the clock and name it Start.
Repeat the same process to insert a Stop box.

After that, right-click on the Start button.
From the available options, select Assign Macro.

Then, assign the StartClock macro to the button.
Finally, click OK.

Again, right-click on the Stop button.
From the prevailing options, select Assign Macro.

Then, assign the StopClock macro to that button.

Finally, click on the Start button to start the clock and on the Stop button to stop the code.

Download Practice Workbook

You can download the practice workbook here.

Analog Clock.xlsm

Conclusion

In this article, we have discussed step-by-step procedures to create an Analog Clock in Excel. This will allow users to look into the clock while working in Excel. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.

Get FREE Advanced Excel Exercises with Solutions!