Share this content

Pivot chart - please help!

I can't work out how to do what I want with a pivot chart

Didn't find your answer?

Hi all,

Hoping someone can help!

I am trying to create a chart which shows income and expenditure by department in a specific way. My data is made up of income, expenditure type 1 and expenditure type 2.  I would like a bar chart or column graph which, for each dept along the x axis shows one bar for income(£), and another bar next to it for the combined total of expenditure type 1 and expenditure type 2, clearly showing the split between type 1 and type 2 by each being a different colour. The difference between the height of the income bar and the combined expenditure bar will be the contribution (so I don't just want one stacked column for each department). Difficult to explain in words - sorry! 

The nearest I've found is to use a combination of a stacked column graph for expenditure, and a line overlaid for income. That's not great though because it means that the income amounts for all the departments are joined by a meaningless line. And, when using a slicer just one department is chosen, the graph looks really silly - one big column with a tiny dot above it!

If someone could point me in the right direction, that would be brilliant! I'm using whatever the latest version of excel is (2019?).




Replies (2)

Please login or register to join the discussion.

By paulwakefield1
06th Mar 2020 08:12

Try the following. In your source data, insert a blank column between the Income data and the two cost data.

Refresh piviot table and create chart using the Combo option. Choose clustered column for income and the blank column. Choose stacked for the two cost fields and plot on the secondary axis.

Format the primary axis with -100% overlap and select the gap width. For the two cost fields, set the Series overlap to +100% and the gap width to 500%. It can be a bit pernickety at holding those settings and you may need to check that none of the fields change from Clustered to stacked or vice versa. But the this should get you close.

Thanks (2)
Replying to paulwakefield1:
By adf2410
06th Mar 2020 10:02

Thanks Paul, that's brilliant and has worked perfectly. The only thing I had to do in addition to your instructions was to set both axes to the same scale.

Thanks (0)
Share this content