Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Excel Chart

Posted on 2016-08-02
Medium Priority
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.

Question by:happy 1001
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

Expert Comment

ID: 41739542
Is this what you want?
LVL 32

Accepted Solution

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.

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.

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 32

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.

Author Comment

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

LVL 32

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. :)
LVL 32

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.

Featured Post


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

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 how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

715 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