Scatter Plot in Excel

[ExplorerOfExcel]

I am looking to create a scatter plot in Excel and looking through the other posts have not seen how to accomplish what I am looking for with the format of my data. I am managing 40+ projects and would like to show the variance by month of forecasted spend versus the actual. The resulting scatter chart would have % of variance on the y axis and January thru December on the x axis.
On the chart you would see the 40 dots on each month and I would add a trendline to hopefully see a trend towards 0% variance throughout the year. Additionally I would like to hover over a dot and have it reference the project name.

I have my data in the following format however I am not able to create the scatter chart I am looking for.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1061"]
<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 4"]January[/TD]
[TD="colspan: 4"]February[/TD]
[TD="colspan: 4"]March[/TD]
[/TR]
[TR]
[TD]Proj #[/TD]
[TD]Project Name[/TD]
[TD]PM[/TD]
[TD]Status[/TD]
[TD]Forecast[/TD]
[TD]Actual[/TD]
[TD]Var $[/TD]
[TD]Var %[/TD]
[TD]Forecast[/TD]
[TD]Actual[/TD]
[TD]Var $[/TD]
[TD]Var %[/TD]
[TD]Forecast[/TD]
[TD]Actual[/TD]
[TD]Var $[/TD]
[TD]Var %[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]Project 1[/TD]
[TD]PM 1[/TD]
[TD]Active[/TD]
[TD="align: right"]41667[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]-38333 [/TD]
[TD="align: right"]92%[/TD]
[TD="align: right"]41667[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]-18333 [/TD]
[TD="align: right"]44%[/TD]
[TD="align: right"]41667[/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]-3333 [/TD]
[TD="align: right"]8%[/TD]
[/TR]
[TR]
[TD="align: right"]23456[/TD]
[TD]Project 2[/TD]
[TD]PM 2[/TD]
[TD]Active[/TD]
[TD="align: right"]35417[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]20417[/TD]
[TD="align: right"]58%[/TD]
[TD="align: right"]35417[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]12917[/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]35417[/TD]
[TD="align: right"]38000[/TD]
[TD="align: right"]-2583 [/TD]
[TD="align: right"]7%[/TD]
[/TR]
[TR]
[TD="align: right"]34567[/TD]
[TD]Project 3[/TD]
[TD]PM 3[/TD]
[TD]Active[/TD]
[TD="align: right"]102875[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]52875[/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]102875[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]27875[/TD]
[TD="align: right"]27%[/TD]
[TD="align: right"]102875[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]2875[/TD]
[TD="align: right"]3%[/TD]
[/TR]
[TR]
[TD="align: right"]45678[/TD]
[TD]Project 4[/TD]
[TD]PM 4[/TD]
[TD]Active[/TD]
[TD="align: right"]12083[/TD]
[TD="align: right"]24000[/TD]
[TD="align: right"]-11917 [/TD]
[TD="align: right"]99%[/TD]
[TD="align: right"]12083[/TD]
[TD="align: right"]18000[/TD]
[TD="align: right"]-5917 [/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]12083[/TD]
[TD="align: right"]11500[/TD]
[TD="align: right"]583[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]56789[/TD]
[TD]Project 5[/TD]
[TD]PM 5[/TD]
[TD]Active[/TD]
[TD="align: right"]73958[/TD]
[TD="align: right"]95000[/TD]
[TD="align: right"]-21042 [/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"]73958[/TD]
[TD="align: right"]88000[/TD]
[TD="align: right"]-14042 [/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]73958[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]-1042 [/TD]
[TD="align: right"]1%[/TD]
[/TR]
[TR]
[TD="align: right"]67891[/TD]
[TD]Project 6[/TD]
[TD]PM 6[/TD]
[TD]Active[/TD]
[TD="align: right"]208333[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]83333[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]208333[/TD]
[TD="align: right"]165000[/TD]
[TD="align: right"]43333[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]208333[/TD]
[TD="align: right"]205000[/TD]
[TD="align: right"]3333[/TD]
[TD="align: right"]2%[/TD]
[/TR]
[TR]
[TD="align: right"]78912[/TD]
[TD]Project 7[/TD]
[TD]PM 7[/TD]
[TD]Active[/TD]
[TD="align: right"]54167[/TD]
[TD="align: right"]28000[/TD]
[TD="align: right"]26167[/TD]
[TD="align: right"]48%[/TD]
[TD="align: right"]54167[/TD]
[TD="align: right"]37000[/TD]
[TD="align: right"]17167[/TD]
[TD="align: right"]32%[/TD]
[TD="align: right"]54167[/TD]
[TD="align: right"]52000[/TD]
[TD="align: right"]2167[/TD]
[TD="align: right"]4%[/TD]
[/TR]
[TR]
[TD="align: right"]89123[/TD]
[TD]Project 8[/TD]
[TD]PM 8[/TD]
[TD]Active[/TD]
[TD="align: right"]79167[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]14167[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]79167[/TD]
[TD="align: right"]71000[/TD]
[TD="align: right"]8167[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]79167[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]-833 [/TD]
[TD="align: right"]1%[/TD]
[/TR]
[TR]
[TD="align: right"]91234[/TD]
[TD]Project 9[/TD]
[TD]PM 9[/TD]
[TD]Active[/TD]
[TD="align: right"]22917[/TD]
[TD="align: right"]54000[/TD]
[TD="align: right"]-31083 [/TD]
[TD="align: right"]136%[/TD]
[TD="align: right"]22917[/TD]
[TD="align: right"]37000[/TD]
[TD="align: right"]-14083 [/TD]
[TD="align: right"]61%[/TD]
[TD="align: right"]22917[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]-2083 [/TD]
[TD="align: right"]9%[/TD]
[/TR]
[TR]
[TD="align: right"]01234[/TD]
[TD]Project 10[/TD]
[TD]PM 10[/TD]
[TD]Active[/TD]
[TD="align: right"]62500[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]-62500 [/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]62500[/TD]
[TD="align: right"]78000[/TD]
[TD="align: right"]-15500 [/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]62500[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]4%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I have been able to create a close visual to the scatter chart I am looking for by putting my data into the format below however it has a few drawbacks:
My data is already in a format for other reporting and I am manually manipulating my data to create this new table.
Along the x axis I am only getting 1, 2, 3 and not January, etc. as that information is not in my table
I am not able to hover over the dot see the project name, I see Period number.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 284"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Proj #[/TD]
[TD]Project Name[/TD]
[TD]Period[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]Project 1[/TD]
[TD="align: right"]1.01[/TD]
[TD="align: right"]92%[/TD]
[/TR]
[TR]
[TD="align: right"]23456[/TD]
[TD]Project 2[/TD]
[TD="align: right"]1.02[/TD]
[TD="align: right"]58%[/TD]
[/TR]
[TR]
[TD="align: right"]34567[/TD]
[TD]Project 3[/TD]
[TD="align: right"]1.03[/TD]
[TD="align: right"]51%[/TD]
[/TR]
[TR]
[TD="align: right"]45678[/TD]
[TD]Project 4[/TD]
[TD="align: right"]1.04[/TD]
[TD="align: right"]99%[/TD]
[/TR]
[TR]
[TD="align: right"]56789[/TD]
[TD]Project 5[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]28%[/TD]
[/TR]
[TR]
[TD="align: right"]67891[/TD]
[TD]Project 6[/TD]
[TD="align: right"]1.06[/TD]
[TD="align: right"]40%[/TD]
[/TR]
[TR]
[TD="align: right"]78912[/TD]
[TD]Project 7[/TD]
[TD="align: right"]1.07[/TD]
[TD="align: right"]48%[/TD]
[/TR]
[TR]
[TD="align: right"]89123[/TD]
[TD]Project 8[/TD]
[TD="align: right"]1.08[/TD]
[TD="align: right"]18%[/TD]
[/TR]
[TR]
[TD="align: right"]91234[/TD]
[TD]Project 9[/TD]
[TD="align: right"]1.09[/TD]
[TD="align: right"]136%[/TD]
[/TR]
[TR]
[TD="align: right"]01234[/TD]
[TD]Project 10[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]Project 1[/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]44%[/TD]
[/TR]
[TR]
[TD="align: right"]23456[/TD]
[TD]Project 2[/TD]
[TD="align: right"]2.02[/TD]
[TD="align: right"]36%[/TD]
[/TR]
[TR]
[TD="align: right"]34567[/TD]
[TD]Project 3[/TD]
[TD="align: right"]2.03[/TD]
[TD="align: right"]27%[/TD]
[/TR]
[TR]
[TD="align: right"]45678[/TD]
[TD]Project 4[/TD]
[TD="align: right"]2.04[/TD]
[TD="align: right"]49%[/TD]
[/TR]
[TR]
[TD="align: right"]56789[/TD]
[TD]Project 5[/TD]
[TD="align: right"]2.05[/TD]
[TD="align: right"]19%[/TD]
[/TR]
[TR]
[TD="align: right"]67891[/TD]
[TD]Project 6[/TD]
[TD="align: right"]2.06[/TD]
[TD="align: right"]21%[/TD]
[/TR]
[TR]
[TD="align: right"]78912[/TD]
[TD]Project 7[/TD]
[TD="align: right"]2.07[/TD]
[TD="align: right"]32%[/TD]
[/TR]
[TR]
[TD="align: right"]89123[/TD]
[TD]Project 8[/TD]
[TD="align: right"]2.08[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD="align: right"]91234[/TD]
[TD]Project 9[/TD]
[TD="align: right"]2.09[/TD]
[TD="align: right"]61%[/TD]
[/TR]
[TR]
[TD="align: right"]01234[/TD]
[TD]Project 10[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]Project 1[/TD]
[TD="align: right"]3.01[/TD]
[TD="align: right"]8%[/TD]
[/TR]
[TR]
[TD="align: right"]23456[/TD]
[TD]Project 2[/TD]
[TD="align: right"]3.02[/TD]
[TD="align: right"]7%[/TD]
[/TR]
[TR]
[TD="align: right"]34567[/TD]
[TD]Project 3[/TD]
[TD="align: right"]3.03[/TD]
[TD="align: right"]3%[/TD]
[/TR]
[TR]
[TD="align: right"]45678[/TD]
[TD]Project 4[/TD]
[TD="align: right"]3.04[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]56789[/TD]
[TD]Project 5[/TD]
[TD="align: right"]3.05[/TD]
[TD="align: right"]1%[/TD]
[/TR]
[TR]
[TD="align: right"]67891[/TD]
[TD]Project 6[/TD]
[TD="align: right"]3.06[/TD]
[TD="align: right"]2%[/TD]
[/TR]
[TR]
[TD="align: right"]78912[/TD]
[TD]Project 7[/TD]
[TD="align: right"]3.07[/TD]
[TD="align: right"]4%[/TD]
[/TR]
[TR]
[TD="align: right"]89123[/TD]
[TD]Project 8[/TD]
[TD="align: right"]3.08[/TD]
[TD="align: right"]1%[/TD]
[/TR]
[TR]
[TD="align: right"]91234[/TD]
[TD]Project 9[/TD]
[TD="align: right"]3.09[/TD]
[TD="align: right"]9%[/TD]
[/TR]
[TR]
[TD="align: right"]01234[/TD]
[TD]Project 10[/TD]
[TD="align: right"]3.1[/TD]
[TD="align: right"]4%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Any help, tips, advice that you could provide would be appreciated.

 

[VBA Geek]

First you will need to rearrange your data as shown below. If it becomes a repetitive task to do it manually then you need the help of VBA.
Then I would probably not show all 40 projects at the same time as it would make your chart a bit confusing I believe, but you can set up your file to have a dynamic range where you can choose the project that you want to show. Also if you use a XY scatter and you want to see the project name you will need a bit of VBA whereas if you use a line chart you won't need it.

download the file

 

[ExplorerOfExcel]

Appreciate the information VBA Geek!
I do need to show all the projects at the same time as there is a desire to not only see the outliers month to month by hopefully a general trend throughout the year to forecast projects more accurately.

Regards,