I have a master data set on one worksheet that I update once a week. I need to create multiple charts that are dynamically updated when I update the data set. I created a pivot table for each chart. When I update the data set, I refresh the pivot table, which updates the chart. Is there a better way to create multiple charts from one data set?
Best way to create multiple charts from one data set
- Thread starter apb
- Start date
Apb
Firstly welcome to the Chandoo.org Forums
As you have mentioned the use of Dynamic Ranges is a good way to do this
There are many articles on that process here at Chandoo.org
As SirJB7 also said that without data we can't offer much more help
I suggest you read the 3 Green Sticky Posts at the top of: http://chandoo.org/forums/
and then post a sample file with some data and maybe with some idea of what you are after
Firstly welcome to the Chandoo.org Forums
As you have mentioned the use of Dynamic Ranges is a good way to do this
There are many articles on that process here at Chandoo.org
As SirJB7 also said that without data we can't offer much more help
I suggest you read the 3 Green Sticky Posts at the top of: http://chandoo.org/forums/
and then post a sample file with some data and maybe with some idea of what you are after
Faseeh
Excel Ninja
Hi apb,
If pivot tables are getting updated you need to update the data they are using to create report/table for you.
In order to do so: Click on Your PT it will activate Options & Design Tabs, on options tab click 'Change data source' and that will lead you to a dialogue box where you can update the data, once updated the change will reflect in you PTable/PChart. If you are satisfied with what you created from Pivot Option this will set things right for you.
Regarding your first question that
SirJB7 is right you should consider uploading a file or at-least give layout of your sheet, other wise have a look at this webpage:
http://spreadsheetpage.com/index.php/tip/update_charts_automatically_when_you_enter_new_data/
Regards,
Faseeh
If pivot tables are getting updated you need to update the data they are using to create report/table for you.
In order to do so: Click on Your PT it will activate Options & Design Tabs, on options tab click 'Change data source' and that will lead you to a dialogue box where you can update the data, once updated the change will reflect in you PTable/PChart. If you are satisfied with what you created from Pivot Option this will set things right for you.
Regarding your first question that
Code:
Is there a better way to create multiple charts from one data set?http://spreadsheetpage.com/index.php/tip/update_charts_automatically_when_you_enter_new_data/
Regards,
Faseeh
Thanks for responding Hui and Faseeh! I read the sticky posts and published my spreadsheet here: https://docs.google.com/spreadsheet/ccc?key=0Ah5e8FktKX3ZdDhXZWMwSXByZXBNeUlYam5MSWw2RlE
I would like to create dynamic stacked column charts, where:
x-axis = month
y-axis = # of visits by medium
Is this possible with the way my data is currently formatted?
I would like to create dynamic stacked column charts, where:
x-axis = month
y-axis = # of visits by medium
Is this possible with the way my data is currently formatted?
Faseeh
Excel Ninja
Hi apb,
I just plotted your data using pivot charts, you can create stacked chart by:
1. Select your data and plot a pivot chart.
2. Select for Axis Field = month and Legend field = medium, and Values = sum of values.
3. Select a stacked chart for plotting the data.
Hope that it will be helpful...
Regards,
Faseeh
I just plotted your data using pivot charts, you can create stacked chart by:
1. Select your data and plot a pivot chart.
2. Select for Axis Field = month and Legend field = medium, and Values = sum of values.
3. Select a stacked chart for plotting the data.
Hope that it will be helpful...
Regards,
Faseeh
Thanks for responding, Faseeh! I know how to create the pivot charts, but I'm wondering if there's a better way to do this. I want to create multiple stacked column charts from one data set. The x axis will always = month; the legend = medium, but the value could be visits, new visits, conversions, etc.
Currently, I create a pivot table/chart for each metric I want to visualize as a stacked column chart. So my spreadsheet is getting quite big. Can I do this using data series?
Currently, I create a pivot table/chart for each metric I want to visualize as a stacked column chart. So my spreadsheet is getting quite big. Can I do this using data series?
Hi Faseeh,
Thanks for responding and apologies for the late reply. How would I format my data if I need to create a stacked column chart for multiple metrics? For example, I need one chart for Visits by Month/Medium and another for New Visits by Month/Medium. In my original data set, the data is formatted so that the months and mediums (Organic, CPC, etc.) are in rows and the metrics (Visits, New Visits) are columns. Should I create separate tables for each metric? Or is it possible to do this in one dataset without creating one pivot table per metric, as I've been doing?
Thanks so much for your help!
apb
Thanks for responding and apologies for the late reply. How would I format my data if I need to create a stacked column chart for multiple metrics? For example, I need one chart for Visits by Month/Medium and another for New Visits by Month/Medium. In my original data set, the data is formatted so that the months and mediums (Organic, CPC, etc.) are in rows and the metrics (Visits, New Visits) are columns. Should I create separate tables for each metric? Or is it possible to do this in one dataset without creating one pivot table per metric, as I've been doing?
Thanks so much for your help!
apb
Hi Montrey,
Thanks for taking the time to create this file! I have not used sumproduct before and I'm intrigued by your solution. However, this still requires multiple tables of data and creates line graphs instead of stacked charts. I was hoping to have all of the data in one table. Maybe it would help if you could see what I'm trying to accomplish using pivot tables. I imported my spreadsheet into Google Docs, so the formatting is a little wonky, but I think you'll see what I'm trying to do:
https://docs.google.com/spreadsheet/ccc?key=0Ah5e8FktKX3ZdFQyRGVvVGwzVmloekhkeWVhTTdmTUE
Thanks again for your help!
apb
Thanks for taking the time to create this file! I have not used sumproduct before and I'm intrigued by your solution. However, this still requires multiple tables of data and creates line graphs instead of stacked charts. I was hoping to have all of the data in one table. Maybe it would help if you could see what I'm trying to accomplish using pivot tables. I imported my spreadsheet into Google Docs, so the formatting is a little wonky, but I think you'll see what I'm trying to do:
https://docs.google.com/spreadsheet/ccc?key=0Ah5e8FktKX3ZdFQyRGVvVGwzVmloekhkeWVhTTdmTUE
Thanks again for your help!
apb
Apb
In general you store all your data in 1 table, have a number of reports or data extractions and use these reports/extractions to feed various charts and other reports as part of a dashboard etc.
Looking at the Google Charts they could all be sourced from a Pivot Table as a Pivot table chart or Dynamic Named Formula from a Pivot Table for subsequent use in Charts
Is there anything wrong with that approach?
Don't worry about the style of chart that is easily changed from a line to a Column chart
In general you store all your data in 1 table, have a number of reports or data extractions and use these reports/extractions to feed various charts and other reports as part of a dashboard etc.
Looking at the Google Charts they could all be sourced from a Pivot Table as a Pivot table chart or Dynamic Named Formula from a Pivot Table for subsequent use in Charts
Is there anything wrong with that approach?
Don't worry about the style of chart that is easily changed from a line to a Column chart
Montrey
Member
But the tables update automatically and you don't have to do anything with the graphs either. because they have the full range of data for 12 months and because of the #n/a's they don't display on the graph until their is data therefore making them dynamic.
I stray from pivot tables because they require pressing refresh every time new data is added.
I stray from pivot tables because they require pressing refresh every time new data is added.
Thanks again for responding, Hui and Montrey. I'm excited to try Montrey's method when I have more time to figure out how it works.
Hui, I'm reluctant to use pivot tables because I have over 10 charts to create, which means I'd have to create over 10 separate sheets, one for each pivot table. Of course, I'm fairly new to pivot tables, so maybe there's a better way.
Hui, I'm reluctant to use pivot tables because I have over 10 charts to create, which means I'd have to create over 10 separate sheets, one for each pivot table. Of course, I'm fairly new to pivot tables, so maybe there's a better way.
Hi Hui,
I have a follow-up question. I'm new to Excel for Mac (2010), which doesn't support pivot charts. In the past, I used Excel for Windows to create a table and then a pivot chart to create a dynamic chart. How do I create a dynamic chart in Excel for Mac? Would you mind posting an example using my dataset? I would really appreciate it!
Thanks!
apb
I have a follow-up question. I'm new to Excel for Mac (2010), which doesn't support pivot charts. In the past, I used Excel for Windows to create a table and then a pivot chart to create a dynamic chart. How do I create a dynamic chart in Excel for Mac? Would you mind posting an example using my dataset? I would really appreciate it!
Thanks!
apb