MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2024: Plotting Employees on a Bell Curve

April 03, 2024 - by

Excel 2024: Plotting Employees on a Bell Curve

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)
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).
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.
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 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.

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.

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.
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.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Алекс Арцибашев on Unsplash