Welcome back! In this post, I demonstrate creating an analog clock as a chart in Excel that’s completely data-driven, and even real-time. The clock, created as a scatter plot in Excel is dynamic, scalable, accurate, and highly customizable. You will discover the way to generate such a chart that updates the clock visual according to any time entered, or just render the current system time everytime like a real-time clock. This is not a static drawing or graphic, it’s a fully data-driven and dynamic chart. Let’s begin.
Essentially, what we want are charts that render via pure numbers such as these examples and beyond (with numbers. roman numerals, with border, without border, background of the clock-face, looks of the hands, etc. etc.):
In order to achieve this feat, the first topic we need to understand before diving into Excel specifics are the geometry of clock itself and some basic math, specifically, trigonometry and parametric equations. In an earlier post, I had shared the parametric formulas for a circle; we will leverage that. You can find that post here.
Remember, the formulas are for a circle with radius (r) and center at (h, k) are:
x = h + r * COS(theta)
y = k + r *SIN(theta)
where theta is the angle parameter that varies from 0 to 2pi. It also helps to grasp that there are 360 degrees in a circular clock with twelve hour hands and therefore 30 degrees separate a pair of hours.
That’s good-enough information to create a circular clock face, but further challenge lies ahead. For example, how do we plot the hour markers in their correct positions (1, 2, 8 O’ clock markers for example)? How do we draw the hour and minute hands within the circle, and what are their tips’ positions at a given hour and minute? That’s where we’ll need some help of trigonometry.
To find the starting and ending x,y coordinates for all the hour markers on the clock we use:
Calculate the x,y coordinates for each hour (12, 1, 2, 3…11) by using these formulas:
x = r * COS(RADIANS(90 – Angle))
y = r * SIN(RADIANS(90 – Angle))
Assuming we set 12 O’ clock marker at 0 degrees, then its (12 hour marker on the clock) x, y coordinates will be: 3.06287E-16, 5 using the formulas above. Then we’ll increment the angle by 30 degrees and that’ll be for 1 O’ clock marker; then so on until we have covered all the hours, the 11 O’ clock would then end at 330 degrees, and we compute the x,y for each of these angles and plot in the XY Scatter plot as a new series.
Next, we need additional formulas for dynamically calculating the tip points (end points) of the hour hand and the minute hand for any given time. The starting x,y coordinates for both are obviously 0,0 but to find the tip points, we need these formulas:
Hour hand tip x =radius*COS(RADIANS(90-(hour*30+minute/2)))
Hour hand tip y =radius* SIN(RADIANS(90-(hour*30+minute/2)))
Minute hand tip x =radius*COS(RADIANS(90-minute*6))
Minute hand tip y =radius*SIN(RADIANS(90-minute*6))
Now, we can draw all the hour markers at exact locations, draw the hour and minute hands at any given time at perfect positions. If we enter an hour and a minute value in two cells, we can simply can calculate the hour and minute hands’ end points. Similarly, we can get the current time using NOW() function in Excel and split into hour and minute components and use the formulas above to find the hour and minute hands’ positions.
What’s left is just customization for the look and feel of the clock. Do you want Roman numerals, no problem…just a column of labels with those numbers and add point the labels to be read from a range instead. Then you can play with the colors, thickness of hands, fonts, borders, background of the clock chart, etc. etc.
While it wasn’t too difficult, there’s a lot of little details, steps and gotchas that can easily make or break your chart. That’s too much detail to include here besides, it’s best described with a real workbook that you can explore and experiment with.
That’s why I have created the complete Excel workbook with full explanation, detailed step by step guide with tips and gotchas, the data, formulas, input cells, dynamic current time extraction, and the final charts in various formats that you can customize as your own. It’ll save you plenty of time and help you learn some skills much quicker. Get the complete Workbook with instructions here.
Progression and transformation of the chart creation from start to finish
This is how the “clock chart” looks like through different stages of development from start to finish (an animated snapshot of creation of a clock showing 6:45):
I hope this was interesting. Be sure to explore my other downloads on my Patreon page. Happy charting!
