How To Make A Histogram In Excel- Best Ways In 2021

How To Make A Histogram In Excel

How to Make a Histogram in Microsoft Excel. Actually, making a histogram in Excel is very easy for you to do. Here we will share some steps to create a histogram in Excel easily and quickly.

In this tutorial, you can do it in Excel 2016, Excel 2013, or the latest version of Excel. The method is almost the same and with a slight difference. Without further ado, here’s how to make a histogram in Excel easily and quickly:

What is a Histogram?

Histograms are general charts used for data analysis in work or the study of people. This is a graphical view where the data will be grouped into ranges and plotted as bars. The height of each bar indicates the volume of data in each range. The histogram looks familiar to a column chart, but you can see the difference in the image below.

How to Create a Histogram in Excel

If you are using Excel 2016, there is a built-in histogram chart type and it will be very easy and convenient for users to create a histogram in Excel.

Step 1: Input Data

The first thing you have to do is enter data into a worksheet. You can type data manually or import data from outside sources.

Step 2: Create Your Histogram

Select the data set, go to the Insert tab and click the Insert  Statistical Chart option in the Charts group.

Then click the first Histogram icon, the histogram will be inserted based on your data set and shown on the worksheet.

histogram option in Excel

Step 3: Adjust Your Histogram

To customize the histogram, there are 4 function areas in Excel 2016 where you can change the style, layout, and color of the histogram, add chart elements, modify axis options, or even change the width of the gap between the bars.

On the  Design and Format tab or the floating menu next to the histogram, you can modify the style, layout, and color options to change the appearance of the chart.

When you right-click on any part of the histogram, the contextual menus will differ from each other. However, most of the options on the menu are also displayed on the Design tabFormat tab, or floating menu.

For the Format proper pane, the options on the panel will also change immediately according to your selected area on the histogram. When you select a histogram bar, the Format Precise pane will provide options to change the width of the gap between the bars.

Step 4: Change the Bins Histogram

The word “Bins” represents a bar on the histogram. If you want to add or reduce the number of binary histograms, you can select the horizontal axis and the axis option will be displayed in an appropriate panel format.

axis option in histogram
  1. By Category: This option is used when your horizontal categories are in text format. For example, if you have sales data for Smartphones, Computers, and Tablets, and you want to know the sales volume of each item, this option will be of great help.
  2. Automatic: This option will automatically decide the number of binaries in the histogram.
  3. Bin Width: This option allows you to specify how large each binary should be.
  4. The number of bins: This option lets you specify how many bins you want in the histogram.
  5. Overflow bin: When you want to see the sum of all values, which are above a certain value in the histogram, you can check this option and enter a specific number.
overflow bin option in histogram
  1. Binary underflow: When you want to see the sum of all values, which are below a certain value in the histogram, you can check this option and enter a number.

Step 5: Move Your Histogram

To move your histogram to another place, you must first select the histogram, click the Move Chart button on the Design tab or the Chart Area contextual menu.

Then, you’ll see a Move Chart window asking you to choose where to place your histogram.

If you select New Sheet, the histogram will be moved to a new sheet called   Chart1  and the histogram will be in the center of the sheet; if you select Object in, you can choose to move the histogram to another worksheet.

How to Make a Histogram in Excel with the Data Analysis ToolPak

Creating histograms with the Data Analysis ToolPak works for all versions of Excel (including Excel 2016). However, if you are using Excel 2016, I would recommend you use the built-in histogram chart as a section below.

Step 1: Install Data Analysis ToolPak

Go to the File tab, select Options; in the pop-up Excel options window, select Add-ins.

add-ins options in Excel

Then in the Manage drop-down menu select Excel Add-ins and click Open.

Next, in the Add-Ins window, select Analysis ToolPak and click OK.

Analysis ToolPak

The Analysis Toolpak add-in will be inserted in your Excel and you can access it in the Analysis group of the Data tab.

data tab in excel

Step 2: Input Data & Add Binary

This step is the same as the steps in the first section. So you can type or import data.

After entering the data, you will also need to create data intervals to determine the binary ranges of the histogram. Binary is a number indicating the interval in which you want to group the data. The intervals should be continuous, not overlapping, and usually of the same size.

Now you have to define binary in an additional column next to the dataset.

Step 3: Create Your Histogram

Go to the Data tab, click Data Analysis in the Analysis group. Then in the Data Analysis dialog box, select   Histogram from the list, click OK.

In the Histogram dialog box, you need to select Input Range and Bin RangeYou can leave the Labels check box unchecked if you don’t include labels in the data selection.

Also, you can choose where to place the histogram in the Output options section Then, don’t forget to select Chart Output.

After that, click OK, the histogram will be inserted on the worksheet with the frequency distribution table.

Once you have created a histogram with the Data Analysis ToolPak, you cannot use Ctrl + Z to restore it. You must delete tables and charts manually.

How to Make a Histogram in Excel with the FREQUENCY Function

Apart from method 2 above, you can also create a histogram by using the FREQUENCY function.  And the histogram will be dynamic, which means when you change the data, the histogram will be updated accordingly.

Step 1: Input Data & Add Binary

Similarly, you need to enter data into a worksheet and then create data intervals.

Step 2: Enter the Formula

Before you enter the frequency formula, you need to add a column named Frequency next to the Binary column.

Frequency column

The FREQUENCY formula  has the following syntax:

FREQUENCY(data_array, bins_array)

In this example, the data array is B2: B17, the bin array is D2: D6, so we get the frequency formula:

=FREQUENCY(B2:B17, D2:D6)

Since the FREQUENCY function is actually an array formula, you need to press Ctrl + Shift + Enter instead of just clicking Enter.

Following are the specific steps for getting the frequency result from the dataset:

  • Select the cell under the Frequency column, which is E2:E6 in this example.
  • Press F2 to enter edit mode for cell E2.
  • Enter the FREQUENCY formula.
  • Press Ctrl + Shift + Enter to make sure the formula will be entered in the cell (E2:E6) with curly square brackets.

Step 3: Create Your Histogram

If you are not using Excel 2016 or the premium version of Excel, you cannot create histograms directly with the built-in templates. But when you get frequency numbers from your dataset using the FREQUENCY function, you will be able to build a histogram with a simple column chart.

Note: Because the FREQUENCY function in Excel is an array function, you cannot edit, move, add, or delete individual cells that are included in a formula. When you need to change the number of binary cells or a data set, you must first delete the existing formula, then add or remove cells, select a new range of cells, and re-enter the formula.

Conclusion

How easy is it not to make a histogram in excel? If you still have questions, you can ask in the comments column that we have provided below.

maybe that’s all GC can convey, hopefully, this article is helpful and useful for you. Don’t forget to comment and share, thank you!!

Leave a Comment

close