Excel Graphing Shortcut

[ang.ru]

Hi everyone,


I'm analyzing a huge set of data and I was looking for a function on excel that would help me greatly in speeding up the process. For example, I have this set of data:

[pre]

Serial Number         Date     Measurement
2600                 4-3-13       12
2600                 5-12-13      18
2610                 3-20-12       3
2620                 2-21-12       7

[/pre]
And so on......


And I want to make scatterplots for each set of serial number; 1 for serial number 2600, 1 for serial number 2610, and 1 for serial number 2620 (date and measurements are x and y axis). Is there an easier way to do this than me manually highlighting and clicking "insert scatterplot"? Correct me if I'm wrong, but macros don't work because I need excel to pick up when the serial number changes.


Does a function like this exist, and if so how do I use it? Any help would be greatly appreciated, thanks!!!

 

[NARAYANK991]

Hi ,


A macro can make the whole job very easy ; if you can upload your file , I can help with the macro.


Narayan

 

[Hui]

Ang.Ru


The techniques described here will also simplify this task without macros

http://chandoo.org/wp/2010/11/04/analysing-large-tables/

 

[ang.ru]

Thank you Hui


and Narayan


Thank you, but unfortunately my file is for business, and therefore, confidential. Is there a code I can write to simplify the process?


This is what I have right now:

[pre]

Sub Graph()
'
' Graph Macro
' creates scatterplot
'
' Keyboard Shortcut: Option+Cmd+Shift+G
'
Range("I2164").Select
ActiveWindow.ScrollRow = 2159
Range (Selection)
ActiveSheet.ChartObjects("Chart 63").Activate
Range("H2186:I2192").Select
ActiveWindow.SmallScroll Down:=5
Range("H2189").Select
ActiveWindow.SmallScroll Down:=-7
Range("H2182:J2197").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("Sheet1!$H$2182:$J$2197")
ActiveSheet.Shapes("Chart 66").IncrementLeft -45.6
ActiveSheet.Shapes("Chart 66").IncrementTop -88
Range("H2195").Select
ActiveSheet.ChartObjects("Chart 66").Activate
Range("H2198:I2201").Select
End Sub
Sub Scatterplot()
'
' Scatterplot Macro
' scatterplot
'
' Keyboard Shortcut: Option+Cmd+Shift+S

Range (Selection)
ActiveSheet.Shapes.AddChart.Select
End Sub

[/pre]

 

[Hui]

Have you considered using a Pivot Chart?


Select the 3 columns

Insert, Pivot Table, Pivot Chart

Drag the fields to suit


If you make the Pivot Chart be based on a Named Formula you can have the Pivot charts update automatically as data is added or deleted

 

[NARAYANK991]

Hi ,


In case you are still looking for a VBA solution , give me some time , since now I'll have to go through your macro , and understand how your data is laid out , before I can rewrite it.


Narayan

 

[ang.ru]

I'd really appreciate it Narayan thanks! and Hui, I'll try your Pivot Chart idea and see how it works