Solved

Excel Chart

Posted on 2016-08-02
7
57 Views
Last Modified: 2016-08-23
Hi Experts

I have an excel sheet in which I have the data for a few stocks. I have created a line chart to easily visualize this price information.

I am looking for some simple method by which I can QUICKLY Change the data series that is being shown in the price chart, with just one mouse click. So that I can quickly visualize 10 different stock charts, one by one, in the same chart window, instead of having the need to create 10 different charts for the same.

Please suggest various methods by which this could be done.

If you have any doubts, then please ask them, and I would explain in more details.

I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64

I have attached the Excel File having the data and the sample chart.
Excel-Chart.xlsx

Thanks
0
Comment
Question by:happy 1001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 1

Expert Comment

by:MotKohn
ID: 41739542
Is this what you want?
Excel-Chart.xls
1
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points (awarded by participants)
ID: 41739677
In the attached, you have two types of Charts, one with CheckBoxes and another with Option Buttons.
In the Chart with CheckBoxes, you have option to plot graphs for one or more series at the same time by checking and unchecking the checkboxes.

And in the Chart with Option Button, you can plot only one series as per the selected option button.

See which one suits your requirement.

To make these charts, I have inserted two sets of helper data on the sheet with formulas. You can analyze the formulas easily and the checkboxes and the option buttons are also linked to particular cells, you can check that by right clicking the checkbox/option button and choosing the Format control.
Excel-Chart.xlsx
1
 

Author Comment

by:happy 1001
ID: 41743653
@MotKohn, than you for trying, but I do not intend to put all the charts into same pane at same time.

@ Neeraj, you have got it right. I need to plot one chart at a time and quickly change the data series, as easy as possible, as you have tried.

But the problem is that, I just posted a very small sample for the sake of explaining the problem. The real data size is much larger. So how will I implement your solution on a much larger data size ? I have uploaded your solution excel file with more data, can you please have a look. I am looking for a solution, which could be easily replicated for large data sets quickly. I would be implementing it on different excel files having data for different stocks for different time duration etc.
Excel-Chart-More-Data.xlsx

Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41743681
The problem with the data layout is your data is already in pivoted form. You should first unpivot your data into a tabular form (so that you will have only three columns Date, Symbols and their values) using PowerQuery and then you can insert a pivot chart and then inserting a slicer for all the stock symbols so that only the symbol selected in slicer will be visible on the chart.

Right now I am away from my PC for few days and I have not installed PowerQuery on my laptop so cannot tell you the procedure to do that. But if you google about how to unpivot data using PowerQuery, you will have enough stuff to get started.
To me that's the easiest and dynamic solution to your issue no matter how large your data is.
1
 

Author Comment

by:happy 1001
ID: 41765544
How do I choose Subodh Tiwari reply as the answer ? I am not seeing that option here.

Thanks
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41765571
See my recommendation for closing this question below and it matches with your choice so no worries. :)
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41766619
The chosen answer provides the best suitable way to deal with that kind of data without normalizing it.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question