Excel charts are excellent tools to visualize and analyze data quickly. Regarding this, it is a very frequent practice to plot a chart by month and year together. It happens mostly for the data organized or calculated on a date basis. Now, if you are looking for something like creating a chart by month and year, you have landed in the right place. Just explore this article fully to learn how to create an Excel chart by month and year following 2 easy methods.
Excel Chart by Month and Year: 2 Examples
Say, you have a dataset of monthly sales quantity for 6 months. Now, you can create a chart of these data by month and year through the following methods. In this article, we have used the Microsoft Office 365 version to accomplish the task. You can use any other version of Microsoft Office to accomplish this using these same methods. If you face any troubles, let us know in the comment section.
1. Excel Chart by Month and Year with Single Legend Entry
You can create an Excel chart using a single legend entry to show the chart by month and year. This is very simple, and quick. Follow the steps below to accomplish this.
📌 Steps:
- First and foremost, create two columns named Year and Month between the Date and Sales columns.
- Second, click on the C5 cell and insert the following formula which involves the YEAR function. Afterward, press the Enter button.
=YEAR(B5)
- Subsequently, place your cursor in the bottom right position of the cell and drag the fill handle downward upon its appearance.
- Next, click on the D5 cell and insert the TEXT function below to extract the month of the following date. Subsequently, press the Enter button.
=TEXT(B5,"mmm")
- Just like the 3rd step, place your cursor in the bottom right position of this cell. And, when the fill handle appears, drag it below to copy the same formula.
- Now, select the cells C5:E10. Subsequently, go to the Insert tab >> Insert Line or Area Chart tool >> Line option.
- As a result, you will see a line chart will appear based on the sales data keeping the month and year on the X-axis.
- Now, for a better and cleaner look, click on the chart >> click on the Chart Elements icon >> untick the Gridlines option.
- Next, for a better understanding of the chart, double click on the Chart Title and write Sales Quantity Per Month.
Thus, you will see there is an Excel chart with month and year according to your data. And, the outcome should finally look like this.
Read More: How to Combine Daily and Monthly Data in Excel Chart
2. Adding a Secondary Axis to Excel Chart by Month and Year
You can also add a secondary axis to create an Excel chart by month and year. Go through the steps below to achieve this.
📌 Steps:
- At the very beginning, you must create a line chart with your given data. To do this, select the cells B5:C10 >> go to the Insert tab >> Insert Line or Area Chart tool >> Line option.
- As a result, you will have a line chart for the selected data range.
- At this time, for a more attractive look, click on the Chart >> Chart Elements icon >> Untick the Gridlines option.
- Now, for a better understanding of the representing chart, double click on the Chart Title and subsequently, write Sales Quantity Per Month.
- Consequently, your chart will look like this now.
- You can see there are dates on X-axis. But you want only the month and number. Now, double-click on the X-Axis.
- As a result, the Format Axis pane window will appear on the right side. Now, at the Axis Options group, make sure the Axis Type is marked as Automatically select based on data.
-  Afterward, put 10/1/2021 at the Minimum Bound and the last data’s date at the Maximum Bound. Keep the Units as 1 month for both Major and Minor axes.
- Next. create two columns named Month & Year and Helper Column.
- Afterward, in the Month & Year column, copy and paste all the given dates. And, put 0 at the Helper Column cells.
- Now, select the cells of the Month & Year column and right-click on the selection. Subsequently, select the Format Cells… option from the context menu.
- As a result, the Format Cells window will appear. Select the Category: as Date and the Type: as Mar-12. Subsequently, click on the OK button.
- As a result, you can see the dates are in month and year now.
- At this time, go to the chart again. Next, right-click on the chart area and choose the Select Data… option from the context menu.
- As a result, the Select Data Source window will appear now. Click on the Add button.
- Subsequently, the Edit Series window will appear. At the Series values: text box, refer to the F5:F10 cells. Last but not least, click on the OK button.
- At this time, you will be back on the Select Data Source window again. Finally, click on the OK button.
- As a result, another data series is added to the chart and it is the orange marked line.
- Now, double-click on the blue line. Consequently, the Format Data Series task pane will arrive on the right side.
- Now, under the Format Data Series task pane, go to the Series Options group. Next under the Plot Series On options, click on the Secondary Axis option.
- As a result, you will have a chart like the following figure.
- At this time, right-click on the chart area again and select the Select Data… option from the context menu.
- Consequently, the Select Data Source window will appear. Now, select the Series 2 entry and click on the Edit button under the Horizontal Axis Labels option.
- As a result, the Axis Labels window will appear. At the Axis label range: select the E5:E10 cells. Last but not least, click on the OK button.
- Now, the Select Data Source window will appear again. Click on the OK button.
- As a result, the chart will now look like this.
- Afterward, select the chart >> go to the Chart Design tab >> Add Chart Element tool >> Axes options >> Secondary Horizontal option.
- Now, you will have a chart with two horizontal axes.
- Now, you don’t need the orange line chart anymore. That’s why double-click on the orange chart. As a result, the Format Data Series task pane will appear on the right side.
- Under the Series Options group, go to the Fill & Line group. Afterward, under the Line group, select the No line option.
- As a result, you will see the orange chart is no more.
- Now, you don’t need the left vertical axis anymore. So select it and press the Delete button.
- As a result, you will see the char is simpler now.
- Now, to take the right vertical axis to left, double-click on the upper horizontal axis. As a result, the Format Axis task pane will appear on the right side.
- Afterward, go to the Axis Options group >> Vertical axis crosses option >> select the At date option >> write the date as 10/1/2021 inside the text box.
- At this time, double-click on the vertical axis again and go to the Axis Options group. Write the minimum bound as 0.0 inside the Minimum text box under the Bounds group.
- Afterward, to remove the upper horizontal axis, double-click on the axis. As a result, the Format Axis task pane will appear.
- Subsequently, go to the Axis Options group >> Labels group >>Â Label Position option >> choose the option as None.
- Afterward, go to the Fill & Line group from the Axis Options group >> Fill group >> No line option >> Line group >> No line option
Thus, you will finally be able to create an Excel chart by month and year. For instance, it should look like this.
Read More: How to Create Graph from List of Dates in Excel
Download Practice Workbook
You can download our practice workbook from here for free!
Conclusion
In a nutshell, in this article, I have shown you 2 easy ways to create an Excel chart by month and year. Read the full article carefully and create your own chart by month and year. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations. Thank you!
