(bright upbeat music)
- Hello, I'm professor Rick Jerz,
and I welcome you to this session titled
"Describing Data Graphically using Excel."
This session will show you how
to produce the most common graphs used to describe data using Excel.
If you are new to Excel or have been away from it for a while,
take your time listening and learning.
If you already know how to produce
graphs in Excel, I encourage you
to listen and follow this session because I believe you will still learn
a few new Excel techniques that many people don't know.
You might want to repeat this session
several times to retain what you learn.
I think that you are going to enjoy this session.
Please let me know if you have any questions.
Let's get started.
For this project, you will need a bag of M&M's.
Usually, in my face-to-face classes,
I provide students a 1.74-ounce bag of peanut M&M's.
But anything close to this will work.
If you were in my online section,
you will need to buy a bag yourself.
Sorry about that.
I opened my bag of M&M's, and my data looks like this.
Of course, your data will look a little different.
If you were unfamiliar with Excel,
watch my Excel Fundamentals video as a refresher.
I produce my videos for both PC and Mac users.
When you hear me say something like hit control-C,
this means hold the "ctrl" key down
and then simultaneously press the C key.
However, Mac users should substitute the command key
for the ctrl key unless I tell you differently.
So ctrl-S on the PC becomes command-S on the Mac.
Also, when I say left-click or right-click,
this means click your left or right mouse button
or equivalent button on your computer,
knowing that some students might be using
a computer's touchpad or touchscreen.
If I say just the word click, this means left-click.
I use the word "drag" to mean "click and drag,"
which again depends upon your mouse, trackpad
or input device.
We begin creating graphs by first creating a frequency table
for our M&M color data.
Your frequency table and charts
should reflect your bag's M&M's.
Begin a new worksheet and enter a title
in cell A1 and column titles "Color" and "Count" below this.
Enter the colors from your bag,
each color's count, and the word "Total" at the bottom.
I make my table look a little bit better by formatting
some of the cells.
I right-justify the Count column heading,
and I center my Rick's M&M's heading
over to cells using this special "merge & center" tool.
I make my titles be boldface
using the ctrl-B keyboard shortcut.
Excel does have quick tools for bold, italics, and underline,
but I find keyboard shortcuts to be faster.
I also want the word "Total" to be boldface,
and I can use ctrl-Y, the repeat again
keyboard shortcut.
Yes, I could have used ctrl-B,
but I like to reinforce using this important ctrl-Y
keyboard shortcut
that reapplies the immediately preceding formatting.
Last, I draw lines around the cells using the Borders tool,
a pretty cool tool. Watch!
This is the way that I like my tables to appear.
I save my work using ctrl-S.
The first time that you save a file,
Excel will ask for its filename.
From this point forward, ctrl-S will quickly save
without asking for a filename.
I will show you three ways
to calculate the M&M total, or sum.
One way is to go into the Total cell and enter
equal 3 plus 3 plus 1 plus 3 plus
4 plus 5.
But the disadvantage of this approach
is that if I made a mistake in counting colors,
for example, my blue M&M should have been 4, when I enter
4 the total is not updated to reflect this correction.
A better way is to use the cell referencing approach.
In this Total cell, enter "=" B3
+ B4 + B5 + B6 and so forth.
Then, if the blue M&M should be 4
the Total cell will immediately reflect the correct total.
This ability to change a value
and have results immediately updated
was the significant principle
of the first commercially available spreadsheet, VisiCalc,
back in around the year 1980.
Yet another way to do this, perhaps the best,
is to use a function.
I type equal, and then the function called sum, S-U-M.
I type a left parenthesis,
then just drag through the data that I want to sum
and hit Return.
It's as simple as that.
In general, I always like to use Excel's functions
instead of writing out the longer equation
because functions are usually simpler.
So when you are trying to create a complex equation,
always explore Excel's functions to do the same thing.
Summing is something that people do frequently,
so Excel provides a quick tool on the Home ribbon
to do the summation.
Begin by clicking in the Total cell
and click the Sum button.
Excel tries to guess the cells that you might want to sum.
If correct, hit Return.
If wrong, just dragged through the correct cells
and press Return.
Now, let's create our first chart, a frequency diagram.
Do this by dragging through your data
and column titles and making sure not to select the total.
Then click on the Insert Chart tool on the Insert ribbon.
Excel tries to recommend a chart.
In this case, the first recommended chart,
which Excel calls a column chart, is what we desire.
You can drag the chart to anywhere you want.
You can also resize it by dragging out a corner or side.
A bar chart is made up of many objects,
and most objects can be formatted to suit your needs.
For example, you can click on the chart title and edit it.
You can also delete or add objects.
When you have clicked on a graphic object,
Microsoft provides you an extra ribbon
called the Chart Design ribbon.
This is one way to control and format chart elements.
For example, I can add a horizontal axis title, "Color."
And a vertical axis title, "Count."
Another technique to format chart elements
is to right-click on any bar
and pick Format Data Series.
A right-side dialog box appears with many settings.
For example, I can select this fill tool
and pick a different solid fill color.
Microsoft has tried to make this dialog box very consistent
from object to object.
We will be using this right-click
format object approach a lot.
I suggest that you spend some time exploring
the many other formatting changes you can make
from this dialog box.
To change an individual bar's color,
slowly click twice on that bar.
You'll notice that the bar, by itself, is highlighted.
Then right-click and format it.
I encourage you to experiment and have some fun.
If you do something that you do not like,
just hit ctrl-Z to undo your mistake.
Our frequency chart is now complete.
Creating a pie chart is just as easy.
Preselect the data,
then again, on the Insert ribbon, pie chart,
and select the first one.
I'll move my pie chart
to the right side of my frequency diagram.
Let's format this chart.
Formatting does involve personal preferences,
so you don't have to format your chart exactly as I do.
I delete the legend and replace the word "Count"
with "M&M's in Bag."
I label these pie chart wedges by selecting the whole pie,
right-clicking, and Add Data Labels.
I then click on one of these labels,
right-click, pick Format Labels, then turn on percentages.
In Label Position, pick "outside end."
To show the leader lines,
select a value, and drag it outwards.
You have to do this one by one.
If you want to color the wedges differently,
slowly double-click on a wedge, just like we did on a bar
for the frequency diagram,
right-click, and pick a color.
I'm done with my pie chart.
This is a good point to hit ctrl-S to save our work.
The last chart to create from our nominal color data
is a relative frequency diagram.
To do this, we must first calculate category percentages,
so adjacent to the count cell, I entered the word "Percent."
Next, I enter the percent formula.
Remember, the percent is equal to the count
divided by the total count.
For example, the blue M&M percent is calculated
as three divided by 19.
Although I could enter "=" 3/19,
it is better to enter an equation by referencing cells.
This way, if the values were to change,
Excel will automatically recalculate the results.
When you want to reference a cell, you could type "B3"
into the equation,
but I tend to prefer clicking on the cells with my mouse
to pick up the reference because I don't have to worry
about mistyping a cell reference, and it is often faster.
Begin with an equal, then click on the number 3,
then add a slash for divide,
and finally click on the 19 to pick up its cell reference.
Press the Return key to finish.
You can verify the equation in Excel's edit area,
or you can double-click on the cell,
and Excel will display the equation
along with colors to help you see
which cells are being referenced.
To make this decimal number look like a percent,
click on this Percent tool on the Home ribbon.
That looks better.
Once you have an equation entered, you can copy
and then paste it into other cells, saving you time.
I click on the cell where I just entered this equation,
press the ctrl-C shortcut keystroke to copy it,
and then press ctrl-V to paste it into the Brown cell.
Hmmm, something went wrong here.
I get a "#DIV/0!" error.
Fixing problems can be troublesome.
The more you use Excel,
the better you will be at fixing problems.
I like to begin troubleshooting
by clicking on this cell with the problem
and observing the equation in Excel's edit area.
I see B4 divided by B10.
Well, the B4 looks correct,
but the cell B10 contains nothing.
We just told Excel to divide 3 by 0,
and mathematically, you cannot divide by 0,
which is why Excel gives us this "#DIV/0!" error.
We really wanted to divide this by B9,
which is our total count, and not B10.
In Excel, by default, when we copy and paste an equation,
Excel makes all the cell references relative.
Pasting the equation down one cell
changed the B9 to B10.
But this is a case where we want to use absolute,
not relative referencing.
We want to absolutely always use B9.
I fix this by going back into the cell
where I first entered my equation,
click somewhere on the B9,
and then press F4 on the PC or command-T on the Mac.
This is a case where I actually
prefer the Mac's keyboard shortcut.
This keyboard shortcuts cycles
through various forms of referencing.
The first time I hit this key,
Excel changes B9 to $B$9.
Think of the dollar symbol as "always."
Always use B and always use 9.
This is how an absolute reference looks.
Okay, now, when I copy
and paste this equation, the results are correct.
A faster way to repeat an equation is to duplicate it "down."
I drag through all the cells and then press ctrl-D.
Everything looks correct.
Now that I have my percentages in my frequency table,
I'm ready to create a relative frequency diagram.
I still have one problem:
The "Count" column separates the "Color"
and the "Percent" columns.
I need to be able to select only the colors
and the percentages.
Saying this in more technical language,
I need to select non-contiguous data.
The technique to select non-contiguous data
is to drag through the color column,
then hold down your ctrl key,
and drag through the percent column.
This technique is an important one to learn in Excel.
Now I can insert a column chart just as before.
Notice that this column chart now has percentages
instead of count showing on the Y-axis.
By definition, a relative frequency diagram.
I'll let you go ahead and move this chart around
and format it to make it look more attractive.
Let's turn our attention to creating charts
for interval or ratio data.
I give you this data describing
the weights of individual M&M's.
Enter this data into a new worksheet in our Excel Workbook.
Manually typing this data into Excel is tedious.
Maybe in another session,
I can show you some faster techniques to do this.
But for now, I manually enter this data.
To stay organized, I rename our first worksheet to M&M's data
by double-clicking on the worksheet tab
and typing a new name.
While I am at it,
I might as well rename Sheet2 to "M&M Weights."
Let's review what we have learned
in the lecture about constructing a frequency table
for interval or ratio data.
In step 1, we have to decide on the number of categories.
We can use the "2 to the Kth" rule as a starting point.
Step 2 is to determine the category interval, or width.
A good place to start is with the equation
(H-L)/K.
In step 3, we set the individual category limits.
In step 4, We construct the frequency table.
Once we have this frequency table,
we will be able to create a histogram.
Since we want to use all this data,
I will create a column called "All Data."
I next copy and paste each column of data
into this single column.
If you want the data sorted, you can preselect the data
and choose Sort from the Data ribbon.
At this point, I remember to hit ctrl-S
to save my work.
Let me show you a more advanced and faster way
of copying and pasting this data.
Many people do not know this technique,
so pay attention and make sure to try it yourself.
Also, this technique is a PC-only technique
that is not available to Mac users. Sorry.
I return to my data as entered.
On the Home ribbon, click this little arrow
right next to the word "Clipboard" and pick Clear All.
Leave this clipboard box turned on.
Then, column by column, select the data,
and press ctrl-C.
You see each column of data appearing
in this special clipboard area.
When done copying this data,
move your cursor right below the heading for "All Data,"
and then click on this "Paste All" button.
Wow, a pretty cool feature!
And again, very few people know about it.
On my Mac, I have tried a variety of clipboard utilities
and right now like a product called "Paste,"
available from the App Store.
Paste works across all Mac applications, not just Excel.
With this data pasted and still selected,
I can see right here, the total count is 100.
In other words, I have 100 data points.
This was a lot of data to enter manually.
Sorry about that,
and this will be the last time I will have you
enter this much data.
Now we need to determine how many categories
to use for our 100 data points.
I will set up a little table in Excel for the
"2 to the Kth" rule
and then use it to determine how many categories
I should have.
For the value of K, I will use the numbers 1 through 20.
A great Excel technique
to create a sequence of numbers quickly
is to enter the first number, in our case "1,"
hold down your ctrl key on the PC, or option key on the Mac,
move your mouse cursor
to the lower right border of this cell
until the mouse cursor changes to a plus plus symbol,
click and drag down.
I stopped when I see 20.
In this cell, I entered the equation equal caret (^)
and reference the cell with K's value.
The caret (^) symbol raises a number to a power.
We can use another Excel special
yet similar technique to copy these equations down.
Press your ctrl or command key,
then double-click the lower right side portion
of the equation cell.
Wow, pretty quick!
For this kind of copying, Excel uses relative addressing,
which is what we want,
so we are fine. Looking at this table,
the first "2 to the Kth" value that is greater than 100
is when K equals 7.
So we should use about seven categories.
Now let's calculate the category width.
Remember, the equation is (H-L)/K,
where H is our largest, L is our smallest,
and K is the number of categories, which is seven.
Since I have sorted our data,
the highest value is at the bottom
and the lowest value at the top.
I enter an "=", click on the highest value,
minus, click on the lowest value, add a slash,
then click on 7.
I add parentheses around H minus L
to force this calculation to be done first.
The category width ends up being 0.027857.
I will use something a bit more reasonable, like 0.025.
This actual category width is left to your judgment.
For example, you might want to use 0.03.
Realize that when the category width is smaller,
we might end up with more than seven categories.
Now I have to set each category's limits.
Our smallest value is 0.838,
so I will start at 0.825,
which is slightly smaller than 0.838.
I then add the 0.025 width to this by referencing its cell.
I make the width be an absolute reference
using F4 or command-T.
I dragged down a little, then hit ctrl-D.
Since the largest weight value is 1.033,
I can delete these extra cells.
Great! We see that our first category
goes from 0.825 to 0.85.
The next category from 0.85 to 0.875, and so forth.
Okay, we are almost done.
We just need to count how many of the weight values
are in each category.
Instead of tallying this data by hand,
let's use an Excel function that few people know.
It's the FREQUENCY() function.
To use this function, you will have to pay attention.
If you happen to be listening to this session
in your casual mode, get out of it
and switch to your "pay attention" mode.
Begin by clicking this first cell
and dragging through all the cells
where you want to count the data.
Then, with all cell selected, type "=FREQ,"
and you will notice that Excel finds the FREQUENCY() function.
Hit the tab key to select it.
Hitting your tab key is a quick way
to have Excel finish typing a function.
This function requires two sets of data.
It requires the actual data values and the bins,
what we call categories.
I drag through our weight values, scroll up, type a comma,
and drag through the category cells.
Be careful at this point.
Type the right parentheses, but do not hit the enter
or return key as you might typically do.
Instead, on both the PC or Mac,
hold down your ctrl and shift keys together
and then hit your enter key.
Yep, three keys to finish this function.
You must hit three keys to complete this operation
because the FREQUENCY() function
is a special Excel "array" function.
Array functions are an advanced Excel feature,
and many people do that know how to use them.
Excel adds left and right brackets around array functions.
Wow, what a function!
Yep, a bit tricky, but learn how to use this function
by practicing a few times
because you will use it again in the future.
If you have some friends or coworkers
who think they're pretty good with Excel,
see if they know about this FREQUENCY() function.
Most don't.
Some students have recently reported
that they didn't have to hold down
the ctrl and shift keys,
that merely hitting the enter key worked for them.
Yes, this is true
if you have the latest Excel 365.
In about mid-2019,
Microsoft introduced newer "dynamic array" functions,
which no longer require the ctrl and shift keys.
Using this FREQUENCY() function, if you get a "spill" error,
it is because your Excel 365 dynamic array
wanted to fill down
but encountered a non-blank cell before completing.
Since I still have students on earlier versions of Excel,
I continue to show the older three-key method.
I do encourage everyone to consider upgrading
to the latest Excel version.
We have learned that a histogram is a preferred chart
for continuous data,
so let's create one.
In our histogram,
I want to use the category midpoints to label each tick,
so I need to calculate these midpoints.
I do this by using the AVERAGE() function,
which calculates the middle of two values.
Finally, I create my histogram
by selecting only the "count" data.
Then go to the Insert ribbon just as before,
select column chart.
Notice that the labels of the horizontal axis,
1 through 9, are incorrect.
These should be the midpoint values.
To replace these,
click once on the horizontal axis label area
to make it active, then right-click, choose Select Data.
Then click in the "horizontal axis labels" textbox,
drag through the midpoint data, then click okay.
I can rotate the midpoints by selecting the axis,
right-clicking, and on the "alignment" tab,
change text direction to "rotate 270 degrees."
Remember that a histogram should not have gaps.
I can remove gaps by first clicking once on any bar,
right-click, and slide Gap Width to 0.
While I'm here, I like to also change the border color
to black to better see each bar.
I will leave it up to you to improve the chart's appearance
by adding a title, axis labels,
colors, and other features.
Okay, I am finished with my histogram.
We create a frequency polygon
using nearly the same histogram techniques,
except pick line chart instead of column chart.
You can format this until it looks as you desire.
To create a cumulative polygon chart,
we must first calculate cumulative frequencies.
I can quickly do this by using an Excel equation
where each new cell is the previous total
plus the new count.
Sometimes you need what I call a "seed" value.
I make my seed value be the addition of the first two cells.
Then, in the next cell,
I add the next count to the previous cell.
Finally, I duplicate this equation
down to finish the cumulative frequency calculations.
I can now create a cumulative frequency chart
with this cumulative count data.
Again, I leave it up to you to format this chart
until it looks as you desire.
Okay, let's do one more chart,
a combination chart.
A combination chart has two vertical axes.
In our case, we will combine the histogram chart
with the cumulative polygon chart,
but we want the cumulative numbers to be percentages
and for this axis to show on the chart's right side.
Since I already have the histogram,
I begin by clicking on it to highlight the entire chart,
hit ctrl-C to copy it,
click in any empty cell,
and paste this chart with ctrl-V.
Gee, I am already half done!
It just so happens that we have exactly 100 weights,
so our cumulative frequencies equal the percentages.
But let's assume that this is not the case,
and that I need to calculate percentages.
In this first cell, I enter the equation, "=",
this count divided by this total.
Since the denominator is always the total,
I changed this cell reference to be absolute.
F4 on the PC, command-T on the Mac.
Copy this equation down.
Then use the Percent tool on the Home ribbon
to make these values appear as percentages.
I add these cumulative percentages to my combination chart
by dragging through the cumulative percent data,
hit ctrl-C to copy it,
and then click one time on my combination chart
and hit ctrl-V to paste this data.
The pasted data shows up as little red bars
because percent values are relatively much smaller
than the counted data.
We want to convert these red bars to a line
and represent the percentages
as values between 0 and 100%.
Click once on any red bar to highlight the series,
then right-click and pick "format data series."
Yes, it might be hard to select these little red bars,
so it might take a few tries.
On this tab, click on Secondary Axis.
To no surprise, this is how you create a secondary axis.
Next, I changed the bars to a line
by right-clicking on the red bars
and picking "change chart type," then line,
and then the top-left example.
I notice 120%, which doesn't make sense.
To fix this, click one time on the percentage axis titles
to highlight them, then right-click and pick "Format Axis"
and change the Maximum from 1.2 to 1.
My combination chart is looking pretty good.
Have some fun changing other formatting settings
to improve the appearance of your chart.
Show this combination chart to a friend or a colleague
who's good with Excel to see if they can do this.
I should mention a slight error in this data,
and you might have seen it
if you are a really sharp student.
If you manually tally the M&M weight counts,
yes, we have one value in our first 0.825 to 0.5 category.
However, in the second category, from 0.85 to 0.875,
if you start manually tallying the values in this category
and follow our rule that borderline values
go into the next higher category,
the FREQUENCY() function reports 14, but the two .875s
should go in the next category.
To fix this problem,
I will subtract a very small value, 0.0001,
from our starting point to force border values
into the next category by the FREQUENCY() function.
Now the count is correct.
You might need to change the view of these values
to three decimal places.
Here are the four charts I created
for interval and ratio data.
We have learned how to make many charts in this session,
and I believe that you have learned
a few new Excel techniques,
some that very few people know.
(bright upbeat music)
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions
or need my help, please let me know.
(bright upbeat music)