Choose source for data in excel chart

[BLuse]

How do I choose the source for labels and data for a chart in excel?

What I want the chart to show is the label on the left to show Item name, then a line to track the sales month over month and the month and the bottom label to show the corresponding month. Also, if possible add another 'dashed' line to show the unit count month over month.

My data:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD="colspan: 2"]Dec Sales[/TD]
[TD="colspan: 2"]Jan Sales[/TD]
[TD="colspan: 2"]Feb Sales[/TD]
[TD="colspan: 2"]Mar Sales[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Units[/TD]
[TD] Sales[/TD]
[TD]Units[/TD]
[TD] Sales[/TD]
[TD]Units[/TD]
[TD] Sales[/TD]
[TD]Units[/TD]
[TD] Sales[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]5[/TD]
[TD]$10[/TD]
[TD]7[/TD]
[TD]$14[/TD]
[TD]$6[/TD]
[TD]$12[/TD]
[TD]8[/TD]
[TD]$16[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]2[/TD]
[TD]$6[/TD]
[TD]4[/TD]
[TD]$12[/TD]
[TD]5[/TD]
[TD]$15[/TD]
[TD]3[/TD]
[TD]$9[/TD]
[/TR]
[TR]
[TD]Plums[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jjasmith4]

Seems best to have an XY chart with a secondary axis. First, though, you can't have more than one row or column for labels, so change "Units" and "Sales" to something like "Dec #"/"Dec $", "Jan #/Jan $", etc. Then select a range from the cell just above Apples, even if it's blank, to March's banana sales, then insert the XY chart that has lines and markers.


Next, if you have only four legend labels which are the fruits, you have to switch categories and series: right-click the chart, choose Select Data, and click the Switch button so the fruits are on the right (categories). Then your legend will have all the months' #/$ numbers, eight in all.


The next step is to plot all the "$" data on a secondary axis, which doesn't exist yet. Click the chart, then on the ribbon's Chart Tools section click Format. Use the dropdown way at the upper left to change "Chart Area" to "Series Dec $", then click the Format Selection tool under it, and on Series Options choose Secondary Axis. Repeat this for all the "Series ... $" entries.


Now you can continue to change the dropdown and click Format Selection to do anything you want: labels, dashes, etc.