Solved

Excel Chart

Posted on 2016-08-02
7
45 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
Comment Utility
Is this what you want?
Excel-Chart.xls
1
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points (awarded by participants)
Comment Utility
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
Comment Utility
@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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
Comment Utility
How do I choose Subodh Tiwari reply as the answer ? I am not seeing that option here.

Thanks
0
 
LVL 28

Expert Comment

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

Expert Comment

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now