• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Histogram Charts and FREQUENCY Function

You are here: Home / Excel Charts / Excel Histogram Charts and FREQUENCY Function
Excel Histogram Charts and FREQUENCY Function
July 20, 2011 by Mynda Treacy

A Histogram, also known as a frequency distribution, is a chart that illustrates the distribution of values that fall into groups.

Since my 5 year old is big into his football (soccer) we’ll take goals scored as an example…even though in 5 year old's football matches you’re not supposed to count the goals scored!

Below is the end result. I've added a cumulative percentage to this chart to aid with further interpretation of the data, but the histogram itself is the column chart.

From the histogram we can see that we've scored 2-3 goals in 11 matches. And from the cumulative percentage we can see that in 80% of matches between zero and three goals are scored.

Histogram Excel

We could also use this type of chart to plot:

  • Distribution of student grades
  • Performance of salespeople e.g. groups might be units sold
  • Distribution of salaries across headcount
  • Plus various scores, ratings and other data you want to see the frequency distribution of.

How to create a Histogram Chart

The first thing we need to do is compile our data into a table that can feed our chart.
Excel FrequencyOn the left we’ve got a list of the goals for the last 25 matches. The first thing we need to do is specify our groups, or bins as they are often referred to.

Since our data range is only small (between zero and 6 goals) our bins will be small: 0 – 1, 2 – 3, 4 – 5 and 6 – 7. In fact we could just as easily have a bin for each number from 0 to 6, but I want to show you how to use the FREQUENCY function so we’ll have small groups.

Next we need to set up a table that will feed our histogram chart like this:

Excel Frequency Distribution

Frequency Table Explained

Group Column

These are simply the groups that will appear on the horizontal axis of the chart.

Bin Column

The bins are the upper limits of the groups and are used by the FREQUENCY formula. You can see that the bin value for the group '2-3' is 3.

Frequency Column

The Frequency Column contains the FREQUENCY formula.  The FREQUENCY function is actually an array formula which means it needs to be entered using CTRL+SHIFT+ENTER.

The syntax for the FREQUENCY function is:

=FREQUENCY(data_array, bins_array)

and our formula is:

{=FREQUENCY(K5:K29,N5:N8)}

Note: the curly brackets are entered by Excel when you press CTRL+SHIFT+ENTER to enter the formula.

Tip: select all the cells that will contain your FREQUENCY formula (in our case O5:O8), then enter the formula, then press CTRL+SHIFT+ENTER and your formula will automatically be entered in all the required cells with the curly brackets.

Note: since this range containing the FREQUENCY formula (O5:O8) is an array you can only edit the whole array. Excel will give you an error if you try to modify a part of the array on its own.

Cum % Column

This is simply a cumulative percentage of the frequency. To calculate this, the formula in the first cell is:

=SUM($O$5:O5)/SUM($O$5:$O$8)

Tip: The first cell reference in the above formula, $O$5, is what is called an absolute reference and the second, O5, is a relative reference. This is so that when you copy the formula down the remaining cells it will automatically update to include the next cell in the formula, and thus calculates a cumulative percentage.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Click here to download the file and have a go yourself. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Now that we have our frequency table ready we can plot our Histogram chart.

How to create a Histogram Chart

  1. Highlight the frequency data table (cells M4:P8)
  2. From the Insert tab of the ribbon select Column > 2-D column. You should end up with something like this.

Histogram in Excel

  1. Remove the Bin data from the chart. Select the blue bin data columns (just click on one of the columns) and press the delete key to delete them.
  2. Change the Cum % to a line chart. Select the Cum % column, right click and select Change Series Chart Type.
  3. Select a line chart.
  4. Move the Cum % to a Secondary Axis. Select the Cum % line on the chart, right click and select Format Data Series.
  5. In the Series Options tab select Secondary Axis. It should look like this now:
  6. Histogram Excel 2007

  7. Remove the gap between the bars. Select the Frequency bars on the chart and right click and select Format Data Series.
  8. In the Series Options tab reduce the Gap Width to 0%.
  9. Add labels and format. Select the chart and from the Layout Tab on the ribbon add a chart title and axis labels. Click inside the labels to add your text.
  10. Reposition your legend to the top right of the chart. Click on the legend and drag the outer edge of the box to the top. Resize the box using the pull handles so that the data is side by side rather than stacked. This will make it fit better at the top of your chart.
  11. Remove gridlines. Click on the horizontal gridlines and press the delete key to remove them.
  12. Resize chart. Click inside the chart area so that the pull handles are visible (see squares and circles on the outline of the chart in the image below) and resize it by grabbing the mid pull handle on the right hand side, and dragging it to the right to resize it and fill the gap left from the legend.

Histogram Excel

  1. Add data labels to line chart. Select the line in your chart and from the Layout tab on the ribbon select Data Labels > Above.
  2. Change bar colour. Select the Frequency bars and from the Format tab on the ribbon and select a new colour from the Chart Styles section.

Your chart should now look like this:

Excel Histogram

While this may seem like a lot of steps, once you get to grips with formatting charts you can do all this in about a minute.

Our premium training has comprehensive video tutorials on Excel Charts. Click here to sign up for our Excel Training.

Excel Histogram Charts and FREQUENCY Function

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel Charts
Previous Post:Excel SUBSTITUTE Function TrickExcel SUBSTITUTE Function Trick
Next Post:Save Time with Hyperlinks for Word, Excel and OutlookSave Time with Hyperlinks for Word, Excel and Outlook

Reader Interactions

Comments

  1. Moses

    March 12, 2019 at 10:22 am

    Whoa,,,! Thanks so much for the vital information on how to contract frequency table and histogram charts.
    I actually tried one of the samples and it really helped me with my project.

    kind regard.

    Moses Mawi

    Reply
    • Mynda Treacy

      March 12, 2019 at 11:29 am

      Glad you found it helpful, Moses 🙂

      Reply
  2. Roman Khan

    November 21, 2018 at 7:01 pm

    How we can draw a histogram having a frequency of zero?

    Reply
    • Mynda Treacy

      November 21, 2018 at 8:53 pm

      Hi Roman,

      Just make the first bin size zero.

      Mynda

      Reply
  3. Uche Uche

    June 19, 2017 at 9:12 pm

    Straight to the point explanation and exploration guide.
    Thanks a lot.

    Reply
    • Catalin Bombea

      June 20, 2017 at 3:19 pm

      You’re welcome Uche, thank you for your feedback 🙂

      Reply
  4. Faisal Alghati

    October 5, 2013 at 11:06 am

    Thank you so much!!
    You just saved my life.

    Reply
    • Mynda Treacy

      October 5, 2013 at 5:13 pm

      🙂 you’re welcome, Faisal. All in a day’s work.

      Reply
  5. Jonathan

    April 6, 2013 at 11:18 pm

    THERE’s an EASIER way. It uses PivotTables (far more flexibility) that have a drag and drop interface. The trick is to put the field with the numbers (like grades) in both the row labels and the values labels. Then you can group (aka bin) the data by any number you want. Also works well with dates. If you want custom groupings, you can manually do that too.

    Reply
    • Carlo Estopia

      April 6, 2013 at 11:30 pm

      Hi Jonathan,

      Nice Info right there.

      Thanks for sharing.

      Cheers,

      CarloE

      Reply
  6. Natalie

    January 15, 2013 at 4:30 am

    Is there anyway to move the cumulative frequency line to the right of the graph?

    Reply
    • MikeBanawa

      January 15, 2013 at 11:39 pm

      Hello Natalie,

      Nope, Excel won’t allow it. Also, moving the line graph to the right would defeat the purpose of presenting the cumulative frequency in the graph. This kind of presentation is actually called “Pareto Chart” used for quality control in companies and businesses around the world. You might want to create two separate charts though if you want the two chart types to be beside each other.

      Thanks!
      Mike

      Reply
  7. Raghu

    September 30, 2012 at 6:47 pm

    Hi, WOW!! – was my expression- once i actually could complete it myself with a different example. Additionally, in this tutorial i learnt how to use frequency function in arrays.
    As usual – Simply Awesome!!!

    Reply
    • Mynda Treacy

      October 1, 2012 at 10:22 am

      Cheers, Raghu 🙂

      Reply
  8. Peter

    September 6, 2012 at 6:34 am

    Thanks for the tip. I’ve been trying to do this using Excel for Mac (Office 2011). One thing I’ve noticed is that once I enter the equation:

    {=FREQUENCY(F12:F10011,Z11:Z22)} and then press control-shift-enter, it seems to work for the first cell, but the subsequent cells increment row numbers (e.g., {=FREQUENCY(F13:F10012,Z12:Z23)}).

    This gives me different results and does not seem consistent with what your worksheet shows (where the rows do NOT increment). Have I done something differently?

    Reply
    • Mynda Treacy

      September 6, 2012 at 7:17 am

      Hi Peter,

      You need to select all the cells that will contain your FREQUENCY formula, then enter the formula, then press CTRL+SHIFT+ENTER and your formula will automatically be entered in all the required cells with the curly brackets.

      You don’t actually copy it down. Is that what you are doing?

      Kind regards,

      Mynd.

      Reply
  9. Tom

    May 18, 2012 at 12:27 am

    Can you do the same with time stamps like 16/05/2012 00:00:00 or it it just limited to number ranges ..?

    Reply
    • Mynda Treacy

      May 18, 2012 at 8:56 am

      Hi Tom,

      Yes, just change the bins to use a time/date format and set your bin sizes to suit your data. As long as your bins are not text values it will work.

      Kind regards,

      Mynda.

      Reply
  10. rashed

    May 5, 2012 at 8:29 pm

    very useful

    Reply
  11. rashed

    May 5, 2012 at 8:27 pm

    Very nice initiative

    Reply
  12. lynn

    April 5, 2012 at 10:38 pm

    I’ve looked for lots of resources on making a histogram in Excel and they are all pretty similar to this one. This is helpful if you are manually entering in all your data but what happens when you have, say… 40,000 data points that you’d like to make into a histogram?

    Reply
    • Mynda Treacy

      April 6, 2012 at 5:03 am

      Hi Lynn,

      It doesn’t matter how many data points you have (as long as you don’t exceed the number of rows/columns available in Excel) since you use the FREQUENCY function to group your data into bins. You would never have 40,000 bins, you’d group those data points into bins just as I have done in the example.

      I hope that makes sense, and your 40,000 data points are no longer insurmountable.

      Kind regards,

      Mynda.

      Reply
  13. Vikas

    February 2, 2012 at 10:13 pm

    Thank you for the post.

    Reply
    • Mynda Treacy

      February 3, 2012 at 11:57 am

      My pleasure, Vikas. Glad you liked it.

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.