Histogram chart that looks like a column chart, though it isn’t. A histogram is a graphical representation of a dataset that displays the distribution of numerical data. It’s a type of bar chart that groups data into intervals, or “bins,” and shows how many values fall into each bin. The bars of a histogram are typically contiguous and represent the frequency of observations in each bin.
Creating a histogram in Microsoft Excel is a straightforward process. However, if you’re a new Excel user, you may find the process tedious and challenging. Several methods can be used a generate a presentable histogram in Excel. Thus, in this blog, we’re going to discuss two methods of creating Histograms in Excel.
Method 1: Using Analysis ToolPak to make a histogram
The Analysis ToolPak is an Excel add-in that provides advanced data analysis tools for statistical, engineering, and financial analysis. It is designed to help users perform complex data analysis tasks quickly and easily without having to write complex formulas or programs. It has descriptive tools that help generate and calculate analysis with ease. Here are the steps to follow when using this feature:
A] Load the Analysis ToolPak in your Excel
Initially, the Analysis ToolPak is not launched in your Excel. Follow these steps to launch and install the tool in your Excel:
1. Open Excel Application
Open a document that will host your Histogram. Rename the document on your device.
2. Click the File Tab
Locate and click on the File Tab to open the Info Screen on the Ribbon. In the Info Screen, there are several features and tools. Choose the Options button.
3. Choose the Add-ins option
In the dialogue box, locate the Add-ins option in the left pane. On clicking, the Add-ins features and tools will be displayed on the right side of the screen. Locate the Manage section at the bottom part of the screen, and choose the Excel Add-ins from the dropdown menu. Then, hit the Go button.
4. Choose the Analysis ToolPak
Toggle on the Analysis ToolPak checkbox. Finally, hit the OK button to save changes and close the dialogue box.
B] Creating Histogram using the Analysis ToolPak
Now, you have the ToolPak ready. Let’s go ahead and create a Histogram. Here are the steps to follow:
1. Prepare the dataset.
Enter the dataset you wish to convert into Histogram in the empty cells. Make sure you have at least two columns of datasets. For example, one column will hold the results of a test, whereas the other will hold the bins of your dataset.
Note: Histogram bins are the intervals to group numerical data into discrete segments or categories. Each bin represents a range of data values, and the height of the bar above the bin indicates the number of data points that fall within that range.
2. Click the Data Tab.
Once your dataset is ready, click the Data Tab on the Ribbon.
3. Locate the Data Analysis Button
On the Data Tab, there are several buttons and sections. Locate the Analysis section, and click on the Data Analysis button.
4. Choose Histogram
On clicking the Data Analysis button, a Data Analysis dialogue box opens. In the dialogue box, scroll downwards and click the Histogram option. Then, click the Ok button.
5. Customize your Instagram
In the Histogram dialogue box, you need to alter several areas to generate a Histogram. Firstly, in the Input Range, enter the cell references of the column that has your dataset. Secondly, enter the cell references of the column with your Bins in the Bin Range section.
Then, in the Output Range, select the cell where the output calculations will be stored. Finally, check the Chart Output checkbox, and hit the Ok button.
That’s it. The Histogram will be generated and will be stored in your current worksheet.
Method 2: Using the Insert Chart Option
Microsoft Excel has a built-in “Insert Chart” tool that allows you to create various charts and graphs to represent your data. Unlike the Analysis ToolPak, you don’t have to install the insert chart feature before using it. One of the charts you can create using this tool is the Histogram. Let’s discuss the steps and workarounds to follow:
1. Prepare the dataset.
Enter the dataset you wish to convert into Histogram in the empty cells.
2. Click the Insert Tab.
Once your dataset is ready, click the Insert Tab on the Ribbon.
3. Locate the Charts Section
In the Ribbon, click on the Insert Statistic Chart dropdown button. On clicking, the Histogram of your dataset will be drawn. From the menu, there are two types of histograms.
4. Adjust bin properties
Right-click on the drawn Histogram and choose the Format Data Series option. From the dropdown menu, choose the Axis Option.
Adjust the bin properties, Width bin, Overflow bin, and Underflow bin. That’s all you need to do.
Customizing the Histogram
Customizing a histogram in Excel allows you to change its appearance to better suit your needs and to make it easier to understand. Typically, two tabs are used to customize Histogram, The Design tab, and the Format tab.
1. The Design Tab
The Design tab in Excel is a contextual tab that appears in the Ribbon when you have selected a chart in your worksheet. It contains a variety of chart styles and formatting options that allow you to customize the appearance of your chart.
Note: the options available on the Design tab may vary depending on the type of chart you have selected. Check some features you will likely come across when customizing the histogram chart.
- Chart Styles: You can choose from various built-in chart styles to quickly change the look of your chart.
- Chart Layouts: You can select from a range of pre-defined chart layouts that include various combinations of chart elements such as titles, legends, and axis labels.
- Add Chart Element: You can add or remove individual chart elements, such as data labels, trendlines, or gridlines, to your chart.
- Switch Row/Column: You can switch the orientation of your data from rows to columns and vice versa, which can be useful when creating a chart that represents your data differently.
- Data: You can edit the data source for your chart or modify the data series used in your chart.
- Quick Layout: You can use the Quick Layout feature to quickly change the layout of your chart with a single click.
- Chart Title: You can add or modify the chart title, axis titles, or data labels.
2. The Format Tab
On the other side, the format tab contains various formatting and styling options that allow you to customize the appearance of the selected object. These options on the Format tab make it easy to customize the appearance and layout of your selected object to best suit your needs.
Here are some features you’ll likely come across when customizing the histogram chart using the Format tab.
- Shape Styles: You can choose from various built-in shape styles to quickly change the look of the selected histogram chart.
- WordArt Styles: You can apply various WordArt styles to the text in the selected chart, such as text effects, fill, outline, and shadows.
- Arrange: You can use the options in the Arrange group to adjust the position, size, rotation, and order of the selected object.
- Size: You can use the options in the Size group to resize the selected chart or set specific dimensions.
- Properties: You can view and modify the properties of the selected chart, such as the name, location, and size.
- Alt Text: You can add alternative text to the selected chart to describe its content and purpose for people with disabilities.
- Selection Pane: You can use the Selection Pane to manage the selection and visibility of the charts in your worksheet.
Using Data Format Series Tool to customize Histogram
The “Format Data Series” option in Excel can be used to customize the appearance of a histogram. Here are the steps to use this feature to customize a histogram:
1. Select your Histogram.
Click on the Histogram to select it.
2. Open the “Format Data Series” options
Right-click on the Histogram and select Format Data Series from the dropdown menu, or go to the Format tab on the Ribbon and click on Format Selection in the Current Selection group.
3. Customize the histogram color
In the Fill & Line tab of the Format Data Series options, you can change the color of the histogram bars. You can select a solid color or a gradient and adjust the transparency of the color.
4. Adjust the gap width
You can adjust the gap width between the histogram bars in the Series Options tab of the Format Data Series options. A wider gap width will result in more whitespace between the bars.
5. Add a border
You can add a border to the histogram bars in the Line tab of the Format Data Series options. You can choose the border’s color, weight, and dash style.
6. Adjust the data labels
In the Data Labels tab of the Format Data Series options, you can customize the appearance of the data labels on the histogram bars. You can choose to display the frequency, percentage, or both and format the labels using different font styles, colors, and sizes.
Similar Reads:
How to combine a Bar Graph and Line Graph in Excel- With Pictures