Solved

Excel Charts from Pivot Table

Posted on 2016-09-13
7
31 Views
Last Modified: 2016-10-18
Hi Experts

I have an excel sheet in which I have the data for a few stocks and I need to create the Line Charts for them. But the data first needs to go through 2-3 stages, in order to arrange it properly for plotting the line charts.

First the data comes into Raw Data Sheet from external source. Then it gets converted into Pivot Table Format. And then it is moved to third sheet, where data for every single symbol gets arranged properly. And then finally in the forth step, the line charts gets created.

I have confusion in the 3rd sheet. How do I properly move the data from the pivot table to this third sheet in such a manner that all the data gets arranged properly under each symbol name, on a daily basis ?

I have explained the requirement in very clear and detailed manner in the attached sample file.

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 charts.
Excel-Chart-with-Pivot-Table-Data.xlsx

Thanks
0
Comment
Question by:happy 1001
  • 4
  • 3
7 Comments
 

Author Comment

by:happy 1001
ID: 41797677
I am trying to put the question in very simple terms -

All I am trying to do is to find a method by which data for specific column could be picked up from the Pivot Table and then it can be copied to FIXED CELL LOCATIONS in the adjoining sheet.

Something similar to doing the Vlookup type of thing. But this time we are picking up the data directly from pivot table, so I am not sure what method to be used.

If anyone want more explanation, then kindly ask, I will explain in as much detail as possible. Please give it a shot, have a look at the sample file. It is not as complicated as it looks :)

Thanks a lot
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41797727
Please refer to the attached and see if this is what you are trying to achieve.
Excel-Chart-with-Pivot-Table-Data.xlsx
1
 

Author Comment

by:happy 1001
ID: 41799551
@Neeraj bhai, thanks again for providing great help. You have understood the requirement 100 % correctly.

We just need to make a slight modification in the solution that you have provided. I have uploaded your file with slight more explanation, please take a look.
Excel-Chart-with-Pivot-Table-Data--.xlsx

Just as you have referred to cell number G3, in the formulas that you have written for Column H, we just need to make sure that we make use of the information given in the Column B and C instead. Everything else is perfect.

Thanks a lot for your repeated help.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41799651
Your description shows that you are trying to get the data from the pivot table on another sheet exactly in the same format as of pivot table itself and then inserting a line chart for all the stocks based on that data. Right?

If this is your intention, why not just insert a line chart based on the Pivot Table itself or rather Pivot Chart and have a slicer based on the stock symbols so that you can filter the chart if required.

I am really not sure why do you want to replicate the data from the Pivot Table on another sheet for all the stocks in order to insert a chart.
1
 

Author Comment

by:happy 1001
ID: 41800016
Your description shows that you are trying to get the data from the pivot table on another sheet exactly in the same format as of pivot table itself and then inserting a line chart for all the stocks based on that data. Right?

Yes, I am replicating the data from the Pivot Table.
BUT in a way, where I am able to Choose and Decide the Exact Location, where exactly the data for a specific symbol goes. I can define that this stock xxx should go into column H, and the data for this stock yyy should go into column K and so on.

If this is your intention, why not just insert a line chart based on the Pivot Table itself or rather Pivot Chart and have a slicer based on the stock symbols so that you can filter the chart if required.

Because once I have arranged the data according to my requirement, as explained above, then I intend to create various different kind of charts for ALL OF THEM in one sheet. I do not need just one chart at a time which can be then changed by using slicers etc. Instead I want to have ALL the Charts for ALL the symbols in Permanent Manner.

That is the reason I need to have a proper method to have the data for specific symbols in specific locations, so that then I can start the work of creating charts for all of them, one by one.

If you have any more confusion regarding anything, then please ask.

I am very grateful for your exceptional help. Thanks a lot
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points (awarded by participants)
ID: 41822038
You can convert your data into an Excel Table and insert a Pivot Chart and a slicer to pick the symbols as shown in the attached.
Refer to the chart sheet.
Excel-Chart-with-Pivot-Table-Data--.xlsx
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41847941
The chosen answer resolved the question.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

743 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

11 Experts available now in Live!

Get 1:1 Help Now