Logo
×

Histogram in Excel

What is Histogram in Excel?

“ A histogram is a graphical tool familiar for summarizing discrete or continuous data, where the data are measured on an interval scale.” 

Karl Pearson described this technique to assess the probability distribution of the given variable. It displays the data in a frequency of interval for the metric variable. The intervals are known as "bins" that have similar widths. It is used for data analysis in business, where the data are represented in specified ranges in the column chart. The Histogram has the feature of simplicity and versatility. It represents the data in continuous frequency distributions, and the data are described in the form of a bar chart. There is no gap present between the bar chart. The data points in the Histogram are grouped to represent the logical range or bit.

Additional Features in Histogram

 To enhance the appearance, additional features like colour and distance are changed. Let's have a look at how to format the Histogram in Excel.

  1. X-axis – The dataset variables are represented in charts X-axis.

Split by A chart table is created for categorical variables where each cell is designed with a separate category by Split by Variable. This feature adds dimensions to the chart by showcasing the information in a particular variable category.

  • KDE curve- If this feature is enabled, the kernel density estimate curve will display in the chart
  • Distribution Curve- If this feature is enabled, the distribution fitting curve will display in the chart.

Types of Histogram

The various types of Histogram as follows,

  • Normal Distribution
  • Bimodal Distribution
  • A Right Skewed Distribution
  • A Left Skewed Distribution
  • A Random Distribution

Normal Distribution

It is one of the statistical functions. For given data or parameters it calculates the probability density function or cumulative normal distribution. It is used in Stock Market Analysis. Formula used to calculate Normal Distribution is,

=NORMDIST(x, mean, standard_dev, cumulative)

The normal distribution curve is bell-shaped with one peak and is symmetric around the mean.

Bimodal Distribution

Usually in Normal Distribution the graph contains one peak. In bimodal distribution it contains two peaks. The data present in the bimodal are separated and analyzed as a separate normal distributions. Another name for this distribution is double peaked distribution.

Step 1: Enter the data in a spreadsheet. Here the id.no and students marks mentioned.

Histogram

STEP 2: Select the Id.no and marks and click Insert>Chart>Scatter.  A chart is created for the given data. It has two peaks based on the variation of data.

Histogram

Right Skewed Distribution

In right skewed distribution large sets of data present in the left side and only a few data present in the right side. Another name for this is positively skewed distribution.

Histogram

A look alike of right skewed distribution is mentioned above.

Left Skewed Distribution

Another name for Left skewed distribution is Negatively Skewed Distribution. In this distribution large number of dataset occur in the right side and only a few data occur in left side. If the range boundary of the data occurs in right side left skewed distribution takes place.

Histogram

Random Distribution

As the name itself suggests, it has several peaks called as multimodal distribution. The data are separated and analyzed and it is a combination of normal distributions.

Histogram

How to create a Histogram in Excel?

Creating the Histogram in the Excel Spreadsheet varies depending upon the user's Excel Version. In the histogram, data intervals are called Bins, and the number of indices is called Histogram. It is used to relate one variable.

To implement normal distribution in Excel, following steps are implemented.

STEP 1: Enter the data in spreadsheet. Here the student Id, Name and marks are entered.

Histogram

STEP 2: To calculate normal distribution, the mean and standard deviation is needed. To find the mean, the average formula is applied. Choose a cell in the worksheet and type the formula as =AVERAGE (range)

Histogram

STEP 3: The mean value for C2:C15 is displayed in the cell D2.

Histogram

STEP 4: To found standard deviation, the formula used are =STDEV (range)

Histogram

STEP 5: The standard deviation for C2:C15 is calculated and displayed in the cell E2.

Histogram

STEP 6: As mean and standard deviation is found, the normal distribution is calculated using the formula =NORM.DIST (C2, $D$2, $E$2, FALSE)

Histogram

STEP 7: The value of normal distribution is shown below.

Histogram

STEP 8: By applying this formula to the rest of the data, the normal distribution is calculated as shown below.

Histogram

Normal Distribution Graph in Excel

To insert a graph in Excel, sort the mark columns from lowest to highest for the normal distribution of the graph.

STEP 1: To sort the values from smallest to largest, select the mark column. Choose Home>Sort & Filter> Sort Ascending

Histogram

STEP 2: The values are arranged from ascending to descending order. The arranged values are shown below.

Histogram

STEP 3: To insert chart select the mark and normal distribution column. Choose Insert>Insert Chart > Scatter.

Histogram

STEP 4: Select the first chart in scatter. A normal distribution graph is created for the given range of data.

Histogram

STEP 5: One can name the horizontal and vertical axes by choosing the Chart>Axis Title> Horizontal/Vertical Axis.

Histogram

STEP 6: The horizontal and vertical axes are named as Marks and Normal Distribution.

Histogram

How to create a histogram for a data in an Excel?

Excel 2016 has in-built histogram chart option.

Excel 2013, 2010 or prior version has no default histogram function. One can install it by means of Data Analysis Tool pack or using Frequency Function.

Let’s have a look about how to create a histogram chart in Excel 365.

To create a histogram for a data in excel following steps are followed.

STEP 1: Enter the data in the spreadsheet. Here various vegetables and quantity of purchased is mentioned below.

Histogram

STEP 2: To create histogram, select the entire dataset. Choose>Insert tab>Chart>Histogram.

Histogram

STEP 3: A histogram chart is created for given data.

Histogram

STEP 4: Right click the chart, it displays the several options as shown below. You can change the histogram settings by choosing your desired color, title and gridlines.

Histogram

STEP 5: By using format option the horizontal and vertical axes are named.

Histogram

Uses of Histogram

  • The histogram is used to determine the data's mean, mode, distribution and median. Data is presented in different frequencies and types.
  • The data presented should be in numerical form.
  • The distribution shape is checked by using this method.
  • The output is predicted using checking two or more process
  • It is used to check whether it meets the customer's requirements.

Related Topics

Excel MINUTE() Function

Excel MINUTE() Function The MINUTE() function in excel converts a serial number to minute (0 - 59). Syntax MINUTE (SERIAL_NUMBER) Parameter SERIAL_NUMBER (required)- This parameter represents the date or time from which you want to extract...

1 minute read.

How to create drop down in excel?

How to create drop down in excel? Drop-down lists are mostly used to facilitate data entry operations. They are commonly used in interactive websites or applications. Microsoft has expanded his Excel's data validation feature...

5 minutes read.

Goal Seek in Excel

Goal Seek in Excel The Goal Seek Excel function in Excel facilitates an automatic solving approach by adjusting a few pointers to reach a required output. Goal seeking is used when you have a value in...

3 minutes read.

Excel Tutorial for Beginners

What is Excel? Microsoft Excel is a software program included in the Microsoft Office suite. It is a commercial spreadsheet application developed by Microsoft for Windows, macOS, Android, and iOS. It...

5 minutes read.

How to make use of the Wildcard in Excel

In this modern world, it is known that the wildcard characters in Microsoft Excel are considered to be the most essential and unrated features of Microsoft Excel. Besides all these,...

8 minutes read.

Dependent Drop-down List in Excel

Excel forms are used to fetch data from the end user. When we have to give choices, we often use the drop-down feature of Excel. In this tutorial, we won’t...

7 minutes read.

Unique Values in Excel

The Unique function in Excel filters the list of unique values from the given data like range or array. The data presented is either number, text, date, time etc. This...

3 minutes read.

Excel MIN() Function

Excel MIN() Function The MIN() function in excel returns the smallest or minimum value from the provided numbers, named range or array. It ignores logical values and text. Syntax MIN(Number1,Number2, ...) Parameter Number1(required)- This parameter represents the...

1 minute read.

Text to columns in Excel

Microsoft Excel is widely used in startups to large companies. The data are entered in required rows and columns, and sometimes the data entered in one cell needs to enter...

4 minutes read.

Excel IFNA Function

Excel IFNA Function The IFNA in Excel is used to remove the #N/A type of errors in excel values cells with our choice of value. IFNA function is from Excel 2013 onwards, so it...

1 minute read.

Excel IF() Function

Excel IF() Function: The IF() function in excel checks whether a condition is true or not and returns one value if the condition is TRUE and another value if it's...

2 minutes read.

Quarter Dates in Excel

Excel is a combination of numeric values and alphabets. The user's project consists of the date depending upon their accounting and financial analysis. Sometimes there is a need to find...

4 minutes read.

Conditional Formatting in Excel

Conditional Formatting What are the conditional formats? Conditional Formatting is a tool that allows the user to format cells or range of cells based on the selected condition or given criteria. The formatting will...

4 minutes read.

How to Delete Row in Microsoft Excel?

Individuals can quickly delete the respective row from the particular Microsoft Excel whenever they want, according to their needs and requirements. In this tutorial, we will discuss the following things in...

7 minutes read.

Excel OFFSET () Function

Excel OFFSET () Function The OFFSET() function in excel returns range of cells that is a specified number of rows and columns from an initial specified range. Syntax OFFSET (reference, rows, cols, [height], [width]) Parameter reference(required)-...

2 minutes read.

Excel INT() Function

Excel INT() Function The INT() function in excel is used to round a number down to the nearest integer. It removes decimal points from any number value & return rounded down number value in...

1 minute read.

How to Highlight Duplicates Words in the Microsoft Excel?

We can easily highlight the values that are duplicated in the selected dataset, whether it could be a column or a row from the particular table, with the help of...

6 minutes read.

Formatting in Excel

Formatting in Excel Formatting in Excel is used to alter and change the appearance of your data in a standardized form. It also helps you to give a professional look to your reports, worksheets,...

7 minutes read.

Excel Unique Values

The Unique function in Excel filters the list of unique values from the given data like range or array. The data presented is either number, text, date, time etc. This...

3 minutes read.

Excel COUNT() Function

Excel COUNT() Function The COUNT() function counts the number of cells in a range that contain numbers. Syntax COUNT([value1], [value [2], ...) Parameter value1(required)- This parameter represents the first cell in the range. value2, …(optional)- It represents the...

1 minute read.