Excel Chart by Month and Year (2 Suitable Examples)

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.


Download Practice Workbook

You can download our practice workbook from here for free!


2 Examples of Excel Chart by Month and Year

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.

Monthly Sales Quantity to Create Chart by Month and Year


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.

Insert Two New Columns

  • Second, click on the C5 cell and insert the following formula which involves the YEAR function. Afterward, press the Enter button.
=YEAR(B5)

Use the YEAR Function to Create Chart by Month and Year

  • Subsequently, place your cursor in the bottom right position of the cell and drag the fill handle downward upon its appearance.

Drag the Fill Handle to Copy Formula

  • 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")

Use the TEXT Function to Create Chart by Month and Year

  • 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.

Drag Fill Handle to Copy Formula

  • Now, select the cells C5:E10. Subsequently, go to the Insert tab >> Insert Line or Area Chart tool >> Line option.

Insert Line Chart to cReate Excel Chart by Month and Year

  • 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.

Excel Chart by Month and Year

  • Now, for a better and cleaner look, click on the chart >> click on the Chart Elements icon >> untick the Gridlines option.

Customize the Chart Elements

  • Next, for a better understanding of the chart, double click on the Chart Title and write Sales Quantity Per Month.

Edit Chart Title

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.

Excel Chart by Month and Year

Read More: Use Scatter Chart in Excel to Find Relationships between Two Data Series


Similar Readings


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.

Insert a Chart to Create Chart by Month and Year

  • As a result, you will have a line chart for the selected data range.

Line Chart

  • At this time, for a more attractive look, click on the Chart >> Chart Elements icon >> Untick the Gridlines option.

Customize the Chart Elements to Create Chart by Month and Year

  • Now, for a better understanding of the representing chart, double click on the Chart Title and subsequently, write Sales Quantity Per Month.

Edit the Chart Title

  • Consequently, your chart will look like this now.

Excel Chart by Dates

  • You can see there are dates on X-axis. But you want only the month and number. Now, double-click on the X-Axis.

Access 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.

Format the X-axis to Create Chart by Month and Year

  • Next. create two columns named Month & Year and Helper Column.

Create Two New Columns to Create Excel Chart by Month and Year

  • Afterward, in the Month & Year column, copy and paste all the given dates. And, put 0 at the Helper Column cells.

Fill the New Columns

  • 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.

Format Cells of the Dates

  • 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.

Fix the Date Format

  • As a result, you can see the dates are in month and year now.

New Formatted Dates

  • 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.

Access the Select Data Source Window

  • As a result, the Select Data Source window will appear now. Click on the Add button.

Add New Series

  • 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.

Refer the New Series Data

  • At this time, you will be back on the Select Data Source window again. Finally, click on the OK button.

Finalize the Entries of the Chart to Create Chart by Month and Year

  • As a result, another data series is added to the chart and it is the orange marked line.

Chart with New Series

  • 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.

Access the Sales Series

  • As a result, you will have a chart like the following figure.

Chart with New Axis

  • At this time, right-click on the chart area again and select the Select Data… option from the context menu.

Access the Select Data Source Window

  • 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.

Edit Series 2 Values to Create Chart by Month and Area

  • 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.

Edit the Value Range of the Series 2 Entry

  • Now, the Select Data Source window will appear again. Click on the OK button.

Finalize the Edit to Create Chart by Month and Year

  • As a result, the chart will now look like this.

Edited Chart

  • Afterward, select the chart >> go to the Chart Design tab >> Add Chart Element tool >> Axes options >> Secondary Horizontal option.

Add Secndary Axis to Create Chart by Month and Year

  • Now, you will have a chart with two horizontal axes.

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.

Access the Series 2 Line Series Options

  • As a result, you will see the orange chart is no more.

Chart without Orange Line

  • Now, you don’t need the left vertical axis anymore. So select it and press the Delete button.

Delete Left Vertical Axis

  • As a result, you will see the char is simpler now.

Chart with One Vertical Axis on Right

  • 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.

Fix the Cross Position of Two Axes

  • 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.

Fix Bounds of Vertical Axis to Create Chart by Month and Year

  • 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.

Remove the Upper Horizontal Axis Label

  • Afterward, go to the Fill & Line group from the Axis Options group >> Fill group >> No line option >> Line group >> No line option

Remove the Upper Horizontal Axis Line to Create Chart by Month and Year

Thus, you will finally be able to create an Excel chart by month and year. For instance, it should look like this.

Excel Chart by Month and Year

Read More: How to Add Data Table in an Excel Chart (4 Quick Methods)


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.

And, visit ExcelDemy to learn many more Excel problem solutions and tips. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo