Forum Discussion
Shortcut to change the column that is plotted in a chart
I use Excel to plot a lot of charts, but so far I have not been able to figure out a shortcut to changing the column that is plotted.
For example, I have a chart that is plotting $D$87:$D$95 vs $B$87:$B$95. I want to make another plot with $E$87:$E$95 vs $B$87:$B$95. The only way I know to do this is by manually selecting the data for the new column in the new plot. However, when there are several series, this gets tedious.
Ideally, I would love a function similar to autofill, where I can easily plot $E$87:$E$95 vs $B$87:$B$95 and $F$87:$F$95 vs $B$87:$B$95 and $G$87:$G$95 vs $B$87:$B$95 and so on.
Any tips for making this easier, or is there simply no better way than to manually change the selected data?
Thanks!
See the attached workbook. Particularly if you work with a consistent number of rows of data, it may be useful to use the INDIRECT function and the OFFSET function as I have done in four formulas – in K6:L7 – to change the content of a fixed range to be charted. (The INDIRECT function can also be used to refer to cells on other worksheets, e.g., if you want the data and the chart on separate worksheets. The OFFSET function in this case spills its selected content to multiple rows.) Modify the values in cells L2 and L3 to see the results.
This would work best if you only need to view/print the chart with each change. If you need to copy the results to a new worksheet, then within that new sheet Copy + Paste Values for the K6:L15 range to itself.- There are many trick to making things easier and depends on the case. If you have a chart you made for D vs B and you make it just the way you like and want to copy it but plot E vs B and then again for F vs B, etc... the easiest is to copy the whole chart then click on the series then just drag the highlighted box for the data source over 1 column. If that doesn't work then just edit the series equation in the formula box from $D$87:$D$95 to $E$87:$E$95 and in only a few clicks you have it. You can also click on the chart, go into the chart menu and click data source and then make changes there but I find that longer. You could also make a Macro to do it for you but unless it is a very specific repeated action you do over and over again I doubt the investment in writing the macro would pay off.
