HELP! Values in a Bar chart to overlay, not stack & produce from a Pivot not range

[JungleJme]

Hi everyone,

I have a couple of issues i need some help with. This is only one module in a much bigger project so you may be hearing a lot from me over the coming weeks!

Anyway, i have a table of data which holds entries for salesman's total sales v target for each region. The layout is determined by a download from a BI system called Cognos which gives me revenue values, to which i append the appropriate targets.

Anyway, i want to be able to produce a bar chart for each salesman (which also builds to a regional level) showing their YTD performance v target.

My first problem is laying out a bar chart so that the entries overlap, rather than stack on top of one another. Someone has done this before but they've left the company and i only have sight of a pdf of what they did and i can't recreate it. All this is on the sheet "Problem1" of the attached. I need to get my chart looking like his.

My second problem is that to produce the chart i have had to do an extract from the table to a target area for the chart. But there are dozens of salesmen, to do this for each would be very "clunky". Ideally a pivot chart where i can filter by salesmen would be ideal but the layout of the data does not lend itself to a pivot. Any ideas on how i should approach making this report slick and easy to manipulate/update? All ideas are welcome.

Thanks in advance for any help

J

[Andy Pope]

For the chart, user cluster column.
Move Actual series to secondary axis. Increase Bar Gap.
Select Target series and Set Overlap to 100%

If you alter the layout of your data you can produce the required charts from pivot tables.

[JungleJme]

Thanks for the Chart info, i don't think i would have got there myself.

On the 2nd, i feel kind of dumb now! What a simple solution, can't believe i missed it.... Thanks a lot! Ur a super star!