This article demonstrates 3 distinct ways to create a frequency distribution chart in Excel. Besides, it focuses on the use of Excel formulas to create normal distribution and then related charts in Excel. Also, suitable data types for different distribution charts are discussed.
Creating different distribution charts in Excel allows you to visually represent and analyze the frequency distribution of data, providing valuable insights into its characteristics and patterns. Excel offers a variety of chart types that can be utilized to create distribution charts, including histograms, box plots, and cumulative frequency charts. By leveraging Excel’s charting features, you can easily generate distribution charts to visualize and analyze the frequency distribution of your data. These charts provide a powerful tool for understanding data patterns, detecting outliers, comparing distributions, and making data-driven decisions
How to Create a Distribution Chart in Excel: 2 Methods
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 area 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.
Alternatively, you can determine Normal Distribution using a mathematical formula. To do this, use the following formula in the D5 cell.
Here, $G$4 and $G$ represent Mean (μ) and Standard deviation (σ) respectively.
The basis of the above formula is the following mathematical formula:
f(x) = exp(-(x - μ)^2 / (2σ^2))/((σ * √(2π))
🔎 Formula Explanation
Now, let’s breakdown our used formula in the D5 cell.
- EXP(-((C5 – $G$4)^2) / (2 * $G$5^2)): This formula finds the squared difference between the C5 and G5 cells first. The output is then divided by 2 times squares value of G5 Finally, we use the EXP function to find the exponential value of exp(-(x – μ)^2 / (2σ^2)) according to the mathematical formula.
- ($G$5 * SQRT(2 * PI())): This formula finds out the value of ((σ * √(2π)). Here $G$5 is σ.
- Using the data in the Normal Distribution column create the Normal Distribution Chart like the previous method.
📌 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: Plot Normal Distribution in Excel with Mean and Standard Deviation
Suitable Data Type for Different Distribution Chart
- Discrete data: Discrete data refers to information that can only take on specific, separate values, typically represented by whole numbers or distinct categories.
- Continuous data: Continuous data refers to information that can take on any value within a specific range or interval. It is measured on a continuous scale and can include fractional or decimal values. Continuous data is not limited to distinct categories or whole numbers.
Distribution Type | Data Type | Example |
---|---|---|
Normal Distribution | Continuous numerical data that follows a bell-shaped curve. | Heights of individuals, IQ scores |
Uniform Distribution | Continuous numerical data that is evenly distributed across a range. | Random number generation, rolling a fair die |
Exponential Distribution | Continuous numerical data representing the time between events in a Poisson process. | Interarrival times in a queue, the time between phone calls. |
Poisson Distribution | Discrete numerical data representing the number of events occurring in a fixed interval. | Number of emails received per day, number of accidents per month. |
Geometric Distribution | Discrete numerical data representing the number of trials needed to achieve the first success. | Number of attempts to make a successful shot in basketball |
Log-Normal Distribution | Continuous numerical data where the logarithm of the values follows a normal distribution. | Stock prices, incomes. |
Weibull Distribution | Continuous numerical data used to model reliability and failure rates. | Time to failure of a mechanical component |
Gamma Distribution | Continuous numerical data used to model wait times, durations, and arrival rates. | Service times in a queue, time between arrivals in a Poisson process |
Triangular Distribution |
Continuous numerical data that is bounded and characterized by a peak at the mode. | Estimating project durations, uncertainty analysis. |
Things to Remember
- Ensure your data is accurate and properly formatted. Incorrect or inconsistent data can lead to misleading distribution charts.
- Choose the appropriate chart type for the distribution you want to visualize. Different types of charts are suited for different types of data.
- Understand the underlying statistics and concepts related to the distribution you’re analyzing. This will help you interpret the chart accurately.
- By default, you won’t get Data Analysis Toolpak add-in in your Excel ribbon. You have to add it.
- If you input any non-numeric value while using the NORM.DIST function, you’ll get #VALUE! Error.
Frequently Asked Questions
1. How to do histogram Excel?
Yes, Excel provides the histogram chart type to display the distribution of your data. You can select your data, go to the “Insert” tab, choose the histogram chart type, and customize it to suit your needs.
2. How can I customize the appearance of my distribution chart in Excel?
Excel allows you to customize various aspects of your distribution chart. You can modify the axes, add titles, adjust labels, change colors, and apply other formatting options to improve readability and visual appeal.
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
- How to Create a Probability Distribution Graph in Excel
- How to Plot Poisson Distribution in Excel
- How to Plot Weibull Distribution in Excel
- How to Plot Particle Size Distribution Curve in Excel
<< Go Back to Excel Distribution Chart | Excel Charts | Learn Excel