Welcome to Excel
basics video number 24. Here in this video
we got to talk about visualizing quantitative
data, which just means number data with Excel charts. And there'll be a big
emphasis on no chart junk. That will be our
guiding rule as we learn how to create charts
like column, bar, line, and XY scatter. Yes, indeed, here's
a big red no. We're going to learn
the one overriding rule, which is no chart junk-- no 3D pies, no ridiculous color,
no unnecessary repetition, and so on. Over on data analysis-- yes,
we've been doing data analysis and business intelligence
throughout this whole class-- always converting raw data
into useful information. Yes, we've made pivot
tables, formulas, and now we want to see how to
create yet another type of useful information,
visualizing quantitative data with Excel charts. Proper data set. Yes, it is possible to create a
chart from a column of numbers. But you absolutely want to
have the field name at the top because when you do that and
you highlight it internally in the chart, then the
number range, called series, will be called sales and our
category label, for example, will be called sales rep. That will be internal
inside of the chart, which will help us manage and
edit the data if we need to. So far in this class, we've
seen all these features for data analysis. And so today, we get
to see Excel charts. Over on charts in
this video, we will see how to create column charts,
bar charts, clustered column charts, stack column charts,
and learn when to use a line and when to use an XY scatter. Now let's go over to
the sheet pictures because, almost always, we're
going from transactional data in to summarize tables,
either with a pivot table or with formulas. Now in this video,
I will already have the data summarized with
either a pivot table or Excel formulas, but we go
from our proper data set with transactional records
into some summarized table and then we create a chart. Now just so we're
all on the same page, let's talk about
chart terminology. Of course, the chart
title is at the top. The numbers will always
have a special name. Excel charts call those series-- series of numbers. Then, we have categories. We can have a category
along the horizontal axis, in this case it's month. We can also have a
category in the legend-- color-coded legend, to tell
us what each color represents. And then, of course,
for a column chart, the column height
comes from the numbers. We also can have an
XY scatter chart. And in an XY scatter, we have
our chart title at the top. The numbers, X and Y,
are still called series. These are the Y series numbers. These numbers here are
the X series numbers. And instead of a
category and a legend along the horizontal axis, we're
going to have axis labels here. Hours studied is the X variable. Test score is the Y variable. And then, of course,
the internal-- that means the intersection
of a particular X and Y-- those are called our markers. Now, we also want to talk
about our overriding rule. No chart junk. This chart is terrible. There's all sorts
of chart junk in it. The biggest mistake
with this chart is that they took a pie chart
and tilted it on its side, and represented
it as a 3D chart. That totally distorts
the proportions. Not only that, but there's
this chaotic color scheme with these crazy patterns. The only time you
want to use chaos when it comes to
color and patterns is if your chart is
actually about chaos. Like if you're doing a
physics chart about chaos or, maybe, you're doing
a sales marketing report for a punk rock band
that's all about chaos. But the rest of the
time, that does not work. Also, look at this
unnecessary repetition. We have months here and
also next to each pie piece. Then, there's this series one. That comes from not
having your field name at the top of the column. And then, of course,
a chart title that adds no useful description
is total chart junk. So every time that
we build a chart, we'll ask if each chart
element is helping to contribute to the message. If it is, then it's
not chart junk. If it's not, then
it's chart junk. Now we want to go over
to the sheet column and bar, and look at
our first example. Now, our source data
said, if we scroll over, as date month sales
channel product in revenue. And we've created a cross
tabulated report, where the row label is month and the
column header is sales channel. We sold it in the store,
mail order, or website. Now when we have a
cross tabulated report, we can use just the
month in our chart, just the sales channel
in our chart, or both. We're going to start
off by doing just month. So I'm going to highlight month. That's the field name
at the top, in essence, or the name for that
set of categories. Then, I'm going to come
over, holding Control, I'm going to click on total
and highlight the numbers. I do not want the
grand overall total. I want just the
totals for each month. Now, charts. Over in insert. Chart group. And when you're comparing
numbers across categories-- in our case, months-- we can use a column chart. So I'm going to
click the dropdown. We do not want to use 3D
charts, that's chart junk. When we're comparing
across categories, we can show column or bar. We're going to start by
using clustered column. We'll come back and look
at stat column later. Let's do clustered column. Now, there's no clustered
column because we only have one set of categories. When you have
multiple categories, like we do when we later
do the crosstab table, then we'll have
horizontal and legend entries for our categories. Now already, that's
looking pretty good. For each element, we're going
to ask is it chart junk. Well, of course, we
have our numbers here. We need those to understand
the height of each column. The columns are fine. The labels are fine. The one thing that doesn't
help articulate our message, perhaps, is our title. And notice that actual word,
title, came from that cell right there. And actually, you can see
the ranges highlighted because this chart is dynamic. If we change any of
these numbers or labels, they will change in our chart. But I want to type a
different chart title. Let's click on the chart title. And when we see
the solid line, not when I click inside and
see that dotted line-- but if you click on
the outside again, when you see the solid
line, you can just start typing some
title and hit Enter. But I actually want to
link this chart title to the text in cell G1. So you ready? With the solid line-- again, not that dotted line
because it won't work-- solid line, type an equal sign. That immediately
starts a formula up in the form of the bar. And I can click in cell G1. It shows me the sheet name
and the cell reference. I hit Enter. That registers the formula
for that chart title. Now this is dynamic, so I can
come up to this cell and type something. I'm going to type, Wind
Sport Incorporated, Space and then Enter. And instantly, our
chart title updates. Now, it doesn't
look like we have any chart junk in our chart. The grid lines-- sometimes,
people click on them. And don't click on the
outside edge of the grid line because then it highlights
the whole inner part. Click on one of the
inner lines and when you see those grid
lines, you can actually delete them if you want. Sometimes, people
like to delete them. I'm going to delete them because
it leaves it less cluttered. Control-Z because I want to
put those grid lines back in. The grid lines simply help
to match up column heights with the numbers in
our vertical axis. Now, I want to point
to the outside edge. And again, be careful if
you're moving your chart, don't click on the inside
because if you click in with your move cursor
right there, move it, it'll actually move the
insides of the chart. I want to click on
the outside edge. And when I see my move
cursor, I can click and drag. Now with the whole chart
selected, I want to copy it, Control-C, and
very carefully, I'm going to click off to
the side in the cells. And now I'm going to Control-V. Now I want to change this to
a bar chart, so we can compare and contrast column and-- what we'll do in a second-- bar. With the chart selected, you can
go up to chart tools, design, and there it is,
change chart type. Or you can right-click the chart
and point to change chart type. On the left, we can choose
what chart type we want. I want bar. The first one,
clustered bar, is fine. Click OK. Now, column and bar
do the same thing. They show you differences
across categories-- differences across categories. But the bar will more
forcefully emphasize the difference between the bars
than will the column chart. And the reason is simple. When we tip the
column on its side, because the chart is
wider than it is tall, that means the bars are
longer and it more forcefully emphasizes the differences. The other thing that
a bar chart will do, that a column chart
doesn't do as well, is if you have
really long labels. Bar charts will list
the label on one line, whereas the column chart
will wrap the labels. But in essence, they
do the same thing. Column and bar compare
differences across category. Now, let's change this
one up a little bit and learn something
about formatting. There are two ways
we can format. We can use the
buttons to the right or we can select an element-- as we will in a
second-- and open a task pane to
format, or change, how that chart element looks. The first thing I want to
do is I want to add numbers to the end of each bar. These are called data labels. So, I come up to the plus. And the plus is great because it
allows us to check and uncheck chart elements. You could see the axes, that's
the numbers and the labels right there. Chart title and grid
lines are all checked. I want to check data labels. And just like that, I
see the exact number from our cells listed
at the end of each bar. This works on
column charts also. Now as soon as we do
that, we either need to put our numbers at
the end of each bar or down in the horizontal axis. It's rare that you would
want them in both places. In essence, right now,
this is chart junk. This is unnecessary repetition. Now, it's nice to have
the horizontal axis because it doesn't clutter
the chart up as much. That's a good reason to
have the horizontal axis. The labels at the end, however,
show you the exact number. So, when you're-- after
showing the exact number, then this would be preferable
over our horizontal axis. Now, let's delete this. So, let's click over here. This is unnecessary repetition,
in essence, chart junk. So I'm simply going to hit
the Delete key to delete that. Now, we can use the green
plus to check and uncheck chart elements. You can come to the format and
change the style or the color. I very rarely use this. A lot of this chart junkie,
but some of it's not. That's kind of nice there. We can also come to the filter
and uncheck certain items, and check certain items. Now, we're not going to
uncheck or check anything. All we did was use that green
plus to add data labels. Now, I want to come and
click on the columns. And when you click
on the columns, notice they're are
all highlighted. If you click a second
time, then you're allowed to do something
to just that column. Now, click in the white. Click back because I want
to highlight all of them. Now, just like we
would in the cells-- use Control-1 to open
up format cells-- in a chart, we can
select the chart element we want and use Control-1
to open up our task pane. Now, the nice thing
about the task pane is, right now, it says
format data series. Series are the numbers. If I click in the chart
area, this changes. The formatting elements
in the task pane now are for format chart area. I'm going to click
on the columns. Come here and click this a
second time to get rid of that. Now once we get to the
particular format chart element we want, you can
click on the different icons at the top. This will format
the actual columns. And for us, there is gap width. We're not going to change that. Effects-- that's
mostly chart junk. We want to go over to the paint
bucket, and there's fill-- we can click that
triangle-- and border. Now, you can select solid and
then change whatever you want. Similarly, you
could have a border, click solid, and
change the color. What I want to come over and do
is check vary points by color. All right, so, we can use
the task pane and our buttons to the right to change
how our chart looks. Now, I want to come over and
create a cross tabulated chart. I'm going to click
the cell with month and very carefully
highlight just the labels at the
top of each column, and the labels at
the top of each row, and the intersecting numbers. I do not want the totals. With that range selected, I can
go to insert, over to charts, click the dropdown, and we
want clustered column again. If I click that, I
see, wow, it almost came out looking perfect
right off the bat. Now I'm going to click
on the chart underneath and then, move it
over to the side. Click on the chart and
move it off to the side. Scroll over. All right, so this
is what happens we see a cross tabulated report. Because our table was
taller than it is wide, those category labels showed
up in the horizontal axis and then, the column header
labels showed up in the legend. Everything's
looking pretty good, except for the chart title. I'm going to select
chart title, equal sign-- that shoots me up
to the formula bar-- click in cell G1, and Enter. Now, I want to copy this chart--
this is a clustered column-- and I want to change
it to a stacked column, and then compare and contrast. So, I'm going to click
on the outside edge, Control-C to copy, click
below, Control-V to paste. I'm going to click and drag
this a little bit to the side. Click and drag this
one right here. Now, I want to change
the chart type. Right-click, change chart
type, and I want to select from column, the second one-- stacked column. I select that, click
OK, and there we go-- the exact same
numbers in a stacked column and a clustered column. Now, the only difference between
these two is the emphasis. Here, clearly, we are
emphasizing the items in the legend. Because for each
month, we're allowed to compare in-store mail
order and web sites. Over in this chart, the emphasis
is on the horizontal axis-- the months-- because I clearly
can compare the total for June to the total in August. You can see over
it in this chart, it's very hard to see
which month was biggest. So when we do
stacked column, we're emphasizing the horizontal axis. When we do clustered column,
we're emphasizing the legend. All right, so we saw column
and bar for a single variable. And then, for a crosstab, where
we have two variables or two conditions or criteria, we
can use clustered columns or stacked columns. By the way, you can do bars
on both of these, also. Now, one last tip
about bar and column-- I'm going to copy
this chart, Control-C, and click down here, Control-V.
Right-click, change chart type, and I want to change it to pie. Click OK. Pie charts are not as
effective for comparing numbers across categories. And the reason
why, research shows that humans, when they're
looking at a bar or a column, can decipher or
understand the difference between the categories more
clearly with a bar or a column than they can a pie chart. And in the last
five years of doing data analysis, or analytics,
or business intelligence, you'll see that most people
prefer the bar or the column to the pie when comparing
numbers across categories. All right, let's look
at our next chart. I want to go over
to the sheet line. Now a line chart
is almost always some number being
compared across time. Occasionally, this
is some category. Here, our time is quarter-- quarter and years. So, we want to see a line
that goes up and down, and shows us the trend,
or pattern, over time. Now, we're going to start
with a simple one here. We have four quarters
and four numbers. I'm going to click in a single
cell in our proper data set. And since we're
using both columns and we want both
labels at the top, we can simply click a single
cell, go over to insert, chart group, and the
line chart is right here. That's the XY scatter. That's when you
have two numbers-- one for the X and
one for the y-axis. When you have a single number
and categories, or time, that's when you
use the line chart. Now, we're going to use
either the first one, that has just line, or the third
one, line and markers. I'm going to choose
line and markers. Now, there's our chart. We can see the up and down
movement of those numbers over our time quarters. Now, one thing about
this is notice we're starting way up at 500,000. So I want to change
where the axis starts. So, I'm going to
click on the axis. Control-1 to open up
or move the task pane to the right section
of the task pane. And there it is, min and max. Sometimes, it doesn't work
when you use Control-1, so you literally have to click
between each one of the icons to find what you're looking for. I'm going to highlight
min 0.0 and type a 5, and then five zeros-- 1, 2, 3, 4, 5, Tab. And instantly, we could see
our axis starting at 500,000. Now, in the same axis
options or series, I want to come down to number. This is number formatting. It doesn't change the number, it
just displays it a certain way. And I want to change
decimal places to 0, Tab. And there we go, we see
it's showing in zeros. Now, if it wasn't obvious
from the context what this was, like the name of the
company or something like that, we would change
that chart title. In fact, let's go
ahead and do that. I'm simply going to click. And instead of
linking it to a cell up here since I
don't have a chart title, with the solid line, I'm
simply going to start typing-- Wind. Notice as soon as
I start typing, it appears up in
the formula bar. Sport, Space, Incorporated
Sales, and Enter. We're going to assume that it's
obvious from the context what year that would be. Now that's just a single line. Sometimes, you have the actual
revenue and expense, which means we do have two numbers. But the two numbers
are not going to be on the horizontal
and vertical axis, like an XY scatter. Both numbers are going to be
plotted on the vertical axis. Also, notice I have
number formatting displaying the number without
two decimals, like up here. So if that's the case,
the chart will pick it up. Click in a single cell,
insert, over to charts, dropdown for line. This time, I'm going to use
just the first one, line. I'm going to click on the chart
title and type, Wind Sport Incorporated Sales, and Enter. Notice right away
we see that there are zero decimals displayed. We see down in the
bottom, because we have two numbers on the vertical
axis, there is a legend. I'm going to leave that
chart just like that. All of the elements are working. There's no chart junk. I'm going to move
this to the side. I can clearly see there
is some chart junk. I didn't spell
this right, so I'm going to click, come
up to the formula bar. Look at that, it's not there. That's why I always like
typing it into the cell. No problem. Now, I'm going to click a
second time in the chart title. I see the dotted line. Now, I can start typing
inside the chart title. Now once I have edited this, I'm
going to click off to the side. Now, one last line
chart-- it's very common we have individual
date, dates, or end of the month dates or years. I'm going to click in a
single cell, go up to insert, over to charts, and
the line chart-- I'm going to select
the marker one. Click. Instantly, I can see
if there is a problem. And what happened is the line
chart was trying to be polite. It sensed that there
was two numbers. And since this is
a line chart, it thought it was supposed
to plot both numbers on the vertical axis. Really, those should
be our category labels along the bottom
horizontal axis. So there's two ways we
can edit the source data. And this is really the
most important trick because lots of
times, our charts do not come out
how we want them. Select the chart, go
up chart tools, design. There it is. Select data. Or you can come and right-click
the chart, select data. And this is the real
power of charting. We have total
control over series-- those are the numbers plotted. Notice we have year and sales
because it plotted two numbers. Over here, category. Those are the horizontal labels. The first thing we want
to do is select year and we can remove,
edit, and add. We want to remove. If it's got the wrong range, you
can select this and click edit. Sometimes we have to come
here and add certain ranges from our spreadsheet. Now, we can come
over and click edit. Sure enough, to
access label range, I can simply come
over and highlight just the labels I want. And there we go. Click OK. Click OK. We're going to leave the
chart title as sales. Click on the legend, Delete. And there we go, we see the
up and down trend over time. In this chart, we
definitely wanted to use that select data
or right-click select. That oftentimes is
the key to making sure your chart is pointing to
the right data from the cells. Now, we want to go look
at our last chart type-- XY sheet. And we have hours
studied and test score. That means we went out and
collected a sample of data from students. This student studied 15 hours
for this big test and got 95. This one studied 22 and got 195. This one studied two
hours and got 51. Once we have these pairs
of numbers, X and Y-- X and Y, we can plot
it and visualize it to see if there is a
relationship between hours studied and test score. Now, I'm going to
click in a single cell. I'll go up to insert, over to
the chart group, the dropdown, and there it is. When you have
sample data, you use the markers, the scatterplot. When you have a model, like
a fixed cost, variable cost model that you
built with formulas that have an X and a Y,
then you use the line. Now in the class after this-- business 218
spreadsheet construction or the Highline class here
at YouTube, or actually some of my other classes-- we do the fixed cost,
variable cost analysis and use that line. But when you have sample
data and you're simply looking at a visual of the
relationship between two numbers, you use the scatter. So I'm going to click that. Now, we look at this
and we immediately want to be able to
pick out what is terribly wrong with this chart. When I look at this
chart, I see some numbers, but I have no idea what
those numbers mean. So we absolutely want to
come up to our green plus and add the chart
element, axis titles. Now when I check
this, it immediately adds two axis titles and the
Y will always be highlighted. So immediately, we can
type an equal sign-- that shoots me up
to the formula bar-- test score, that
is our Y variable. I click on that
cell and hit Enter. Now, I come down to the
X label, equal sign-- that shoots me up
to the formula bar-- and hours studied. Enter. Immediately, I want
to come up and change the label in the cell. Hours studied, F2,
Space, equal sign, Space. That is our X variable. Tab, F2, Space,
equal sign, Space, Y. That is our
predicted variable if we were doing regression
and creating a regression line. Now, we have our labels. Already that is a
hundred times better than when we first clicked the
button for the scatter chart. Because now, we can clearly
see hours studied, test score. Now, we want to click
on the chart title. And actually, I want to click in
the cell, F1, and type, title, Colon, Tab. And the title is
going to be, is there a relationship between hours
studied and test score. Enter. Now I'm going to click on
chart title, equal sign, shoots me up to the formula
bar, I click in G1, and Enter. Now if you want to
change the font, there's a few
places we can do it. I'm going to come up to the
font group, click the dropdown, and choose, like, 11 or 12. How about 11. And there you go. There's our XY scatter. And we're looking at this. The whole reason that this
is such an amazing visual is now, from all
of this raw data, we can see if there
is a relationship. What happens as we
increase our study? Does test score tend to go down? Or does test score
tend to go up? Well, it looks like
it tends to go up. As the number of hours
studied increases, it looks like the
test score increases. Now in a stat class, we'd build
the slope and the intercept, and build the line
and everything. But if you have a
chart, you can simply come to any of the
markers, click on them, right-click, and
say, add trend line. There's also, over in the
green plus, an add trend line. I'm going to right-click
and point to add trend line. Look at that. Now, over in our task
pane, it says linear. I'm going to accept that. And I'm going to display the
equation and the R squared-- the goodness of fit--
right on the chart. Now, I want to click on the
grid lines inside and Delete. Click on the vertical
grid lines and Delete. Click on the equation. And with my move cursor, I'm
going to move it down here. Now in a stats class, we'd talk
about intercept, slope, and R squared. But this isn't a stat class. It simply is learning
how important it is to visualize data. And when it comes
to XY scatter, they have some really awesome tools
built-in right to our chart. All right, that's XY scatter--
when you have two numbers and you want to plot the
relationship between those two numbers. Now, let's go over
to one last example. I'm going to click on RC. And RC stands for
Recommended Chart. This is a new feature
they added in Excel 2013. Now notice, we have
not summarized this. If I want to figure out
what the total for each one of the products is
and then make a chart, well, there's an
intermediate step. I have to, either with
formulas or pivot table, create the total
for each product. Well, we can now, with this
transactional data set, click in a single cell,
go up to insert, and there's recommended charts. I click on this and it, in
essence behind the scenes, you can see that
pivot table icon. It goes and, behind the scenes,
builds a bunch of pivot tables, suggesting ones
that you might want. There's average of revenue. This one right here
is count of revenue. This one is sum of revenue. That's pretty amazing. Now, that one's actually
plotting individual transaction and that's not what we want. But watch what happens
when I click on this. It'll actually
insert a new sheet. So, I'm going to click
on that, click on OK, and it inserts a new sheet. I'm going to scroll over. And it built the pivot
table and a chart connected to that pivot table. Now, I'm immediately going to
come down here, double click, and call this product R for
product report, and Enter. If I click in
here, I clearly see there's the pivot table fields. When I click on the chart, it
changes to pivot chart fields and instead of listing
column and row, it lists series and category. Now, that's pretty amazing
with a single click. And you could format this
as you'd like, including-- there's this important
button over here-- field buttons,
you can actually-- if you don't want those, you
can simply toggle them off and you could format
that as you want. Now, there are a bunch
of homework problems over here if you want to
practice for charts in Excel. Now in this video, we saw a
bunch of charting examples, including the amazing
recommended chart that builds a pivot table and a
chart from a transactional data set. We talked about the XY scatter. Remember, when you have
two variables, X and Y, and you want to see
the relationship, go ahead and plot it, and
remember to put your X and Y labels over on the line chart. We saw that line
charts are good when you have a single number
on the vertical axis and you want to plot it
against, almost always, a time category on
the horizontal axis. Then over on column and bar,
we talked about bar and column to compare numbers across
categories, as opposed to a pie chart. Then, we saw the clustered
column and stack column, where the stack
column emphasizes the horizontal labels
and the clustered column emphasizes the legend items. All right, that's a
little bit about charting. And this is our second to last
hour, penultimate Excel basics video. In our next video-- our
last Excel basics video-- we'll have Excel basics 25. And be sure to tune in
because it'll be a fun one. All right, you
liked this video, be sure to click that thumbs
up, leave a comment, and sub because there's
always lots more videos to come from ExcelIsFun. We'll see you next video.
