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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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.

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

Alternatively, you can determine Normal Distribution using a mathematical formula. To do this, use the following formula in the D5 cell.

=EXP(-((C5 – $G$4)^2) / (2 * $G$5^2)) / ($G$5 * SQRT(2 * PI()))

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.

Normal distribution chart using Mathematical formula without 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.

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


Download Practice Workbook


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


<< Go Back to Excel Distribution Chart | Excel Charts | 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.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo