Solved

Excel Charts from Pivot Table

Posted on 2016-09-13
7
67 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
[X]
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
  • 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 31

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
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 31

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 31

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 31

Expert Comment

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

634 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