How to Make a Stacked Histogram in Excel (3 Easy Methods)

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we insert graphs or charts to analyze data in a better way. Such a kind is the Histogram. There are some ways available to create a stacked histogram. You’ll find all the prevalent processes here. This article will show you 3 easy methods to Make a Stacked Histogram in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


3 Easy Methods to Make a Stacked Histogram in Excel

A histogram is a type of chart where the data points are grouped over specified ranges. This kind of graphical representation is effective when working with a large number of data values. It reduces the complexity of going over every single data point. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains the Student names and their obtained Marks in a subject. Here, we’ll organize the marks in specified intervals. Users can define these intervals and they’re better known as bins. Now, learn all the methods described below to make a stacked histogram.

how to make a stacked histogram in excel


1. Apply Built-in Histogram Chart in Excel

The easiest way is to use the built-in histogram chart. Therefore, follow the steps below to perform the task.

STEPS:

  • First, select the marks column i.e. the range C5:C10.
  • Then, go to Insert ➤ Histogram.

Apply Built-in Histogram Chart in Excel

  • As a result, you’ll get a histogram chart.
  • But, that is not our desired output yet.
  • So, right-click on the X-axis legend.
  • Choose Format Axis from the Context Menu.

  • Consequently, the Format Axis pane will appear.
  • There, input 10 in the Bin width under the Axis Options section.

  • Thus, you’ll get your desired stacked histogram.
  • See the figure below which is our final output.

Read More: How to Create a Histogram in Excel with Bins (4 Methods)


2. Make a Stacked Histogram Through Data Analysis

Moreover, the Data Analysis feature of Excel helps us to perform various operations. In this method, we’ll make use of the data analysis toolpak. So, learn the following steps to carry out the operation.

STEPS:

  • Firstly, select File ➤ Options.
  • Hence, the Excel Options dialog box will pop out.
  • Go to the Add-ins tab.
  • After that, choose Excel Add-ins from the Manage drop-down.
  • Click Go.

Make a Stacked Histogram Through Data Analysis

  • Next, check the box for Analysis ToolPak.
  • Click OK.

  • Now, we’ll create a Bins column according to our required interval.

  • Afterward, select Data ➤ Data Analysis.

  • Subsequently, choose Histogram.
  • Press OK.

  • As a result, the Histogram dialog box will emerge.
  • There, select the Marks column as the Input Range.
  • Then, choose the Bins Range.
  • Select the F4 cell as the Output Range.
  • Check the box for Chart Output.
  • Press OK.

  • Thus, it’ll spill Bin and Frequency as shown below.

  • You’ll also get the Histogram chart.

  • To transform it to a stacked histogram, right-click on the columns.
  • Choose Format Data Series.

  • Make the Gap Width 0%.

  • Choose a Solid line as Border and Black as Color.

  • In this way, we’ll get our stacked histogram.

stacked histogram output

Read More: How to Make a Histogram in Excel with Two Sets of Data (4 Ways)


3. Use Excel FREQUENCY Function to Insert Stacked Histogram

The FREQUENCY function in excel provides us with the number of occurrences of data values in a specified range. Here, we’ll insert this function and a simple column chart to get a Stacked Histogram. Hence, follow the process below.

STEPS:

  • First of all, click cell F5.
  • Then, type the formula:
=FREQUENCY(C5:C10,E5:E9)
  • Press Enter.

Use Excel FREQUENCY Function to Insert Stacked Histogram

  • Now, select the Bins range and the Frequency range.
  • Next, click Insert ➤ 2-D Column chart.

  • After that, right-click on the columns and choose Select Data.

  • Select the Frequency range in the Legend Entries.
  • Subsequently, make the Bins range as the Horizontal Axis Labels.
  • Press OK.

  • Finally, the stacked histogram is ready to be displayed.

Use Excel FREQUENCY Function to Insert Stacked Histogram

Read More: How to Make Histogram Using Analysis ToolPak (with Easy Steps)


Conclusion

Henceforth, you will be able to Make a Stacked Histogram in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo