Solved

Excel Charts from Pivot Table

Posted on 2016-09-13
7
41 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 29

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 29

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 29

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 29

Expert Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

776 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