Creating dynamic histograms in Excel Hi, I'm Dr Nic from Creative Maths and
Statistics Learning Centre and in this video I'm going to show you how to make
dynamic histograms in Excel. To start with I'll show you a completed histogram
so that you can see where we're headed and this is the data we're going to be
using which is taken from the gameshow Deal or No Deal and you can see that
this is a histogram of the winnings from lots of different people. Now down the
left-hand end you can see I've got a figure here less than or equal to $5,000
so you can see that 57 people won less than or equal to 5 thousand dollars. And
up here we've got greater than $70,000 and one person won more than $70,000
and in between you have the different amounts. You can see this
terminology here: You've got a parenthesis on the left hand side and a
square bracket on the right hand side. The way this terminology works is that
the left hand bracket is a less then amount and the right hand bracket is a
less than or equal to amount so you can see that this term here 5,000 comma
10,000 means that the amount is between $5,000 and $10,000 and it includes
scores of 10,000 but it does not include scores of 5,000. If you're going to print
this out to use in a report you might need to reformat what's happening down
below here but as a mathematically correct way of doing it this is great.
Now that you've seen where we're going to, I'll show you how to make this.
So here we have the data on its own. We highlight the column with the data
and we go Insert and we go to charts. In this case it has recommended the
histogram we want but if it doesn't you go to all charts and choose histogram
and you choose the one without the line on it. So here we have a rough idea
of what the histogram is going to look like but you can see there are some
strange things happening down here. That is because Excel has arbitrarily chosen
the bin sizes. The bin size is the width of the interval. You can see that this
column contains all of the values between $9400.50
and $18 800.50
which are sort of weird numbers so we want to fix this and
we do that by right-clicking and selecting format axis. I will move the
dialog box over here so that we can fit more on the screen or make the screen
bigger. Now you can see at the moment it's got automatic and we don't want it
automatic we can say what we want the bin width to be and it chose a bin width
of 9400 so we could try 10 000 and you can see
that as soon as it's entered it changes the width of the bins so that they are
including a wider range of values.
You can also see we've got this problem with the fifty cents down there and it's a
bit annoying so what we can do about that is put an underflow bin and make it
go from say anything under ten thousand so that nicely changes it to
less than or equal to ten thousand and now we've got much more rounded or
sensible looking values along here. Now I don't think that this is really telling
us as much as we might want so I'm going to change my bin width to $3000
but when I do that we get this strange crenelated effect so you've got
these high values and low values and that is because this interval here
13,000 to 16,000 includes 15,000 which is a value that is more likely to be
given out as a prize. We can actually see this quite interesting if we want to
make it 1,000 you've got lots and lots of little numbers little
bars down here
so you can see it's not evenly spread over the thousands and
when you have something like a $3,000 bin width you get strange effects so
we're gonna go to five thousand and make sure the under flow bin fits
with the five thousand as well and now we've got even bins of five thousand so
we do have this strange one out to the right so what we'll do is we'll make an
overflow bin so I will change the overflow bin to $70,000 and so
now you can see you've got one over here with greater than seventy thousand
dollars and all of the others are just equally spaced $5,000 intervals.
We can look back at the spreadsheet we're trying to get to and you can see I've
got a heading: "Deal or No Deal winnings". I've got an x-axis label of amount won, a
a y-axis label of number winning this amount. These are all done by
clicking on the graph going to design and going to add chart element and
adding primary horizontal axis primary vertical axis or adding chart title the
Now the thing the thing about a histogram being dynamic is that we can change some
values and the histogram itself will change so say all of these values
suddenly became 20,000 you can see that this bin here has grown a lot and this
one's gone down because this reflects the data so I'll just take it back again
and you can see that so when we change the data the graph changed. When we
change the data back the graph changed back. And that's the beauty of this.
The old kind of histogram that you used to make in Excel was not dynamic so each
time the data changed you would have to change the histogram itself.
So that's about it for now. You might want to know some more and if you do put a question
in the comments below and I'll make a video to explain anything that wasn't
clear here. So remember to click on the like, subscribe to this channel and click
on the bell to be notified of any new videos that will help you with your statistics.
So happy times with Excel and statistics!