Chart with multiple data in columns and raws

[kosciap]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sales rep[/TD]
[TD]Region[/TD]
[TD]Week 1, no of files[/TD]
[TD]Week 1, sales value[/TD]
[TD]Week 2, no of files[/TD]
[TD]Week 2, sales value[/TD]
[TD]Week 3, no of files[/TD]
[TD]Week3, sales value[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]North[/TD]
[TD]5[/TD]
[TD]20000[/TD]
[TD]10[/TD]
[TD]50000[/TD]
[TD]15[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]South[/TD]
[TD]10[/TD]
[TD]30000[/TD]
[TD]15[/TD]
[TD]12000[/TD]
[TD]10[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]North[/TD]
[TD]15[/TD]
[TD]8000[/TD]
[TD]10[/TD]
[TD]18000[/TD]
[TD]15[/TD]
[TD]8000[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]South[/TD]
[TD]5[/TD]
[TD]17000[/TD]
[TD]15[/TD]
[TD]7000[/TD]
[TD]5[/TD]
[TD]15000[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]North[/TD]
[TD]15[/TD]
[TD]13000[/TD]
[TD]5[/TD]
[TD]15000[/TD]
[TD]10[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]South[/TD]
[TD]10[/TD]
[TD]6000[/TD]
[TD]10[/TD]
[TD]15000[/TD]
[TD]15[/TD]
[TD]13000[/TD]
[/TR]
</tbody>[/TABLE]

I would like to make a combined chart out of this data, where I have stacked columns for each name, summing up the regions and than a line chart for sales value. I don't know how to make this data "separate". Right now when I try to make the line chart from sales only it takes both no of files and sales so it doesn't make any sense.

 

[Maverick27]

The new Excel BI tools - Power Query, Power Pivot, Power View does some groovy stuff. It's FREE Add-Ins, but it's only compatible with Office 365 & Office 2013 Pro Plus.

 

[Steve=True]

Hi kosciap,

Are you looking to make a chart like this?

Steps to Recreate the Chart:
1) Remove Every other Name so that Excel will create a multi-level category label for the horizontal axis
2) Highlight A1:H7 and create a stacked column chart
3) Switch Row/Column
4) Change all Sales Value Series to Line Chart
5) Change all Sales Value Series to 2nd Axis

More about:
Swtich Rows/Columns: why-does-excel-switch-rows-columns-in-my-chart
Multi-Level Category Axis Labels - fixing-your-excel-chart-when-the-multi-level-category-label-option-is-missing

Let me know if this helps.

Steve=True