How to position month and year between chart tick marks
The picture above shows a line chart with month and year labels between tick marks instead of date values below each tick mark.
This tutorial demonstrates in great detail how to position month and year values between chart tick marks. You will need an additional series and a secondary axis to accomplish this.
To insert a line chart simply select the values you want to use, I am using the values shown in the image to the right.
Then press with left mouse button on the "Insert" tab on the ribbon, press with left mouse button on the "Line" chart button and a line chart instantly appears.
The chart shown below is the default chart Excel creates when you insert a line chart.
The line chart x-axis shows dates with seven days interval to the next tick mark beginning with 11/22/2017.
Double press with left mouse button on with left mouse button on the dates right below the chart x-axis to open the Format Axis Task Pane.
Here you can easily change which date the x-axis begins with and ends with, it also allows you to specify the interval.
Change Minimum Bounds to 11/1/2017.
Change Major Units from 7 to 1 and also change Days to Months.
This will change how the x-axis will display the dates, it will begin with 11/1/2017 and the next tick mark is going to have the first date in the next month.
This, however, is not what we are looking for.
We want month and year between tick marks, not the entire date below tick marks.
The chart above shows the first date in each month right below tick marks.
To position dates between tick marks we need an additional series, I am going to use the values displayed to the right.
My dates in the first dataset start in November 2017 and end in February 2018, this series also has month and year values in the same date range as my first data set.
The dates look like this 11/1/2017, 12/1/2017, 1/1/2018 and 2/1/2018.
To format the dates simply select the dates, press CTRL + 1 to open the Format Cells dialog box.
Here press with left mouse button on "Custom" and type: mmm-yy
Press with left mouse button on OK button.
The dates show only month and year now, see picture to the right.
The next column contains 0's (zeros), these values are not needed.
They are not shown in the chart and I will show you how to hide them.
Now, press with right mouse button on on the chart and select "Select Data...".
The "Select Data Source" dialog box appears.
Press with left mouse button on the "Add" button, demonstrated in the image above.
Select the cell range containing the 0's (zeros) and press with left mouse button on the OK button. Press with left mouse button on the next OK button, as well.
The chart above shows the second series in orange.
Double press with left mouse button on with left mouse button on the first series (blue line in above chart).
The Format Data Series Task Pane appears, select "Secondary Axis".
This will move the first series to a secondary axis allowing you to choose the axis you want to be displayed.
This made the chart look like it broke or something, however, don't panic.
We just need to customize the chart so the right axis and values are displayed.
Press with right mouse button on on the chart, press with left mouse button on "Select Data...".
Select Series2, press with left mouse button on the "Edit" button.
Select the dates formatted as month and year (mmm-yy). Press with left mouse button on OK button.
Press with left mouse button on the next OK button.
The month and year values are now in the correct positions, however, the blue series is missing data points.
Now add a secondary horizontal axis, first select the chart.
Press with left mouse button on "Design" tab on the ribbon, and then "Add Chart Element". Press with mouse on "Axes" and then "Secondary Horizontal.
You are almost there, we need to hide the orange series and a few axes.
To hide the orange series double-press with left mouse button on the orange series to open the Format Data Series task pane.
Select "No line" shown in the picture to the right.
If you have markers, hide those as well.
The orange series is now hidden, however, the axes are still visible.
You can now delete the left vertical axis, simply select the left vertical axis and then press Delete on your keyboard.
It is now time to move the right vertical axis to the left side of the chart area, double press with left mouse button on the top horizontal axis to open the task pane.
It may seem counter-intuitive to select the top horizontal category when you want to move the right vertical axis, however, the two axes are connected, meaning you can choose where you want the axis relative to the other axis.
Select "At date" below "Vertical axis crosses" and type the earliest date in your data series.
In this case 11/1/2017.
The chart now looks like this.
One last thing to do, hide the top horizontal axis.
Double press with left mouse button on the top horizontal axis to open the task pane.
Press with mouse on the black arrow next to "Labels" to expand settings.
Press with mouse on drop-down list next to "Label Position" and select "None", see picture to the right.
The chart is now complete but the first value is not in the beginning of November 2017? It is 22/11/2017.
We need to use the same start date and end date on both the hidden horizontal axis and the visible one.
Double press with left mouse button on the bottom horizontal axis to open the Format Axis task pane.
Use the following start date: 11/1/2017 and end date: 2/28/2018
See picture to the right.
Apply the same minimum and maximum bound to the hidden top horizontal axis.
Go to "Format" tab on the ribbon, if the tab is not visible select the chart again.
Select the other horizontal axis, see picture to the right.
Make sure the minimum and maximum bound values shown in the Format Axis task pane are the same.
In this case, the start date is 11/1/2017 and end date is 2/28/2018.
The picture below shows what the chart looks like when both horizontal axes are aligned with the same start and end date.
I have added axes lines and changed major grid lines, see this post if you are interested in how:
Components of an Excel chart
Get Excel *.xlsx file
How to center month and year between chart tick marks.xlsx
Use the same technique to separate months and years, see picture above.
Simply use another column containing dates formatted as years.
Get the following file to see how this chart is constructed.
Get Excel *.xlsx file
How to center month and year between chart tick marks_test.xlsx
Charts category
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
Table of Contents How to graph a normal distribution How to build an arrow chart How to graph an equation […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
Excel categories
One Response to “How to position month and year between chart tick marks”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Didnt work for me. Labels are still below the tick marks.