A former student of EduPristine who underwent the Financial Modeling Training session, which includes Excel Modeling, asked me a very relevant question. Relevant because of the implications of the same as it can be used in various walks of one’s professional life.

Take a look!

Now that you have read this student’s question, we can state that what is given below is the sample he wanted to create:

Let’s notch the heat up a bit! To chalk out a systematic plan on how to design the map, I took some random data and wrote down the steps to see how it will be plotted. Given below is the data that I took:

Let’s have a little more fun! If I create the scatter plot from the two variables, I will get:

However, this chart seems incomplete and cluttered. I would want to ideally add one vertical and one horizontal line to turn it into quadrants. So, I created four columns to create these two lines as following:

So, on our current chart if I right click and add the above data as two new series, I will get the following graph:

We are on the right track! But still it is cluttered. So, first I will delete the legend and the gridlines, and I will have the following result:

It is infinitely cleaner now. But if I notice carefully, the axes are going off-limits. So, to control them I will select the individual axis and will limit them (manually) to 1 in the case of x-axis, i.e, Impact on Satisfaction and 100 in the case of y-axis, i.e, Component Score respectively. I also see that the vertical and horizontal lines have scattered data points. So I will change them to the scatter lines and following is the result after adjustment:

Much better! But I want to make these lines dotted and thinner, so I will select them and go for “Format Series”. I will select width as 1pt and line style as “Dash”. I also want to see the axis title, which I can select under “Chart Tools – Layout – Axis Title”. I will add the boundary too, so that it looks like a box. After these adjustments, following is the result:

Now, I am left with one more thing. I need to label the data points (A, B, C, etc.) Now there is a tool available as an excel add-in which can be installed. You can download the add-in from this link. After installing you will see the excel ribbon as following:

Now within “XY Chart Labels” you can go to “Add labels” and select the labels. Then boom! You get the following as your result: (Do not forget to add Text boxes to name the quadrants)