Multiple Charts from Pivot Table

  • I have a spreadsheet with 4 columns of data. Date, Country, Population and Expected Population.


    Example: Date is the same for 4 rows, countries, pop and exp population change each row. Then this is repeated for the next 4 rows for the next date and this is repeated for many days. Sample is below.




    [TABLE="class: cms_table, width: 500"]

    [tr]


    [td]

    Date

    [/td]


    [td]

    Country

    [/td]


    [td]

    Population

    [/td]


    [td]

    Expected population

    [/td]


    [/tr]


    [tr]


    [td]

    feb 23

    [/td]


    [td]

    Canada

    [/td]


    [td]

    50 mil

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td]

    feb 23

    [/td]


    [td]

    USA

    [/td]


    [td]

    300 mil

    [/td]


    [td]

    290

    [/td]


    [/tr]


    [tr]


    [td]

    feb 23

    [/td]


    [td]

    England

    [/td]


    [td]

    50 mil

    [/td]


    [td]

    60

    [/td]


    [/tr]


    [tr]


    [td]

    feb 23

    [/td]


    [td]

    Jamaica

    [/td]


    [td]

    30 mil

    [/td]


    [td]

    25

    [/td]


    [/tr]


    [tr]


    [td]

    feb 24

    [/td]


    [td]

    Canada

    [/td]


    [td]

    51 Mil

    [/td]


    [td]

    49

    [/td]


    [/tr]


    [tr]


    [td]

    feb 24

    [/td]


    [td]

    USA

    [/td]


    [td]

    299 mil

    [/td]


    [td]

    305

    [/td]


    [/tr]


    [tr]


    [td]

    feb 24

    [/td]


    [td]

    England

    [/td]


    [td]

    49 mil

    [/td]


    [td]

    50

    [/td]


    [/tr]


    [tr]


    [td]

    feb 24

    [/td]


    [td]

    Jamaica

    [/td]


    [td]

    26 mil

    [/td]


    [td]

    25

    [/td]


    [/tr]


    [/TABLE]




    I want to make a chart for each country that has its different: dates, population and expected population, This would make things faster instead of filtering for each country(col b) then making a chart manually, how can I automate this and make a chart for each country?(there are many countries in my true data)


    So the macro would filter to the below and make a chart, for each unique value in a specified column(this time being b), And then another chart would be made for USA, and so on.

    [TABLE="class: cms_table, width: 500"]

    [tr]


    [td]

    Date

    [/td]


    [td]

    Country

    [/td]


    [td]

    Pop

    [/td]


    [td]

    Expected Pop

    [/td]


    [/tr]


    [tr]


    [td]

    Feb 23

    [/td]


    [td]

    Canada

    [/td]


    [td]

    50 Mil

    [/td]


    [td]

    45

    [/td]


    [/tr]


    [tr]


    [td]

    Feb 24

    [/td]


    [td]

    Canada

    [/td]


    [td]

    51 Mil

    [/td]


    [td]

    49

    [/td]


    [/tr]


    [/TABLE]


    Code:

    Code
    Sub Chart()    ActiveSheet.ListObjects("Timings").Range.AutoFilter Field:=7, Criteria1:= _        "Canada"    ActiveSheet.Shapes.AddChart.Select    ActiveChart.ChartType = xlColumnClustered    ActiveChart.SetSourceData Source:=Range("Data!$A$1:$AA$163")End Sub



    This is what i have so far, which does the one chart, but not sure if this will be dynamic enough to get multiple charts made of a filtered column of unique values I specify.


    any help would be greatly appreciated.

  • Re: VBA, looping to make multiple charts


    I would propose that your data is already in a perfect layout. If you select your data table, and go to Insert - PivotTable, you'll be able to quickly build a nice table and chart. Putting the country in the Report Filter slot means we've now created a single chart, so we only have to do our formatting once, and we can easily change which country we want. See attached for example.

  • Re: VBA, looping to make multiple charts


    Quote from Luke M;766044

    I would propose that your data is already in a perfect layout. If you select your data table, and go to Insert - PivotTable, you'll be able to quickly build a nice table and chart. Putting the country in the Report Filter slot means we've now created a single chart, so we only have to do our formatting once, and we can easily change which country we want. See attached for example.


    Hey Luke! thanks, great way to show one chart instead of many. Just a question, if the values of the population are instead times,ex: elapsed sunrise time and normal sunrise time so numbers like 22:00 or 03:00, how would be a good way of displaying this? It shows as a count right now in your format.

  • Re: VBA, looping to make multiple charts


    In the PivotTable, right-click on the field name, "Count of Population" (or something similar). Click on 'Value Field settings", and change it to summarize by Sum (or average...not exactly sure which one you want). Make sure that the data you put in is truly a number (time, date, etc.) and not a text string.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA, looping to make multiple charts


    Okay thanks got it. But I have trouble. One aspect shows hours as in a clock such as 08:00 is 8 am. But what about counting of total hours not on a clock. So if 48 hours pass by it shows 48:00. Its 2 different things i am having trouble setting.

  • Re: VBA, looping to make multiple charts


    Managed to get it thanks. One last, how did you make that dashboard look? With the filter on the side like that?

  • Re: VBA, looping to make multiple charts


    That's a Slicer object, introduced in Excel 2010. With the active cell in a PivotTable, you can go to Options - Sort & Filter, insert slicer. It works the same as a filter dropdown, but is a bit nice to look at. :)

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA, looping to make multiple charts


    Thanks Luke. Would you know how to format the Y axis numbers distinctly for each graph? I have a problem as some graphs look awful due to the y axis scale.

  • Re: VBA, looping to make multiple charts


    Generally, I let the Axis scale automatically to try and get a best fit. What type of formatting are you wanting?

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA, looping to make multiple charts


    I have a pivot table and from there I constructed a bar chart.

    The y axis has a set of time(0 to 12 hours), but the data im using varies from 0 hours to 50:00. So the data can be hard to read.
    Is there any strategies on getting a proper time setting on the y axis so my data doesn't look awful. Some graphs are barely legible because they come be from 5 minutes to 50 hours..


    So they end up looking like this, 1st being an ideal but 2nd being what most of my charts look like
    :


    [ATTACH=CONFIG]68407[/ATTACH][ATTACH=CONFIG]68408[/ATTACH]



    [INDENT]can I individually alter the axis on a pivot chart? Or do you guys have any ideas on how to make this better?[/INDENT]



  • Re: VBA, looping to make multiple charts


    For the 2nd chart, would you cut the y-axis short then, so that the current tall bars get chopped? That would seem to lose data integrity.


    While we could use VB to change the axis each time, I'm not sure how to tell the macro what number to pick. It's not the max value...it's not the min value. An average value??

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA, looping to make multiple charts


    Quote from Luke M;766220

    For the 2nd chart, would you cut the y-axis short then, so that the current tall bars get chopped? That would seem to lose data integrity.


    While we could use VB to change the axis each time, I'm not sure how to tell the macro what number to pick. It's not the max value...it's not the min value. An average value??



    Hi Luke, yes for the 2nd chart I would cut it short Sigh. Your right it would lose data integrity, but I can barely see the small data bars. What to do?
    An average value could work, do you mean 1 average value for all the charts? or each chart to you an average value based on that particular data set? If so I could provide some average values to use.

  • Re: VBA, looping to make multiple charts


    I suppose the better question to ask is, what does your data represent? Having small values isn't necessarily a bad things. If we're measuring outbreaks of a disease, for example, we'd want to see small columns.
    For the average value, I was thinking from the viewpoint of the macro, it would read all the values in the chart, find the average, and set the y-axis to that max.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA, looping to make multiple charts


    The data represent times, for example running times, one may be 10 seconds for a sprint, but another takes hours for a marathon. Sigh.
    Yes that macro would be great, that would work.

  • Re: VBA, looping to make multiple charts


    Hi wonka,


    Here's a rough shot at the idea. In a regular code module, put this. Modify the beginning lines as necessary. You could then tie this in to an event macro, or assign to a button to update the axes. Hopefully it provides some ideas.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA, looping to make multiple charts


    Hey thanks, just some questions:


    Is "Data" the sheet containing the pivot table?


    I get an error: application-defined or object defined error

    Code
    [COLOR=#333333]Set rngData = Worksheets("Data").Range("D2:D9")   
    
    
    [/COLOR]

    I need to understand this better, so running this will give an average of that data and allow y axis to change?

  • Re: VBA, looping to make multiple charts


    That line of code is looking at the data you want to take an average of. It should be wherever you have the numbers being charted. If the numbers in chart are in a PivotTable (like in our dashboard), then you would need to change the sheet name to match sheet name w/ PT, and change the range reference to reference the correct cells.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: VBA, looping to make multiple charts


    I seem to got the macro working. However will I need to run this for each time I use the slicer? Or is this a one time use for each chart?


    Also how do I determine which is my y axis values? They go from 0 to 12 hours for each chart, but i dont have 0-12 hours in my chart anywhere.

  • Re: Multiple Charts from Pivot Table


    No, you can make it automatic. In this example, I used a worksheet_change event macro to make it more automatic.


    For your 2nd question, I'm not quite sure what you mean. Whichever data is being plotted as the values is what would be controlling the y-axis. Also, feel free to change from using the average to using max, mean, etc.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!