Hi,
I have a question about how to create a bar / stacked bar chart in a pivot chart. I'm having problems so i'm sure it has something to do with adding a different series or the way I have my data prepared. I'm not a complete beginner at charts, but don't have a lot of experience either.
Basically I want to compare a quantity of an item that was scheduled to be worked on in the 1st Quarter (Oct - Dec) of 2015 as a single solid bar, and compare that to what was actually worked on. I would like to display what was actually worked on as a stacked bar chart (planned / unplanned) compared to the scheduled solid bar.
I am calling it planned if it was scheduled to be worked on and was actually worked on in that QTR, and unplanned if it was not scheduled to be worked on in that QTR but was worked.
I'd like to figure out the best way to display this data in order to use a pivot chart and slicers.
Basically it would be 2 bars for each QTR in the year. One bar for the plan, and one stacked bar for the execution (planned / unplanned)
I tried to upload an image example, but don't have permissions.
Appreciate any help you can offer!
Mike
Hi Mike,
I haven't tested as it's tricky without your data, but I don't think you can create this as a combo Pivot chart. Plus Pivot charts are buggy when you make lots of changes to their default format in that they tend to lose then changes on refresh.
You're better off creating a regular chart from the PivotTable as described here: https://www.myonlinetraininghub.com/creating-excel-charts-from-multiple-pivottables
Kind regards,
Mynda
Hi Mike
To further add to Mynda's reply, the combo chart you wanted (a stacked bar next to a normal bar) will require that the chart's data source be arranged in a special way. Pivot Table will not be able to do that.
Sunny
Hi Mike
See if this is what you are looking for.
Although the data is not from a Pivot Table, you will need to ensure that your chart's source data is arranged like this.
Unless you are able to get the Pivot Table to give you this arrangement, I would suggest you use formulas to extract the data and then create a normal chart from it.
Hope this helps.
Sunny
Mynda / SunnyKow,
Thank you for the replies. Thank you for the detailed example SunnyKow! That is exactly what I was trying to do.
I appreciate the help!
Michael
Hello!
After five years, maybe a way has been found? What Mike wants is exactly what I need, too. SunnyKow, your solution works, and I’ve found one that works with filler columns. But neither out of a pivot table. But the chart must come from a pivot table since that is where my data is.
Could Power Query be the answer? But I have no idea!
Have a great day, all.
Hi Steve,
Welcome to our forum! Power Query won't be the answer. Please share a sample file containing a subset of your data including a PivotTable so we can help you further.
Mynda
Hi Steve
Frankly I can't recall how I created this chart 5 years ago (sign of old age I guess
)
I was able to recreate the chart via a Pivot Table (using Excel 2019) although it is not that elegant.
I guess it will again depend on your data.
Like Mynda said, do attach a sample of your data and pivot table.
It will help us understand your requirements better.
Hope this helps.
Sunny
Thank you Mynda, thank you Sunny.
I will try to prepare something to clear up what I need.
This will, however take some time because I am not able to work on that all the time and I may have to "translate" from Excel 2016, German version.
Also, I must go to hospital for a few days next month (nothing very serious, I hope).
Have a great weekend!
Steve
Hi Steve,
You shouldn't need to translate your file as Excel will automatically convert formulas to whatever language settings we have. It might be helpful to put your column labels in English so it's easier for us to follow.
All the best for a speedy recovery.
Mynda
Hello Mynda, hello SunnyKow!
I hope I am giving you something you can work with.
In the attachment, “Original Data” is basically what I get from our Software.
I’ve trimmed it down a lot, there are around 100 Objects and more accounts to each object.
Then I would unpivot it and build a pivot table, “PQ unpivoted” and “pivot table” respectively.
“chart” is what I would like to achieve. (OK, it could be improved!) As you can see, there is no connection from “chart” to the rest.
And that is my problem! How can I build a chart like that from that pivot table? And also use slicers to change the object shown in the chart. All costs are of course negative, I’ve multiplied them by -1 so the primary and secondary axis are the same, but is that necessary?
I hope you have enough information. I’ll try to provide should anything be missing.
Thank you for your help!
Steve
Sorry, I'll try again
Where is the start button?
once again, I'm a dunce
Hi Steve,
Thanks for sharing your file and example chart. Unfortunately, Pivot Charts are not flexible enough to enable you to build this chart. If you want your chart connected to a PivotTable so you can use Slicers etc. then you can build a regular chart from a PivotTable.
Hope that points you in the right direction.
Mynda
Hi Steve
I managed to create the Pivot Chart and included a Slicer as well (using Excel 2019).
Please note that I have to use a dummy series (using the Profit or Loss values) to create the effect for the gap between the column and stacked chart.
The dummy Profit or Loss column chart color is set to No Fill.
Hope this is what you are looking for.
Regards
Sunny
