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.


Download Practice Workbook

You can download the practice workbook here.


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

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.

inserting formulas to create analog clock in excel

  • 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.

inserting formulas to create analog clock in excel

  • 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.

inserting formulas to create analog clock in excel

  • Thereafter, select the C15 cell and insert,
=SECOND(NOW())
  • Then, press the Enter button.

inserting formulas to create analog clock in excel

  • 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.

inserting formulas to create analog clock in excel

Read More: How to Create Digital Clock in Excel ( 2 Easy Methods)


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.

inserting frame to create analog clock in excel

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

inserting frame to create analog clock in excel

  • Next, click on Add in the prompt.

inserting frame to create analog clock in excel

  • 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.

inserting frame to create analog clock in excel

  • 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.

inserting frame to create analog clock in excel

  • 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.

inserting frame to create analog clock in excel

  • 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.

inserting frame to create analog clock in excel

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

inserting frame to create analog clock in excel

  • 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.

 inserting frame to create analog clock in excel

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

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

inserting frame to create analog clock in excel

  • 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.

inserting frame to create analog clock in excel

  • 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.

inserting frame to create analog clock in excel

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

inserting frame to create analog clock in excel

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

inserting frame to create analog clock in excel

  • 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.

inserting frame to create analog clock in excel

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

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

inserting frame to create analog clock in excel

Read More: How to Make a Clock Chart in Excel (with Easy Steps)


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.

inserting time hands to create analog clock in excel

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

inserting time hands to create analog clock in excel

  • 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

inserting time hands to create analog clock in excel

Read More: How to Make a Running Clock in Excel (2 Easy Ways)


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.

inserting time hands to create analog clock in excel

  • 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.

inserting time hands to create analog clock in excel

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

inserting time hands to create analog clock in excel

  • Then, set it at the center of the clock.

Read More: How to Use VBA Code for Creating Digital Clock in Excel


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.

applying vba code create analog clock in excel

  • 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

applying vba code create analog clock in excel

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

applying vba code create analog clock in excel

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

applying vba code create analog clock in excel

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

applying vba code create analog clock in excel

  • 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.

applying vba code create analog clock in excel

  • 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.

applying vba code create analog clock in excel


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.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo