Start learning today, and be successful in your academic & professional career. Start Today!

• ## Related Books

 0 answersPost by Roland Amofa on June 25, 2016Can we have the lecture slides in a pdf format? 0 answersPost by Ray Gaytan on October 30, 2015Thank you, very much! Very helpful 0 answersPost by steven bain on December 5, 2013The exercise files seem to wrong or missing altogether. 0 answersPost by Kathleen Donovan on April 11, 2010Professor Yates, you're great! I like all your videos on Statistics and they are extremely helpful! I would definitely recommend them. Previously I used Youtube videos but these are definitely worth paying for --though I'm all for free education:)Kath

### Dotplots and Histograms in Excel

Lecture Slides are screen-captured images of important points in the lecture. Students can download and print out these lecture slide images to do practice problems as well as take notes while watching the lecture.

• Intro 0:00
• Previously 1:02
• Data, Frequency Table, and visualization
• Dotplots 1:22
• Dotplots Excel Example
• Dotplots: Pros and Cons 7:22
• Pros and Cons of Dotplots
• Dotplots Excel Example Cont.
• Histograms 12:47
• Histograms Overview
• Example of Histograms
• Histograms: Pros and Cons 31:39
• Pros
• Cons
• Frequency vs. Relative Frequency 32:53
• Frequency
• Relative Frequency
• Example 1: Dotplots vs. Histograms 34:36
• Example 2: Age of Pennies Dotplot 36:21
• Example 3: Histogram of Mammal Speeds 38:27
• Example 4: Histogram of Life Expectancy 40:30

### Transcription: Dotplots and Histograms in Excel

Hi and welcome back to www.educator.com.0000

We are going to be talking about dot plots, and histograms in Excel today.0003

First we are going to talk about going from data to dot plots.0009

Remember before we always have to go from data to frequency tables and then to some visualization.0013

Dot plots are nice because they can let you go straight from data directly to the visualization.0019

We are going to talk about going from data to histograms.0024

Histograms are going to be really helpful to us especially because a lot of times we are going to have variables that have many values.0028

We are going to talk about how do we have grouped, ungrouped values, which we have looked up before, and grouped values.0039

Finally we are just going to talk a little bit about plotting frequencies versus relative frequency.0046

Relative frequency is just a fancy way of saying it is frequency but divided by how many cases you have.0052

It is really like percentage.0060

Previously we always have to go from data and we had to stop over at making a frequency table and then go to the visualization.0065

But now with dot plots we cut out the middleman and we can go directly from data to the visualization.0073

That is a really handy thing there.0079

If you look in your Excel file the data is going to be the same as the data we have been working with, the 100 www.facebook.com friends.0085

Here is what we are going to do, before we had created a nice little graph0094

using the Excel tools to create visualization but now we are going to use dot plots.0100

Excel will create dot plots for you directly.0108

Instead we have to sort of fidget, the fudge actually comes in handy sometimes.0111

Let us go birth month.0119

We already know that birth month should have a uniform distribution.0121

We already looked at this data before.0125

What we are going to do is just look at how to transform it directly0127

from data into a visualization without having to use the Excel graphs or chart.0131

If you go to your birth month sheet here I have just put up the months, 1 through 12.0140

It just looks sort of like a frequency table but if you watch carefully we are going to transform it.0148

Let us go ahead and put in our regular formula for how to find frequency.0156

That is equal sign (=) because we are starting off with a function.0163

Count if because we wanted to count if that person was born in the month of January.0167

Let us put in our data.0177

If we click on data and we scroll down to months, here is birth month.0180

I'm going to select all of these rows.0188

So far it seems like we are just making a frequency table.0192

I’m going to put in a comma because I know I’m going to need that.0197

Let us go back to birth month.0201

I want you to count it if the birth month is January.0203

If we just hit enter here, that would mean we are just counting how many people are born in January.0210

We are going to do something a little bit different.0217

I want Excel to visualize for me how many people there are.0219

Not give me a number but actually show me a pictures.0224

Here is what we are going to use.0229

We are going to use the repetition function and so that is rept, you do not have to put it in capital.0230

I just wanted to distinguish it from the count if and put a parentheses because that is how you are going to put in the inputs.0238

Here Excel reminds us that we need text, whatever text you want to repeat over and over again and the number of times.0247

The text, you can pick your favorite text.0256

You just have to make sure that it is in quotation marks.0259

I'm going to put in an at symbol, that is my favorite one.0263

I’m going to close parentheses and put in a comma.0271

The beautiful thing about count if is that it is going to return to me a number.0274

The output is going to be a number.0279

If I just leave this here it will just output to me 7.0282

This function will actually read repeat this at symbol 7 times.0287

At the end of this I’m going to put a close parentheses.0296

That my parentheses match up.0302

And then I’m going to hit enter.0305

Great.0308

Let me just make these rows a little bit larger so you can see everything.0311

Here instead of having the number 7, I have 7 little symbols.0317

And you do not have to use the at sign (@) if you do not want to.0323

You could use a star, an asterisk.0327

You could use anything you want.0332

You could use an o, anything to help you see there is this many people born in the month of January.0333

This is a direct way of going into the visualization.0347

We can actually copy and paste this just like we did before.0351

All we have to do is make sure that our data is locked with our dollar signs.0357

I’m going to hit enter and all I’m going to do is take that, drag it all the way down and we have a visualization right there.0367

We do not have to make Excel do any extra work with the charts or anything like that.0377

That is the handy thing about a dot plot.0382

We are making dot plot in Excel but a lot of times you may be asked to make dot plots on paper and pen.0385

No actual statistician does that anymore but in a statistics class you maybe asked to do that.0392

The nice thing about dot plots is that you do not have to put the data in order or anything.0399

You could just go and just sort of go down the line and put a dot where 5 is,0404

put a dot where 3 is, put a dot where 6 is, put a dot where 9 is.0412

You can see that it is a really easy way to visualize the distribution and you do not have to do anything to your data before hand.0416

But once again doing it by hand pretty tedious and people do not really do it that way.0426

At least not when they are doing real statistics.0431

Alright, that is dot plots but let us talk about the pros and cons.0435

Dots plots, the pros and cons.0445

The pro is that it is nice and quick.0447

It is quick and dirty, right.0451

You could go directly from data to dot plots, no middleman, no frequency tables.0453

You do not have to know anything even about statistics and you could do it.0458

You can only do this with small data sets.0461

It is not useful with giant data sets.0465

Even your data set of a 100 cases, it is considered a relatively small data set.0467

Imagine if you have like a thousand of cases, I mean just to even see your dots go all the way to the side.0473

I mean it is going to be pretty crazy, right?0481

That is sort of a pro and a con, when you have a small data set this is a really nice thing to do.0485

Con is it cannot handle a lot of data points.0494

Another pro is that it shows the actual individual values so0499

you will know exactly what each dots stands for and it does in group cases together.0503

Everything sort of spread out for you and it shows you the shape of the distribution.0509

With our example we saw once again that it is a uniform distribution and you could see that right away.0514

The cons is sort of the opposite but it is really the same features as the pros but considering it from a different goal perspective.0521

One of the pros is that you can handle small data sets really well but the con is that it cannot handle a lot of data points.0533

The other con is that because it shows these individual values, it cannot group the cases together.0540

I forgot to show you one thing.0549

If you go back to the birth month sheet, one thing is this is a vertical0551

or horizontal but here each row represents one of the values of this variable.0563

Now sometimes you may be asked to create a dot plot where the dots are going up and down.0571

I will call that vertical version.0580

It is really easy to make that once you have created this.0582

Here is how to do it and this is a nice trick.0584

It is just something nice to know with Excel as well.0590

If you copy this, I'm using command C here and I'm just going to paste it over here on this side.0595

I guess I will paste it here for now.0605

We are going to have to change this, I will paste this down here so that you could see it is going up.0609

Here is what I’m going to do, I’m going to paste it special.0616

If you go to add it, you should be able to see something that says paste special.0621

Not a lot of people use this but if you click on paste special something like this will come out.0633

What I want Excel to do is transpose it, what is used to be columns, I want it to put it in rows.0639

I’m going to hit transpose.0645

It is just to paste it but it is going transpose paste it.0649

I will hit okay.0653

Here what we find is that it has put all of my rows into columns and columns into rows.0656

What I’m going to do is take these months here and0664

I’m going to cut them command x and I’m going to paste them below my frequency command v.0672

I’m going to select these dots from my dot plot and0681

then I’m going to use my formatting palette to tell it to face it in a vertical matter rather than horizontal.0684

Let us look at what this looks like.0698

I’m going to take this row and make it a little bigger so you could see.0701

I’m going to take all of these and shrink my column a little bit so you could see it all at the same time.0713

Hopefully you are able to follow along with that,0722

You will see that what I have done is I have taken my previously horizontal dots and I have made my dots vertical.0725

It is just formatting.0734

It is nothing special.0735

Honestly you could also do this from the very beginning if you know that is the way you need to do your dot plot.0737

Basically the information is the same though, that you could see that it is a roughly uniformed distribution.0743

There is not one month that is particularly popular.0751

They are just seem to be repeating pattern.0754

That is transposing dot plots.0758

Let us go on.0766

Let us go on to histograms.0769

Histograms will probably be the most frequent frequency distribution you use in your statistics course and in statistics real life.0771

Histograms are pretty easy to understand.0780

All you have to do is imagine drawing a rectangle around the dots0783

so that you do not see individual dots anymore but you will see rectangles.0787

What is nice about histograms is that you could group cases into bins.0793

For an instance, we could put everybody who is born in the winter months together into one rectangle.0798

We can have December, January, February, in one rectangle and then we could bin other months into another rectangle.0806

This is going to be useful to us when we have variables that have a lot of values.0818

For instance, take the example of variable like friends.0824

Friends is going to be an interesting variable to look at.0828

To look at what is the distribution of friends like in our www.facebook.com sample but people can have 1 friend.0831

People can have 15 friends, people can have 500 friends or 600 friends or 1000 friends.0838

The question is are we going to have a column for 1, 2, 3, 4, 5, all the way up to 1000?0847

That seems pretty crazy, right?0857

If we had an ungrouped frequency table it would look insane.0859

We probably have from like 1 all the way up to, let us be conservative and say 500.0864

We have one all the way up to 500 and then maybe we have one person that has 10879

and then another person that has 3 and a couple people that have 10, 12 and 15.0884

Probably not one particular value of friends like 273 is going to be that popular, right.0890

It will be like everybody just has 1 person in there mean, right?0897

Every value would not have very many cases.0902

An ungrouped frequency table would be unhelpful to us.0905

In this case we want to use a grouped frequency table.0912

We probably want a mean of everybody who has from 0 to 100 friends together and then from 101 to 200 friends together.0915

And from 201 to 300 friends together something like that.0924

That will probably be a lot more useful for us.0927

Let us go back to our examples in the Excel file and let us go friends.0929

In friends I have prepared what looks like a regular frequency table so far.0940

It has frequency here, but here I have min of mean.0946

My minimum value of the mean and my maximum value of the mean.0951

For instance, if I want my bin to go from 0 to 100 then I would put in frequency all the people who are in between 0 to 100.0955

In order to establish the mean size that you want, one thing that is helpful to know in advance0969

is what is the minimum value in your data set?0974

And what is the maximum value in your data set?0979

Here I'm going to be using Excel functions to help me do that.0982

The Excel function is pretty easy to remember because it is just mean, in order to find the minimum value.0988

I will put in mean and I put in my parentheses and I'm going to make data.0994

I'm going to my friends variable.1001

See we have people who have like thousands of friends, 700 friends.1005

I'm going to close my parentheses there.1024

Here this is just saying look at all of this data and find the minimum value.1027

It turns out that somebody in our data set has 0 friends, it is sad for that person but cest la vie, life is okay.1035

It will go on even if you do not have any www.facebook.com friends.1043

Let us look at the maximum value.1047

The maximum value is pretty easy because it is just max and parentheses and putting in that same data.1049

Obviously you could also copy and paste.1066

I have put in a close parentheses and I just hit enter.1069

And I see that somebody in our data set has over 2000 friends.1072

Can you imagine having an ungrouped frequency table where you have to number from 0 to 2257?1078

And then look at every single value in between?1085

I mean a lot of them will probably be 0 and even a really frequent one will only be like 2.1088

This is a pretty useless link to look at in an ungrouped way.1094

That is why we want to look at it in a grouped histogram.1098

What kind of mean size would you like?1102

Maybe 100 but instead of 100 I’m going to put in 99 because it is going to be the formula that I'm going to create.1105

You could like to subtract one later but I am going to make it inclusive, I will explain it in just a second.1116

I'm lazy and I do not want to type in like 100 and 200, and 201 to 300.1123

I’m just going to use Excel functions in order to help me do that.1130

Now I know that my max bin here is from 0.1135

My first bin is from 0 to 100.1139

My next bin obviously is going to start where this bin leaves off.1141

I’m going to take my max of bin number and then just add 1, for now I have 101.1146

And because it is starting from 1 already I know that if I added 100 it would be 201 and then the next one will start at 202.1155

That is when start I made my been size 99 because I know it already starts off at 101 and that way I can make it nice and even.1162

When I tell my max been to be this thing, my mean of bin size plus bin size, then will get to 200.1175

Sometimes it may require little bit of playing around with the formulas.1187

You might have to add 1 or subtract 1 or something here.1192

I’m sure you can figure it out as you go.1194

I’m just going to copy and paste this all the way down.1199

I know what I forgot, I’m just going to undo that.1204

What I now is that this cell, I do not want it to ever change.1209

I want this to be locked in.1214

What I'm going to do is take that now it should be locked in and then copy and paste it.1221

A few paces down and then you can see it goes from 101 to 200, 201 to 300.1230

I’m going to go all the way up until I capture 2257, at least up to 2300.1236

That is like twice as much.1246

That is pretty good.1253

I have all my bins and even though I have how many rows, I have 23 rows but that is a lot fewer than having 2200 rows.1255

That is why grouped bins are better.1270

Now let us put in a range.1274

The range is just telling me what is my mean bin size and what is my max bin size1276

This is just to help me so that when I graph it, when I make a chart out of it1282

That this range will help us put something on the X axis that is really useful for us.1288

Let me put in a range and basically I'm going to make my range out of the mean of bin and the max of bin.1295

These two boarders and I’m just going to put a dash in the middle.1302

The way to do that is by using the concatenate function.1306

Concatenate just means to put things together.1309

I'm putting in an equal sign (=) and the word concatenate.1313

And I will put in a parentheses and I’m going to put in this value, that is my first text and a comma (,)1320

to separate my text but then whatever is in this Excel will just concatenate it together, put it all together.1331

And then I want to put in it a dash (-) and then I want to put it in my max order1339

and enclose that parentheses and let us see what happens.1347

Now I will get 0 to 100 and this is going to be helpful for us later when we want to label our X axis in a useful way.1352

Now that we have that, what we are going to ask Excel to do is find me the count of all1363

the people who have a friends counts between 0 and 100.1374

Before we have only learned how to count if it is one particular value.1379

How do I tell Excel count if it if it is within a certain range?1385

We are going to use what we call count ifs, it is not just count if it is count ifs1391

because we are going to ask Excel find everybody above 0 but also under 100.1397

I’m going to put equal sign (=) and put in count ifs.1406

Whenever I put in count ifs, I treat almost like it is two separate count is statements that I have just put into one set of parentheses.1414

I will put in the range for the first one and then the criteria.1423

I will put in the range for the second thing and then put in the criteria.1427

Here is what I mean, my range is going to be my friends data and I will put in a comma there1431

and here I know that this data is not going to change.1451

I’m going to lock it in place.1454

It is already been locked in, I’m going to delete this part for now.1461

We want to tell it do not just count it if it is equal to 0 but count it if they have a friend count that is greater than 0.1467

How do I do that?1478

I use my quotation marks, see if you could see that.1479

I use my quotation marks and I will tell Excel count it if their friend value is greater than or equal to,1486

and I’m going to close my parentheses because what I want to say is count it if it is greater than or equal to whatever is in here.1496

For Excel to know that you want to treat this is a number you want to put in a and symbol and then you want to click on that bin.1505

It is going to be counting everybody who has a friend count greater than 0 but that is everybody in your data set.1518

That is not what you want.1525

You also at the same time want Excel to tell you if the friend count is under 100.1527

I’m going to put in a comma and now we need to put in my criteria range number 2 and my criteria number 2.1537

I know that my criteria range is actually the same as this.1544

I’m just going to copy and paste this.1548

I’m using command c and I’m putting it here, command b so that now I just have that same range that is never going to change.1550

I'm going to tell Excel now tell me if these values are less than or equal to the max of bin.1565

The so less than or equal to, close the quotation marks.1574

Put in my and symbol and click on that.1581

It looks a little crazy but it just means count it if it is greater than 0 or less than 100 and then I'm going to close that.1586

And I find out we have 8 people will fit that profile.1597

The beautiful thing about Excel is that although it is hard to do that one cell it is like it is pretty complicated,1602

once you put in your dollar signs and lock everything in place that needs to be locked in place,1609

you could just drag that all the way down and you will get this beautiful frequency table that is been bins for you.1615

And now that you have this what I would do is select both the range and frequency, hit charts and hit XY scatter.1624

This is what sort of your frequency table looks like but let us put it into columns so that the range will show up well.1641

I’m just going to cover all my frequency table stuff, so you could see this a little bit better.1653

Now you will see that our histogram has the whole range from 0 all the way to 2300 and it is binned all my people together.1659

Its binned people who have 0, 1, 2, 3, 4, 5, 99 friends all the way into one bin.1674

In histograms, one thing you will learn is that because this axis is continuous you want your histogram1684

to also reflect that you have not skipped any values down here.1691

What you want to do is double-click on your on your bars and a format data series should pop-up.1696

What you are going to do is click on options and I’m going to tell Excel do not put any gaps in between my bars.1712

In this way histograms tell you there are no values that are skipped along here, every value has been accounted for.1722

That is our first binned histogram.1733

Obviously you could change your bin size and we are going to do that just that you could see.1737

I’m going to make this real tiny and leave it over here and we are going to do another example where we are going to change the bin size.1744

Maybe instead of 100, we will want a really big bins like 250.1754

Maybe instead of 100 and then I want may bin size to be to 250.1768

Because of that I should change this to 249 and all the work has basically been done for us already because we put in those formulas.1777

Let us see, I only needed to go up to 2257, 2500 is the earliest I can close it off.1790

I’m just going to delete these rows, they are not useful to me.1804

Let me delete all of these guys.1815

Let us look at our bins, I’m just going to make this histogram large and so we could see wow like Excel1828

has already done all the work for us, making the graphs and everything.1837

All we had to do is change a few numbers here and there and we see that we basically have that same distribution.1840

Remember this distribution is called a skewed distribution and we have this tail that goes out to the right side where the values get larger.1847

That is what we call a skewed right table and that is because we have a couple of people1858

who have an inordinately large number of friends but most people are between 0 and 500 friends.1864

www.facebook.com actually says that their average number of friends is 130 or something like that1873

which is pretty low in consideration with our data set where it seems like a lot of people have even 250 to 500 friends.1879

And so now let us go back to our slides.1893

I so that was the grouped frequency table.1896

Let us look at histograms, pros and cons.1902

The pros side when you have a large number of values to plot just like with things like friends1904

that is certainly a variable where you have a large number of values.1912

There are also things like a number of tagged photos, that is going to be a large number of values.1916

Some people have just a few photos.1924

Some people have a ton of photos right?1926

These kind of variables have a large number of values to plot and in that case a histogram is really nice particularly a grouped histogram.1927

Also a histogram is dead if you do not need to see individual values1937

and a histogram will definitely show you the general shape of the distribution.1943

For example in the case of friends we see that it is a right skewed distribution.1947

One con though is that if you have many distributions that you want to compare simultaneously, histograms are going to be a little bit hard to do.1953

You can probably compare up to two separate distributions on one histogram1961

and see how the distribution flow but more than to it gets a little bit clunky.1966

Let us talk a little bit about frequency versus relative frequency.1975

Frequency is simply how many cases have a particular value so how many people have between 0 and 100 friends.1980

Well maybe it is something like 250 people.1991

How many cases have a particular value between 101 and 200.1998

Well maybe that is 240 people.2009

This is basically what frequency looks like, it is just counting.2014

Now relative frequency is what proportion of cases from the sample have that particular value.2018

If you had 250 people having friends between 0 and 100 friends, what is that in relation to your entire sample?2023

Well if your sample, if your (n) is 1000 people in your sample then it would be 25% or .25.2038

And then between 101 and 200 instead of being 240 people it would be 24% or .24.2054

A pretty simple idea but it will come up again in the future.2069

Let us go into some problems.2075

First example, if we want to visualize the distribution of uploaded photos should we use dot plots or histograms?2077

Let us think about uploaded photos, is that like months where there is sort of this set finite number?2085

Or is it more like friends? the variable friends where there is like a ton of potential values?2094

Uploaded photos I’m pretty sure that people have up to like thousands of uploaded photos sometimes.2102

I'm thinking that it looks more like the distribution of friends than it looks like months.2108

Uploaded photos because there are so many values we would probably want to use grouped histograms as well.2118

We probably do not want to look at every single value.2129

How many people have one photo?2132

How many people have two photos?2133

How many people have three photos? All the way up to like thousands.2135

Instead we would probably want to look at them in a grouped way.2137

How many people have between the 0 and 500 photos?2141

How many people have between 500 and 100,000 photos?2145

And why? mostly because there are going to be a lot of potential values.2150

Values is what we always plot on our X axis and whenever you could sort of imagine these values2166

being like cut up into like thousands of pieces, you probably want to use grouped histograms.2175

Example number 2, consider this dot plot with the age of pennies in a sample collected by a statistics class.2182

What are the cases and the variables that you see here?2189

It is really asking you what does each little dots in my dot plot represent?2193

Well I know that each dot probably represents a penny but not just a penny, it has to be some sort of number about the penny.2200

I can see it actually the case is the penny and the variable that it has is the age, probably in years.2211

Because that was written on the penny it says like this penny was made in 2010.2229

What is the shape of this distribution?2238

This looks to me if I just draw a little outline here, it draws outline, this looks to me like a tail.2241

I'm guessing this is the right skewed distribution.2254

And why does it have this distribution?2262

That is going to take a little bit of thought.2266

It looks to me as though lots of pennies that is in circulation pretty new and then there are as many old ones.2268

Maybe it is because there is this wall right here.2280

Penny cannot be any newer than 0 years old.2284

You cannot quite go past that and probably a lot of pennies that are really old sort of fallout of circulation2291

or maybe the US Mint takes them out of circulation.2298

That is what I would answer.2305

Let us go onto example 3.2309

Considering this histogram of mammals speeds is this a grouped or ungrouped frequency distribution?2310

Well to me it looks like there are bins here, this is a probably a bin that goes from 0 to 10 and this is between 10 and 20.2319

This is between 20 and 30.2331

In here there might be animals that go are able to run at 23 mph or 28 mph.2334

I would say that this a grouped frequency distribution.2343

This is a good question, if we used relative frequency will this change the shape of our distribution?2349

Well let us reason about that a little bit.2356

These peaks right here, these animals are running at speeds of between 30 to 50 mph and that seems to be the mode.2360

The most frequent type of mammal and that is probably not going to change if you take 6 and divided it by however many animals are here.2371

This should still be the peak and this should still be the peak.2384

This should still be one of the lower values.2388

This should still be one of the lower values.2391

I’m going to say that this would not change.2394

No it would not change the shape of our distribution.2397

I have made the relative frequency distribution down here and you could see2401

that even when we put it in percentages that the shape of it looks largely the same.2411

That is one handy thing to find out that, that when you use relative frequency the distribution does not change.2422

We are going to be using that in the future.2428

Last example, in this histogram of life expectancy which do you think is bigger?2431

The mean or remember it is like the balancing point of this distribution or the mode the most frequent value, explain.2437

In this histogram of life expectancy the mode is probably the easiest thing to figure out at first.2447

We could see right here the biggest peak is under at around 70 in life expectancy.2454

Each of these little bins look like they are 4 years.2463

This is probably between 68 and 72 years.2469

That is the mode but where do you think the mean might be?2477

We have to imagine taking this whole thing, cutting it out with cardboard and trying to balance it on your finger.2483

I think that we probably have to balance it somewhere here but not too far down, not quite in the middle but somewhere right here.2490

That is what I’m going to guess and that might be the mean.2503

This is the mode.2509

Because if I put my balance right here, I think it probably tip on this side.2512

I probably shifted over a little bit and this looks like my mode is my bigger value than my mean.2522

Explain.2535

I think I have already explained it right?2537

My mode is right here but my mean is a little bit over here because2542

I think that if I balanced it right underneath the mode, it would tip on that side.2543

There would be too many people here on this side.2551

I’m going to move over my mean and because of that I can clearly see that my mode is bigger.2554

There you go.2560

Thanks for using www.educator.com.2561