?
Solved

Excel Chart

Posted on 2016-08-02
7
Medium Priority
?
74 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 36

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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 36

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 36

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 36

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

568 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