How to Create a Distribution Chart in Excel (2 Handy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Distribution Charts help to visualize and analyze data more quickly and in a meaningful way. With this in mind, this article demonstrates useful methods for how to create a distribution chart in Excel.


Download Practice Workbook

You can download the practice workbook from the link below.


2 Methods to Create a Distribution Chart in Excel 

Fortunately, you can easily represent this dataset in a Distribution Chart using Microsoft Excel. So, without further delay, let’s dive into the methods!


1. Creating a Frequency Distribution Chart in Excel

A Frequency Distribution or Histogram represents the data in ranges or bins, making it easy to interpret the data.

Let’s say we have the information for Oakmont Ridge Golf Club shown in the B4:C14 cells below. Here, the dataset shows the names of the club Members and their Ages respectively.

Dataset 1


1.1  Applying FREQUENCY Function to Make Frequency Distribution Chart 

For our first method, we’ll utilize the FREQUENCY function to create a frequency Distribution Chart or Histogram. So, just follow these simple steps.

📌 Step 01: Calculate Bins and  Frequency

  • At the very beginning, add a column for the bins, in this case, Age Bracket 1.

Now, in this dataset, the Age value starts at 25, so we set the starting value of the bin to 20. In addition, we chose a Bin Size of 10.

  • Then, we enter the expression given below.

=E7+$G$4

Here, the E7 and the G4 cells represent the Age Bracket 1 and Bin Size respectively.

It is important to note that you must lock the G4 cell reference with the F4 key on your keyboard.

Using FREQUENCY Function

  • Next, we calculate the Age Bracket 2 as shown below.

="<="&E7

In the above formula, we combine the less-than-equal sign (“<=” ) with the E7 cell using the Ampersand (&) operator.

Using FREQUENCY Function

  • Following this, type in the expression given below.

=E7+1&"-"&E8

In this expression, the E8 cell refers to Age Bracket 1.

Using FREQUENCY Function

  • In turn, add the frequency column with the header Number of Member and enter this formula.

=FREQUENCY(C5:C14,E7:E13)

In the above formula, the C5:C14 and the E7:E13 cells indicate the Age and the Age Bracket 1 columns respectively.

How to Create a Distribution Chart in Excel Using FREQUENCY Function

📌 Step 02: Insert the Chart and Add Formatting

  • Secondly, select the Age Bracket 2 and the Number of Member columns.
  • Next, go to Insert > Insert Column or Bar Chart > Clustered Column.

How to Create a Distribution Chart in Excel Using FREQUENCY Function

  • Then, double-click on any of the bars to open the Format Data Series window.
  • Now, set the Gap Width to 0%.

How to Create a Distribution Chart in Excel Using FREQUENCY Function

  • Following this, go to Border > Solid Line and choose a Color. In this case, we chose Black.

How to Create a Distribution Chart in Excel Using FREQUENCY Function

  • Lastly, insert Axis Titles from the Chart Elements option.

How to Create a Distribution Chart in Excel Using FREQUENCY Function

Eventually, the results should look like the below image.

Histogram


1.2  Using Data Analysis ToolPak to Create Frequency Distribution Chart

If you’re hoping for a shortcut, then I have some good news for you! Here, we’re going to utilize the Analysis ToolPak to create the Frequency Distribution Chart. Now, allow me to demonstrate the process bit by bit.

📌 Steps:

  • Initially, navigate to File > Excel Options.

Using Data ToolPAk

  • Now, a dialog box opens where you have to click the Add-ins > Go button.

Using Data ToolPak

  • In the next step, choose the Analysis ToolPak option and click OK.

How to Create a Distribution Chart in Excel Using Data ToolPak

  • Then, go to Data > Data Analysis.

How to Create a Distribution Chart in Excel Using Data ToolPak

  • From this list, choose the Histogram option.

How to Create a Distribution Chart in Excel Using Data ToolPak

  • In turn, enter the Input Range, Bin Range, and Output Range as shown below. In addition, check the Chart Output option.

How to Create a Distribution Chart in Excel Using Data ToolPak

Consequently, you should get the following output as shown in the screenshot below.

Histogram


1.3  Inserting Frequency Distribution Chart Pivot Table

The third and final way to insert a Histogram is to use Excel’s PivotTable where we’ll apply the Group Data feature to make a Distribution Chart. So, let’s see it in action.

Considering the Sales Report dataset shown below in the B4:D14 cells. Here, the first column indicates the Store number, following that we have the store size in Square Feet, and lastly, we have a column for the Sales amount in USD.

Dataset 2

📌 Step 01: Insert a Pivot Table and Group Data

  • At the very beginning, select any cell within the dataset and go to Insert > PivotTable > From Table/Range.

Using Pivot Table

  • Next, a dialog box appears in which you have to check the New Worksheet option and press OK.

Using Pivot Table

  • Then, on the PivotTable Fields pane drag the Square Feet and Sales fields into the Rows and Values fields respectively.

Using Pivot Table

Just like that, you’ve made a PivotTable, it’s that easy.

Pivot Table

  • In turn, you can format the numeric values by right-clicking the mouse and selecting the Field Value Settings.

How to Create a Distribution Chart in Excel Using Pivot Table

  • In the next step, click the Number Format button.

How to Create a Distribution Chart in Excel Using Pivot Table

  • Following this, choose the Currency option. In this case, we chose 0 decimal places for the Sales value.

  • Now, select any cell in the PivotTable and right-click on the mouse to go to Group Data.
  • In the next step, we group the store size into bins. Just enter the start value (Starting at), the end value (Ending at), and the interval (By).

How to Create a Distribution Chart in Excel Using Pivot Table

📌 Step 02: Insert Histogram

  • Secondly, select any cell in the PivotTable and go to PivotChart.

How to Create a Distribution Chart in Excel Using Pivot Table

  • This time, choose Column > Clustered Column chart.

How to Create a Distribution Chart in Excel Using Pivot Table

  • Next, add formatting to the chart using the Chart Elements option.

How to Create a Distribution Chart in Excel Using Pivot Table

Finally, the resulting Histogram should look like the image shown below.

How to Create a Distribution Chart in Excel Using Pivot Table

If you wish, you can learn more about Frequency Distribution Charts.


2. Making a Normal Distribution Chart with NORM.DIST Function in Excel

In our last method, we’ll make a Normal Distribution Chart, also known as a Bell Curve. But first, let’s dwell a little upon what Normal Distribution Chart is.

Normal Distribution Chart is a continuous probability function that calculates if an event will occur or not.

Seems quite complex, right? However, Excel’s built-in NORM.DIST function makes it easy for us to create a Normal Distribution Chart. Just follow along.

Assuming the dataset shown below where the student Names and their corresponding Marks in Mathematics are provided.

Dataset 3

📌 Step 01: Calculate Mean and Standard Deviation

  • Firstly, create two new rows for the Mean and Standard Deviation.
  • Now, enter the formula shown below to compute the mean Marks.

=AVERAGE(C5:C14)

In this formula, the C5:C14 cells point to the Marks. Moreover, we use the AVERAGE function to obtain the mean Marks.

How to Create a Distribution Chart in Excel Using NORM.DIST Function

  • In a similar fashion, type in the formula to calculate the Standard Deviation of the Marks.

=STDEV(C5:C14)

Here, we’ve used the STDEV function to get the Standard Deviation.

How to Create a Distribution Chart in Excel Using NORM.DIST Function

  • Next, we calculate the values of the Normal Distribution Table using the NORM.DIST function.

=NORM.DIST(C5,$G$4,$G$5,FALSE)

In this expression, the C5 cell (x argument) refers to the Marks column. Next, the G4 and G5 cells (mean and standard_dev arguments) indicate the Mean and Standard Deviation values of the dataset. Lastly, FALSE (cumulative argument) is a logical value determining the form of the function.

How to Create a Distribution Chart in Excel Using NORM.DIST Function

Wow, your Normal Distribution Table  is complete! Let’s insert a chart.

How to Create a Distribution Chart in Excel Using NORM.DIST Function

📌 Step 02: Insert Normal Distribution Chart

  • Secondly, select the Marks and Normal Distribution columns.
  • Then, go to Insert > Insert Scatter or Bubble Chart > Scatter with Smooth Lines.

How to Create a Distribution Chart in Excel Using NORM.DIST Function

Subsequently, you should get the outcome as shown in the screenshot below.

Normal Distribution

Read More: How to Make a Cumulative Distribution Graph in Excel


Conclusion

I hope this article helped you grasp how to create a Distribution Chart in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website, ExcelDemy.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo