How to Make Bell Curve in Excel for Performance Appraisal

Get FREE Advanced Excel Exercises with Solutions!

The Bell Curve is one of the most useful tools used in statistics and financial data analysis. This helps us to visualize the normal probability distribution of a range of data. In this article, we are going to see how we can make a bell curve in Excel for performance appraisal.


What Is Bell Curve?

As the name suggests, the bell curve is a curve that resembles the shape of a bell which depicts the normal distribution. The highest point of the curve indicates the most probable event in the range of data, which can be either the mean, mode, or median of the range. Other data are evenly distributed around this point and so have an evenly distributed probability of occurring. While the standard deviation of the dataset is depicted by the relative width of the curve.

The curve looks something like this.

bell curve in excel for performance appraisal

This bell curve feature indicates that 68.2% of the distribution is within one standard deviation of the mean. And 95.5% of the distribution is within two standard deviations of the average. While 99.7% are within three standard deviations of the mean.

The practical application of this curve is huge. It helps us to visualize how the mean, mode, or median is above the rest. The curve also shows how other values are clustered around them.


How to Make Bell Curve in Excel for Performance Appraisal: 2 Suitable Examples

In this tutorial, we are going for two different approaches to making bell curves in Excel for performance appraisal. The first example will focus on calculating the normal distributions and then making the bell curve out of it. The second example will focus on determining the total percentiles of employees based on their performance and then make a bell curve from it. Each scenario is described in detail in its own sub-section. Follow along to see how you can determine the bell curve in each scenario or find the one you prefer.


1. Bell Curve of Sales Increase

In this example, we will work with the following dataset.

bell curve in excel for performance appraisal

This dataset consists of sales of each employee in two consecutive months and the increase in their sales. We are going to make a bell curve for this increase in sales. In order to do that, we are going to first determine the average, then the standard deviation for all the increases in sales. And then find out the normal distribution for each of them. We can then finally proceed to plot the curves and thus have the bell curve for performance appraisal- all done in Excel.

In pursuit of these, we are going to use the AVERAGE, STDEV.P, and NORM.DIST functions. The AVERAGE function returns the average of the numbers it takes as arguments. On the other hand, the STDEV.P function also takes a series of numbers as arguments and returns the standard deviations. While the NORM.DIST function takes a value from a range of data, mean and standard deviation for the dataset, and a boolean value as its arguments and returns the normal distribution of the number in the number.

Follow these steps for a more detailed guide.

Steps:

  • First, select a cell to put in the average value of the range of data. We have selected cell D16 for this.

  • Now, select cell D16 and write down the following formula.

=AVERAGE(E5:E14)

  • Once done, press Enter on your keyboard. Thus you will have the average for all the data.

  • Then select a cell for standard deviation (we have selected cell D18 for this) and write down the following formula.

=STDEV.P(E5:E14)

  • Now press Enter on your keyboard.

bell curve in excel for performance appraisal

  • After that, make a column for the normal distribution.

bell curve in excel for performance appraisal

  • Now select cell F5 and write down the following formula.

=NORM.DIST(E5,$D$16,$D$17,FALSE)

bell curve in excel for performance appraisal

  • Then press Enter on your keyboard.

bell curve in excel for performance appraisal

  • Now select the cell again. Then click and drag the fill handle icon to the end of the column to fill up the rest of the cells with this formula.

  • While the column is selected go to the Insert tab on your ribbon and select Recommended Charts from the Charts

bell curve in excel for performance appraisal

  • Now in the Insert Chart box, select All Charts
  • From the left side of the box, select X Y (Scatter), and select Scatter with Smooth Lines from the right side of the box.

bell curve in excel for performance appraisal

  • Finally, click on OK.

As a result, the bell curve will now be plotted on the spreadsheet.

bell curve in excel for performance appraisal

After some modifications, the bell curve for performance appraisal will look something like this.

bell curve in excel for performance appraisal


2. Bell Curve for Project Completion Remarks

In our second example, we will use this dataset for demonstration.

It is another list of employees with how many days they needed to complete a project and a remark based on how they performed. We are going to use this dataset to plot the bell curve in this example.

To do that, we are going to make a different dataset from this one with the help of the COUNTIF and SUM functions. The COUNTIF function takes a range and a condition as arguments and returns how many times the condition has been met in the range. While the SUM function takes a range as an argument and returns the total of those numbers.

Follow these detailed steps for a clear guide on how we can plot the bell curve for performance appraisal for this dataset.

Steps:

  • First, make a chart for the performance appraisal list as shown in the figure below.

bell curve in excel for performance appraisal

  • Then go to cell H6 and write down the following formula in it.

=COUNTIF($E$5:$E$24,G6)

  • After that, press Enter.

  • Now select the cell again and click and drag the fill handle icon to the end of the column to fill the rest of the cells with the formula.

  • Now, select cell I6 and write down the following formula.

=H6/SUM($H$6:$H$10)

  • Then press Enter on your keyboard.

  • After that, select the cell again and click and drag the fill handle icon to fill the rest of the column with the formula.

  • After that, select the Remarks and % of total columns.

bell curve in excel for performance appraisal

  • Then go to the Insert tab on your ribbon and select Recommended Charts from the Charts

bell curve in excel for performance appraisal

  • Next, select the All Charts tab in the Insert Chart
  • Now select the X Y (Scatter) option from the left of the box and select the Scatter with Smooth Lines from the right side.

bell curve in excel for performance appraisal

  • Finally, click on OK.

As a result, a bell curve for performance appraisal based on the dataset will be inserted into the spreadsheet.

bell curve in excel for performance appraisal

After some modifications, it will look something like this.

bell curve in excel for performance appraisal

Read More: How to Create a Bell Curve in Excel


Download Practice Workbook

You can download the workbook with the examples used for demonstration in this example from the link below. Try downloading and practicing it while you go through the guide.


Conclusion

These were two different examples of making a bell curve in Excel for performance appraisal. Hope you will be able to make a bell curve for your dataset now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.


Related Articles


<< Go Back to Bell Curve in Excel | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

2 Comments
  1. Very nicely explained the complicated subject.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo