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.

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

<tbody>
</tbody>

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.

Proj # Project Name Period Variance 12345 Project 1 1.01 92% 23456 Project 2 1.02 58% 34567 Project 3 1.03 51% 45678 Project 4 1.04 99% 56789 Project 5 1.05 28% 67891 Project 6 1.06 40% 78912 Project 7 1.07 48% 89123 Project 8 1.08 18% 91234 Project 9 1.09 136% 01234 Project 10 1.1 100% 12345 Project 1 2.01 44% 23456 Project 2 2.02 36% 34567 Project 3 2.03 27% 45678 Project 4 2.04 49% 56789 Project 5 2.05 19% 67891 Project 6 2.06 21% 78912 Project 7 2.07 32% 89123 Project 8 2.08 10% 91234 Project 9 2.09 61% 01234 Project 10 2.1 25% 12345 Project 1 3.01 8% 23456 Project 2 3.02 7% 34567 Project 3 3.03 3% 45678 Project 4 3.04 5% 56789 Project 5 3.05 1% 67891 Project 6 3.06 2% 78912 Project 7 3.07 4% 89123 Project 8 3.08 1% 91234 Project 9 3.09 9% 01234 Project 10 3.1 4% <colgroup><col><col><col span="2"></colgroup><tbody> </tbody>

<tbody>
</tbody>

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,