Home

Chart data

%3CLINGO-SUB%20id%3D%22lingo-sub-750337%22%20slang%3D%22en-US%22%3EChart%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-750337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123037iA12B1F1062D39FCF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22123.JPG%22%20title%3D%22123.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20above%2C%20I%20have%20data%20of%207000%20rows%20in%20one%20column%20and%20I%20want%20to%20see%20graphical%20change%20for%20every%2030%20odd%20blocks%20of%20data.%20is%20there%20any%20easy%20way%20to%20insert%20any%20button%20or%20anything%20to%20move%20the%20block%20down%20when%20I%20press%20the%20arrow%20so%20that%20I%20can%20see%20the%20graphical%20changes%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-750337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751459%22%20slang%3D%22en-US%22%3ERe%3A%20Chart%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374851%22%20target%3D%22_blank%22%3E%40Santhosh750%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20that%20can%20be%20done.%20It's%20one%20of%20the%20core%20techniques%20of%20dynamic%20charts%20in%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20you%20need%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EA%20cell%20where%20the%20user%20indicates%20which%20data%20to%20show%3C%2FLI%3E%0A%3CLI%3EA%20formula%20that%20uses%20that%20cell%20input%20to%20build%20a%20dynamic%20range%3C%2FLI%3E%0A%3CLI%3EA%20chart%20that%20uses%20the%20dynamic%20range%20as%20the%20chart%20series%20source.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EFor%201%3A%20You%20may%20want%20to%20use%20a%20Spin%20Button%20or%20a%20Scroll%20Bar%20from%20the%20Developer%20ribbon%20%26gt%3B%20Insert%20dialog.%20If%20you%20use%20a%20Scroll%20bar%2C%20set%20it%20up%20so%20that%20a%20page%20change%20equals%2030%2C%20the%20minimum%20is%201%20and%20the%20maximum%20is%20the%20number%20of%20rows%20in%20your%20data%20set.%26nbsp%3B%20The%20Scroll%20Bar%20writes%20its%20value%20into%20a%20spreadsheet%20cell.%20Give%20that%20cell%20a%20range%20name%2C%20like%20%22SelectedBlock%22%20so%20it%20is%20easier%20to%20read%20in%20formulas.%20(you%20could%20also%20let%20the%20user%20type%20in%20the%20start%20row%20of%20the%20data%20block%20into%20that%20cell%20manually)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%202%3A%26nbsp%3B%20Create%20two%20named%20ranges%20with%20these%20names%20and%20formula%20references.%20(Assuming%20your%20data%20starts%20in%20A6%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EgraphX%20%3DOFFSET(Sheet1!%24A%246%2CSelectedBlock-1%2C0%2C30%2C1)%3CBR%20%2F%3EgraphY%20%3DOFFSET(graphX%2C0%2C1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%203%3A%20Create%20a%20new%20chart%20or%20use%20an%20existing%20chart%20to%20plug%20in%20the%20names%20in%20the%20series%20dialog.%20When%20you%20enter%20the%20names%2C%20you%20need%20to%20prepend%20it%20with%20the%20sheet%20name%2C%20like%20in%20the%20screenshot.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20751px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123104i826815F90D10079D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-12_14-53-29.png%22%20title%3D%222019-07-12_14-53-29.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20the%20user%20clicks%20on%20the%20Scroll%20Bar%20slider%2C%20the%20value%20in%20the%20SelectedBlock%20cell%20will%20change.%20This%20will%20cause%20a%20recalculation%20of%20the%20graphX%20and%20graphY%20range%20names%20and%20the%20chart%20will%20adjust.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-767608%22%20slang%3D%22en-US%22%3ERe%3A%20Chart%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-767608%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much%20for%20responding%20to%20my%20Query.%20can%20you%20please%20add%20some%20pictures%20to%20the%20point%201.%20in%20your%20description%3F%20I%20am%20not%20able%20to%20figure%20out%20exactly%20how%20to%20setup%20scroll%20bars%20or%20spin%20buttons%20to%20my%20data.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772841%22%20slang%3D%22en-US%22%3ERe%3A%20Chart%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374851%22%20target%3D%22_blank%22%3E%40Santhosh750%3C%2FA%3E%26nbsp%3B%20First%2C%20you%20need%20to%20enable%20the%20Developer%20ribbon.%20On%20that%20ribbon%20you%20can%20find%20the%20%22insert%22%20drop-down%2C%20which%20has%20two%20sections%2C%20Form%20Controls%20and%20ActiveX%20Controls.%20Use%20only%20the%20Form%20Controls.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20you%20have%20inserted%20a%20control%20like%20a%20spin%20button%20or%20scroll%20bar%2C%20you%20can%20right-click%20and%20format%20the%20control%20to%20get%20to%20all%20the%20settings.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Santhosh750
New Contributor

123.JPG

 

As you can see above, I have data of 7000 rows in one column and I want to see graphical change for every 30 odd blocks of data. is there any easy way to insert any button or anything to move the block down when I press the arrow so that I can see the graphical changes?

 

 

3 Replies

@Santhosh750 

 

Yes, that can be done. It's one of the core techniques of dynamic charts in Excel.

 

What you need:

 

  1. A cell where the user indicates which data to show
  2. A formula that uses that cell input to build a dynamic range
  3. A chart that uses the dynamic range as the chart series source.

For 1: You may want to use a Spin Button or a Scroll Bar from the Developer ribbon > Insert dialog. If you use a Scroll bar, set it up so that a page change equals 30, the minimum is 1 and the maximum is the number of rows in your data set.  The Scroll Bar writes its value into a spreadsheet cell. Give that cell a range name, like "SelectedBlock" so it is easier to read in formulas. (you could also let the user type in the start row of the data block into that cell manually)

 

For 2:  Create two named ranges with these names and formula references. (Assuming your data starts in A6

 

graphX =OFFSET(Sheet1!$A$6,SelectedBlock-1,0,30,1)
graphY =OFFSET(graphX,0,1)

 

For 3: Create a new chart or use an existing chart to plug in the names in the series dialog. When you enter the names, you need to prepend it with the sheet name, like in the screenshot.2019-07-12_14-53-29.png

 

When the user clicks on the Scroll Bar slider, the value in the SelectedBlock cell will change. This will cause a recalculation of the graphX and graphY range names and the chart will adjust.

Thank you very much for responding to my Query. can you please add some pictures to the point 1. in your description? I am not able to figure out exactly how to setup scroll bars or spin buttons to my data.@Ingeborg Hawighorst 

Highlighted

@Santhosh750  First, you need to enable the Developer ribbon. On that ribbon you can find the "insert" drop-down, which has two sections, Form Controls and ActiveX Controls. Use only the Form Controls.

 

After you have inserted a control like a spin button or scroll bar, you can right-click and format the control to get to all the settings.