Blog Archives

Excel – Create a Dynamic Scrolling Chart

In a couple of my other blogs, I have shown you how to create dynamic ranges ( http://wp.me/p2EAVc-9Y ) and also how to use these to set up a 12 month rolling chart ( http://wp.me/p2EAVc-ah ). This time I am going to show you how to create a dynamic scrolling chart.

By that, I mean a chart that can cover many (100s, 1000s) of data points but only show a limited number at any one time and the user uses a scroll bar to select which data is displayed. This type of chart allows you to scroll through years’ worth of data but only show a set number of data points but only using a single chart.

Here is what we are going to create…

To achieve this we are going to have to combine a number of techniques. So…if you are not familiar with the OFFSET function to create dynamic ranges, then check out this blog first ( http://wp.me/p2EAVc-9Y). If you are not familiar with creating a dynamic chart then check out this blog ( http://wp.me/p2EAVc-ah ). Without this prior knowledge, setting up this scrolling chart will not be obvious…to say the least.

Let’s say I have 8 years of data. If I plot all of it on one chart it’s going to be messy, and not easy to read – too many points and not enough labels!

All data points showing

All data points showing

It would be so much easier to read if there were only 12 months of data visible for example, but I need to look at all my data over the full 8 year period and beyond as I add more data.

Create your chart, including all your data…we will make changes later to convert it into the scrolling dynamic chart we want.

As always, I am going to create my dynamic named range to cover the data points first but to start with I am going to add in my scroll bar as I will need its control cell as part of my dynamic range formula. Place it wherever you want…I quite like to put it directly above or below my chart area.

To add a scroll bar, make sure your developer tab is turned on. From the FORM CONTROLS, select the SCROLL BAR.

Form Controls window

Form Controls window

Click and drag your mouse pointer to draw the scroll bar.

Chart with scroll bar underneath

Chart with scroll bar underneath

ScreenHunter_166 Jul. 15 14.10

 

Right click on your SCROLL BAR and select FORMAT CONTROL.

Format control window for the scroll bar

Format control window for the scroll bar

In the CONTROL tab of the FORMAT CONTROL window, set the start value to 0. This may vary slightly depending on where you start your dynamic range from, but in this example, the starting point of my dynamic range is the first data cell in the column so I will leave the default value 0.

Your maximum value will depend on how many data points you have all together. I have left it here at the default 100, but change this according to your own data.

Now assign a cell to link to your SCROLL BAR. This cell is important as this stores the value generated by moving the bar to the left or right. When you close the window you should see 0 in the cell you selected. In this example, I have chosen $G$4

In another cell (again, does not matter where) enter the number of data points you want to see in your chart. For this example I have chosen 12 but you can set any number you like, and in fact this can be changed as you use the chart.

Now to create the dynamic ranges. As always I prefer to set the data range first and base my labels and any subsequent data series off that one.

For the data points, my formula will be as follows;

Dynamic range formula for the data

Dynamic range formula for the data

The start row for my dynamic range is set by the value in the linked cell. Like that, as I move the SCROLL BAR right or left, it sets the first row to start that many rows away from B2. The HEIGHT of my data set to use in the chart is set by the value I have manually entered in G4.

Now for the dynamic range to calculate which labels to use;

Dynamic range formula for the labels

Dynamic range formula for the labels

Again if you are unsure as to why the formula is set up like this refer to my other blog “Excel – Create a 12 Month Rolling Chart” ( http://wp.me/p2EAVc-9Y).

The final step is to edit all the series and label range references, replacing them with the dynamic range names just like we did in the 12 month rolling chart.

Edited data cell references

Edited data cell references

Edited label cell references

Edited label cell references

You should now have a fully dynamic and interactive chart. All you have to do now is use the scroll button to view all your data.

The final chart with only 12 data points

The final chart with only 12 data points

 

Advertisements

Excel – Create a Dynamic 12 Month Rolling Chart

If like many people producing reports at work you report on a rolling yearly basis, you are probably manually changing the data range of your chart every month too either by changing the cell references or even worse, deleting the first month of data and adding the most recent data to the end of your table. All time consuming, and ultimately unnecessary.

It is possible to create a dynamic 12 month rolling chart that automatically displays the last 12 months of data (or any other time frame in fact). All you have to do is add data to the end of your data table and let Excel do the rest!

For this you will need to use the OFFSET function. If you are not familiar with this function, then go to my “Creating Dynamic Ranges” blog ( http://wp.me/p2EAVc-9Y ) to understand how this function works before tackling this…otherwise this will be a complete mystery and quite unfathomable.

Let’s start with some basic data – one year of sales figures.

Basic data table

Basic data table

To create a dynamic chart using this simple table we will need two named dynamic ranges – one for the data itself and one for the labels. Note that when working with charts you will need to create a separate dynamic range for each series as charts treat each series separately so you cannot create a single dynamic named range that includes all rows and columns.

Personally, I like to start by creating the dynamic range that handles the data.

Go to NAME MANAGER and select NEW, or go to DEFINE NAME. Give the range a name of some sort. In this example I will use ChtData. Don’t use the word Chart in your name, apparently it won’t work (Mr Excel).

In the refers to box enter the following formula;

Dynamic range formula to select 12 months of data

Dynamic range formula to select 12 months of data

We use -12 in the HEIGHT argument as we always want to count back 12 rows from the last cell containing data.

Then we need a dynamic named range to pick out the correct labels (in this case dates) to match the dynamically selected data. In this example let’s call it ChtLabels.

Following the same steps as above enter the following formula;

Dynamic range formula to select the correct date labels

Dynamic range formula to select the correct date labels

Rather than work everything out from another reference cell, we can use our ChtData range as the reference point or in this case reference range. This will work out what to select based on the first dynamic range, and select values from one column to the left (WIDTH argument = -1).

Check that both ranges work properly by adding some new data at the bottom of the chart and click into the refers to box of the named range to see which cells it selects.

Now time to create our chart. Just follow the usual steps to create a chart…INSERT, CHART etc.

Once our “static” chart is set up, now comes the clever bit.

Select Data icon on the ribbon

Select Data icon on the ribbon

 

 

 

 

 

 

 

Make sure you have clicked on your chart and then click on SELECT DATA.

 

Edit data source window

Edit data source window

In the SELECT DATA SOURCE window, click on the series you want to turn into a dynamic one. If you have multiple series, then you will need to do the following steps for each one.

Click on EDIT.

Original and updated range reference

Original and updated range reference

Currently you should see the cell references relating to the series in the SERIES VALUES box. Remove any cell references but leave the sheet name and exclamation mark. Replace the cell references with the dynamic range name for the data – in this example ChtData. Click on OK. Now do the same for the labels.

Click on any one of the labels under HORIZONTAL (CATEGORY) AXIS LABELS and then click on EDIT.

As before, remove any cell references from AXIS LABEL RANGE, leaving the sheet name and exclamation mark exactly as before. So in this example we should now have Book1!ChtLabels.

Amended label range reference

Amended label range reference

All ready to go! Now just add new data and watch your chart automatically update to always show the last 12 months of data.

Completed chart with extra data but still only showing most recent 12 months

Completed chart with extra data but still only showing most recent 12 months

Excel – Creating Dynamic Named Ranges

If you use named ranges in Excel, you’ll know how useful they can be. If you are not familiar with them check my blog ( http://wp.me/p2EAVc-99 ) on setting up named ranges and how they can be used.

If you are currently using them you will also know that it can be a bit of pain having to update the range all the time as new records are added or removed from your data. If your data does change regularly, wouldn’t it be nice to have a named range that automatically adjusts to the correct number of rows or columns? Well…you can, by creating dynamic ranges.

At the heart of a dynamic range is the OFFSET function. Before we embark on creating ranges, let’s look at the syntax first as it is not that straightforward.

The official syntax you get from Microsoft when you enter the function is;

=OFFSET(reference, rows, columns, [height], [width])

…which doesn’t really tell you much if you have never used this function before.

Reference: every range has a starting point, even dynamic ones. Reference is like an anchor point from which the rest of the range is referenced.

Rows: is the number of rows away from the reference or anchor point. Positive numbers represent rows down, and negative numbers rows up.

Columns: is the number of columns away from the reference or anchor point. Positive numbers represent columns to the right, and negative numbers columns to the left.

=OFFSET($A$1,0,0 would mean the range starts from A1

=OFFSET($A$1,1,0 would mean the range starts from A2 (1 row below)

=OFFSET$A$1,0,1 would mean the range starts from B1 (1 column to the right)

…and so on and so forth.

Height: this is optional, but represents the number of rows you want to include in your range. So if we had =OFFSET($A$1,0,0,5 then the range would be 5 rows starting from A1.

Width: another optional argument. This sets the number of columns that make up your range. Continuing the formula from above, if we have =OFFSET($A$1,0,0,5,3) then we would set our range to 5 rows high and 3 columns wide starting at A1.

Using OFFSET to create a defined range

Using OFFSET to create a defined range

This of course gives us a fixed range, as we are specifically defining the number of rows and columns we want in the named range.

So how do we get it to be dynamic?

We need to build in functions that calculate the number of rows and/or columns within the OFFSET function. Generally speaking we do this within the height and width parts of the function, but you may need to alter the other arguments such as when you work with dynamic charts.

So let’s build a dynamic range;

First of all, you can’t create a dynamic named range in the same way as a normal named range i.e. select a bunch of cells and give them a name. We need to go to the NAME MANAGER and click on NEW, or go to DEFINE NAME.

In either case, give your range a name.

Decide whether the range is specific to the worksheet or can be referenced from anywhere in the workbook.

Add a comment…purely optional as a note to yourself or anyone else vaguely interested in the named range.

And then the important bit – REFERS TO: Rather than entering a basic range, this is where we have to use the OFFSET function.

Starting with a basic table, I want to create a dynamic range that automatically works out the number of rows in a fixed width table of 5 columns.

Working out what we have and what we need to create our dynamic range

Working out what we have and what we need to create our dynamic range

So our formula will look like this;

=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A),5)

In plain English…starting 1 row below A1, count the number of cells that contain numbers in column A (to work out number of rows) and set the width to 5 columns.

In this example I am excluding the heading row, but you may well need to include it, if for example, you are creating a dynamic range to use in a Pivot table.

Below, we can see the correctly selected data.

Correctly selected range based on rows found

Correctly selected range based on rows found

If I add a few rows of data, the range is automatically recalculated to include them.

Recalculated range with extra rows

Recalculated range with extra rows

If I want the range to check the number of columns too then I need to modify the formula as follows;

=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Recalculated range with extra columns and rows

Recalculated range with extra columns and rows

Now, not only does the formula count the number of cells containing numbers in column A, it counts the number cells containing text (hence COUNTA)across the top of my data table to give me the number of columns that make up my table.

So now you can create either a one-way or two-way dynamic range depending on your personal requirements, and no need to manually update the REFERS TO cell references.

In another blog, I will show you how you can use the OFFSET function to create dynamic charts.