How to Create Animated Charts in Excel

Copy of New Blog Post Format-3-2.png

Overview

A scrolling chart is a useful to display a long timeline of data.

An animated scrolling chart is a great way to create interactivity

An animated scrolling chart is a great way to create interactivity

For example, trying to view the sales for a whole year might not fit well on the screen, and the chart can become cramped and hard to read. A scrolling chart allows a smaller population of data to be displayed at once, but retains the ability to move through a longer time period. This can be done by using a scroll bar, and without having to amend any of the underlying data.

This chart has too many data points to read!

This chart has too many data points to read!

How to Create the Chart

This process requires several components:

  1. Original data – this is all the data to be displayed for all the periods, in this example, this data is located in cells A1:B60, this contains the sales for the first two months of the year.

The example Data Set

The example Data Set

  1. Chart table – decide how many dates do to display at the same time in the chart. This dataset has 59 days, but we can set the chart to only display 20 rows at once. Therefore this table needs to be 20 rows high.

A scroll bar can be used to select which 20 days to display in the chart. Several steps are required to prepare the data for the scroll bar.

In D1, set the starting row for the chart, by entering a 0. A 0 will indicate to Excel to display 20 rows from the first data available in the chart. Similarly enter a 1 to see 20 dates from the 2nd date, 6 from the 7th date and so on.

Creating a 20 row table that will dynamically populate

Creating a 20 row table that will dynamically populate

Now use the Offset formula to create the data for our chart based on the value of this cell:

Enter in cell F2 (the first cell of the data for the chart) the following formula:

Use the offset function to pull in data from the original table.

Use the offset function to pull in data from the original table.

The Offset function will return the value of a cell, in reference to another cell.

The arguments that this function requires are:

Reference- this is the cell reference to get the value of another cell, A2 in this example

Rows- specify how many rows, up or down in relationship to the reference cell (A2), the required value is. Here we will enter cell D1 (using absolute reference as this cell won’t change when we drag the formula down)

Columns- enter 0 as the column does not need to be offset from A2

The formula is displaying the cell value that is 0 rows below cell A2 (this is the value of A2 itself)

When set at 0, the first row of data is pulled.

When set at 0, the first row of data is pulled.

Change the value in cell D1 to 1 to display the value of the cell one row below cell A2

When set to 1, the second is pulled

When set to 1, the second is pulled

Change the value in cell D1 to 6 display the value of the cell  6 rows below cell A2:

When set to 6, the 7th row of data is displayed

When set to 6, the 7th row of data is displayed

Drag (or copy and paste) the formula down to the end of your chart data as well as on the right to get the sales not only the dates. The table should look like this:

Now our 20 row table is dynamically populated depending on what is entered in D1

Now our 20 row table is dynamically populated depending on what is entered in D1

Chart – now insert a chart that will use this table as the data source.

Now create a chart on this data table

Now create a chart on this data table

Try and change the cell D1 value to change the date range displayed in your chart.

Now the chart will change each time you change cell D1

Now the chart will change each time you change cell D1

A scroll bar – Instead of changing the value of cell D1 manually, a scroll bar can be added for ease of use. To add a scroll bar go to : Developer in the toolbar, Click Insert in the Controls section and click on the Scroll Bar:

Add a scroll bar

Add a scroll bar

Once you click, you should be able to draw a rectangle.

To customise the scroll bar, right click on the scroll bar and click on Format Control:

Format the scroll bar

Format the scroll bar

In the Format Control window define:

Set up the scroll bar properties

Set up the scroll bar properties

Current value - leave this as a 0, it’s the initial position of the scroll bar

Minimum value – this is the lower position of the scroll bar, so if the scroll bar is fully on the left, what value to display. For this example, this is 0, the very first value

Maximum value – this is the upper value of the scroll bar. Remember the scroll bar (and the value of cell D1) will decide the first position of the data that to display. In this example there’s a total of 59 rows of data, and the chart displays 20 values at a certain time, so the starting position for the highest range that to display is 39 (59 less 20 rows).

Incremental value – this will define how many values to move every time the arrows left or right are clicked. Enter one, so each click on the arrows left or right of the scroll bar will move one date at a time.

Page change – to move the data with the scroll bar either click on the arrows left or right, or actually click in the scroll bar itself. The incremental value sets the change for arrow clicks, but this Page change variable sets the change for each click on the scrollbar itself. This can be a larger number, 10 for example.

Cell link – enter D1, so the scroll bar will automatically change the value of cell D1, thus changing the data of the chart.

If you right click on the scroll bar, it will enter the edit mode so that it can be drag positioned on the sheet. Position your scroll bar under the chart and resize to the chart width

We are all set!

The finished chart

The finished chart

Further Reading

To learn more about the excel features and functions used here, visit these Microsoft Office links:

Inserting an excel scroll bar

Offset function

Inserting a chart in excel

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit