MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Plotting Employees on a Bell Curve


March 27, 2019 - by Bill Jelen

Excel Plotting Employees on a Bell Curve. Photo Credit: Dose Juice at Unsplash.com

Rather than creating a generic bell curve, how about plotting a list of employees or customers on a bell curve?

Start with a list of people and scores. Use the AVERAGE and STDEV.P functions to find the mean and standard deviation.

13 employees in A2:A14. The scores in B2:B14. Off to the right, calculate the Mean using =AVERAGE(B2:B14) and Standard Deviation using =STDEV.P(B2:B14)

Once you know the mean and standard deviation, add a Y column with the formula shown below.


The score in column B will be the X value. To calculate a Y value in column C, use =NORM.DIST(B2,Mean,StDev,False).

After adding the Y column, sort the data by Score ascending.

You now have Name in A, Score (X) in B, and Y in C. Sort the employees by Score ascending.

Select Score & Y columns and add a Scatter with Smooth Lines as shown in the previous technique. Labelling the chart with names is tricky. Use the + icon to the right of the chart to add data labels. From the Data Labels flyout, choose More Options. In the panel shown below, click the icon with a column chart and then choose Value from Cells and specify the names in column A.

The tricky part is labeling each point with the name of the person. This screenshot shows the Format Data Labels panel. Choose Value From Cells and point to the names in column A.

The result:

The 13 employees are arranged in a bell curve, from Hank on the far left to Mike and Jared as average and Gary and Ed at the far right.

Interested in improving Employee Engagement? Check out 8 Employee Engagement Ideas for Small Businesses from our friends at MoneyCrashers.com.


Tip

You will often have two labels in the chart that appear on top of each other. You can rearrange single labels so they appear with a small leader line as shown for Gary and Ed at the right side of the chart. Click on any label and all chart labels are selected. Next, click on either of the labels that appear together. After the second click, you are in "single label selection mode". You can drag that label so it is not on top of the other label.

Title Photo: Dose Juice at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.