Dynamic scroll chart

Today I will show you to make dynamic scroll chart, it is particularly useful in making dashboard, where you have large data point to be shown in single chart

Before I make a quick scroll chart, some background work

1)      Put your data in place (e.g.I have 26 data point in cell B3 to C28)

2)      Paste a scroll bar from developer ribbon

3)      Format scroll bar (I want 6 data at a time in chart)

SBC1

cell link is F2

4)      Learn offset formula

OFFSET formula, Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells.

‘=OFFSET(reference,rows,cols,height,width)’

In our case reference = B2, rows=F2, cols=0/1, height=6, width=1

5)      Using ‘name manager’ in formula tab name range: cell B3 to B8 ‘label’ and C3 to C8 ‘data’

6)      In name manager select ‘label’, refers to(bottom):  =offset(Sheet4!$B$2,Sheet4!$F$2,0,6,1)

7)      select ‘data’, refers to: =offset(Sheet4!$B$2,Sheet4!$F$2,1,6,1)

SBC3

I m making my chart in sheet4, that’s why you see sheet4 reference

8)      Insert line chart with data B3 to C8

9)      Select the line of line chart, following formula appears in formula bar ‘=SERIES(,Sheet4!$B$3:$B$8,Sheet4!$C$3:$C$8,1)’

10)   In the formula delete $B$3:$B$8 and put ‘label’, delete $C$3:$C$8 and put ‘data’, do not delete ‘!’ mark, go to end of the formula press enter

11)   My man, you are ready for scrolling, scroll the bar and your data will change.

SBC2

If you select the line of line chart you can see how your data is moving

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s