Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can make clock charts very easily in Excel for better representation. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you a step-by-step procedure to make a Clock Chart in Excel. Hence, read through the article to learn more and save time.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
What Is a Clock Chart?
In general, there is no direct way to make a clock chart in Excel. However, you can make one using the bubble chart from the charts group of commands, and you can easily draw it if you have the hourly data. In general, the Bubble Chart is a type of scatter plot chart that replaces data points with bubbles. Moreover, it has horizontal and vertical axis like the scatter plot chart and also has an additional dimension of data that represents the size of the bubble. Fortunately, it is really useful to represent your dataset of 3 variables in a single plot.
Step-by-Step Procedure to Make a Clock Chart in Excel
Often, we need to make a clock chart, and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. Hence, go through the following steps to create your desired clock chart using Excel.
📌 Step 1: Select Dataset
For the purpose of demonstration, I have selected the following sample dataset. Here, the dataset contains Hours of day time and the number of Visitors who have visited a grocery store hourly.
📌 Step 2: Convert Data to Make Clock Chart
In this part, we will convert the given data to X value and Y value so that we can draw the bubble chart. For instance, we have taken 100 as a chart circle. Hence, follow the steps below.
- Firstly, select cell D5 and insert the following formula to get the value of the X-axis.
=ROUND(SIN(RADIANS(B5*30))*100,0) + 100
🔎 Formula Breakdown:
- Initially, each clock contains 12 hours and covers a whole circle (360°). So, every part is 30° (360°/12).
- Now, RADIANS(B5*30) converts the product of 30 and cell B5 into a radian unit.
- Then, the SIN function provides the output as 5. It is multiplied by 100.
- After that, the ROUND function takes the output value and rounds it.
- Finally, 100 is added to it in order to get the final output.
- Secondly, press Enter and use the AutoFill tool to apply the formula to the entire column.
- Similarly, click on cell E5 and write down the formula below to get the value of the Y-axis. Here, I have used the COS function instead of the SIN function.
- Finally, hit the Enter key and utilize the AutoFill tool to the whole column.
📌 Step 3: Create Clock Chart in Excel
- Initially, select the X and Y columns.
- Then, go to the Insert tab.
- Next, select Bubble chart from the Charts group of commands.
- After that, a bubble chart will appear.
- From the Chart Elements, check the mentioned options.
- Now, double-click on 1 to open the Format Data Labels dialog box.
- Afterward, check Bubble Size as a Label Options if not checked.
- Similarly, click on the bubble to open Format Data Series.
- Now, select Scale bubble size to 65.
- Furthermore, right-click on the chart and choose Select Data from the context menu.
- Now, press Edit.
- Lastly, select the Series bubble size as your number of Visitors column and press OK.
📌 Step 4: Final Output
Last but not least, you can perform some modifications on styles and colors. In the end, you will get your desired clock chart as in the image below.
💬 Things to Remember
- First of all, there is no direct chart option for the clock chart.
- However, you can modify the chart according to your personal preference.
- At last, in the background, you can overlay a clock diagram in order to look the chart more appropriate.
These are all the steps you can follow to make a clock chart in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.