All right so it's time to put Excel to the test.
Let's see what we can do to create a chart.
Unlike most people I've ever seen an Excel go ahead and open up the section 5 look up reference file
.
I'm going to jump to the last tab here.
Scroll chart tab.
And our goal here is going to be to create a chart with dynamic data reference.
And we're going to do that using the offset function.
And the goal is going to be to create a chart that we can zoom and scroll to change the view of data
that we're looking at.
So we're going to step beyond the static charts that 99 percent of Excel users are out there using.
So a little bit of housekeeping items.
The first is that we're going to use scrollbars and to do that you need to insert them through the developer
tab so take a look at the top of your cell screen.
If you don't see the developer time where you're going to do and this is in 2013 it's going to the Home
tab click Options customize the ribbon and then over here on the right you should see a checkbox next
to developer years is likely unchecked to go ahead and give it a check.
Press OK and you should see it pop up.
If the user experience is a little different in Excel 2010 or if you are using an older version.
Honestly just google it and make sure you have your developer tab activated.
So I'm in developer I'm going to go to the insert button and find this scroll bar form control click
it and then drag out one next to this scroll line.
OK then I'm going to do the exact same thing and drag a second next to that Zoom line.
And if you want to edit the appearance of them you can control click them.
I made that one a little fat and I'm a perfectionist so I'm going to want them kind of like it's close
to identical as I can.
That's fine.
So now we're going to do is added what these squirrel charts actually do.
So I'm going to right click the first on and click Format control.
So this is basically saying what values can the scroll bar take.
What cells are they linked to.
Basically what are the scroll bars going to control.
So in this case the scroll scroll bar.
I want to have a minimum value of 0.
I'm going to set the maximum value to six increment of one and Page change.
That's fine I don't really care about that.
The cell link is going to be cell d 16 which is right next to the scroll indexed text.
So that's it for that one.
And then right click the zoom scroll bar click Format control again.
In this case I want it to have a minimum value of 1 a maximum value of 12 and you'll see why in a little
bit the one in the 10 are fine.
In this case the cell link is going to be 17 next to the zoom index text so it.
OK.
And there you go.
We've set up both of our bars.
And now if you just click through you'll see that they're linked to the cell values in D16 and 17.
So that's step one.
Step two is going to be to work with some named arrays that are going to feed our chart data reference
.
So I'm going to go into formulas name manager and this is my fielding name or I can just kind of leave
it as is and create a new one.
The first one is going to be called scroll index and that's just going to refer to sell these 16.
So I pressed a little data selector press it again so scroll chart d 16.
OK.
And create another one called Zoom index and that's going to just refer to.
So the 17.
So I've created two new named arrays called scroll index and zoom index.
So this is where it gets a little bit more complicated.
I've got my scroll index.
I've got my Zoom index.
The next piece that I need is the dynamic array that's going to feed into my chart and I'm going to
need at least two of these one for my x values which are the months and one from my y values which we'll
start with by plotting impressions.
If we wanted to plot both impressions and clicks we would need two named arrays for each series of y
values.
So let's start with the x values here.
I'm going to create a new named array.
I'm going to call it X values and note exactly how you write these named arrays because we'll need to
use the exact same syntax and the exact same name with the same capitalizations and lower case letters
.
Once we feed them into the chart.
So I'm using capital X and then a lowercase V.
So my x values are going to refer to a formula and that formula is going to be an offset.
So type offset and my x values live in column B So my reference or starting point will be B-3 and then
remember the second piece of the offset function which was how many rows down do you want to move.
It's a little trickier now because we don't have the help or box kind of guiding us through the formula
.
This is kind of the Wild West.
We're on our own here so we need to remember how the offset formula is built.
In any case the second piece is how many rows down and that depends on what the value of the scrollbar
is which lives.
And so the 16 which is tied to the scroll index named array.
So I'm going to type scroll index here.
The second piece how many columns over do I want to move.
In this case my x values in my month's only live in column B so I don't want to move any columns over
at all.
So when I type 0 there the next piece is going to be the height of the array that I want to return.
So this is dependent on the Zoom scroll bar so whatever value the zoom scroll bar has output into D
17 is the height of the array that I want to feed into that chart because that's the number of months
that I want to look at any given view either crunched down to one or two months or zoomed out to all
12.
So that's just going to be my Zoom index named array.
And then last but not least the width of my reference is just going to be 1 because it's 1 column wide
.
Close the parenthesis and hit OK.
And that's created my x values field.
I'm going to create my y values field in the exact same way.
So it's going to be an offset function.
The only difference is that the starting point rather than being B-3 is going to be whichever field
I want to plot here.
So let's plot impressions for this example.
So my starting point will be.
And then remember how many rows down it's going to be scroll and guess how many columns over the 0.
What's the height zoom index and what's the width.
One close the parenthesis and hit OK.
And now I've created an x values array and a y values array along with my scroll index and my Zoom index
.
So when I go ahead and close out of the name manager the last piece then I need to do is populate this
chart that I've basically set up a shelter right here.
So I'm just going to Right-Click select data and I'm going to add a data series here.
Syria's name is going to be impressions since that's the field that I'm going with from my y values
and the series values rather than just selecting an individual cell or range of cells here.
What I'm going to do is make a selection just to give me the beginning part of this reference because
I want this scroll chart and the exclamation point.
But I'm going to delete everything following the exclamation point and I'm going to replace all that
with y values written exactly as I defined it so check that.
OK.
You can already see in the background things are starting to populate.
And now in my horizontal or category axis labels you press the button and the range is going to be again
rather than this fixed array.
When I make a selection just to get the starting point in place delete everything following the exclamation
point.
And I'm going to put the x values here.
OK.
You'll see that our x axis has updated to show names of months.
And there we go.
So as you can see we're charting out impressions.
And now as I move the zoom scroll bar the width of view which is actually captured by the height of
this array is shifting.
And similarly if I shift the scroll index now our entire view however wide or However zoomed we set
it is moving from left to right in the chart.
So right now I'm looking at six months of data and I'm seeing how trends month over month as I zoom
in I can look at just a single day at a time or two months or three and so on and so forth.
So if we wanted to include clicks as a secondary y value.
That's easy enough to do it just going in the name manager create a y values to named array and then
just change the starting point of that offset function to D-3 and the rest will be exactly the same
.
So there you go scrolling zoom level dynamic chart using the offset function