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.
