Excel Tutorial: How To Make A Histogram In Excel With Intervals


When it comes to visualizing and analyzing data, histograms are a powerful tool. They provide a graphical representation of the distribution of numerical data and are commonly used in statistics to show the frequency of data within certain ranges. In this tutorial, we will explore how to create a histogram in Excel using intervals, and discuss the importance of using intervals to accurately represent data.

Key Takeaways

  • Histograms are a powerful tool for visualizing and analyzing data distribution
  • Using intervals in a histogram is important for accurately representing data
  • Setting up the data in Excel and sorting it into intervals is crucial for creating a histogram
  • Customizing the histogram with bin width, axis titles, and color scheme enhances its visual appeal
  • Analyzing the histogram helps in interpreting data distribution and identifying patterns or outliers

Setting up the Data

When creating a histogram in Excel with intervals, it is important to properly set up your data in order to accurately represent the distribution of your values.

A. Inputting the data into Excel
  • Start by opening a new Excel spreadsheet and input your data into a single column. This could be any set of values that you want to analyze and represent graphically.
  • Ensure that there are no empty cells or extra spaces in your data to prevent any errors in the creation of the histogram.

B. Sorting the data into intervals
  • Decide on the intervals you want to use for your histogram. These are the ranges into which your data will be grouped.
  • Create a new column in your Excel spreadsheet to represent the intervals. These should be evenly spaced and cover the range of values in your data.
  • Use Excel's functions such as COUNTIF or FREQUENCY to count the number of occurrences of each value within each interval.

Creating the Histogram

When working with data in Excel, creating a histogram can help you visualize the distribution of your data. Here are the steps to create a histogram in Excel with intervals.

A. Accessing the histogram tool in Excel

To access the histogram tool in Excel, go to the Data tab and click on the Data Analysis option. If you don't see this option, you may need to enable it by clicking on File, then Options, and then Add-Ins.

B. Selecting the data range for the histogram

Once you have the Data Analysis tool open, select Histogram and click OK. In the Input Range field, select the data range for which you want to create the histogram. This data should be in a single column.

C. Choosing the interval range for the histogram

Next, in the Bin Range field, enter the cell range that contains the intervals you want to use for the histogram. You can manually enter these intervals in a separate column or use Excel's MIN and MAX functions to calculate them automatically.

Customizing the Histogram

When creating a histogram in Excel, it is important to customize it to suit your specific data and preferences. Here are some ways you can customize your histogram to make it more visually appealing and informative.

A. Adjusting the bin width

One way to customize your histogram is by adjusting the bin width. The bin width determines the size of each interval on the x-axis. To do this, you can right-click on the x-axis, select "Format Axis," and then adjust the bin width in the Axis Options menu. Experiment with different bin widths to find the one that best represents your data distribution.

B. Adding axis titles and labels

Adding axis titles and labels can enhance the clarity and professionalism of your histogram. To do this, select the chart, go to the "Chart Tools" tab, and click on "Add Chart Element." From there, you can add axis titles and labels to ensure that your audience knows exactly what the histogram is representing.

C. Choosing a visually appealing color scheme

Choosing a visually appealing color scheme can make your histogram more engaging and easier to read. To change the color scheme, click on the bars of the histogram, go to the "Fill" option in the Format Data Series menu, and select a color that complements your data. Consider using colors that are easy on the eyes and provide good contrast to make your histogram more visually appealing.

Analyzing the Histogram

Once you have successfully created a histogram in Excel with intervals, it's time to interpret the distribution of the data and identify any outliers or significant patterns.

A. Interpreting the distribution of the data
  • Central Tendency:

    Look at the peak of the histogram to determine the central tendency of the data. Is it skewed to one side or does it have a symmetrical distribution?
  • Spread of Data:

    Examine the width of the bars to understand the spread of the data. Are the bars narrow, indicating a small range of values, or are they broad, suggesting a wide range of values?
  • Shape:

    Assess the shape of the histogram to identify any patterns such as bimodal or uniform distributions.

B. Identifying any outliers or significant patterns
  • Outliers:

    Look for any bars that are significantly higher or lower than the others. These could indicate outliers in the data that require further investigation.
  • Patterns:

    Examine the overall pattern of the bars to identify any significant trends or patterns in the data. Are there any recurring peaks or valleys?

Saving and Sharing the Histogram

Once you have created a histogram in Excel with intervals, you may want to save and share it for presentations or reports. Here's how you can do that:

  • Exporting the histogram as an image or PDF
  • To export the histogram as an image or PDF, simply right-click on the chart and select "Save as Picture" or "Save as PDF". Choose the file format and location where you want to save the histogram. This will allow you to easily insert the histogram into other documents or share it with others.

  • Including the histogram in presentations or reports
  • Once you have saved the histogram as an image or PDF, you can easily include it in your presentations or reports. Simply insert the saved file into your presentation slides or document. This will allow you to effectively communicate your data analysis and findings to your audience.


In conclusion, using intervals in a histogram is crucial for accurately representing the data and understanding the distribution of values. By using intervals, we can effectively group data points and visualize the frequency of occurrences within each interval.

As you continue to practice making histograms with different datasets, you will gain a better understanding of how to interpret and analyze the distribution of data. I encourage you to experiment with different interval sizes and datasets to enhance your skills in creating and interpreting histograms.

Excel Dashboard

ONLY $99

    Immediate Download

    MAC & PC Compatible

    Free Email Support

Related aticles