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. In this tutorial, we create a rolling chart in Excel to produce a report like this.

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 in Excel 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).

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.

When using column charts to compare values, you may want to highlight the max and min values on the chart. By highlighting the maximum and minimum values, it removes any confusion identifying the top and bottom values.

Finding the Max and Min Values

To show the max and min values on a column chart, we will first need to identify the max and min values of our range. These values will then be used as a second data series when we create the column chart.

We will use the following formula to check the values in our range, and return the value if it is the maximum or minimum. Otherwise the NA() function is used to return the #N/A error. We want this because the chart will not plot these error values.