Hi,
I'm trying to create two charts in Excel and unfortunately I've hit a brick wall with both. These are both to compare our budget against our actual spend. The budget and actual spend figures are both in a worksheet which our finance team maintain, here's a screenshot of how it looks for the first few months (the actual spreadsheet covers three financial years).
Capture.PNG
The Spent figure is the amount which we've actually paid for, and the Anticipated Spend is that figure plus the cost of some items which we've ordered but haven't paid for yet. The hidden columns are different departments, but it's only the figures in column M which I need. The 2.5m figure at the top of column M is the budget, I think that'll need to move for the charts to work.
The first chart I'm trying to create is a simple Line chart. I want three lines - one with the budget (it'll remain the same from month to month, but will change between financial years), one with the Spend and one with the Anticipated Spend, similar to the screenshot below.
CAPTURE2.PNG
The second chart I'm trying to create is a column chart with the same three figures, but with the budget column behind the other two, similar to the screenshot below (excuse the terrible use of MS Paint!)
CAPTURE3.PNG
Unfortunately the way the data is laid out is making this difficult. I can create charts where it's a simple "one row heading, one column heading, a value in each" table, but the month and Spend/Anticipated Spend labels both being the column headings (and the month not appearing next to one of those) has confused me. I can't change the table layout, since it's used by a lot of people for various different purposes.
Can anyone help with how I can create these charts?
Thanks
Bookmarks