Chart with multiple data in columns and raws

[kosciap]

Sales rep	Region	Week 1, no of files	Week 1, sales value	Week 2, no of files	Week 2, sales value	Week 3, no of files	Week3, sales value
Amy	North	5	20000	10	50000	15	10000
Amy	South	10	30000	15	12000	10	12000
John	North	15	8000	10	18000	15	8000
John	South	5	17000	15	7000	5	15000
Sam	North	15	13000	5	15000	10	20000
Sam	South	10	6000	10	15000	15	13000

<tbody>
</tbody>

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