Unleash The Power of Data Analysis: Learn How To Create Mind-Blowing Histograms In Excel For Windows And Mac!

Data analysis is a critical tool in business decision making. By analyzing data, businesses can identify trends, forecast future outcomes, and optimize their operations. One essential tool in data analysis is the histogram, a graph that displays the distribution of data. Histograms make it easy to see the central tendency, dispersion, and shape of your data. In this article, we’ll show you how to create beautiful histograms in Excel for Windows and Mac!

What Is A Histogram?

A histogram is a chart that shows how often specific ranges of values occur in a dataset. It is a graph that displays the distribution of data over a continuous interval or certain ranges. Each bar in the histogram represents a range of data, and the height of each bar represents the frequency of data occurring in that range.

Histograms are widely used in data analysis to visualize data for better interpretation. Visual representation of data enables the researcher to quickly identify trends, distributions, and patterns in the data.

Steps to Create Mind-Blowing Histograms in Excel

Creating a histogram in Excel is a straightforward process. Here are the steps to follow:

Step 1: Gather Your Data

The first step is to gather the data you want to analyze into Excel. Ensure that your dataset is clean, complete, and accurate. The data should be in columns with a single header row.

Step 2: Prepare The Data

Once you have your data, you need to prepare it for the histogram. You need to group your data into intervals or bins. Bins are the ranges of values that you will use to create the histogram. Excel provides a tool called “Data Analysis” that can automatically create bins for you.

To enable the Data Analysis tool, go to the Excel Ribbon, click on the “Data” tab, select “Data Analysis” from the “Analysis” group, select “Histogram,” and then click “OK.”

Step 3: Create the Histogram

After setting up your data for histogram generation, the next step is to create your histogram. Here is how:

  1. On the Excel ribbon, click on “Insert.”
  2. Click on “Recommended Charts.”
  3. Select “All Charts” or “Histogram” from the drop-down menu.
  4. Choose the histogram chart you want, and then click “OK.”

Excel will automatically create a histogram, grouping the data into bins and displaying them as bars.

Step 4: Modify The Histogram

The chart you get from step three may not always meet your preferences. However, with Excel, you can change the chart appearance easily. To modify the histogram, do the following:

  1. Click anywhere on the chart to select it.
  2. Excel will show you the “Chart Elements” and “Chart Design” tabs.
  3. You can choose to add or remove fields such as chart titles, data labels, gridlines, axis titles, and legend.
  4. You can format the chart by editing colors, filling, borders, or text.

Conclusion

Creating a histogram in Excel is an excellent way to analyze data effortlessly. This tool provides businesses with a quick understanding of data distribution without significant data expertise. It is a powerful method to visualize data trends and distributions, and adoption of it should be a priority for every business. With this guide, the process of creating histograms in Excel for Windows and Mac is made simpler. Happy data analysis!

This article explains how to create a histogram in Excel. Histograms are supported by Excel 2019, 2016, 2013, 2010, 2007 and Excel for Mac, but the steps you take depend on which version of Excel you’re using.

What to Know

  • 2016: Enter data > in Insert tab, choose Recommended Charts. In All Charts tab, choose Histogram > format.2013, 2010, 2007: File > Options > Add-ins > Excel Add-ins > Go > Analysis ToolPak > OK.Mac: Tools > Excel Add-ins > Analysis ToolPak. Exit and restart Excel. In Data tab, create histogram.

How to Make a Histogram in Excel 2016

Excel 2016 has a histogram maker that’s especially easy to use, as it’s one of the built-in charts available. 

  • To begin, enter the data you want to use in your histogram into a worksheet. For example, enter the names of the students in a class in one column and their test scores in another.
  • Select the entire dataset.
  • Go to the Insert tab and select Recommended Charts in the Charts group.
  • Go to the All Charts tab and select Histogram.
  • In Excel 2016, histograms are a built-in chart option.
  • Select the Histogram option, then select OK.
  • Right-click the vertical axis (the numbers beneath the chart) and select Format Axis to open the Format Axis pane and customize the histogram.
  • Select Categories if you want to display text categories.
  • Select Bin Width to customize the size of each bin. For instance, if the lowest grade in your dataset is 50 and you enter 10 in the Bin Width box, the bins will display as 50-60, 60-70, 70-80, and so on.
  • Select Number of Bins to establish a specific number of bins displayed.
  • Choose Overflow Bin or Underflow Bin to group above or below a specific number.
  • Close the Format Axis pane when you have finished customizing the histogram.

How to Create a Histogram in Excel 2013, 2010, or 2007

Excel 2013 or earlier requires the Analysis ToolPak add-in to use the histogram tool. Make sure it’s installed before you create a histogram in Excel.

The Analysis ToolPak add-in is required to use the histogram tool. This add-in is not supported in Excel Online (Microsoft 365). However, you can view a histogram created in a desktop version of Excel using Excel Online. 

To begin, enter the data you want to use in your histogram into a worksheet. For example, enter the names of the students in a class in one column and their test scores in another. 

Select the entire dataset.

Go to the Insert tab and select Recommended Charts in the Charts group. 

Go to the All Charts tab and select Histogram.

Select the Histogram option, then select OK.

Right-click the vertical axis (the numbers beneath the chart) and select Format Axis to open the Format Axis pane and customize the histogram.

Select Categories if you want to display text categories.

Select Bin Width to customize the size of each bin. For instance, if the lowest grade in your dataset is 50 and you enter 10 in the Bin Width box, the bins will display as 50-60, 60-70, 70-80, and so on. 

Select Number of Bins to establish a specific number of bins displayed.

Choose Overflow Bin or Underflow Bin to group above or below a specific number. 

Close the Format Axis pane when you have finished customizing the histogram.

  • Go to the File tab, then select Options.
  • Select Add-ins in the navigation pane.
  • Choose Excel Add-ins in the Manage drop-down, then select Go.
  • Select Analysis ToolPak, then select OK.
  • The Analysis ToolPak should be installed.

Once you’ve added the Analysis ToolPak, you can access it in the Analysis group under the Data tab. 

Go to the File tab, then select Options.

Select Add-ins in the navigation pane. 

Choose Excel Add-ins in the Manage drop-down, then select Go.

Select Analysis ToolPak, then select OK.

The Analysis ToolPak should be installed.

  • Enter the data you want to use in your histogram into a worksheet. For example, enter the names of the students in a class in one column and their test scores in another.
  • Enter the bin numbers you want to use into a third column. For instance, if you want to display test scores by letter grade, you might enter 40, 50, 60, 70, 80, 90, and 100 into the cells of the third column.
  • Go to the Data tab. In the Analysis group, select Data Analysis.
  • Select Histogram in the Data Analysis dialog box, then select OK. The Histogram dialog box will open.
  • Select the Input Range (which would be the test scores in this example) and the Bin Range (which are the cells containing the bin numbers).
  • Choose the Output Range if you want the histogram to appear on the same worksheet. Otherwise, choose New Worksheet or New Workbook.
  • Select the Chart Output checkbox, then select OK. Excel will insert a static histogram on the sheet you selected.

Create a Histogram in Excel 2016 for Mac

You can easily create a histogram in Excel 2016 for Mac after installing the Analysis ToolPak.

Enter the data you want to use in your histogram into a worksheet. For example, enter the names of the students in a class in one column and their test scores in another. 

Enter the bin numbers you want to use into a third column. For instance, if you want to display test scores by letter grade, you might enter 40, 50, 60, 70, 80, 90, and 100 into the cells of the third column.

Go to the Data tab. In the Analysis group, select Data Analysis. 

Select Histogram in the Data Analysis dialog box, then select OK. The Histogram dialog box will open.

Select the Input Range (which would be the test scores in this example) and the Bin Range (which are the cells containing the bin numbers).  

Choose the Output Range if you want the histogram to appear on the same worksheet. Otherwise, choose New Worksheet or New Workbook. 

Select the Chart Output checkbox, then select OK. Excel will insert a static histogram on the sheet you selected.

  • Go to the Tools menu and click Excel Add-ins.
  • Choose Analysis ToolPak in the Add-ins Available box and click OK.
  • Click Yes to install the add-in if prompted.
  • Exit Excel and restart the program. The Data Analysis option will appear on the Data tab.

Once you’ve installed the add-in, you can create a histogram:

The add-in is not available in Excel 2011 for Mac.

Go to the Tools menu and click Excel Add-ins.

Choose Analysis ToolPak in the Add-ins Available box and click OK. 

Click Yes to install the add-in if prompted. 

Exit Excel and restart the program. The Data Analysis option will appear on the Data tab. 

  • Enter the data you want to use in your histogram into a worksheet. For example: enter the names of the students in a class in one column and their test scores in another.
  • Enter the bin numbers you want to use into a third column. For instance, if you want to display test scores by letter grade, you might enter 40, 50, 60, 70, 80, 90, and 100 into the cells of the third column.
  • Go to the Data tab and click Data Analysis.
  • Select Histogram and click OK.
  • Select the Input Range (which would be the test scores in this example) and the Bin Range (which are the cells containing the bin numbers).
  • Choose the Output Range if you want the histogram to appear on the same worksheet. Otherwise, choose New Worksheet or New Workbook.
  • Click the Chart Output check box, then click OK. Excel will insert a static histogram on the sheet you selected.
  • Create a custom histogram; Excel makes it easy.
  • Tricia Goss
  • You’re done!

What is a Histogram?

Histograms look a lot like other bar charts, but they group numbers into ranges based on your determinations. Compared to other types of graphs, histograms make it easier to identify different data as well as categories and frequencies of occurrence.

Enter the data you want to use in your histogram into a worksheet. For example: enter the names of the students in a class in one column and their test scores in another. 

Go to the Data tab and click Data Analysis. 

Select Histogram and click OK.

Click the Chart Output check box, then click OK. Excel will insert a static histogram on the sheet you selected.

You’re done!

Get the Latest Tech News Delivered Every Day