How to generate several charts from one data table

Copper Contributor

Hello, I have a large sales data table, which contains information on projected and real sales for about 5 product lines.  In short, we take sales projections from one software package, real sales from store reports, and do some work with tables/merges to get a final output table with the data we need.




This is very convenient, since from there we put that into a Pivot table and group into columns.


And from there on, a line pivotchart gives us monthly sales vs projected sales. We just filter "Software OS (real)" and "Software OS (projected)" on the pivot chart, and so on for each product.


But, the problem with that solution is customization. Our chart title has to be "Real vs. Expected Sales", for all products. To get a chart titled "Real vs. Expected sales for computer parts", we'd have to manually change the title each time we filter. If we have a company color code by product, we have to check it every time we update the pivotchart. If we want a specific note on a certain chart, we have to add it and then delete it... you get the picture. We save bunch of time grouping and adding data, but we have to invest a lot of time if we want a nicer looking chart.


So, I'd like to implement a more "manual chart" solution, but with my automated tables. I'd like to have 6, 8 or how many charts are needed, taking data from my pivottable and displaying it. They're not pivot charts, just regular old charts. At the start of a season, I do some manual work to customize the titles, look, notes, etc. for each chart, and from there on, my data source and query routines feed the updated data.


My question is: how do I go about doing this? I can't create 10 pivot charts from one pivot table, and having 10 pivot tables floating around my workbook doesn't seem orderly. And it doesn't seem like just creating a manual chart linked to pivot columns would work (it would fall apart every time the pivot columns change and I'd probably have to adjust the row range every time I update). 


Sure, I could copy the data from the pivot table and paste it into another table to feed my charts. But that's a step I'd like to avoid, if possible.

I imagine there maybe there's a way to use queries to take my data and group it into columns, like a pivot table would do? And then just reference table column names in my charts?

1 Reply
Do you know you can tie the chart title to a cell? Just click on the chart title to select it and after that, click in the formula bar. Type the = sign and point to a cell in which you have prepared a dynamic title to go on the chart. You can do the same with any shape you place "on" the chart to create other dynamic labelling.