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.
How to Make a Stacked Histogram in Excel: 3 Easy Methods
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.
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 plot a Histogram chart in Excel.
STEPS:
- First, select the marks column i.e. the range C5:C10.
- Then, go to Insert ➤ Histogram.
- 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
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 make a Histogram in Excel using Data Analysis.
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.
- Next, check the box for Analysis ToolPak.
- Click OK.
- Now, we’ll create a Bins range or 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.
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.
- 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.
Download Practice Workbook
Download the following workbook to practice by yourself.
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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Make a Histogram in Excel with Two Sets of Data
- Difference Between Excel Histogram and Bar Graph
- How to Create a Histogram in Excel with Bins
- How to Change Bin Range in Excel Histogram
- [Fixed!] Excel Histogram Bin Range Not Working
<< Go Back to Excel Histogram | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
