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.
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.
- 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.
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.
- 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 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.
- How to Create Probability Histogram in Excel (with Easy Steps)
- Plot Histogram with Unequal Class Intervals in Excel
- How to Plot Histogram in Excel (5 Easy Ways)
- 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.
- First of all, click cell F5.
- Then, type the formula:
- 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.
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.