how to create charts for the last 6 month in excel

Home / Advanced / Create a Rolling Chart for Last 6 Months

When creating reports in Excel, a common requirement is to report on a rolling basis. For example, this could mean the last 12 months, the last 6 weeks or the last 7 days.
Whatever the timeframe being reported, this can mean a lot of time editing chart sources and formulas to show the right data.
This blog post looks at creating a dynamic rolling chart to show the last 6 months of data, so when new data is added to the table, the chart automatically updates to report the last 6 rows (months).
Rolling chart showing last 6 months sales
To create a rolling chart, we will first create two dynamic named ranges. These will automatically capture the last 6 months data. One named range for the chart data, and the other for its labels. We will then use these named names for our chart source.
Watch the Video instead?

Create the Dynamic Named Ranges

The OFFSET function will be used to make the named ranges dynamic. This function enables you to reference ranges, relative to another range of a sheet. So this can be used to capture the last 6 rows.
Let's first create the chart data named range.

Click the Formulas tab of the Ribbon and then the Define Name button.
Enter a name for the defined name. You can use whatever name you wish. I have called this one ChartData.
In the Refers to area, enter the formula below. The different parts of this formula can be edited to meet your needs.

This formula starts from cell B1. It then moves to the bottom of the column. The column bottom is found by counting how many values in column B. The -6 and 1 on the end of the formula are used to return the last 6 rows and 1 column from that cell.

=OFFSET(Sheet1!$B$1,COUNT(Sheet1!$B:$B),0,-6,1)

We now need to create another dynamic named range for the chart labels. The OFFSET function below will be used.
This function uses the previous defined name and selects a range of equal height that is one column to the left. In this example, that is column A.

=OFFSET(ChartData,0,-1)

Defined range for the chart labels

Editing the Charts Data Source to use the Dynamic Ranges

The chart new needs to be edited to use the named ranges for its data and its labels.

Click the Design tab under Chart Tools on the Ribbon.
Click the Select Data button.

Editing the chart source

Click the Edit button from the Series section on the left.
Cell B1 has been selected for the Series name. This uses the Sales header for the name of the data series (in this example that is kind of redundant as I only have one series, but you may have more).
For Series Values, the ChartData named range has been entered. Be sure to keep the sheet name in there too like in the image below.

Using a defined name for the chart series

Click Ok, and then click the Edit button for the Labels section on the right.
In the Axis label range area, the ChartLabels named range has been entered. Once again be sure to keep the sheet name in the reference. And click Ok.

Editing a chart labels
The dynamic rolling chart is created. If you add more rows to the table, the chart automatically updates to show the last 6 months.

Watch the Video

Reader Interactions