Eshrak Kader
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.
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.
- 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.
- Following this, type in the expression given below.
=E7+1&"-"&E8
In this expression, the E8 cell refers to Age Bracket 1.
- 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.
📌 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.
- Then, double-click on any of the bars to open the Format Data Series window.
- Now, set the Gap Width to 0%.
- Following this, go to Border > Solid Line and choose a Color. In this case, we chose Black.
- Lastly, insert Axis Titles from the Chart Elements option.
Eventually, the results should look like the below image.
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.
- Now, a dialog box opens where you have to click the Add-ins > Go button.
- In the next step, choose the Analysis ToolPak option and click OK.
- Then, go to Data > Data Analysis.
- From this list, choose the Histogram option.
- In turn, enter the Input Range, Bin Range, and Output Range as shown below. In addition, check the Chart Output option.
Consequently, you should get the following output as shown in the screenshot below.
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.
📌 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.
- Next, a dialog box appears in which you have to check the New Worksheet option and press OK.
- Then, on the PivotTable Fields pane drag the Square Feet and Sales fields into the Rows and Values fields respectively.
Just like that, you’ve made a PivotTable, it’s that easy.
- In turn, you can format the numeric values by right-clicking the mouse and selecting the Field Value Settings.
- In the next step, click the Number Format button.
- 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).
📌 Step 02: Insert Histogram
- Secondly, select any cell in the PivotTable and go to PivotChart.
- This time, choose Column > Clustered Column chart.
- Next, add formatting to the chart using the Chart Elements option.
Finally, the resulting Histogram should look like the image shown below.
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.
A 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.
📌 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.
- 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.
- 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.
Wow, your Normal Distribution Table is complete! Let’s insert a chart.
📌 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.
Subsequently, you should get the outcome as shown in the screenshot below.
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.
