?
Solved

Excel Chart

Posted on 2016-08-02
7
Medium Priority
?
71 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
  • 4
  • 2
7 Comments
 
LVL 1

Expert Comment

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

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 34

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 34

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 34

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

621 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