The article will show you how to scale time on the X-axis in an Excel Chart. Excel charts help us to visualize the change in data graphically which is useful to predict further changes. When we work with time, we need to be a little bit tricky to represent the time data in the Excel chart. Without scaling time data, sometimes the chart can be hard to understand. So, it’s important for a user to scale the time data on the X-axis to get a better view of the chart.
How to Scale Time on X Axis in Excel Chart: 3 Methods
In the article, we will discuss different scenarios on how to scale time on the X-axis. Generally, if you use a Line Chart, it works fine. Let’s have a look at the following picture.
But, if we create a Scatter Chart based on this dataset, you may find the chart inconvenient. The axis starts from the zero hour (12:00 AM or 0:00) and there are some unnecessary gaps in the chart.
This becomes more inconvenient if the dataset contains more time values.
We will show you how to get rid of this problem in the later sections of this article.
1. Scaling Time in X Axis in an Excel Chart
This section will provide you with the solution to the problem we stated above. Let’s show you how to make a chart first and then make the chart look good.
Steps:
- First, select the data range (B4:C20) and then go to Insert >> Chart >> Scatter Chart.
- Next, click on the plus icon of the chart and then go to Axes >> More Options.
- After that, the Format Axis window will appear. Insert the values like the following image.
In the Bounds section, the Maximum value represents 24 hours which is 1 unit. So, 1 hour becomes 1/24 = 0.04167 units. As the time data here starts at around 18:30, the minimum value becomes 18.5*0.04176 = 0.75.
That’s why we set the Minimum Bound value to 0.75 and the Maximum Bound value to 1.0. As 1 hour equals 0.04167 units, we set Major Unit to this value. The Minor Unit value becomes 0.008334 automatically by default.
After that, the Scatter Chart looks more convenient and easily understandable.
Thus, you can scale time on the X axis in an Excel chart to get a better view of it.
Read More: How to Change X Axis Scale in Excel
2. Scaling Time in X Axis by Date in Excel Chart
In this section, I’ll show you another scenario to scale time in the X axis when a date is involved. If you have different data for different times but on the same date, you will face some trouble using the chart. Let’s see the following description for the solution. We will use a Line Chart this time.
- First, select the data and go to Insert >> Chart >> Line Chart.
- Next, you will see that there is a vertical line on the date 9th September although this date has two different data. To solve this dilemma, click on the plus icon of the chart and select Axes >> More Options.
- After that, just change the Axis Type to the Text axis.
- Thereafter, you will see that the chart shows different data on the 9th September.
Thus you can scale both date and time in the X axis.
3. Changing the Number Format
We can achieve a similar result of Method 2 by following the steps given below.
Steps:
- First, create a Line Chart following the process of Method 2.
- Next, just change the format of the Date column to Number from the Number group of the Home Tab. You need to select the range B5:B12 and then click on the drop down icon of the Number group and select the General or Number option for this purpose.
- After that, you will see separate data for the 9th September. But you may find it inconvenient as the date on the horizontal axis is turned into numbers.
- In order to retrieve from this problem, click on the plus icon of the chart and select Axes >> uncheck Primary Horizontal.
This operation will remove the horizontal labels and gives you a better view of the chart.
Thus, you can scale the date and time on the X axis in an Excel chart.
Download Practice Workbook
Conclusion
In the end, we can consider that you will learn effective ways to scale time in the X axis in an Excel chart. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
Related Articles
- How to Change Axis Scale in Excel
- How to Change Y Axis Scale in Excel
- Automatic Ways to Scale Excel Chart Axis
- How to Set Logarithmic Scale at Horizontal Axis of an Excel Graph
- How to Change Axis to Log Scale in Excel
- How to Break Axis Scale in Excel
- How to Set Intervals on Excel Charts
<< Go Back to Excel Axis Scale | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
