Scatter (XY) Plots – Visualizing Employee Utilization

In this tutorial we’ll have a look at the process required for the creation of a scatter plot, in order to visualize employee utilization data.

Scatter plots can be daunting at times, but the steps outlined in this tutorial are easy to follow and should have you plotting in no time.

For this example. we’ll use fictional data for 4 production streams (named Production Stream 1 to 4). Each production stream consists of 10 employees which are required to maintain a certain level of utilization (/chargeable hours). Our fictional organization has outlined that all employees should have an average utilization of between 90 and a 110% (tolerance band) for the year. We’ve been tasked by Management to visualize the average utilization of each production stream and employee on a single scatter plot in order to provide management with a visual overview of employee utilization within, above and below the tolerance band.

Below is a snippet of our source data:

 

You will note a column named “X_Plot”. For a scatter plot to be populated correctly, we require two data points (/axis points), namely “X” and “Y”.

The “X_Plot” column is a numerical value associated with each production stream. We cannot us the name “Production Stream 1” or the employee’s name as a data point because it is formatted/viewed as a text value and not a numerical value. We therefore create a “numerical id” for each production stream that serves as our x-axis data point. (The data point is also automatically associated with an employee as you will see later).

Before we start plotting our utilization data, let’s first create the tolerance bands (namely, 90, 100 and 110%).

Step 1: Click on a blank cell outside of your data table

Step 2: On the ribbon, click on “Insert” –> “Scatter with Smooth Lines”

You should see a blank canvas like this (A):

 

Step 3: Right click the blank canvas and select “Select Data…”

 

Step 4: Click on “Add”

Step 5: For the “Series name:” select the heading of column E (“Lower Tolerance”)

Step 6: For the “Series X values:” select the whole “X_Plot” column but exclude the column heading

Step 7: For the “Series Y values:” select the whole column E but exclude the column heading

 

Step 8: Click “OK”

Your graph should look something like this:

Now for the next band.

Step 9: Right click the graph and click on “Select Data…”

Step 10: Click on “Add”

Repeat steps 5, 6 and 7, but select the “On Par” column heading as the “Series name” and the “On Par” columns data as the “Series Y values”. The “Series X values” is still the “X_Plot” column’s data.

 

Your graph should look something like this:

Now repeat these steps for the “Upper Tolerance” band.

Now to add our employee/production stream utilization data.

Step 11: Right click the graph and click “Select Data…”

Step 12: Click on “Add”

Step 13: For “Series name:” select “Employee Utilization” (column D’s heading)

Step 14: For “Series X Values:” select the whole column C – “X_Plot”, but exclude the column heading

Step 15: For “Series Y Values:” select the whole column D (“Employee Utilization”), but exclude the column heading

 

And if you did everything correctly, your graph should completely freak out like this:

Now for the finishing touches.

Step 16: Right click the graph and click on “Change Chart Type”

Step 17: Click on “Combo” and change the “Chart Type” as per the image below. Make sure that the secondary axis boxes are not ticked.

 

Step 18: Click “Ok”

And like magic, your graph should look like something this:

 

And after some adjustments, the final product:

I hope you enjoyed this tutorial and learned something new.

If you have any comments or suggestions, please feel free to reach out in the comment section below.

Until next time…

Leave a Reply

Your email address will not be published. Required fields are marked *