scrolling-period-chart-update

The scrolling period chart is a special solution in that case if we would like to examine a longer time phase and within it an emphasized period. There are two instances of portraying the results of a period.

The first one is when we create a detailed statement. This variant is especially for analysts, every little detail is displayed on the chart or on the dashboard.

In the second case we creating a form control based dashboard and only portray the highlighted events.

The purpose of this is that the manager overviewing the report wouldn’t get lost in the details. It is important for a manager to get only the final data because he / she will make strategic decisions based on these.

What will happen when we would like to portray the details and the summarizing data at the same time? This can be solved with the help of the scrolling period chart!

scrolling-period-chart-start

Let’s see the base data! In this case we have built the starting Excel table especially simple. The goal of this is so we don’t get lost in the sea of numbers. Let’s examine the following figure closely!

The source of data for the scrolling period chart is a column containing dates and two columns containing of values. With the dates we have no problem; we will move the chart on the base of these. Of course we are talking about fixed values. This is also true for the values of the second column.

In the third column we use formulas for the display of one part of the data row of the whole data base on the chart. (We will talk about this at the end of the article.)

On the bottom a scroll bar can be found. It plays an important role in the handling of the dates. We’ll explain in short terms why did we need it! The scroll bar is responsible for the movement of the scrolling period chart. It is a built in Excel controller that you can reach with the help of the Developer Tab.

On the horizontal axis we have set the dates and on the vertical axis the values.

scrolling-period-chart-02

Scrolling Period Chart – Graphs

The function of the three charts is as follows. The first one (can be seen on the very top) is a line chart (marked by blue color) portrays a part of the whole time period. In this Excel example we have chosen a 10 day interval.

The second is a line chart represented by a dashed line (marked by dark blue color) portrays the values of the whole time period.

With simpler terms we have drawn here the values of the second column.

The third part of the scrolling period chart is an area chart (we have highlighted this with a red circle) and its function is this: The area located under the dashed line, just like on the first chart, only portrays a fragment of a period.

What really great about it is that it portrays this in comparison to the whole period.

Scrolling Period Chart – In motion

How the scrolling period chart will move?

We are not going to overcomplicate this. Here is a little help! Right click the scroll bar, then the Format Control menu.

In the appearing Format Control window we can set that by the moving of the scroll bar how big of a time interval we shall portray.

The value of the current setting is 6. This value continually increases or decreases by the right or left movement of the scroll bar.

The minimal value doesn’t need extra explanation, the maximum value is 83 and this is exactly how many rows our data table contains. For this example we have portrayed an 83 day interval.

The value of the Incremental Charged field is 1, so the scroll bar steps this many days when moved to the right or to the left. The Cell Link field points to the F1 cell.

scrolling-period-chart-03

And this is what the Excel formula looks like that is responsible for the 10 day interval portrayal.

=IF(AND(ROW(B1)-1>=$F$1;ROW(B1)-$F$1<11);C1;0)
When we click on the arrow on the right side of the scroll bar then the value of the F1 cell will increase by 1. It is true inversely: by clicking on the left side arrow the value of the F1 cell will decrease by 1.

The formula continuously watches the value of this cell and choses the ten day interval to be portrayed.

Let’s see some examples: if the value of the F1 cell is 6, than values between the 7th and 16th days will appear. If the value of the F1 cell is 0, than we will see the values of the first 10 days.

Have fun! We recommend you download the material for this article and with its help analyze the formulas and the operation of the chart!