Welcome to Office 2013
class video number 43.
If you want to download this
workbook and follow along,
click on the link
below the video
or go to our class website.
Hey, we're still studying Excel.
This is Excel Basics 25.
And we get to talk about
Excel 2013 charts and graphs.
Now in 2013, they have
changed the charting engine.
So there's a bunch of exciting,
new improvements for charts.
We're going to talk about, in
this video, column, bar, pie,
line, X Y scatter,
sparklines-- oh,
and the amazing
recommended charts.
Now we're going to start
off by asking the question,
what do charts do?
Then we'll look at
different chart elements.
And then we'll talk about
what makes an effective chart.
And then we'll go
on and see examples
of all these different charts.
All right, let's go over
to the sheet Pictures.
All right.
So what does a chart do?
Here it is.
Here's our chart.
We went from unsummarized
data, summarised it, and then
created a chart.
And what charts do
is they visually
portray quantitative data.
Now, quantitative just
means number data.
You've got to have numbers if
you're going to make a chart.
This is a column chart, we could
have pie, line, x y scatter.
All of those charts have to
have numbers in order to plot.
Now, you have to have labels
also for an effective chart.
Labels below, so we know
what these numbers are.
Now what does this do?
This is like a picture
saying a thousand words.
By creating a chart, we
have a visual portrayal.
I can quickly see the
tallest one is in June,
and it was website sales.
Looking at these
numbers here, that's
hard to pick out quickly.
Picking out the second
biggest one, hard
to do when you're
looking at the numbers.
But boom, the chart
does it immediately.
It's August, in store sales.
So what do charts do?
Give you a quick
impression of number data.
Now let's scroll down here.
And we want to be clear about
what all the different chart
elements are.
Now, this is a column chart.
We'll look at other types also.
Chart title.
These heights for the columns
come from the numbers.
So the numbers always
go into the chart
and determine the essential
part of the chart.
Vertical axis.
Horizontal axis.
In this case, we have
numbers on the vertical axis.
The numbers, both here and
the heights of the columns,
are going to be called series.
So you've got to
take note of that.
The numbers are series.
This horizontal axis and even
these items in the legend here,
these are categories.
Sometimes we could
switch this chart,
and the categories will
be here and the numbers
will be down here.
But they call these categories.
When we look inside the chart
at the dialogue box that's
pointing to the cells, the
numbers will be called series,
and the labels will
be called categories.
Now let's scroll up here.
Notice we have a
legend, and we have
categories on the
horizontal axis.
How did they get there?
Well, notice, here
are our row headers.
These are our column headers.
This is our summarized data
set, either with the SUMIF
formula or pivot tables.
Now, earlier in the class we
learned all about pivot tables
and all about the SUMIF
functions to summarize data.
But check this out.
These are the row headers.
These are the column headers.
If you have more row
headers than column headers
or they're exactly
the same in number,
these row headers will show
up on the horizontal axis.
If the number of columns
is less than row headers,
those show up in the legend.
Now it's easy to switch
with a single button,
but that's the default set up.
Now, before we go
and create a chart,
we want to talk about what
makes an effective charge.
This is the worst chart I
could have possibly made.
Effective charts come from
eliminating chart junk.
Now, there's all sorts
of chart junk in here.
This is a terrible chart title.
If this is sales
data, then we need
to put sales data for summer
sales or something like that.
How about this?
We have a legend over here.
It says May, June, July.
But May, June, July are
also in the category labels.
That's unnecessary repetition.
You don't want
unnecessary repetition.
That's chart junk.
Crazy colors and patterns.
That's chart junk
we just don't need.
How about here in these labels?
It says Series1.
Series1.
That means someone didn't
label this properly.
Anytime you see Series1, Series2
in a chart, that's chart junk.
And the cardinal rule for pie
charts has been broken here.
Pie charts show you
percentages or proportions.
Parts compared to the whole.
So anytime you to tilt
the chart on its side,
then a pie chart
has distortions.
This pie piece,
12%, looks bigger
than this pie piece, 14%.
So you don't want to tilt
a pie chart on its side.
That's chart junk.
Now in every example-- we have
about 10 charting examples
here-- we will run
into some chart junk,
and we'll make sure
that we eliminate it.
Remember, our goal is to
articulate a message quickly.
This is a picture.
We want someone to look at
it and immediately get it.
Right here, we can look at it.
Immediately get it.
It's the summer sales, and
each one of these categories
down here has a column height.
Website sales in
June is the biggest.
In store sales in August looks
like it's the second biggest.
And the smallest one is
probably in store sales in May.
All right.
Let's go over and
create our first chart.
I'm going to go
to column and bar.
Now, on this sheet, we
have our unsummarized data.
And here we've
already summarized
using this SUMIF function.
Now notice, we
have sales channel
at the top of each column.
So those are our column headers.
And at the front of each
row are row headers, month.
So this is adding
with two conditions.
Now we'd like to make
a chart, and we'd
like to show, for
each month, the number
for each sales channel.
Now, notice, along
the horizontal
axis we'll have our months.
So we're going to use a
column chart, because we
want to show differences across
the months or the categories.
Now for cross-tabulated
tables like this,
Excel charting is perfect.
You simply highlight the
labels and the numbers, not
the totals, and go
to insert, and there
is our charting group.
Next to the columns icon,
we'll click the drop down.
And we're going to select the
first one, clustered column.
You gotta be kidding me.
Just like that?
It almost came out
perfect right off the bat.
That's because the
charting engines
are perfect for this
cross-tabulated type of table.
Now I want to move this
chart, and there's trouble
sometimes with moving.
If you click inside the
chart in that move cursor
click-and-drag, you
might accidentally
move something inside the chart
instead of the whole chart
itself.
Control-Z when that happens.
You've got to point
to the outside edge.
So now I'm going
to click and drag.
Now, the column chart
shows us the differences
across our categories.
Our legend tells us what each
one of the color coding means.
It's looking perfect
right out of the box.
The one thing that's chart
junk here is Chart Title.
That doesn't contribute
anything to our chart.
So we want to come and
click on the chart,
and now we want to change it.
There's three ways we
can change a chart title.
With a solid line
around the chart title,
come up to the formula
bar, and you simply
can type whatever
title you want.
I'm going to type
WinSport Summer Sales.
And when I hit Enter,
our chart title is done.
Another way is to
click a second time,
and when you see
a dash line, you
can simply highlight and
type whatever you want.
So Summer Sales.
I'm going to click
outside the chart title.
A third way-- and this
is my favorite way--
is we can link this
label to the cells.
Now, right now, all
of these numbers--
the heights of the columns,
the category labels, the legend
labels-- all of
this is connected.
If I change any of it here,
the chart will update.
Well, the chart title
is not connected.
But we can connect it.
Now, with a solid line
around the chart title,
come up to the formula bar.
Click, type an equal sign,
and then click on cell G1.
Now it puts the name of the
sheet, an explanation point--
that's part of the sheet
name-- and then G1.
Hit Enter.
And there, we've linked to
our chart title to the cells.
Now we want to create
a second column chart
to show differences
across categories.
And watch as I would like to
highlight May to September,
and then hold my Control key
and highlight the totals for May
to September.
Now, here's the problem.
Don't do this.
Don't start with your cell here
and then hold Control and click
on your labels and then
click on your numbers.
You have that extra cell.
Excel charting won't
know what to do.
So Insert Column,
Clustered Column.
It gives you a message.
I don't know what to do.
So when you do that,
highlight the labels first,
then hold Control and
highlight the numbers.
All right.
You ready?
Insert, chart drop
down, Clustered columns.
I'm very carefully going to
point to the outside edge.
And when I see my move cursor,
I'm going to click and drag.
Scroll down.
Do the same thing.
Highlight the titles, scroll
up, find my formula bar,
equals sign, click
on cell G1, Enter.
Now I'm going to scroll down.
Now, let's talk about
these great formatting
buttons off to the side.
The Chart Elements, if
I click on the plus,
I can add an axis title,
which we don't need here.
Chart title, we already have.
Data labels.
Look at that.
It'll put the numbers right
at the top of the column.
I'm going to select that one.
That's pretty cool.
There's some other
elements we could add.
Oh, that's how you add a legend.
We do not need a legend.
That's chart junk.
Or a trend line.
That's for a different
type of chart.
So a bunch of chart elements.
And this is actually a
really big improvement
over earlier versions.
These items used to
be up in the ribbons,
and they were
really hard to find.
Now they're right
next to the chart.
We could also format if I
click here and do style.
I'm not going to do any these.
Once you picked a
style, you could
go over to color, if you
wanted, and select a color.
I'm not going to use any
of the built in styles.
You can also filter.
This is amazing.
Select All, June,
August, September.
Click Apply.
And instantly, I can see
just the ones I want.
Come back.
Select All.
Click Apply.
That looks just like our
filter we learned about.
All right.
These icons off
to the right don't
contain all of the
formatting chart elements.
But here's the deal.
You can click on any
chart element title--
vertical axis, horizontal
axis, columns, labels,
whatever it is.
Once you select the item--
and you've got to be careful.
Watch this.
If I click a second
time, now I've
highlighted just
that single column.
I could do the same
thing for labels.
If I wanted to add bold all
of them, I would Control-B.
Now I'm going to Control-Z.
If I wanted just one,
select it a second time.
I've highlighted
just one element.
Control-B. Now I'm going
to-- Control-B is bold.
Control-Z to undo.
I would like to
format the columns.
So I've selected that
particular element.
Now, sometimes it's
hard to click and select
the element you want.
Once you have one
element selected,
you can use your arrow
keys to toggle through.
And when I toggle through and
I see my columns, all of them
selected, now I want to either a
right click Format Data Series.
That opens up task pane.
And a lot of the formatting
is now done with task panes.
Now, I want to close this,
because there's another way.
Once you select your element,
you can use Control-1.
And the way I remembered
it is remember,
if I have a cell on a Control-1,
that's format the thing
that I've selected,
which is a cell.
Here, if I format-- if I select
the column when I Control-1,
it opens up not a
dialog box, but the task
pane Format Data Series.
And watch this.
If I click on the
labels, oh, it changes.
Now there is
something that's more
difficult about these
panes than earlier version.
So I've selected a column.
In earlier versions,
notice it's-- the green is
on the series options--
at this series options.
Well, there's a bunch
of different things
that are available
to change when
you have a column selected.
Here's some of the effects.
We're not going to
use 3D here or fill.
There's the paint bucket.
Fill and border.
In earlier versions-- and I can
use my collapse and uncollapse.
In earlier versions,
the dialogue box
would list fill, border-- all
four of those in series options
all to the left.
And so it was little bit easier.
So the trick for us learning
in 2013 is you select an item,
and you can't find
what to want over
in the task pane for formatting,
just click through these icons
until you find the
thing you want.
All I know is sometimes I get
totally lost in the chart,
but I remember, oh yeah.
Just come over and
click, click, click,
until you see the
thing you want.
Now I want to click
on the column.
And what do I want?
I want fill.
So that's not it.
Series options.
OK, paint bucket and fill.
Now I want to come down here--
and this is a great option.
Vary colors by point.
I'm going to boop.
And there you go.
That's looking pretty cool.
Now there's some
chart junk here.
You have to pick this up.
There's unnecessary repetition.
If we have numbers here,
we don't need them here.
Now you can choose.
Sometimes people
think this is clutter
and this is less cluttered,
and then the horizontal lines
are good because you
can kind of line up
with the height of the column
and the axis over here.
But I want these at the top, so
I'm going to click on the axis
and use the keyboard
shortcut for Delete.
Delete.
I'm going to try and select the
horizontal lines, and Delete.
That's looking good.
Remember, we want the minimum
amount of stuff in our chart.
No chart junk.
We just want only
the elements that
will articulate our message.
And all we want is the months,
the height, the number,
and something that
says what it is.
Now I want to copy
this chart and then
look at a different chart type.
The way you copy is you click
on the edge and Control-C
to copy, and then
come down here and I'm
going to Control-V.
I'm selecting a cell.
Don't click the chart,
because then you'll
paste a chart inside of a chart.
Cell, Control-V.
And now I'd like to
change this from a column.
Now, what does a column do?
It shows us differences
across categories.
Well, a column is
similar to a bar.
Remember, columns hold
up Greek architecture.
They're vertical.
Bars are horizontal.
Now, you've got to be careful.
Sometimes in statistics
and other fields,
they'll call this a
bar chart, which it is.
But in Excel, this
is called a column.
And I always remember, columns
hold up Greek architecture.
But I want to change
this to a bar chart.
Right click, Change Chart Type.
Or you could go up to Design,
and there's Change Chart Type.
Right click, Change Chart Type.
Over here on the
left, I select Bar.
And we want Clustered Bar.
Click OK.
Now, bars are great.
They are just like
a column chart.
They emphasize differences
across categories.
But notice the categories
are listed here
on the vertical axis.
Ah, but horizontal bar charts
emphasize the differences
more forcefully
than a column chart.
Also, if you ever have
really long labels,
the big trick is
use a bar chart.
Now I want to change this.
These are very skinny, so
I'm going to do our trick.
Select what I
want, and come over
and look through the task pane.
OK, so it's not fill.
And it's definitely not effects.
Maybe it's this one.
And sure enough, under
series options, gap width.
I'm going to decrease it.
I'm just going to come in
here and say 100%, Enter.
And there I have it.
That's looking much better.
Hey, I kind of like that.
That's a cool bar chart.
Now I'm going to
come up here and show
you two more bar
and column charts.
Notice, category in
the legend, category
on the horizontal axis.
Actually, I'm going to
show you three charts.
So I'm going to click on the
outside edge, Control-C. Scroll
down.
Control-V. How about
just switching?
You want to see Sales Category
on the horizontal axis
and Months in the legend?
You can come up to
design and there it is.
Switch Row and Column.
And just like that.
Hey, that's awesome.
Let's scroll up.
Control-C. Come down here.
Control-V. Now,
instead of having
a legend and a horizontal
axis separated,
I'd like to see all of the
elements for June together.
So we're going to
change this chart type.
Right click, Change Chart Type.
And instead of clustered,
I want stacked.
Now there's a stacked 100%.
I really don't like those,
although they have their place.
It's like a pie chart,
but it's using columns.
I like the stack column, because
it'll give us-- if I click OK,
it'll clearly show us which
month had the most sales.
So clearly, this
is the tallest one.
So it really gives us
the three elements--
the different sales
channels-- clearly in June.
Website sales were the biggest.
And it gives us the tallest.
June is bigger than
all the other months.
That's a stacked column.
Control-C on the outside edge.
Control-V. And as
you guessed, there
can also be Change
Chart Type, Bar.
Stacked Bar.
Click OK.
Oh, I think I liked
increasing the gap width.
I'm going to change it to 70.
Enter.
And just like that.
All right.
So that's pretty amazing.
We saw on this sheet a
bunch about column and bar.
Now let's go over
to the sheet Pie.
I'm actually going to scroll
over here and go to Pie.
Now, pie charts compare
parts to the whole.
And I would like to
see for each month.
So our labels, then our numbers.
And I'd like to see each one of
these numbers in a pie chart.
So go up to Insert.
Pie drop down.
And we're going to use the flat
one, the two dimensional, not
the 3d.
That'll distort the proportions.
All right.
And in general rule, once you
get this past seven elements,
then you can use
one of these two
to explode all the smaller
elements into a second pie.
But we don't have that many now,
so I'm simply going to select.
Now let's do some formatting.
Click on the chart title.
Formula bar, equal, G1, Enter.
Select Legend, Delete.
How about chart elements?
Data labels.
That's pretty cool.
But let's go to more.
And let's try Category
Name, since we
don't have the legend,
and percentage.
And what's so
nice-- look at that.
Instantly, it's calculated
the percentages for us.
Now, that's kind of cluttered
and all in the center--
hard to see-- so we'll come
down and say, Outside End.
Oh, that is cool.
Now, you know, you
could even spin this.
Sometimes you want
to spin the pie.
Maybe we'll try and see if we
can get these labels not right
under.
So I'm going to
come over to angle,
and you can click and drag.
And it will actually
spin your pie.
Look at that.
So there we go.
And I'm going to go
down just a little bit.
All right.
So a pie always shows parts
compared to the whole.
It's good for percentages.
All right.
Now let's go over to our
next sheet, Line Chart.
Now, a line chart is great
when you have some category
labels and some
numbers and all you
want to do is see the numbers--
for example, Quarter 1
to Quarter 4-- how
they're going up and down.
Now, we're going to
highlight this data set.
We've got the field
names at the top.
Labels and numbers.
And we're going to go
up to Insert, Charts.
And now there's
Line and Scatter.
And you don't ever want
to get these confused.
Line is when you
have one number.
Scatter, or x y scatter, that's
when you have two numbers.
Out the x-axis a certain amount,
up the y-axis a certain amount.
All right.
So Line.
We have one number.
Click the drop
down, and I'm going
to select-- how about markers.
There we go.
Up and down through
the quarters.
Now, this is not a
very impressive chart.
We'll have a bigger set
of data numbers over here.
But there is something
that could vastly
improve this chart.
It's kind of floating
above nothing.
So maybe we could
change the axis here.
Select, and now we're going to
go hunt through our task pane.
It's probably not under Fill.
Not under Effects or Properties.
How about Axis Options?
So I'm going to click this, and
there's a bunch of things here.
Ah, there it is.
Min and max.
So guess what?
We're going to look over here,
and we're going to guess.
OK, so the minimum,
let's say 500,000.
Now notice something.
This is a text box
that gets a number.
And most of the
text box we've seen
have that collapse
button, which means
you can link this to the cells.
This is not linked.
It's in essence hard coded
into this dialogue box.
I'm going to hit
Tab, and we can see
it perfectly changes our chart.
The one thing you have
to be careful of when
your hard coding numbers into
this axis option-- or even
earlier versions, it
was the same problem--
is that if the data
changes and you
get numbers that are
less than 500,000,
it just won't show them.
This doesn't update.
This is a number
hard coded, so you
have to remember to
come and change it later
if you want it changed.
But that is a way to
change-- in essence,
truncate that vertical axis.
Now, I'm going to
point to the corner.
And when I see that diagonal
white arrow, click and drag.
Now I'm going to
drag this down here.
Now let's try
another line chart.
We have-- even though it
looks like two numbers,
these are categories--
equidistance categories
for a horizontal axis.
And then we want to see
the up and down line
for our sales number.
Insert, Chart Group, Line.
Select the same one.
Ooh, look at that.
This is going to be one of
the most important tricks
for charting.
When we select data
and select a chart,
often times it
won't guess right.
So the most important
trick in charting
is knowing where
the dialogue box
is that will allow us to change.
Because what's happening here?
It's plotting two lines.
It tried to put this
as a line-- one line,
and this as a second line.
So now, here it is.
I need to change this.
I need to say, hey,
chart, these we're
supposed to go down on
the horizontal axis.
So I go up to Design
and there it is.
Select Data.
Now if you're in
earlier versions--
this series is not
about earlier versions.
Actually, that button used
to be over on the left,
but now it's on the right.
Select Data.
And this dialogue box is
the most important thing
about charting.
We can come and select
any one of the series.
And notice, we always put
field names at the top.
We've learned throughout this
class to label everything.
Name things smartly.
So when we come to this
dialogue box, we're not tricked.
It doesn't say Series1, Series2.
I know Year Sales.
So Year, I need to remove that.
So I click Remove.
If I needed to edit-- like
the sales range wasn't correct
or we added more records
later-- we would come and edit.
And look at that?
There's our collapse buttons.
So the actual chart--
labels, numbers,
all that data for the
chart-- is linked.
If this changes,
our chart dialog box
will see that change.
There's the series name,
and there's the numbers.
That's perfect for us.
I'm going to click OK.
We were just
looking at the edit.
This is the edit we want.
Click Edit.
And now Axis Label Range.
Click OK.
Notice something else.
This says Series.
Remember, the word
Series means the numbers.
All the series there could be
listed here, those are numbers.
Horizontal category axis labels,
even though they're numbers,
they're still going
to be our categories
for the horizontal axis.
Category.
Series.
Click OK.
Oh, that is much better.
It got it perfect.
Now, is there any chart
junk in this chart?
Probably.
It says Sales and Sales.
You know, I'm going to
click here and Delete.
Then I'm going to
click on the legend.
Now I have to go look
through the legend.
It's probably under
Legend Options.
Ah, there it is.
Show at the top.
So that's looking pretty good.
Again, the Select Data button
here in the Design Chart Tools
ribbon is the most
important trick
you could know with charts.
All right.
Let's drag this down here, and
let's look at one more example.
I should have cut and paste.
All right.
So here-- wait a second.
Two numbers?
Yeah.
We can have two numbers
in a line chart,
but each one number
will define a line.
That's much different
than an x y scatter.
So for line charts,
you certainly
are allowed to have two numbers.
And this'll be the category
for both revenues and expenses.
So I'm going to highlight.
Insert.
Chart Group.
Go up to the Line.
I'm going to do the
marker one again.
All right.
So let's see.
Delete.
Click on the legend.
Show at top.
Click on the axis.
Actually, let's change
the number formatting
in the actual cells, and watch
that it will change here.
So if I change--
highlight all of this,
Control-1, zero
decimals, click OK.
Instantly, because
it's linked, it
recognizes that number
formatting and changes it.
If we ever wanted to change
the number formatting,
we'd come over here,
and actually it's
in the series options.
And we have tick marks,
labels, and numbers.
Down here there's a whole number
category linked to the source.
If this is not
checked, then you can
put any of your normal
number formatting
and select from
this drop down here.
All right.
So line charts.
One number for each of the
lines across some category.
Click on the corner
of the chart.
Control-X.
Click in a cell.
Control-V. So we can
certainly move a chart,
just like we can copy a chart.
So line charts.
Now let's go look
at x y scatter.
I'm going to scroll over to x y.
Now we have two numbers here.
And x y scatter charts-- if you
know algebra or linear algebra,
you're doing regression
analysis in statistics--
the charting for x y scatter
is absolutely amazing
and a powerful tool.
But even if we don't do
algebra and statistics,
we can still use
a scatter chart.
And here's how.
If we have two sets of
number-- this is hours studied
and this is test
scores on a test.
If we suspect there might
be some relationship
between the two
numbers, we can simply
plot it as a scatter
chart and see.
We would suspect that
the more hours you study,
the higher your test score.
So let's test it.
Let's just plot it.
Now, the one trick
is you do have
to put the x
variable to the left.
If you put it to the
right, then you'd
have to use the Select
Data button and change it,
because Excel will always assume
that the x variable is first.
Now, you could think
about it this way, too.
Which one of these variables do
you think influences the other?
Yeah, probably the
more hours you study,
the higher your score.
So you put the variable
that has influence first,
or the x variable.
All right.
Control-* to highlight
the whole table.
Insert.
There's our little
drop down for scatter.
Now, the dots or
markers, that's the chart
you use if you have sample data.
If you have a model-- see, for
example, this is a sample data.
You can see that
makes no sense at all.
But if you have a model,
like in accounting
you have the fixed cost,
variable cost analysis.
That's a model.
You create a formula
where there's
an x and a y that moves
in a predictable pattern.
That's when you use the line.
When you have actual sample
data that you've collected,
that's when you use the scatter.
So boop, and there you go.
You can see if this is
supposed to be hours studied
and this is test score, it looks
like as we increase the hours,
the scores go up.
If you drew a line, it
would kind of go like that.
As one variable goes up,
the other one goes up.
Now, we've got a bunch
of work to do here.
Even if you don't know
algebra or statistics,
what is wrong with this chart?
Just look at it.
Hit pause.
Now you're back.
It doesn't have any labels.
If someone's looking
at this, they
have no idea what it means.
We need a label here and here.
Now, this is cool.
I love this.
We click the Plus, axis titles.
Much closer and easier
than earlier versions.
And now let's do our trick.
Click up in the formula bar,
equal sign, hours studied,
x is on the horizontal axis.
Axis title, formula bar,
equals, test score, Enter.
So now that is
already much better.
Kind of looks-- as you
increase, this goes up.
Now, let's change
this title up here.
And I don't have
anything in the cells.
We're going to type Relationship
Between Hours Studied and Test
Score, Enter.
So here's a trick.
If you ever go do
algebra or statistics,
you learn how to do
the regression line.
And it takes a long time
to calculate by hand.
But check this out.
You can come to the markers,
and right click the markers.
And it says add trendline.
Or remember, over here?
Click our plus and
add trend line.
It assumes you want a linear.
Let's click on the line,
and look over here.
I'm going to click
that to turn it off.
Trendline Options.
Ooh, you could do fill
and line and effects,
but we don't want
to do any of that.
We just want to come over here.
Linear is the one it assumes.
And if you ever take
algebra or statistics
and learn how to create
the equation and r squared.
And if you do it by hand,
it takes a long time.
And the charting engine does
it automatically for you.
It's amazing.
Y equals 1.513x plus 65.
The 1.5 is the slope, the
65 is the y-intercept,
r square is the influence the
x has on the y, not causation.
All right.
So that's probably
more than you ever
wanted to know
about x y scatter.
But it is a very useful chart.
I'm going to click and
drag this over here.
Now let's go to more charts.
We've got to talk
about sparklines.
I'm going to go over to
sheet called Sparklines.
Now, here we have
some test scores.
And we want a little cell
chart called a sparkline.
Now, the famous author about
graphing and charting, his name
is Edward Tufte, and he wrote a
book, Articulating Quantitative
Data Visually.
And he invented this
little sparkline.
Sparklines will actually
have a little chart here.
And if we do a column chart
for each one of these,
we can get a quick
visual impression
of how the data changed
from test one to test three.
So you highlight the cells.
Actually, I'm going to
hold Control and roll
in a little bit.
Insert, Sparklines, Column.
It's asking me where
the data range is.
I'm just going to
give it the numbers.
And then click OK.
Now, very important.
There's a sparkline design,
and there's one, two, three,
and there could
be a different min
and max for each one of these.
And if I'm comparing
them, I need them all
to have the same min and max.
So you've got to go to axis.
It's asking you,
vertical axis minimum.
Same for all sparklines.
Axis.
It's asking you
vertical axis max.
Same for all sparklines.
And so now, here we can
clearly see that test one,
there's lots of low bars.
Not very many high bars.
And then there's more
high bars here, and then
lots more by test three.
So that's called a sparkline.
Hey, you know, here's another
cool sparkline thing we can do.
I'm going to click on a
browser and go to yahoo.com.
And finance, Yahoo Finance.
And I'm going to type GOOG for
Google and get some stock data.
And then over here on the
left, historical prices.
I'm going to come down here to
just the data set they have.
And I want to copy this.
You can't copy just the whole
column, so check this out.
We're going to copy the
field names at the top,
and then copy all the way down.
This will bring lots
of junk with it.
I'm going to try to come
right to the bottom.
And you could pick whichever
stock you want or date range.
Control-C. Alt-Tab.
And instead of just pasting,
I'm going to click in this cell
and go up to Home.
Paste.
Paste Special.
And this is a great trick when
taking stuff from the internet.
Click on Text.
Paste Special, Text.
And then I don't
get any of the junk.
Sometimes there's links.
Sometimes there's
little objects.
Now, I actually want to keep
the date and the adjusted price.
Highlight from Open to
Volume, and then use
Control-Shift-Down Arrow.
Delete key.
And I'm going to
click in this cell,
Control-* to highlight it all.
And I'm going to
point to the edge,
and this is the Move cursor.
Double click.
And I'm going to call
this GOOG Stock Price.
Control-B.
Now watch this.
I'm going to make a
sparkline of all this
GOOG-- this Google stock data.
So I'm going to go to
Insert, Sparklines, Line.
It's asking me
where the data is.
I'll click in the first cell.
Control-Shift-Down Arrow.
OK.
Oh, look at that.
That's terrible.
That's backwards.
I'm going to come here
and sort this column date.
So right click.
Sort.
Oldest to newest.
And instantly now we get
from left to right, right?
Here's our August 16 all
the way down to November 18.
And that would be
a little sparkline.
Now what would
you do if you were
adding a new number every day?
You know what?
We could highlight
this whole thing--
Control-Shift-Down Arrow--
convert it to a table.
Insert Table or
Control-T. Click OK.
And now check this out.
If we come down to the bottom,
as you remember earlier
in this class, Tab allows us to
add a new record to our table.
And I'm going to
type 19-Nov-13--
by the way, that's
just number formatting.
Tab.
And just to show
you that it works,
Let's say tomorrow Google
stock goes down to 870.
And let's see.
Did it update?
You betcha.
That is so cool.
So I have a little
sparkline at the top.
And I have my data set
saved as an Excel table.
And sure enough, that
is picking it up.
All right.
We have one last
amazing chart trick.
RC stands for Recommended Chart.
Now we know this data
set is not summarized.
So we can easily
come over and create
a pivot table or some
formulas, but recommended
charts will do both for you.
Now, recommended charts
cannot do a lot of charts.
But when you have
basic information
like a sales channel,
product, sales rep,
it'll just-- with one
click-- summarize the data
and add a chart.
So our goal is to find
all of the quads, right?
That's $99 there, and
then $66, and then $68,
and add up all these numbers.
Summarize them, with
one condition, right?
So instead of doing
it over there,
we'll let recommended
chart do it for us.
Insert, Recommended Chart.
And it's not going
to get everything.
But again, it will
get some things.
Count of revenue by product.
It actually went
through and counted
how many of each boomerang
product was sold.
If we click on the next one,
sum of revenue by product.
That's exactly what we want.
If we click on the next
one, it says average revenue
by product.
That is totally cool.
So we want the second one,
sum of revenue by product.
And notice this icon
means pivot table.
So it's going to
create the pivot
table and the chart for you.
I'm going to click OK.
And it will put this pivot
table chart on a new sheet.
I'm immediately going to come
over here and call this Pivot
Table Chart.
And that is totally amazing.
Now, we have to probably
do a little fixing here.
I'm going to come over
and this is a pivot table.
So remember, we right
click, number formatting,
not format cells.
Number formatting.
And I'm going to add currency
with no decimal showing.
Click OK.
Oh, look at that.
It picked it up.
Click on the columns.
I'm going to close my
pivot chart fields.
And I want to change the fill.
So I'll come over here.
Vary colors by point.
That is absolutely beautiful.
So there we have a quick chart.
We didn't have to summarize it.
It summarized it for us.
All right.
So charts.
We saw how to do
recommended charts.
Sparklines.
X y.
That's when you
have two numbers.
Line chart.
That's just one
number and a category.
Pie chart.
That is comparing the
parts to the whole.
Column and bar.
When you have a
cross-tabulated table,
oh, column charts are perfect.
Comparing numbers
across categories.
Here's a single series.
We turn it on its
side and call it
a bar chart when we want to
emphasize the differences.
And sometimes, we even
stack them, column or bar.
All right?
We'll see you next video.
