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
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
How to use the BINOM.INV function
The BINOM.INV function calculates the minimum value for which the binomial distribution is equal to or greater than a given […]
Find numbers in close proximity to a given number
This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close […]
How to use the NORM.DIST function
The NORM.DIST function calculates the normal distribution for a given mean and standard deviation. Formula in cell C7: =NORM.DIST(C2,C3,C4,C5) Excel […]
Remove duplicates based on date
Question: Column B has dates Column C as data B5 : 1/1/2010 : 5000 B6 : 2/1/2010 : 4000 B7 […]
A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to […]
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are […]
I found a chart that I wanted to show you how to build. It contains values both horizontally and vertically, […]
This article demonstrates how to create a stock chart with two series. Weekly Blog EMAIL [newsletter_signup_form id=1] Welcome! I am […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
This stock chart built in Excel allows you to change the date range and the chart is instantly updated. What's […]
Charts in Microsoft Excel lets you visualize, analyze and explain data. Charting in Excel is very easy and you […]
It might be impossible to read the smaller series values on the y-axis if you have two series of data […]
To be able to resize a chart you must first select it, you do that by press with left mouse button […]
The image above shows you categories (countries) grouped into regions making this chart a lot cleaner and easier to read. How […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
This article demonstrates how to highlight a group of bars in a chart bar, the techniques shown here works fine […]
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]
Excel allows you to insert a linear chart trendline that displays a straight line calculated based on the method of […]
Excel lets you easily add a best-fit curved logarithmic trendline calculated based on the method of least squares. How to […]
A moving average smooths out short-term variations to show a long-term trend or cycle. The chart above shows random values […]
The image above demonstrates data labels in a line chart, each data point in the chart series has a visible […]
Chart gridlines are great for making the chart data more readable and detailed, Excel allows you to add major and […]
Enable error bars when you want to show, for example, standard deviations in a chart, Excel lets you insert error […]
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
The image above shows lines between each colored column, here is how to add them automatically to your chart. Select […]
This trick is so simple and also an incredible time-saver if you want to build beautiful worksheets or dashboards where […]
This article demonstrates how to create an animation using a line chart in Excel. The user selects a series in […]
This article demonstrates macros that animate bars in a chart bar. The image above shows a bar chart that animates […]
This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]
This chart is an arrow chart that has horizontal and vertical lines, positive arrows are green and negative arrows are […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]
The 100% stacked column chart lets you graph values in a group. Each value in a group is a stacked […]
The bar chart is simply a column chart rotated 90 degrees right, this makes it great if you have long […]
The Box and Whisker chart allows you to plot distributions in statistics, it also displays local minimum, first quartile, median, […]
The bubble chart allows you to plot data just like the scatter chart but also the size of the bubbles. […]
The candlestick chart allows you to plot price movements of stocks, derivates, bonds and currencies. A black candlestick in Excel […]
The clustered column chart allows you to graph data in vertical bars, this layout makes it easy to compare values […]
The combination chart category lets you build a chart that has columns on the primary axis and the line on […]
The Column and Line combo chart is a great choice if you want to differentiate two data series and make […]
Combine the stacked area chart and the clustered column chart if you want to differentiate at least two data series to […]
A doughnut chart is just like a pie chart showing the relationships of segments to a total except that the it […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
A funnel chart is mostly used to display a series of steps within a process, each step shows a proportion […]
The histogram chart lets you automatically group data values in intervals you specify, it is often used in statistics to show […]
The line chart lets you chart data points as a line, this chart type is useful if you have many […]
Excel 2016 owners with an office 365 subscription can now easily build beautiful map charts. Excel uses maps from Bing […]
A Pareto chart is often used in statistics to demonstrate how sample values are distributed across given ranges, the values are […]
The pie chart is used to graph parts of a whole, the total of our numbers must be 100%. The […]
The radar chart allows you to plot values based on a center point and outwards to categories you specify. Each […]
The scatter chart is great for charting numeric values in pairs, for example, coordinates. It lets you compare multiple data […]
A sparkline is a visual representation of a data series in only one cell, the image above shows sparklines next […]
A sparkline is a visual representation of a data series in only one cell, the image above shows sparklines next […]
A win/loss sparkline is a visual representation of a data series in only one cell, the image above shows a […]
The stacked column chart is great for comparing parts of a whole and how they change over time or categories. […]
The high-low-close stock chart displays the high, low and closing price for a given date range. Each line represents a […]
(Press with left mouse button on to expand image) The sunburst chart is great for showing relationships of hierarchical data and […]
The surface chart allows you to graph multiple data series in a 3D chart, color-coded to make the chart easier […]
Use the treemap chart when you want to compare segments to a total and you have categories in a hierarchy. […]
The waterfall chart allows you to plot values as bars, however, depending on whether they are negative or positive […]
The area chart is similar to the line chart except that the area below the line is filled, use this […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
To change the looks of a data series simply double-press with left mouse button on with left mouse button on […]
Most Excel charts consist of an x-axis and a y-axis, Excel allows you to easily change the looks of […]
The x-axis and y-axis titles are just as easy to format and customize as the other chart elements. How to […]
The image above shows tick mars in a line chart, there are two types of tick marks, major and minor […]
The chart area contains all components. If you choose to format the chart area you can change things like border, […]
You can easily customize the font, font size, font color etc of the chart legend: Select legend. Go to tab […]
I recommend that the chart title is short and easy to understand. How to add a chart title Follow these […]
The plot area refers to the location of the chart that displays the actual data represented by lines, bars, columns, […]
What if you want to show a selection of a data set on a chart and easily change that selection? […]
The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart. This curve is often […]
The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]
I found an interesting chart on CNN's website: Rise of the supersize rugby player It shows the average height of athletes […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
The chart above contains no legend instead data labels are used to show what each line represent. How to build […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
The image above demonstrates a line chart containing two data series and two y-axes, one for each data series. I […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
Slicers let you control data displayed in a chart, simply press with left mouse button on a button to quickly […]
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.