Locked lesson.

Upgrade

Quick reference

Create a Histogram Chart

Create a Histogram chart to group data into buckets to show its distribution. 

When to use

Histogram charts can be used whenever we have distributed data. We can group our data into automatic or custom buckets to show how many values fall within a range. 

Instructions

Histogram charts are useful for grouping data. We can get Excel to create bins or buckets for us or we can create our own custom buckets. 

In this example, we have a list of employees and their ages. The HR team has asked us to analyze the company demographics so we can make sure we are hiring enough people from each age bracket. We can visualize this analysis using a Histogram chart. 

NOTE: The Histogram chart is another chart that cannot be created directly from PivotTable data. 

Create a Simple Histogram Chart

  • Click anywhere in the data. 
  • From the Insert tab, in the Charts group, click the small diagonal arrow in the corner of the group. 
  • Click on the All Charts tab. 
  • Select Histogram from the list. 
  • Click OK. 

Excel will create default bins to group the data. In this example, we have bins that span a 13-year period. 

Customize Bins

We can customize the bins that display in the Histogram chart. 

  • Click on the data labels in the horizontal axis. 
  • Press CTRL+1 to open the Format Axis pane. 
  • Notice the bin width is set to automatic by default. 

We can modify this to display a specific number of years in each bin. 

  • Click Bin width. 
  • Change the value to 5. 

We now get a much more detailed analysis. 

We can also specify the exact number of bins that we want and Excel will work out the rest. 

  • Click on the data labels in the horizontal axis. 
  • Press CTRL+1 to open the Format Axis pane. 
  • Select Number of bins. 
  • Change the value to 15. 

Create Custom Bins

We can create our custom bins. 

For example, maybe I want to show the same data but display the generational labels instead of age buckets. 

We first need to define our labels and values. In the table below, we have the age range label, the bin/bucket, and the lower threshold. The lower threshold is just the lowest value from each bucket. 

 

We can now use the XLOOKUP formula to assign the correct label to each employee based on their age. It is important when doing this that the label column is before the age column. 

  • Select column C. 
  • Press CTRL+SHIFT++ (CTRL++ on some keyboards) to insert a blank column. 

The XLOOKUP formula looks up the age in cell D7, in the Lower Threshold range (J7:J10), and returns the age range (H7:H10). The -1 is the search mode. This means that if Excel cannot find an exact match of the age in the lower threshold column, it will return the next smallest. 

  • Press Enter and copy the formula down. 

  • Select the Age Range and the Age columns only. 
  • From the Insert tab, in the Charts group, click the small diagonal arrow in the corner of the group. 
  • Click on the All Charts tab. 
  • Select Histogram from the list. 
  • Click OK. 
  • Click on the Data Labels on the horizontal axis. 
  • Press CTRL+1 to open the Format Axis pane. 
  • Under Axis Options, select By Category. 

Login to download
  • 00:04 Histogram charts allow us to show the distribution of data.
  • 00:08 For example, if we take a look at the data that I have in this spreadsheet, you
  • 00:14 can see that I have a list of employees and I have their ages in column C.
  • 00:19 So maybe I'm doing some kind of analysis on the demographics of all
  • 00:24 employees across the company.
  • 00:26 And I want to see grouped into different buckets, the distribution of ages.
  • 00:31 Because this is going to help me to determine if we're not hiring enough
  • 00:35 people from a certain age group.
  • 00:37 So we can do this very effectively using a histogram chart.
  • 00:41 And a histogram chart is another one of those charts that you can create
  • 00:46 using pivot table data.
  • 00:48 So just remember that little trick that I showed you in a previous lesson.
  • 00:51 If you do have your data in a pivot chart as opposed to just typed into
  • 00:56 the spreadsheet, like I do here.
  • 00:58 Now I'm going to click anywhere in my data, let's jump up to Insert and
  • 01:03 I'm going to click on the little diagonal arrow just to open up
  • 01:07 the Insert Chart dialog box.
  • 01:09 And we're going to jump straight across to the All Charts tab.
  • 01:12 Now in here we have an option for Histogram, and
  • 01:16 you kind of get a preview as to what this is going to look like.
  • 01:20 So let's click on OK and it inserts that Histogram chart.
  • 01:24 Now, one thing to notice here is that Excel has automatically
  • 01:29 defined the buckets for me.
  • 01:31 So you can see at the bottom I have one bucket that's showing the amount
  • 01:35 of people that we have working at the company between the ages of 18 and 31.
  • 01:40 We then have 31 to 44, 44 to 57, and finally 57 to 70.
  • 01:46 Now, it might be that I want to distribute these in a slightly different way,
  • 01:52 and we can do that simply by formatting the horizontal axes.
  • 01:56 So if we click on the axes right click and
  • 01:59 choose Format Axes we get our little format pane over on the right hand side.
  • 02:05 Now let's drag this down a little bit, remember, you can dock this over to
  • 02:09 the right-hand side or you can drag it out if you prefer it to be floating.
  • 02:13 And we want to make sure that we clicked on axis options at the top and
  • 02:17 we've expanded this axis options section just here.
  • 02:21 Because this is where we can customize the look and feel of our bins or our buckets.
  • 02:26 Now notice here I could choose to display these by category which is
  • 02:30 going to display all of the employee names.
  • 02:32 Now that really kind of takes away from the effect that I'm going for here.
  • 02:37 If we choose automatic, we're going to get those automatic buckets that
  • 02:41 have been created by Excel, but the next two options allow us to customize these.
  • 02:46 So if I choose Bin Width,
  • 02:48 this is where I can determine how many years I want in each of those buckets.
  • 02:53 So currently it's showing 13 years, 18 to 31 is the first bucket.
  • 02:59 So maybe I want these grouped into 10 year buckets.
  • 03:03 So I can change like so, and you can see the chart updates.
  • 03:07 If I wanted to group this in a different way I could maybe choose
  • 03:11 five year buckets.
  • 03:12 And then I get a lot more information display so I can drag this out and
  • 03:16 we get a more refined result.
  • 03:18 I could even specify the exact number of bins that I want.
  • 03:22 So I could click in here and say that I actually want, let's say we want 20 bins.
  • 03:27 I can select that, click away and it's going to give me those 20 bins,
  • 03:32 so we can really customize how we want to display our histogram chart.
  • 03:37 Now I don't particularly like the way this is currently displaying so
  • 03:41 let's just put that back again.
  • 03:43 I'm going to choose Bin Width and let's go for 10 years,
  • 03:46 I think that looks a little bit better.
  • 03:49 And of course we can then go in and
  • 03:51 apply a regular chart formatting to make this look as we want it to look.
  • 03:56 So that is the first way that you can use a histogram chart to
  • 04:00 show the distribution of Ages across a company.
  • 04:04 Now let's take a look at a slightly different method.
  • 04:06 This time,
  • 04:07 we want to show which generation each of our employees belong to.
  • 04:13 Now we can use a histogram chart to do this, but
  • 04:16 we need to do a little bit of prep work first of all.
  • 04:18 So I have the same data over here on the left hand side, but
  • 04:22 notice that I've also added another little table of
  • 04:26 data just here that really defines those generations and the buckets.
  • 04:31 So I can see generation Z is people 11 to 26 years old,
  • 04:36 millennial 27 to 41, Gen X 42 to 56, and Baby Boomer 57 to 75.
  • 04:44 Now what we also have to do here to get this to work is provide a lower threshold.
  • 04:49 And that is really just the lowest number from your bucket range,
  • 04:54 so I have 11, 27, 42 and 57.
  • 04:57 So now that we have this little table we can use Xlook Up to
  • 05:01 assign a generation based on the employee's age.
  • 05:06 Now I'm going to insert a column in here Ctrl+Shift+ and
  • 05:09 we're just going to put this in here.
  • 05:11 So let's say generation, so now we're going to do Xlookup,
  • 05:16 our lookup value is going to be the age.
  • 05:19 Where are we looking it up?
  • 05:20 Well, I want to look it up in this lower threshold just here.
  • 05:25 Now I'm going to copy this formula down, so
  • 05:27 I want to press F4 to lock my cell range.
  • 05:29 What's our return array?
  • 05:31 Well, we want to return the generation.
  • 05:33 So my return array is this array just here.
  • 05:36 Once again, F4 to lock.
  • 05:39 Now the important thing here is that we add in the match mode.
  • 05:43 So I'm going to press comma and comma again just to jump over the if not
  • 05:48 found argument to get to the match mode argument.
  • 05:51 Because what we want to do here is we want to add in minus one so
  • 05:55 that we're doing an exact match or next smaller item.
  • 05:59 So what that basically means is that if the age in the cell is, let's say, 35,
  • 06:04 it's going to look in that lower threshold column,
  • 06:07 and if it can match 35, that's great, it will return the result.
  • 06:11 If it can't find exactly 35, it will go to the next lower number.
  • 06:16 So let's close the bracket, hit Enter, looking good so far.
  • 06:20 Let's double click to copy this down, and
  • 06:23 we have a generation assigned to each of our employees.
  • 06:28 And I think I've actually spelt millennial wrong here, I need another n in there.
  • 06:33 Let's just quickly update that while we're here.
  • 06:35 So now that I have this information I can use this in a histogram chart as well.
  • 06:40 So what we going to do is we going to select just the data we want to include.
  • 06:44 So we're going to select generation and
  • 06:47 age Ctrl+Shift Down Arrow to select both of those columns.
  • 06:51 Let's jump up to insert, let's open up our charts and
  • 06:55 we're going to choose histogram.
  • 06:58 Click on OK, so now I have my histogram chart,
  • 07:01 we're going to format axes options again.
  • 07:05 So let's jump into here and expand Axes Options.
  • 07:08 And if I choose By Category,
  • 07:10 that is going to show the generations as opposed to the buckets.
  • 07:16 So that is another way that you can display distributed
  • 07:22 data in buckets using formulas and a histogram chart.

Lesson notes are only available for subscribers.

Gift this course