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?).