@marshallagg87 You’re welcome - glad to hear that it provided some inspiration!
I’m wrapping up a solution that I’ll share as a Show and Tell soon - it’s on the Sheets platform and has an automated chart. Because my primary use case is Sheets, I have to admit that I took a shortcut in my initial response and built the Excel version of the chart from source data that was just pasted as a table into a blank Excel workbook.
To try and help with your follow-up questions, I went a step further this time and downloaded the Excel foundation template and populated it with some Transaction data. I created a pivot table and attempted to generate a PivotChart. But you got further than I did in that respect. After a little research I think it’s possible that I don’t have all of the PowerPivot features on my Excel for Mac version and even so, I just couldn’t figure out a way to get to my original chart as a PivotChart. So unfortunately I can’t help much further in taking that route,
however, I can offer two alternative methods to construct a dataset that you can then just apply a regular chart to:
#1 - The pivot table data can be pulled out to a separate table in the spreadsheet using the GETPIVOTDATA function. A tip to get that going is to simply pick a cell where you want the data and put an equals sign and then cursor select a cell in the pivot table and the GETPIVOTDATA function will get built out automatically for that cell. From there, just a few adjustments are needed to fill out the rest of the table. I am pasting a screenshot of the formulas so that you can see what I did. The data does dynamically update with refreshes to the pivot table data.
#2 - You can build your dataset with formulas instead of using a pivot table and I did this in a separate sheet and also included a screenshot of the formulas. This solution was designed to allow a selection for the starting year and month. (Note that I did have to add the “Type” and “Hide” columns to the Transactions tab in order to make the filtering easier- these are Columns O and P in the formulas).
After the data is established, it’s just a matter of making a regular chart. I initially chose the Chart Type: Combo Chart. But it seems that you can change the type of a data series by clicking on the chart to select that series and then use the menu item Change Chart Type and it will just impact that selected series. The only thing I haven’t yet resolved is how to get the chart to dynamically size to the available data so that if months are missing at the end of the set it doesn’t show them as zero.
Any other questions, just let me know and hopefully that helps!
