IntroductionWelcome| 00:04 | Hi! I'm Dennis Taylor and
welcome to Excel 2010: Charts in Depth.
| | 00:09 | Creating charts is one of Excel's
most powerful yet easy-to-use features.
| | 00:13 | In this course, we'll be looking at the
concepts underlying charts and the tools
| | 00:17 | to implement those concepts.
| | 00:19 | I'll show you how to create standard
useful charts very quickly, and then how to
| | 00:24 | fine-tune those charts with a
variety of tools and features.
| | 00:27 | We'll see how to give greater
impact to your charts with pictures and
| | 00:31 | shapes, and then examine the most
effective use of titles, labels, legends,
| | 00:35 | axes, and gridlines.
| | 00:37 | We'll look at analytical tools that'll
help you make sense of chart data and we'll
| | 00:41 | show you how to use an exciting
new chart-like feature in Excel 2010,
| | 00:45 | Sparklines ?-charts inside of a cell
that reveal trends and totals at a glance.
| | 00:52 | If you're not using charts, then you're
missing out on one of the most popular
| | 00:56 | and easy-to-use features of Excel.
| | 00:59 | So join me in Excel 2010: Charts in Depth.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you are a premium member of the
lynda.com Online Training Library or if
| | 00:04 | you're watching this tutorial on a
DVD-ROM, you have access to the wxercise
| | 00:09 | files used throughout this title.
| | 00:11 | There is one file per chapter and as
I'm opening files here, you'll be seeing
| | 00:17 | the same file names
yourself, one file per chapter.
| | 00:21 | As you open a file, you will see that
there are a number of sheet tabs. Many of
| | 00:26 | these will be referred to in the
training here and there, some of them will not
| | 00:30 | be used at all, but feel free to take a
look at them and use them if you wish.
| | 00:34 | If you are a monthly subscriber or
annual subscriber to lynda.com, you don't
| | 00:38 | have access to the exercise files,
but you can follow along from scratch
| | 00:43 | with your own assets.
| | 00:45 | Let's get started.
| | Collapse this transcript |
|
|
1. Understanding Excel Chart ConceptsIdentifying chart elements like plot area, chart area, gridlines, and legends| 00:00 | Even before actually creating charts
in Excel, it's a good idea to start
| | 00:04 | becoming familiar with some of the
various components of charts and some of the
| | 00:07 | terms you're going to see
repeatedly as you work with Excel charts.
| | 00:12 | On this particular worksheet here,
we see some data I've highlighted in
| | 00:18 | green and this data is being
reflected in a number of different charts on
| | 00:21 | this same worksheet.
| | 00:23 | The chart to the right of
the data is a line chart.
| | 00:25 | A very common type. I'm going to click on it.
| | 00:28 | And one of the things you'll start to
recognize as you work with charts is, as
| | 00:31 | you move the mouse pointer around, and
you don't need to click and I haven't
| | 00:36 | clicked here, but the term Plot Area
has appeared just below the cursor.
| | 00:40 | If I slide out into the green
outer area, that's called a Chart Area.
| | 00:45 | And nearly every chart will have
those two areas. And I wouldn't say you
| | 00:49 | memorize these terms, but you will
quickly start to become familiar with them.
| | 00:54 | And what's this title here?
| | 00:55 | Well, it's a chart title.
| | 00:57 | And how about this line. What's that line?
| | 01:01 | It's a series.
| | 01:02 | If you're near a point, it will
actually refer to a point and give you a
| | 01:06 | reference back to the actual data as well.
| | 01:08 | Here's a different series.
| | 01:10 | There is a pie chart off to the
right, and there is a chart area.
| | 01:14 | The chart area is the outer perimeter
area near the border. The inner area, and
| | 01:20 | we don't really have to find it all the
time, but here it's called a plot area.
| | 01:24 | Now that's kind of vague.
| | 01:25 | Think of that pie chart as being in a rectangle.
| | 01:28 | And what's this piece of the pie here?
| | 01:30 | It's part of a series, and
you'll see these more and more.
| | 01:34 | Moving back onto the line chart and
maybe to the lower left corner, a very
| | 01:37 | common type of chart, a column chart.
| | 01:40 | What do we see down the left-
hand side? A vertical axis.
| | 01:44 | How about below the data? A horizontal axis.
| | 01:48 | What do we see to the
right many times in charts?
| | 01:52 | You might see an individual series.
| | 01:54 | If you move more toward the edge, that's a
legend, and you've seen those on maps before.
| | 01:59 | The chart to the right of this is a 3D
column chart. Colorful, not always the
| | 02:04 | most readable, but what do we have in
here that might be a little bit different?
| | 02:09 | As I slide the mouse right
here, that's a side wall.
| | 02:12 | I'm sure enough a back wall, and by now
you've probably figured out what's on the bottom.
| | 02:17 | It's the floor of the chart.
| | 02:20 | So it's not to obsess over this or to
go to all the different chart types in
| | 02:24 | Excel and figure out what every single
item of it is called, but these terms
| | 02:28 | generally fall under the
category of a chart element.
| | 02:31 | each one of these is a chart element.
| | 02:34 | And there will be times in certain charts
when you need to make changes to these elements.
| | 02:39 | Now something new, or better yet
something resurrected in Excel 2010, when you
| | 02:44 | want to make a change to a chart element,
if you have been using charts in Excel
| | 02:48 | 2007, one rule of thumb has
been right-click on an element.
| | 02:54 | Suppose in this line chart you want to
make a change to this line somehow.
| | 02:58 | If you right-click the element,
you can go to a series of choices.
| | 03:01 | And the last one is always the
word Format followed by the item that
| | 03:06 | you right-clicked on.
| | 03:07 | Then you can go there and explore
within a dialog box some changes you
| | 03:12 | might want to make there.
| | 03:13 | Now you can still do that and I'll
show you how to do it here in 2010.
| | 03:17 | But in 2007, what a lot of people
missed was the ability to double-click on a
| | 03:23 | chart element and get to a dialog box.
| | 03:26 | That has existed in prior versions of
Excel and now it's back in 2010 and
| | 03:32 | generally it's faster.
| | 03:33 | So if I want to make a change to this
line here, I'll point to it and double-click.
| | 03:37 | And there is that dialog box,
and you'll find that very handy.
| | 03:40 | And it often is a fast way to get into
that sequence of steps that you need to
| | 03:45 | get to when you're trying to change an element.
| | 03:48 | There will be times when you try and change a
chart element but you can't quite click on it.
| | 03:52 | It could be a line that's kind of tiny,
and maybe the chart is small and you're
| | 03:56 | trying to click, and maybe you
can't quite click on the line.
| | 03:59 | I was able to do it here, but here
and there that's not always so doable.
| | 04:02 | And there's a chart below this
where it's particularly difficult.
| | 04:05 | One of the series here is incredibly tiny.
| | 04:08 | It's the Miles Per Gallon.
| | 04:10 | And it's hard to click on that.
| | 04:11 | YOu can easily fumble around a little bit
trying to do that because you want to make a change.
| | 04:16 | So something else to be aware of on the
Layout tab, and we will talk in another
| | 04:21 | movie about how this plays out, but
you will see the term Layout and the
| | 04:25 | extreme left side has a drop arrow, and
here are all the chart elements within
| | 04:31 | that particular chart.
| | 04:33 | So here's a variety of them.
| | 04:35 | Again, you don't want to be memorizing
these but I think you'll quickly absorb
| | 04:39 | the meaning of these various portions
of a chart. So if you want to click on
| | 04:43 | this element or that element.
| | 04:45 | Now in doing this I had clicked back
on the line chart, so right now we're
| | 04:48 | seeing the elements within the line chart.
| | 04:51 | If I click back on the Worksheet here,
scroll down to this lower chart here and
| | 04:55 | just click this chart, and then go
to that Layout tab, left button in the
| | 05:01 | Current Selection group, click the
drop arrow next to whatever element is
| | 05:05 | currently selected, and we
see those other elements.
| | 05:08 | So if we click Series "MPG", then we
could go ahead and make some changes there
| | 05:13 | by the choice below it, Format Selection.
| | 05:15 | So sometimes you'll need to go down that path.
| | 05:18 | Now again, some of this is premature because
maybe you're not quite ready to create charts.
| | 05:23 | But becoming familiar with the
terminology in Excel is very helpful not only in
| | 05:28 | this course, but anytime you pick up a
book on Excel, you will see some of the
| | 05:32 | terms that we've already brought out.
| | 05:34 | Chart area and plot area and
series and walls and legends and so on.
| | Collapse this transcript |
| Selecting the right chart type| 00:00 | Excel has a number of different chart
types available and they're not all
| | 00:04 | equally effective in depicting the
data that you've got in a meaningful way.
| | 00:08 | Most Excel users agree that a line chart
like the one we see to the right of the
| | 00:12 | data here is probably the
best type for showing a trend.
| | 00:15 | If you're trying to accentuate the
volume, perhaps a column chart like we see
| | 00:19 | below the data is better, and there's
a variation on that, a 3-D column to the
| | 00:24 | right of that as well.
| | 00:25 | There are other chart types
too that might have special uses.
| | 00:28 | There are stock charts, there are area
charts, and here and there some are going
| | 00:33 | to be more effective than others.
| | 00:34 | And a lot of this is subjective to your
particular take on it, your particular
| | 00:38 | read on it. Sometimes you're trying to
adhere to conventions that your company
| | 00:42 | has used in prior years too for charts.
| | 00:45 | Quite a few choices here.
Over 70 basic chart types available.
| | 00:49 | And it's a good idea to
experiment with different kinds of charts.
| | 00:53 | And even if you haven't created a chart,
if you're looking at this particular
| | 00:57 | worksheet for example, you might say,
well, how else might this data look?
| | 01:01 | And so clicking the chart below the
data here, this green border chart,
| | 01:06 | just click this chart.
| | 01:08 | How might we view this in a different way?
| | 01:11 | And you'll notice, when you do click
on a chart, you will see some new tabs.
| | 01:15 | Here's a Design tab at the top of the
screen and the extreme left button is
| | 01:20 | called Change Chart Type.
| | 01:22 | And ultimately, in this
dialog box you will see 73 choices.
| | 01:27 | And we can start at those. You will see
some unusual chart types. Perhaps some
| | 01:31 | of these you've never seen or used before.
| | 01:34 | But the very first one is the most
common one and you're likely to see a lot of
| | 01:38 | charts of this type.
| | 01:39 | They are referred to as column charts.
| | 01:42 | And almost the same, but rotated 90
degrees, you'll see these farther down the
| | 01:47 | list here, bar charts.
| | 01:48 | same general idea, but just rotated differently.
| | 01:52 | Now we might turn this into a bar chart.
| | 01:55 | Choose this, click OK. Is that better?
| | 02:00 | Maybe, maybe not.
| | 02:01 | I don't think it's better, but at
different times I've changed my mind about
| | 02:04 | how things are viewed.
| | 02:05 | It depends on what you're looking for.
| | 02:07 | Line chart here, we've mentioned this.
| | 02:09 | Pretty good at showing a
trend. Let's change it.
| | 02:12 | Now how much time does this
take to change? Not much at all.
| | 02:16 | And to make it slightly faster, double-click.
| | 02:18 | So let's double-click this column choice maybe.
| | 02:20 | How does that look?
| | 02:21 | Maybe pretty good.
| | 02:23 | Is that better than the line chart? Maybe.
| | 02:25 | Change the chart type.
| | 02:26 | It's so easy, it's so fast.
| | 02:28 | And you do want to explore a little bit.
| | 02:30 | Maybe you want to try a pie chart, or
how about one of these cylinder charts?
| | 02:34 | Is that a better choice? I don't think so.
| | 02:38 | One thing that will help you in this
process too is to Undo, and most of you are
| | 02:43 | familiar with this button perhaps on
the Quick Access Toolbar, which might be
| | 02:48 | above or below the Ribbon, and a
great keystroke shortcut, you see it right
| | 02:52 | there, Ctrl+Z. And in this
course I'll be using this a lot.
| | 02:55 | I'm going to press Ctrl+Z right now.
| | 02:57 | Sometimes you make a choice, you
don't like it. And you can press Ctrl+Z
| | 03:01 | repeatedly or even click the drop arrow
there to review recent actions, and the
| | 03:07 | Undo process can remember
up to 100 of your last steps.
| | 03:11 | And that becomes invaluable at times
when you're making changes and then you
| | 03:14 | want to reverse direction.
| | 03:16 | So I can press Ctrl+Z a few more times and
this chart will change back to a line chart.
| | 03:21 | And how about the chart below the data?
| | 03:23 | It's going to change back to the
column chart that we saw before.
| | 03:26 | The pie chart to the right of the data
has its uses, but it's definitely limited
| | 03:31 | in how much it can show and when you
look at this pie chart, which has labels
| | 03:35 | on it, I think you can begin to see why
maybe these are not the best charts for
| | 03:39 | certain kinds of data.
| | 03:41 | Now one by one, we will see in-depth
in different movies in this course
| | 03:46 | more information about line charts
and column charts, and say this pie
| | 03:52 | chart that we're looking at.
| | 03:53 | But just a quick look, look at the
March value here of 125, and then the value
| | 03:59 | on the back, sort of a gold
-orange color. It's 180.
| | 04:01 | Do they really look that different?
| | 04:04 | Some people might say well, I think maybe
the green piece looks bigger. Well, maybe not.
| | 04:08 | Maybe it does, but you begin
to see what's going on here.
| | 04:11 | Some charts are not that great for showing
the data the way you want it to be shown.
| | 04:16 | And how about this 3-D column chart here?
| | 04:19 | Colorful, interesting, we can tilt it.
There are all kinds of things you can do
| | 04:23 | with there to manipulate it.
| | 04:25 | But can you tell me how much sales or
profits, whatever this is showing for one
| | 04:30 | of those green columns? Can you read that?
| | 04:32 | Is that over 100, is that under 100?
| | 04:34 | Now when you point to it with the mouse, a
nice little pop-up tells you what the value is.
| | 04:40 | I think if you're just glancing
at this it's hard to read these.
| | 04:44 | As a measure of how they vary
relatively, perhaps that makes sense.
| | 04:48 | We can see that the green totals here
representing Asia are certainly taller
| | 04:53 | than the Europe totals,
which are in front of them.
| | 04:56 | But the relative difference,
well, here and there, not so easy.
| | 05:00 | And sometimes you're trying to come up
with that ideal chart that on the one
| | 05:03 | hand shouldn't require a whole lot
of explanation when you're trying to read it
| | 05:07 | and at the same time is
interesting and more colorful.
| | 05:10 | So there are quite a few different choices.
| | 05:13 | Again, some other choices as I move
to different sheets here, here are some
| | 05:16 | different column charts. I'll flip over
to bar charts and again, recognize here
| | 05:22 | we're simply talking about practically
the same kind of chart just rotated 90 degrees.
| | 05:27 | Bar charts, column charts.
| | 05:30 | A few more line charts here.
Actually this is only one line chart here.
| | 05:33 | There's another one off to the right, you can see these.
| | 05:36 | And at different times again, we
emphasize showing data over a period of time,
| | 05:41 | in this case months.
| | 05:43 | But I've seen line charts that cover
hourly activity, perhaps hourly sales, even
| | 05:47 | by minutes. Certain
kinds of scientific data maybe.
| | 05:51 | So, quite a few different
choices here. Some pie charts.
| | 05:54 | One is 3D, one is 2D,
there are some area charts.
| | 05:58 | We'll talk about why you
might want to use these.
| | 06:01 | There's another kind of chart called
scatter chart, and that's when you're
| | 06:04 | comparing two sets of values.
| | 06:06 | There are quite a few different chart
types and as we said, 73 to be exact
| | 06:11 | major chart types and then you can
take it from there and change them
| | 06:14 | quickly and easily.
| | 06:16 | And the more you work with charts,
the more you'll begin to gravitate
| | 06:19 | toward just a few types.
| | 06:21 | And if you are only going to be using
charts sparingly, I would recommend that
| | 06:25 | you more or less stick to the big four,
and that would be column charts, line
| | 06:30 | charts, pie charts, and of course,
from time to time, bar charts as well.
| | 06:35 | And so here remember, it's very
easy to change the chart type.
| | 06:38 | don't hesitate to do that and you'll
quickly reduce your chart choices to a few favorites.
| | Collapse this transcript |
| Understanding chart terminology| 00:00 | Column and bar charts comprise the bulk
of chart type usage for many Excel users.
| | 00:05 | Three terms that you're likely to
encounter when using these kinds of charts as
| | 00:09 | well as others are the terms
clustered, stacked, and 100% stacked.
| | 00:15 | Let's look at these with column
charts and then point out how you'll also
| | 00:18 | encounter them with other chart types as well.
| | 00:21 | The chart to the right of the data
here is a clustered column chart.
| | 00:26 | It's probably the default chart type
in Excel, unless you've made a change to it,
| | 00:30 | and it's certainly one of
the most common chart types.
| | 00:34 | Now when you are creating charts or
you're possibly changing charts, if you go
| | 00:40 | to the Design tab, Change Chart Type,
this is where we see all those choices.
| | 00:46 | I'm just putting the mouse
over this Clustered Column.
| | 00:50 | In line charts, you'll have roughly the
equivalent, but you don't see the word Clustered.
| | 00:55 | We will see these as we point to
bar charts. There's a Clustered Bar.
| | 01:02 | It's equivalent in area. You don't see
that. And going back up here you will see
| | 01:07 | them in this kind of a bBar chart.
| | 01:09 | That's a Clustered Horizontal Cylinder.
| | 01:11 | Without beating this to death here,
here's a Clustered Horizontal Cone.
| | 01:15 | And I guess the best way to describe
clustered is that it's not stacked.
| | 01:19 | There's a Clustered Cylinder and a
Clustered Cone and there's a Clustered
| | 01:23 | Pyramid in there as well.
| | 01:24 | So you will see that term a lot.
| | 01:25 | And the chart to the right here is a
clustered column chart, the one right here.
| | 01:32 | Possibly we could change this,
although let me close this instead of
| | 01:35 | actually changing it. Point to the
chart below the data here. This is a
| | 01:40 | stacked column chart.
| | 01:42 | And this type has its advantage in
that at a glance we can see grand totals.
| | 01:47 | And it is slightly cleaner looking, and
when you're dealing with lots of data,
| | 01:51 | sometimes this simplifies the view.
| | 01:53 | What isn't so good with these is
when you're trying to compare colors
| | 01:58 | across different months.
| | 01:59 | Let me zoom in on this a little bit
so we can see it better. Comparing the
| | 02:03 | greens for example, the two Asia totals
here, certainly because of the pop-ups
| | 02:07 | we can see those are identical.
| | 02:10 | But how about when you're trying to compare
February over here and then June over here?
| | 02:15 | A little bit tricky.
| | 02:16 | And the ones we're going to read most
easily are the ones that start at the bottom.
| | 02:20 | But again, this is a common chart style.
| | 02:22 | It's called stacked.
| | 02:23 | And once again, we may not be trying to
change this, but as we point back to the
| | 02:28 | Design tab and go to Change Chart Type,
recognize that this is a stacked column
| | 02:35 | and we also have the stacked cylinders,
pyramids, and cones, and of course, as
| | 02:39 | you would expect by now, we have these
with bar charts and some of the other
| | 02:43 | options here as well,
including area and a few more.
| | 02:47 | So you'll see that term.
| | 02:49 | Now a third choice here, not
nearly as popular as those two, but is
| | 02:53 | depicted here to the right.
| | 02:55 | Let me zoom back just a little
bit here so we can see this better.
| | 02:58 | The chart right here below looks
initially as if it's stacked, but you see
| | 03:04 | percents down the side.
| | 03:06 | And these kinds of charts are
trying to do what a pie chart does.
| | 03:09 | Pie chart can only handle one series,
but this is called 100% stacked.
| | 03:15 | And so when we look at the left column
here, we're saying in effect these four
| | 03:19 | totals add up to 100%, and they do.
| | 03:22 | But for February we've got a different set
of totals, but of course, they add up to 100%.
| | 03:27 | And here you can see something very misleading.
| | 03:30 | If you were glancing at these and not
absorbing what the chart really means,
| | 03:35 | you would say Asia had a relatively high
total in January, but not so high in February.
| | 03:42 | Well, yes and no.
| | 03:44 | Asia for January is 110, but it
is a bigger portion of the total.
| | 03:50 | Asia for February is 120.
| | 03:52 | It's a smaller portion of the total,
because sales did go up in February.
| | 03:56 | We can go over and see the data.
| | 03:58 | But January data adds up to 290.
| | 04:01 | We see that over in cell B10.
| | 04:03 | February data adds up to 390.
| | 04:06 | So on these kinds of charts, and you
will be able to and we'll show you how to
| | 04:09 | add additional labeling, the idea here,
each column here represents 100% and
| | 04:14 | we're getting a relative
breakout of the pieces here.
| | 04:19 | So this is called 100% Stacked and again,
if we go to the Design tab and Change
| | 04:25 | Chart Type, and even though we don't
really want to change it again, we'll begin
| | 04:29 | to see this term again.
| | 04:31 | 100% stacked and we'll see it here
and there and of course with bar charts
| | 04:35 | and with area charts and
some other choices as well.
| | 04:38 | So those terms, clustered,
we don't see it on area chart.
| | 04:42 | But stacked, 100% stacked, and then
clustered on our bar and column charts.
| | 04:48 | Terms we'll see a lot as we
work with various Excel charts.
| | Collapse this transcript |
| Understanding the Ribbon and the Design, Layout, and Format tabs| 00:00 | One aspect of charting that might
overwhelm you is the idea that there are so
| | 00:05 | many different things we can do with charts.
| | 00:07 | And the number of command possibilities is
in the millions. Nobody is really counting.
| | 00:12 | A quick read on how we use the menu system
in Excel is going to help enormously here.
| | 00:18 | Now we're not ready to create charts
just yet, but we will, when ready, go to
| | 00:22 | the Insert tab in the Ribbon.
| | 00:24 | The Ribbon system introduced in Excel
2007 is a colorful way to get started and
| | 00:30 | is much easier than prior menu systems.
| | 00:33 | And in inserting charts, we'll go
there first, and start the process here of
| | 00:38 | picking the chart type.
| | 00:39 | Now once a chart is selected, suppose
for example we just created the chart
| | 00:43 | below the data here.
| | 00:44 | When a chart is selected, the Ribbon
system changes and depending upon which
| | 00:50 | book or information you're reading, it
might be described in one of two ways.
| | 00:54 | When you do click a chart, you
see the term Chart Tools here.
| | 00:59 | This is sometimes described as a new Ribbon.
| | 01:01 | At other times, it's simply described
as a heading for three new tabs that
| | 01:06 | exist on the Ribbon.
| | 01:07 | And if you're seeing this for the first
time, if you click Design, quite a few
| | 01:12 | choices there. You can
click Layout, many, many more.
| | 01:16 | You click Format, you might be thinking, how
can I ever master charts with all these choices?
| | 01:22 | And it can be-- if you let it
get to you-- a bit much here.
| | 01:26 | So here's the general thought process
and we have structured this course to
| | 01:30 | recognize that you will use the Design
tab, perhaps it's the most important of
| | 01:35 | the three, and we'll talk
about a lot of the choices here.
| | 01:38 | You will frequently use the Layout
tab. Perhaps one way to describe this,
| | 01:43 | this is the fine-tuning tab with lots
of choices for adding elements to a chart
| | 01:48 | and controlling them.
| | 01:50 | And then the Format tab, although
not unimportant, perhaps less important.
| | 01:54 | It tends to be more about the visuals that
we need to adjust as we work with charts.
| | 01:59 | And as I had mentioned in a prior movie,
anytime you happen to double-click a
| | 02:04 | series perhaps, or a different chart
element, that leads you into dialog boxes
| | 02:10 | that are you might say
independent of that Ribbon system.
| | 02:14 | So at different times, it is a bit
bewildering to figure out how to get started.
| | 02:18 | But again, a quick reminder here.
When you click on a chart, the Ribbon menu
| | 02:22 | system has been altered. We have a
Chart Tools section or Ribbon and we've got
| | 02:27 | a Design tab, a Layout tab, and a Format tab.
| | 02:32 | Quite a few different choices here.
| | 02:35 | And the chart creation process
begins with after selecting the data,
| | 02:40 | we go to the Insert tab.
| | 02:41 | So different locations here as
we use the Ribbon menu system.
| | 02:46 | But there's no question that your
knowledge of charting is going to be vastly
| | 02:50 | improved if you remember these three
tabs and think of them as being most
| | 02:55 | important from left to right.
| | 02:57 | Design, Layout, and Format.
| | Collapse this transcript |
|
|
2. Creating Basic Charts QuicklySelecting data to display as a chart| 00:00 | The critical first step in creating
charts is actually selecting the data you
| | 00:04 | want to appear on a chart.
| | 00:06 | On the worksheet here we see a lot of
numbers and we might want to show what's
| | 00:10 | happened in the first quarter of
the year or maybe the first half.
| | 00:14 | That's all the data that we have here.
| | 00:16 | It covers various regions.
| | 00:18 | You wouldn't necessarily think or know
that text information that appears with
| | 00:23 | your numbers should be
included in that selection.
| | 00:26 | Let's suppose that we wanted to
show all six months of the data here.
| | 00:30 | Let's include the text that's typically
above the data and to the left of it.
| | 00:35 | Maybe we want to show just those
three regions or just these four.
| | 00:38 | As a general rule you'll want to not
include totals along with detail.
| | 00:42 | We have got an extra column on
the right of Totals, one below too.
| | 00:45 | Just leave those off here for now.
| | 00:48 | For the moment we're not
emphasizing how to create the chart.
| | 00:50 | I want to use a keystroke
shortcut, which you will see in a bit.
| | 00:53 | But here is a chart that's been created
simply based on the data that I selected.
| | 00:58 | Let move this over a little bit,
shrink it so we can see what's happened.
| | 01:02 | You can see what's been highlighted.
| | 01:04 | When you do create a chart sometimes
you do change your mind and say, well, I
| | 01:08 | think I wanted Totals there.
| | 01:10 | Well, I'll move the chart over a little
bit more here and actually drag this corner.
| | 01:14 | Let's see how the Totals
might look on this chart.
| | 01:17 | I think you can see why many
times we don't want Totals there.
| | 01:21 | How about the Totals below it instead?
| | 01:23 | What if we were to not include these
in and then Shift+Down and get these?
| | 01:26 | That's a different look too.
| | 01:28 | I don't think you would want that.
| | 01:29 | Most of us wouldn't. So as a general rule
keep off Totals, but the main idea here
| | 01:34 | is you're highlighting the data you want.
| | 01:36 | Suppose we want to create a different chart.
| | 01:38 | Maybe we'll move that one over
or keep it here for the moment.
| | 01:41 | Maybe just the first three
months. Highlight this data.
| | 01:45 | Again, I'll use a keystroke shortcut for
that and as you would expect we're just
| | 01:48 | seeing three months of data here.
| | 01:50 | Now many many times you'll simply
drag across the cells that you want to
| | 01:55 | appear in the chart.
| | 01:57 | Excel knows what to do with that text
on top your data and to the left of it.
| | 02:01 | Now what if we wanted a different
chart here that didn't include one of these
| | 02:05 | regions or maybe we only
want to show two of the regions?
| | 02:08 | How about showing Europe and
Latin America, but not the other two?
| | 02:12 | We would want the monthly information.
| | 02:14 | So let's highlight this.
| | 02:15 | Let go of that left mouse button.
| | 02:18 | Then hold down the Ctrl key, highlight
the Europe data. Let go of the left mouse
| | 02:23 | button, hold down Ctrl,
highlight the Latin America data.
| | 02:27 | We're all set here.
| | 02:28 | We'll use another keystroke shortcut
for creating this quickly. There we go!
| | 02:32 | Just Europe and Latin America.
| | 02:34 | There are other times when all of the
contiguous data you want to appear in a
| | 02:39 | chart and this might be a huge list.
| | 02:41 | It's not that long really, but it
could be thousands of rows and if all the
| | 02:46 | contiguous data here, all this data
here, and we don't have to highlight it.
| | 02:50 | If we want all of that to appear in a
chart, simply click on one cell here.
| | 02:55 | Once again, quick way, there it is.
| | 02:58 | It's showing all of the data.
We don't have to highlight at all.
| | 03:01 | Had I done that with the previous
worksheet, going back here again and getting
| | 03:06 | rid of this chart and that raises a
question, how do you get rid of a chart?
| | 03:10 | Click on its border or just
inside in the Chart Area. Press Delete.
| | 03:15 | I don't want this one either. Press Delete.
| | 03:17 | I don't want this one, press Delete, and so on.
| | 03:20 | If I were to click in this cell and
create a chart quickly, Excel will
| | 03:24 | automatically pick up all
of this contiguous data.
| | 03:27 | I think you know this is not going to
be an acceptable chart. There it is.
| | 03:31 | Once again Total is there.
| | 03:33 | And look at the percents.
Hopelessly lost in all this too.
| | 03:36 | Now there is the opportunity sometimes
to say for example I want to show the
| | 03:41 | Totals here, but not the other data.
| | 03:43 | This might work well in a pie chart.
| | 03:45 | Maybe highlight that data.
| | 03:47 | Letting go of that left mouse button,
using the Ctrl key, highlight this data.
| | 03:52 | Once again keystroke shortcut.
| | 03:55 | I might eventually turn that into a
pie chart, but that's not so bad either.
| | 03:58 | So sometimes you will select non-
contiguous areas, as we saw earlier with the regions.
| | 04:03 | Here we're doing it with columns as well.
| | 04:05 | So that first key step when you're
creating a chart is to indicate what is
| | 04:10 | going to go into the chart, and a
number of different ways to approach that.
| | Collapse this transcript |
| Creating charts instantly with shortcuts| 00:00 | After indicating what data you want
depicted as a chart there are two major
| | 00:04 | keystroke shortcuts that let
you create charts instantly.
| | 00:08 | In this worksheet here I'd like to create a
chart based on this data right here. I'm in a hurry.
| | 00:14 | I might press F11.
| | 00:15 | Watch what happens.
| | 00:17 | A chart instantly, but on a new sheet.
| | 00:20 | If you look at the bottom of
the screen you'll see a Chart1.
| | 00:23 | The data is on a sheet
called ChartData right back here.
| | 00:26 | So anytime you select data and press F11,
you will get a new chart to the left of
| | 00:32 | the current sheet and it'll be
called Chart1, Chart2, etcetera.
| | 00:35 | Now unlike in older versions of Excel
you cannot resize this chart here, but the
| | 00:40 | advantage of working with a chart all
by itself on its own sheet is it's not in
| | 00:45 | the way of other data in the sense
you're devoting your full attention to it.
| | 00:49 | You can use all the different chart
tools that you might want to get to here,
| | 00:53 | all focused only on this particular chart.
| | 00:55 | At any time you could change the
location of this chart. You could move it onto
| | 01:00 | a different worksheet. Probably
the one that has the data on it.
| | 01:03 | So even if you don't want to keep the
chart on a separate sheet, you can move
| | 01:07 | it anytime you wish.
| | 01:08 | Back to the chart data sheet, the other
keystroke shortcut, one that was new in
| | 01:12 | Excel 2007, is for those times when you
want to create a chart right on the very
| | 01:18 | worksheet where the data resides.
| | 01:20 | So here the data is
highlighted. I'm going to press Alt+F1.
| | 01:24 | In this example and in the previous one
the chart type that we get here is the
| | 01:29 | default type in Excel, unless
you have changed that default.
| | 01:32 | It's called a clustered column chart and
here too very fast. You might want to resize it.
| | 01:37 | You can certainty do that
quickly and easily from the corners.
| | 01:41 | Remember you cannot do this
when the chart is on its own sheet.
| | 01:44 | Of course, what you'd like to do
perhaps in some situations is have more than
| | 01:48 | one chart on this worksheet.
| | 01:50 | You might want to show a different
aspect of the data. Maybe you want to focus on
| | 01:54 | the Totals only, or maybe use a
line chart for just one of the regions.
| | 01:58 | It's fast, it's easy.
| | 02:00 | Sometimes it might involve
maybe just one more step.
| | 02:03 | Maybe this time we will show just the
first quarter. Highlight the data, press
| | 02:08 | Alt+F1, there is a chart, and maybe now
you'll quickly apply a design feature or
| | 02:15 | some other technique to make it
almost be what you want it to be.
| | 02:18 | So it's fast and easy with our two
keystroke shortcuts. F11 to take the
| | 02:22 | selection and create a new chart on a
different sheet or Alt+F1 to create a
| | 02:27 | chart on the current sheet.
| | Collapse this transcript |
| Creating charts with standard menu commands| 00:00 | The standard way to create a chart
using the Ribbon menu system requires just
| | 00:04 | a few mouse clicks.
| | 00:06 | In this particular worksheet let's
imagine that we want to create a chart maybe
| | 00:09 | based on this data right here.
| | 00:11 | The standard approach is to click the
Insert tab in the Ribbon and then in a
| | 00:17 | group called Charts we might want a column chart,
a line chart. Depending upon which of these we choose,
| | 00:23 | suppose we're thinking about a line
chart, we will get various options.
| | 00:27 | The example here, we see six examples
of a 2D line chart, one example of 3D line.
| | 00:32 | Column, quite a few more examples.
A lot of sub-examples here, cylinder,
| | 00:38 | cone, pyramid, and so on.
| | 00:40 | Bar chart, lots of choices here.
| | 00:43 | If the chart type that you're looking
for, if you don't find here, you might
| | 00:46 | choose Other Charts. One of these here.
| | 00:48 | I'll just stick with Column for the
moment, pick the very first choice, click,
| | 00:54 | and we have our chart.
| | 00:55 | And it lacks just a few things. For many
people who are on the verge of creating
| | 00:58 | a chart that's all set for
publication or presentation, fast and easy.
| | 01:03 | Even without using keystroke
shortcuts, creating a chart is a straightforward process.
| | 01:08 | it requires just a short sequence of commands.
| | 01:10 | After selecting the data, press Insert,
press the chart type you want, and
| | 01:15 | there is your chart.
| | Collapse this transcript |
| Creating presentation-ready charts with just a few adjustments| 00:00 | Whether you're in a hurry to create a
chart or maybe you're just a casual chart user,
| | 00:03 | applying the final touches to a
chart sometimes requires just a few
| | 00:07 | commands and you can get to them pretty quickly.
| | 00:10 | There are so many features in Excel
related to charts, but what if we are in
| | 00:13 | a bit of hurry here?
| | 00:14 | What do we get out of a basic chart building?
| | 00:16 | Let's highlight this data here.
| | 00:19 | Simply press Alt+F1. We've got a
chart and maybe we want a column chart.
| | 00:24 | Looking pretty good, but to someone
looking at this maybe the data isn't visible
| | 00:29 | and we do want to accentuate right
on the chart what these numbers mean.
| | 00:33 | We probably want a title on top.
| | 00:35 | A couple of quick things to do here.
| | 00:37 | Now anytime you have a chart, if it's
selected, the Ribbon system has changed.
| | 00:41 | Here is a Design tab and one of
the features here not so obvious is
| | 00:46 | called Chart Layouts.
| | 00:48 | Click this option right here.
| | 00:50 | Now without exploring every single one
of these, what a lot of these have is a
| | 00:54 | gray bar across the top, room for chart
title, a lot of them will have a gray bar
| | 00:59 | down the left-hand side.
| | 01:01 | So we can put in some kind of
a title for these numbers here.
| | 01:04 | Most of them show a legend,
sometimes on the right side.
| | 01:08 | I often if it's a column chart choose this one.
| | 01:10 | If this were a bar chart we would see
almost as many choices. A pie chart,
| | 01:14 | maybe not so many, but depending upon the
chart type these options give us a quick lead in.
| | 01:20 | I'm going to click right here and
obviously I don't want those words Chart
| | 01:24 | Title to appear there.
| | 01:25 | So I'll just click this banner here and start
typing, for example, Sales - 2011. There we go.
| | 01:36 | And I'll click the title on the left-hand side,
Sales in Millions of Dollars, and press Enter.
| | 01:49 | I might not want a title at the bottom or I
might. I'll just press Delete if I don't want it.
| | 01:53 | The legend is all set and
avoiding all fancy options, that's ready for
| | 01:58 | presentation and that's all it take sometimes.
| | 02:01 | Despite all of these choices that we
might get to later, it's pretty complete.
| | 02:05 | It explains itself based on the
colors that we're seeing, the monthly
| | 02:09 | information, and the titles that we
simply added by choosing Chart Layouts.
| | 02:14 | So despite all of the numerous
adjustment you can make to a chart, making a
| | 02:17 | chart "complete" only takes a few steps.
| | Collapse this transcript |
| Creating graphic-in-cell charts with sparklines| 00:00 | A new feature in Excel 2010 and one that
expands your ability to provide a quick
| | 00:05 | visual representation of
data is called Sparklines.
| | 00:09 | You could describe it as in-cell charts.
| | 00:12 | It's not actually located with
any of the other chart commands.
| | 00:15 | It's a whole new series of possibilities.
| | 00:18 | As we look at the data here on this
worksheet called SalesResults we're actually
| | 00:23 | showing how much particular regions have
exceeded their sales goals or possibly,
| | 00:29 | with the negative numbers, have
fallen short of those goals.
| | 00:32 | The chart below this is an attempt to
show that information in a graphical way.
| | 00:37 | It's a column chart.
| | 00:39 | And if you're trying to look at the
performance in the East here, I think you'd
| | 00:42 | realize that it's not the easiest thing to do.
| | 00:44 | In fact one by one as you
look at the different regions,
| | 00:47 | the East columns here are the blue ones
and as I'm pointing to each one of them
| | 00:51 | we're not seeing this so clearly.
| | 00:53 | Sure, you can read it, but not so very
clearly as we're trying to isolate each region here.
| | 00:58 | You might be saying, well, how
might this look as a line chart?
| | 01:00 | So I'm going to go up to the Design tab
and then that left button Change Chart Type.
| | 01:06 | I want make this to be
a line chart. There we go.
| | 01:10 | And that's probably even worse. Although
we can see the lines connecting, but we
| | 01:15 | can't see a single region without
the clutter of the other regions there.
| | 01:20 | So what if in cell N3 for starters
we can actually show a line chart
| | 01:26 | representing these values right here?
| | 01:29 | Let's go to cell N3 and in the
Ribbon the Insert tab the choice Sparklines.
| | 01:37 | Possibly a Line, possibly a Column,
or possibly something called Win/Loss.
| | 01:41 | Let's take a look at Line first.
| | 01:44 | I'm going to put the chart right in cell N3.
| | 01:48 | The data that we want to be represented
there are the 12 months of data we see
| | 01:52 | right here. Click OK.
| | 01:56 | That chart shows us what has happened
throughout the East region month by month.
| | 02:00 | Now there's no indication in there as
to whether anything is negative or not.
| | 02:05 | So we'll show you in a bit different ways
to make that happen. And why not have this
| | 02:09 | line for each of the rows below this?
| | 02:11 | In fact even the Totals at the bottom.
| | 02:14 | So any one of these represents, for
example, this is the South, this is the
| | 02:18 | Midwest, and even though we can see that
data in the line chart below, this gives
| | 02:22 | us a good quick read on what's going on.
| | 02:25 | Now let's put one here and do the
same kind of thing. Another type of
| | 02:29 | Sparkline is a column.
| | 02:32 | The data range for this one is
going to the same we saw before.
| | 02:36 | Right there and here too
we could drop this downward.
| | 02:38 | Here we see more clearly when there
has been negative data and that jumps
| | 02:43 | out at us pretty readily.
| | 02:45 | Another type here simply shows Win/Loss.
| | 02:48 | Positive or negative.
| | 02:50 | Let's insert that kind of a
Sparkline here. Again using the same range.
| | 02:55 | Now I wouldn't say you need all three of
these, but certainly one or two in your
| | 03:00 | choice there, but a good quick
visual representation what has happened.
| | 03:05 | In some cases of course it's
kind of boring with a Win/Loss.
| | 03:07 | Look at the last choice
here that shows our totals.
| | 03:10 | In all cases here they come out positive,
but at other times and depending upon
| | 03:14 | the nature of the data one of these
Sparkline types is likely your favorite over
| | 03:18 | the other one for the specific data in place.
| | 03:22 | And we can make adjustments to these too.
| | 03:24 | Possibly I want these lines to be a
different color. Recognize that when you
| | 03:28 | do select a cell or cells with the
Sparklines in them, you do have a new
| | 03:32 | Ribbon, Sparkline Tools, with a tab on it
called Design and of course lots of choices there.
| | 03:38 | Maybe I'll choose this one.
| | 03:40 | Another thing we could do here too on
the Design tab you'll notice some choices
| | 03:44 | over here under Edit Data. We can
change the source of the data. We don't want
| | 03:48 | to do that right now.
| | 03:50 | We could deal with the
issue of hidden and empty cells.
| | 03:52 | Would we want to hide the
detail and just show the Sparklines?
| | 03:56 | I'm going to right-click and Hide.
| | 03:58 | We're not seeing any of these for the moment.
| | 04:00 | So let me undo that and on the Design
tab as we choose just one of these, edit
| | 04:06 | the data. Hidden & Empty cells.
Show data in hidden rows and columns.
| | 04:13 | So possibly we might want
to do that and hide these.
| | 04:17 | So there is some use for
that capability as well.
| | 04:20 | In other words we're still seeing the charts,
we're not seeing the data, and sometimes
| | 04:24 | that's something we would want to be able to do.
| | 04:26 | I'm going to press Ctrl+Z to unhide again.
| | 04:29 | Another option you might want to
consider here too and perhaps more so on the
| | 04:33 | lines than the other options is on the
Design tab the Axis choice off to the right.
| | 04:39 | You can see where we might want
to show the axis and that shows us where
| | 04:45 | the negatives are occurring.
| | 04:46 | Now we can see that in the data of course,
but that makes these Sparklines a bit better.
| | 04:51 | We can also choose to have a dot at
the high point. You see what's happening there.
| | 04:55 | The green dot's on the high point
each time, and the low point if we wish,
| | 05:00 | or just the negative points.
So quite a few variations in there too.
| | 05:05 | Zooming in and out on these might help too.
| | 05:07 | Another option, and this of course
has something to do with the worksheet
| | 05:10 | design, we could make the column wider.
| | 05:13 | That stretches out the line.
| | 05:15 | We would take these rows
here and make them taller.
| | 05:18 | That's another approach to
this that accentuates that.
| | 05:21 | So we've got different ways to make this
data stand out a little bit differently.
| | 05:25 | In other words, you can get pretty creative.
| | 05:27 | Again, recognize this isn't
part of the standard chart feature.
| | 05:31 | It's new in Excel 2010 and it gives you
a lot of capability for getting a quick
| | 05:36 | read on the data in a variety of different ways.
| | Collapse this transcript |
|
|
3. Fine-Tuning Charts with Design Tab ChoicesSwitching rows and columns for a different view of the data| 00:00 | Changing a chart's orientation is not
an intuitive kind of thing to do, but I
| | 00:04 | always recommend it as a way to give
you a different perspective on the data.
| | 00:08 | Many times this alternative is
revealing and sometimes it's even better than
| | 00:12 | the initial display.
| | 00:14 | What are we talking about here?
| | 00:15 | It's a capability that we see
on the Design tab in the Ribbon.
| | 00:20 | Third button from left Switch Row/Column.
| | 00:23 | This chart to the right to the data of
the data, a clustered column chart, is
| | 00:26 | showing in clusters what's happened
each month for the regions here, and not
| | 00:31 | a bad chart at that.
| | 00:33 | What happens when we Switch
Row/Column? Watch the chart.
| | 00:36 | Now I wouldn't say this is
better. You might though.
| | 00:39 | What do we see now?
| | 00:41 | Instead of the clusters being by month
now they're by region. A different way to
| | 00:46 | see the data. You can
easily switch back and forth.
| | 00:51 | Sometimes you'll realize, hey,
I'd like to see both charts.
| | 00:54 | Well, create another one simply by
copying this. Change its orientation.
| | 00:59 | The chart below the data here, a stacked bar
chart, shows the data in a reasonable way.
| | 01:05 | How might this look?
| | 01:06 | Again, I'm going to trying to keep
some of the data and I will zoom back a
| | 01:08 | little bit here, so we can see that
chart along with the data above it,
| | 01:13 | the chart below the data here.
| | 01:14 | Let's change the orientation of this.
| | 01:16 | Again, Design tab, Switch Row/Column,
and you can see what's happening there.
| | 01:22 | Here too, I think this is not
nearly as good as the first. Trying to
| | 01:25 | differentiate those colors there isn't so great.
| | 01:28 | It does at least momentarily
accentuate that the Domestic region has the
| | 01:32 | largest share of the sales.
| | 01:34 | Of course, we can see that in the data as well,
but switching back and forth I recommend.
| | 01:39 | I think you'll see on line charts
there's nearly always a clear alternative.
| | 01:43 | Most times a line chart is about a
changing set of data over a period of time
| | 01:49 | and that's one of its great strengths.
| | 01:51 | If you change the orientation on this
chart, Switch Row/Column, I think you'll
| | 01:55 | see totally unacceptable there.
| | 01:57 | On pie charts the alternative often is
pretty hopeless. Switch Row/Column here too.
| | 02:02 | Now something about the way this
works in a different sense too.
| | 02:06 | If I'm highlighting data here to put
it into a chart, suppose here I've got a
| | 02:10 | situation. I just want to show three months.
| | 02:13 | I'm going to press Alt+F1, a quick
way to create a chart, and it clusters
| | 02:17 | the data by region.
| | 02:19 | What if I included formats?
| | 02:21 | Not nearly as commonly done.
I'm going to press Alt+F1.
| | 02:24 | Now it clusters them by month.
| | 02:27 | Depending upon the number of rows and
number of columns that you're highlighting
| | 02:30 | or selecting to go into a chart, many
times the chart you're getting here is not
| | 02:34 | quite the one you want.
| | 02:36 | So just switch the orientation.
| | 02:37 | If you really did want to see the
three months of data here, but you want the
| | 02:42 | clusters by months then
simply Switch Row/Column.
| | 02:46 | So once again, as you're creating
charts, I highly recommend use the Switch
| | 02:50 | Row/Column option often.
| | 02:52 | It just helps sometimes to view your data
in a different way and it's an easy switch.
| | 02:56 | Think about it as a toggle back and forth.
| | Collapse this transcript |
| Setting a default chart type and creating a template| 00:00 | If the default clustered column chart is
not the kind of the chart you're likely
| | 00:03 | to need most often, you can change
the default chart to another type.
| | 00:08 | Furthermore, if you have a certain
chart type that has design features on it
| | 00:12 | like a blue background, white
foreground, or whatever, you can save different
| | 00:16 | chart types as templates and then the
next time you create a chart you can use
| | 00:20 | one of those templates instead of one
of those standard built-in chart types.
| | 00:24 | Let's suppose you've decided that
this clustered column chart, although
| | 00:28 | valuable, is not the one you're using most often.
| | 00:31 | When you create charts quickly using
perhaps keystroke shortcuts or even
| | 00:35 | the menu, you want the chart type that
comes up to be a cluster bar chart or
| | 00:40 | a stacked bar chart.
| | 00:42 | So the chart below the data on this
worksheet here is a stacked bar chart
| | 00:48 | and if you've decided that's the one you'd like
to see most often, make it be your default type.
| | 00:53 | Having selected it we can go to the
Design tab in the Ribbon, Change Chart Type,
| | 01:00 | and while we're here simply say
Set as Default Chart and click OK.
| | 01:04 | All right, the next time we create a
chart and from now on, regardless of how
| | 01:10 | many cells are selected, maybe we only
want to show three months. That's okay.
| | 01:13 | How about six months here?
| | 01:15 | I'm going to press Alt+F1 to create a
chart quickly and it's not going to be
| | 01:19 | the clustered column chart anymore.
| | 01:21 | It's now a stacked bar chart,
because we changed that to be the default.
| | 01:26 | What if further more another one of
your favorite chart types is this kind of a
| | 01:30 | chart with certain kinds of colors added.
| | 01:33 | So without going to all the details
of this I'm going to the Format tab,
| | 01:37 | give this a gold background, and maybe
change the inside of it to be a different color.
| | 01:42 | Maybe that's something I really like
and from now on when I create charts I
| | 01:47 | might want to have this option out
there as something that I would use.
| | 01:50 | It doesn't mean that we're going to
make this be the automatic choice, but I
| | 01:54 | want it as part of my
repertoire of frequently used charts.
| | 01:57 | In other words, we're going
to create a template here.
| | 01:59 | So with this chart chosen, click the
Design tab, the second button from the left
| | 02:04 | in the Ribbon, Save As
Template, and we'll just call this
| | 02:09 | GoldBackgroundStackedBar, and you don't have
to make it this long necessary, and Save.
| | 02:20 | At a later time when I'm highlighting
some data to create a chart, maybe I do
| | 02:25 | want a chart like that.
| | 02:27 | So on the Insert tab,
Other Charts > All Chart Types.
| | 02:35 | How about Templates?
| | 02:37 | Now I only have one and as I
point to it what does it say?
| | 02:39 | GoldBackgroundStackedBar.
| | 02:40 | There we go, and OK.
| | 02:45 | So here is that new chart.
| | 02:47 | It has the colors, the color selections,
and I presume I've done a lot more too
| | 02:51 | with that, but nevertheless, I've got a
certain look that I want and I need that
| | 02:55 | as part of our repertoire of chart types.
| | 02:58 | Every time I create a chart
these templates are available.
| | 03:02 | So different approaches
here to the template idea.
| | 03:05 | You can easily change the default
chart from a clustered column to any of the
| | 03:09 | other chart types as you want, and
furthermore, you can have your own
| | 03:12 | collection of templates for frequent
use and you can get to them at any time
| | 03:17 | when you create charts.
| | Collapse this transcript |
| Dealing with empty and hidden cells | 00:00 | Two issues that you might have to deal
with in working with charts might be
| | 00:04 | what happens when you hide columns or
rows in data that's being depicted in a
| | 00:10 | chart, and what happens to blank cells.
| | 00:13 | The chart that we're seeing here to
the right of this data represents only
| | 00:17 | data from a few states and there is a
bit of an oddity in it in that some of
| | 00:22 | the cells are blank.
| | 00:24 | So for example, Arizona
third quarter 2010 is blank.
| | 00:28 | How does that display on a
line chart? Simply as a gap.
| | 00:32 | Now if the data is truly accurate
then the chart is truly accurate.
| | 00:36 | If those empty cells represent data
that has not yet been reported, we might
| | 00:41 | want to consider an alternative.
| | 00:43 | Do they truly represent zero? We
can actually put in the zero there.
| | 00:47 | But we do have some options, and on
the Design tab if you choose Select Data,
| | 00:53 | it's the fourth button from the left,
| | 00:56 | in the lower left-hand corner of this
dialog box Hidden and Empty Cells, two
| | 01:01 | separate issues really.
| | 01:03 | But let's deal with the empty cells issue here.
| | 01:06 | Show empty cells as: Gaps.
| | 01:08 | That's what we're seeing right now.
| | 01:09 | Would we want them to be displayed as zero?
| | 01:12 | Oh, possibly. Let's see how
that looks though on a line chart.
| | 01:15 | Let's click OK, click OK, and we see
what's happened now and I'm going to press
| | 01:21 | Ctrl+Z here for contrast.
| | 01:24 | That's how they had looked when we were
not showing them as zeros, and this is
| | 01:28 | how they look when they
are being shown as zeroes.
| | 01:31 | So is that the solution? If could be.
| | 01:35 | Let's click the chart again and go
back in on the Design tab to Select Data >
| | 01:42 | Hidden and Empty Cells.
| | 01:43 | How about this third choice,
Connect data points with line?
| | 01:48 | Click here and click OK.
| | 01:50 | Now watch the difference on
the chart when we click OK here.
| | 01:54 | See what's happening.
| | 01:55 | I'll press Ctrl+Z to go back for a second.
| | 01:58 | So here they are displayed as zero and here
they are displayed as connecting points.
| | 02:05 | Now, if those blank cells represent
data that hasn't yet been reported and what
| | 02:12 | we're saying in effect is the value
here as, for the moment at least or for a while,
| | 02:17 | assumed to be midway
between the two values that surround it.
| | 02:21 | We see no actual point right here
but that's what's being displayed.
| | 02:26 | Same thing happened over here with
Colorado between these two entries.
| | 02:29 | That's referring to cell G4 up there.
| | 02:32 | So you have to decide what's going to work
best for you here in these kinds of situations.
| | 02:36 | Now if I switch to a bar chart by
switching to Design and then Change Chart Type
| | 02:43 | and I'll use a Clustered Column perhaps.
| | 02:45 | It'll look better maybe.
| | 02:47 | Here we do have the absence of columns
but I think the display here might be
| | 02:51 | more reasonable if we truly
want to show these as missing data.
| | 02:55 | So we've got some options there.
| | 02:57 | Now another issue that in certain
sense isn't really related but we go to
| | 03:01 | the same dialog box and the same location on
the Design tab, is what we do with hidden data.
| | 03:08 | Suppose we still want to show this data here
but for the moment we only want to show 2011.
| | 03:15 | So I'm going to hide Columns B
through E, drag across, right-click, Hide, and
| | 03:22 | we're seeing the data this way, and
we see across the bottom we're only
| | 03:26 | seeing data from 2011.
| | 03:29 | That's the default view of this.
| | 03:32 | Now it could be that you might want to
show the hidden data and yet simplify the
| | 03:37 | display in the background.
| | 03:39 | So once again, clicking on the chart,
going to the Design tab, Select Data >
| | 03:46 | Hidden and Empty Cells > Show
data in hidden rows and columns.
| | 03:51 | You see immediately what happens in the
background here. We are seeing the data
| | 03:55 | for all eight quarters here, click
OK, even though it's not showing.
| | 04:02 | So maybe you can even hide these.
| | 04:03 | Now why do we want to hide the data?
| | 04:05 | I think most of the time you wouldn't,
but if it's busy and it takes up a lot of
| | 04:10 | room up there, maybe you just don't
want to show the data, and now all of the
| | 04:13 | numerical data is hidden.
| | 04:15 | In fact we can even do that for the moment,
and we're only focusing on three states here.
| | 04:21 | We're seeing all the data
and yet the numbers are hidden.
| | 04:24 | So at different times you do want
to think out the possibilities here
| | 04:28 | about what's to be done.
| | 04:30 | The default is not to show the hidden data.
| | 04:33 | So if you want to set it back to the
default we can go to the Design tab,
| | 04:36 | Select Data > Hidden and Empty Cells,
uncheck the box for Show data in hidden
| | 04:43 | columns and rows, click OK, and what
are we about to see on this chart now?
| | 04:46 | Nothing. Not so useful, is it?
| | 04:51 | Bring back the hidden columns.
| | 04:52 | We'll drag across here and double-
click the boundary that's one way and
| | 04:56 | there's our data again.
| | 04:57 | In this example too, we might
eventually consider showing all the states.
| | 05:01 | We'll just grab that lower right-hand corner.
| | 05:04 | It's like that it would be busy chart
and that's not a great chart at all.
| | 05:08 | Here the fesign change I think most
of us would want to at least explore is
| | 05:13 | changing the Chart Type
here to a Stacked Column.
| | 05:16 | Possibly a Stacked Bar.
| | 05:18 | Double-click there and even
that probably need some work too.
| | 05:22 | Here, a better choice might be to
Switch Row/Column, there we go, and maybe
| | 05:27 | ultimately that could be a better chart.
| | 05:30 | But again, we have methods of dealing
with hidden data and that might happen
| | 05:34 | when we hide rows and columns or
missing data or blank cells. Both of them come
| | 05:40 | from the Select Data button on the Design tab.
| | Collapse this transcript |
| Choosing a chart layout| 00:00 | No matter which method you use to
create a chart, adjusting a chart's layout is
| | 00:04 | often the critical step too, and the
chart layout options provide you with good
| | 00:09 | quick techniques to complete this vital phase.
| | 00:12 | This clustered column chart here to the
right of the data needs a title on top,
| | 00:17 | Probably needs a title down the left-hand side.
| | 00:19 | Maybe one across the bottom.
| | 00:20 | The legend maybe is placed
where I want it to be. Maybe not.
| | 00:24 | So what kind of options do we have?
| | 00:26 | There are a lot of extended
options by way of the Layout tab.
| | 00:31 | But quite a few choices here.
Take you long time to do what we want to
| | 00:35 | do relatively quickly.
| | 00:36 | That's on the Layout tab.
| | 00:38 | On the Design tab-- and we see the word
Layout here so a little bit of confusion
| | 00:43 | possibly-- the option called Chart Layouts.
| | 00:46 | Now because this is a clustered
column chart, the drop-arrow here for Chart
| | 00:50 | Layouts, there are 11 different options.
| | 00:55 | Just to show that things can vary
depending upon chart type, what if we change
| | 00:59 | this chart type to be a line chart?
| | 01:02 | What options do we have there?
| | 01:04 | We've got 12 options there.
| | 01:07 | Now, no matter which chart type we're
choosing these options, which I think at
| | 01:11 | first are a bit hard to figure out
what they're doing, most of them are
| | 01:15 | suggesting a title across the top.
| | 01:17 | Now you don't know that at
first until you try one of these.
| | 01:20 | Suppose I'll just pick one at
random, this one right here.
| | 01:22 | In other words, it gives
me space for a chart title.
| | 01:25 | So I'll click on this and I might
type in Sales - 2011 and type Enter.
| | 01:33 | I want an axis title over here, so I'll
just type in Sales in Millions, Enter.
| | 01:41 | It didn't give me a title down
below and what happened to the legend?
| | 01:44 | So maybe I didn't make a great choice here.
| | 01:46 | Go back to the drop-arrow.
Let's choose another one.
| | 01:49 | I want a Legend on the right-hand side,
so some of these show the legend on
| | 01:52 | the right-hand side.
| | 01:54 | I want a title on top.
I want a title on the bottom too.
| | 01:57 | Maybe this one here.
| | 01:58 | How is this one going to look?
| | 01:58 | Now that's got some kind of drop-lines in it.
| | 02:01 | Maybe I don't want this.
| | 02:03 | Let's try another one.
| | 02:04 | I think you get the idea here.
| | 02:06 | You're going to find one or another
that works best for you. And now that puts in
| | 02:10 | data labels. I could manually take
them out, but how about this one?
| | 02:13 | Now that doesn't show titles on top.
| | 02:15 | In other words, you just have to come to
some realization that some of these are
| | 02:19 | going to work better for you than others.
| | 02:22 | You'll find out eventually some of the
things that you need to do can be done manually.
| | 02:26 | Eventually here I'll find perhaps this
one here. It doesn't have a title on top.
| | 02:31 | Now maybe I'm picky or maybe
I just don't like the options.
| | 02:34 | In fact, maybe I'll change
this back to another type.
| | 02:37 | I might like Column better
anyway come to think of it.
| | 02:39 | Go back to Column and explore that.
| | 02:42 | Maybe I like this one.
| | 02:44 | Chart title? Sales.
| | 02:48 | Over here Millions of Dollars. Enter.
| | 02:55 | Maybe although it says Axis Title on
the bottom, maybe I don't want a title
| | 02:58 | there, so I'll just press Delete.
| | 03:00 | So that could be all I need and the
chart layouts options here give you choices.
| | 03:05 | Again, the choices you get not always
every single one will be to your liking
| | 03:10 | and you won't see as many choices or you'll
see more choices depending upon the chart type.
| | 03:15 | But it is for many people after having
created a chart, all they really need to
| | 03:19 | complete the look of the chart.
| | Collapse this transcript |
| Choosing a chart style from 48 colorful variations| 00:00 | One of the big changes introduced
into Excel charting in Excel 2007 was the
| | 00:05 | tremendous expansion of
capability regarding color.
| | 00:09 | When you click a chart you'll notice
on the Design tab Chart Styles and
| | 00:14 | initially you'll see eight choices,
but there's a drop-arrow over there and
| | 00:17 | suddenly you'll see 48.
| | 00:18 | Now many times this overlaps your chart,
but we can still make quick changes here.
| | 00:23 | There are so my choices here that
some people are just sort of overwhelmed.
| | 00:27 | Again, personal choice
here, I like this one here.
| | 00:29 | It gives a sort of a glossy,
sort of three-dimensional look.
| | 00:32 | But you can do this on any
chart you want, any type you want.
| | 00:36 | There is a pie chart to the right.
| | 00:37 | You'll see the pie chart choices there.
| | 00:40 | Maybe I'll choose this one and so on.
| | 00:42 | No matter what the chart type.
Pick the one that looks best to you.
| | 00:48 | With line charts I think you'll find
that the symbols are probably larger than
| | 00:51 | you want them to be, I know they are
for me, but those are adjustments that you
| | 00:55 | can make on the Layout tab or on
the Format tab or many times simply by
| | 00:59 | double-clicking, and we'll
cover that in another movie.
| | 01:02 | But you can easily make those changes as well.
| | 01:04 | Now, if the color selections here
aren't to your liking, which you can explore--
| | 01:09 | And this may take some time
and it could be huge tangent.
| | 01:12 | If you go to the Page Layout tab and
choose a different Theme and as you slide
| | 01:18 | over some of these themes, we can see
the charts in the background changing.
| | 01:22 | You might would have zoom back or in on
this before but as you slide over some
| | 01:27 | of these choices here, the joke is if
you're an indecisive person you're going
| | 01:31 | to have some problems here because the
choices seem to just go on and on and on.
| | 01:36 | Now when you're doing this too by the
way you're also making font changes.
| | 01:40 | Another reason for doing this might be
that maybe these charts are part of a
| | 01:44 | presentation that you are pulling
together and maybe that you are also using
| | 01:47 | PowerPoint and Word. You have similar
Theme choices there too. In fact the
| | 01:52 | exact same choices.
| | 01:53 | So you might want to be using the same
theme in all of those different software
| | 01:57 | packages so that your color
selections are compatible.
| | 02:01 | So this does create a
whole new set of variations.
| | 02:04 | Maybe I like Module here. I've chosen Module.
| | 02:07 | Notice how the font change.
More importantly perhaps the color.
| | 02:11 | But if we go here to the Design tab,
these are not the color choices we saw before.
| | 02:16 | They are quite a bit different.
| | 02:17 | They overlap here and there,
but generally different.
| | 02:20 | So you've got some flexibility
here as to how you want to use
| | 02:23 | these capabilities.
| | 02:25 | The main idea here is simply the chart
styles, the 48 different options that
| | 02:29 | you have to make your chart look
different and perhaps more appealing from your perspective.
| | Collapse this transcript |
| Changing the location of a chart| 00:00 | Sometimes it's preferable to position a
chart right next to the source of the data, but
| | 00:05 | at other time you might want this chart
on its own separate sheet and sometimes
| | 00:08 | the reverse is true.
| | 00:10 | You have got a chart occupying an
entire chart sheet and you've decide that you
| | 00:14 | want to work with it next to the data.
Move it from there back to a worksheet or
| | 00:19 | any other worksheet for that matter.
| | 00:22 | On the current screen we were seeing a
worksheet with a number of charts on it
| | 00:25 | and this chart here to the right
you've decided you want it to be on its own sheet.
| | 00:30 | Simply right click on the
chart area and choose Move Chart.
| | 00:37 | We want this to go to a new
sheet? Here it is. Click OK.
| | 00:42 | And it's on its own sheet and no longer will
we find it on the worksheet that it came from.
| | 00:47 | The new sheet will be to the left
of the sheet where it came from.
| | 00:51 | Here is that sheet. We no
longer see the chart here.
| | 00:54 | Chart to the right.
| | 00:55 | Maybe we want to do the same thing
there and of course you can also do this
| | 00:59 | from the Design tab.
| | 01:00 | It's the rightmost button,
Move Chart. There we go.
| | 01:03 | I want to put this on a
new sheet, Chart2. Click OK.
| | 01:07 | The advantage of working with a chart
all by itself is it's large, it's clear,
| | 01:12 | it's the only focus we have.
| | 01:13 | We're not seeing any data with it
and it's easier to work with perhaps.
| | 01:18 | If however you decide that this chart
is going to work better by having it on
| | 01:21 | the worksheet with the data or maybe
on a totally different worksheet, you can
| | 01:26 | do that as well too.
| | 01:27 | Once again, but this time
going in the opposite direction.
| | 01:31 | This chart is here on a sheet by itself.
On the Design tab, rightmost button, Move Chart,
| | 01:38 | let's click it and let's put
it on either the worksheet that has the
| | 01:44 | source data or possibly a new sheet.
| | 01:46 | Now we don't really want to put it on
the new chart sheet, and we could put it
| | 01:50 | on another data sheet if we wanted to.
Put it on the YearData sheet and that's
| | 01:54 | where we had it originally.
| | 01:56 | Click OK, and there it is back here.
| | 01:58 | And that other sheet, that
Chart2 sheet, has disappeared.
| | 02:02 | So it's fast and it's easy and at
different times you might change your mind
| | 02:05 | about the location of a chart.
And again, when you select the chart you can
| | 02:09 | either right-click on chart area or go
to the Move Chart button, the rightmost
| | 02:14 | button, on the Design tab
in the Chart Tools Ribbon.
| | Collapse this transcript |
| Moving and resizing a chart| 00:00 | With a worksheet chart you can easily
resize the chart and move it around as you choose.
| | 00:04 | You can use the Ctrl key, the
Shift key and Alt key for various kinds of
| | 00:09 | creativity in this process.
| | 00:10 | Suppose this chart to the right of the
data, I want to move it little closer
| | 00:14 | to the data maybe and I want to make it
larger or smaller, resize it in a variety of ways.
| | 00:19 | Let's explore some of the options here.
| | 00:21 | Sometimes you want a chart to be bigger
or smaller and at the same time you want
| | 00:25 | to maintain that ratio of height to width.
| | 00:28 | That sometimes is
referred to as the aspect ratio.
| | 00:31 | So on this chart here, first of all, if
I drag one of the corners, point to the
| | 00:36 | corner and see the double arrow there,
if I click and drag I can make this
| | 00:40 | wider, narrower, smaller, taller and go
anywhere I want, just sliding the mouse
| | 00:45 | around, left-mouse button held down.
| | 00:48 | However, if I hold down the Shift key
and I'm holding it down now, I can only
| | 00:53 | make this chart bigger or smaller and
keep that same ratio of height to width,
| | 00:58 | and if it is important I do want to be
doing this because I like the look of it,
| | 01:02 | I like the proportions, then be sure to
let go with the mouse before the Shift key
| | 01:08 | if you want that ratio to hold.
| | 01:09 | So I'm letting go with the mouse now.
So I made this chart somewhat bigger, but
| | 01:13 | I kept that same ratio.
| | 01:15 | Another option less likely to be used
is the idea that sometimes you want to
| | 01:19 | make a chart wider and yet keep it
same height. Sometimes you want to make it
| | 01:24 | shorter and keep the same width.
| | 01:26 | This time I'm about to drag this right
border and as I'm dragging it you can see
| | 01:31 | how it's going to get wider. No
surprises there, but look what happens when I
| | 01:34 | hold down the Ctrl key.
| | 01:36 | It's going to make it wider or
narrower as it keeps the same center.
| | 01:41 | So maybe it helps to remember C for center.
| | 01:43 | If you're holding down the Ctrl key
and resizing, you making it wider or
| | 01:47 | narrower if you drag a side handle
like this, but you are keeping the same
| | 01:51 | center. Here too I'll let go with the
mouse first or maybe I want to make this
| | 01:56 | shorter this way, but I
want to keep the same center.
| | 01:58 | And by the way, it's always best here
to start the dragging then hold down the Ctrl.
| | 02:02 | So I'm holding down Ctrl now,
you can see what's happing here.
| | 02:06 | Let go with the mouse first.
| | 02:08 | And similarly dragging from the corner
here, if I hold down Ctrl I'm changing
| | 02:12 | its size around the same center.
| | 02:15 | Now if you've worked with shapes and
objects in PowerPoint or Word or Excel,
| | 02:20 | these are old tips. Perhaps you are
familiar with them, but just an idea here.
| | 02:23 | I'm holding down Ctrl here,
resizing this, keeping the same center.
| | 02:28 | When you actually move a chart around
or change its edges sometimes what you
| | 02:32 | might want to do to make it look a
little neater and tidier with regard to the
| | 02:36 | data here on a worksheet, on this chart
here if I'm dragging the left edge here,
| | 02:40 | I can drag it anywhere I want, but
suppose I want the line up perfectly with the
| | 02:43 | cell boundaries there. Hold down the
Alt key and then it's as if the line has
| | 02:48 | like a magnetic attraction to a
cell edge. Let go of the mouse here.
| | 02:53 | And it's more efficient to do this on
opposite corners, because then you take
| | 02:56 | care of two edges at once.
| | 02:58 | I'm about to change the upper right-
hand corner here and drag it anywhere
| | 03:01 | I want, but if I hold down Alt it simply
forces the edges to line up with cell boundaries.
| | 03:07 | Let go with the mouse and then in
the lower left corner drag this one
| | 03:11 | around. Hold down Alt.
| | 03:12 | Maybe I'll put it right there.
| | 03:13 | Let go with the mouse.
| | 03:15 | And when you click outside of here,
you'll see how perfect that is if
| | 03:18 | that's your objective.
| | 03:19 | Maybe I'll do this one here too. Alt
key, that sort of thing, line it up that
| | 03:23 | way in the lower left, and so on. And not
to turn it into a make-work project, but you
| | 03:28 | can see how sometimes you like
that look. You like that appearance.
| | 03:32 | One other small issue here with
regard to moving charts, sometimes this is
| | 03:35 | important, if you hold down Ctrl and
click on a chart, and the term sounds a
| | 03:40 | little funny at first, you have allowed
the chart to be nudged, meaning I'm going
| | 03:45 | to push the right arrow few times now.
| | 03:47 | I'm moving the chart ever
so slightly. Now down arrow.
| | 03:50 | I'm not lining up with cell boundaries
here, but I'm moving the chart by using
| | 03:54 | the keys on the keyboard.
| | 03:56 | So if you Ctrl+Click on a
chart you activate those features.
| | 04:00 | So no shortage of ways here to move
and adjust the placement of charts on worksheets.
| | Collapse this transcript |
|
|
4. Layout Tab Options: Inserting Pictures, Shapes, and Text BoxesUsing pictures as chart elements| 00:00 | To enliven a column or a bar chart
simply to provide some descriptive imaging,
| | 00:04 | you can insert a clipart image or a
picture in place of the column or bar and
| | 00:10 | you can do this with pie charts as well.
| | 00:12 | In this particular worksheet, we've
got a pretty simple looking column
| | 00:15 | chart below the data.
| | 00:17 | Instead of having a red column here
let's put in a picture instead. Click the
| | 00:22 | red column and you might be tempted to
go to the Layout tab and choose Picture
| | 00:28 | and you certainly can do that. And if
you just pick a picture, I'm just picking
| | 00:32 | one of the standard ones out her,e say
Chrysanthemum, this isn't quite what we
| | 00:36 | want because it does use up the entire image of
the chart here. So I'm going to press Ctrl+Z here.
| | 00:42 | Fastest way to get in to this is
actually to double-click or to right-click
| | 00:46 | the series in question.
| | 00:48 | So in place of the red columns here
for Europe, why don't we double-click the red
| | 00:52 | columns and that takes us into a dialog box.
| | 00:56 | In 2010 double-clicking to activate
the features of an element is in effect a
| | 01:01 | resurrection of a feature that had
been in Excel 2003 and in prior versions,
| | 01:06 | but not in Excel 2007.
| | 01:09 | So simply by double-clicking, we
activate this dialog box, Format Data Series.
| | 01:15 | Choose the Fill option on the
left hand side, Picture or texture fill.
| | 01:20 | Now something might pop in
automatically here. Whoever was using this feature
| | 01:24 | last on this computer
used this particular texture.
| | 01:27 | For the moment that what's been chosen.
| | 01:29 | We could insert from, if we choose
File here, and of course we could look
| | 01:34 | anywhere on any of our drives.
| | 01:36 | This image or that one how
about the Koala bear right here?
| | 01:39 | Try this. Insert or double-click that
and we see the Koala bear on the image there.
| | 01:44 | And I have no reason for putting
a Koala bear in this, but if you run a
| | 01:48 | petting zoo and you have got koala bears,
| | 01:50 | maybe you want to that in your column chart.
| | 01:53 | Pick something else. File.
| | 01:55 | Chrysanthemum, we tried that
earlier in a difference sense.
| | 01:57 | This time it will work. You see what's
happening there. Maybe you sell flowers.
| | 02:01 | Part of the idea of course is something
that might be pertinent to the business at hand.
| | 02:04 | But let's take a look at
clipart and as usual, we might be spending a
| | 02:08 | long time out here finding just the
image we want. We want to show a car.
| | 02:12 | You could use race car. This one
just to show how this would work.
| | 02:16 | We're ready to insert the car here for
example. Just double-click it and it goes
| | 02:20 | right into the image this way, but
you'll also notice a choice here called stack
| | 02:25 | below here and you see the
difference in the image as you get close here.
| | 02:29 | Make this bigger possibly, although
it's not always the case we need to do.
| | 02:33 | And you can see what's happening
here with stacking of the images.
| | 02:36 | Maybe we want to do the
different image here for domestic.
| | 02:39 | And again, I'm using images seemingly
out of nowhere. In a real life situation,
| | 02:43 | I thinking you would want to be
using images that somehow relate to the
| | 02:46 | particular kind of data being depicted,
but it's easy to do and again just a
| | 02:51 | simple double-click or right-click on
the element in question and Format Data
| | 02:56 | Series and then use the Fill
option in Format Data Series.
| | 03:01 | I use this on a column chart. You can
see how on the bar chart here to the right
| | 03:05 | of the data these images are going to
be awfully small. Probably wouldn't be a
| | 03:08 | good choice there and on pie charts
these are usually not good choices as well.
| | 03:13 | I'm going to click on just one of the
wedges of the pie chart and then right-
| | 03:17 | click here on that wedge
format data point. And here too,
| | 03:21 | I think we would be hard-pressed to
come up with the statement that say we
| | 03:24 | really need to have a picture here.
| | 03:26 | Right now, popped in there was the car
because we had last used that. And maybe
| | 03:31 | a flower or something else
might look different here.
| | 03:33 | So putting some tulips, but once
again, what's the need for that?
| | 03:37 | Here and there, it makes good sense.
| | 03:38 | Maybe introduce a note of whimsy or
something, but at least you have this
| | 03:42 | possibility and I think it's more likely
to be used in column charts, putting in
| | 03:45 | either clipart or photo instead of column.
| | Collapse this transcript |
| Adding shapes and arrows | 00:00 | In certain charts, you may want to
shift the focus to a high point or low point,
| | 00:04 | by adding a line or an arrow possibly a
box or different shape. You might later
| | 00:09 | add text into those shapes as well.
| | 00:11 | Let's click on this chart just to the
right of the data here. It's a bar chart.
| | 00:16 | Select the chart and on the Layout tab
in the Chart Tools Ribbon, over in the
| | 00:21 | Insert group, Shapes.
| | 00:23 | There is a line, there is
an arrow. Let's pick an arrow.
| | 00:26 | If you're drawing a line or an arrow,
you can start anywhere on the chart, click
| | 00:30 | and drag it anywhere you want.
| | 00:32 | If you happen to be holding down the
Shift key as you do this, it only let's
| | 00:36 | you drag at certain angles.
| | 00:39 | So sometimes that's important. Sometimes
you want to be perfectly horizontal for
| | 00:42 | example. Draw it anywhere you want.
| | 00:44 | If you're concerned about angle, hold
down that Shift key but be sure to let go
| | 00:48 | over the mouse first.
| | 00:50 | And once it's drawn you can drag either
edge. If you want to change the angle,
| | 00:53 | do that with it, but notice at the same
time because this is an object, a Format
| | 00:59 | tab is active in the Ribbon and over
here for example under Shape Styles we
| | 01:03 | might want to choose one of these
options. And as I'm sliding over these, you can
| | 01:07 | see what's happening to that arrow.
So I'll pick one of those we might like,
| | 01:10 | something like that.
| | 01:12 | So that's an arrow and again we might
be pointing to a high value. That's often
| | 01:16 | the case, something like that.
| | 01:17 | Later we'll put in the box maybe or at
this point go back to the Layout tab,
| | 01:22 | back to Shapes, put in a box. It doesn't have to
be a box exactly. It could be any of these shapes.
| | 01:28 | I can't think of a good reason
necessarily to use a hexagon, but we could do that too.
| | 01:32 | And if you work with these in
PowerPoint and Word or possibly Excel you know
| | 01:36 | that if you hold down the Shift key,
whatever shape you're drawing here is a
| | 01:40 | so-called perfect shape, provided you
let go over the mouse first. And again, I
| | 01:45 | can't give you strong reasons as to
why you would want to use a hexagon there.
| | 01:48 | Maybe it's somehow related to the company logo.
| | 01:51 | Later you might want to right-click on
that and add some text, put it at the end
| | 01:55 | of the arrow, somewhere up here.
| | 01:56 | Maybe shrink it first.
| | 01:57 | If you're shrinking it or changing its
size, if you drag a corner, handle as they
| | 02:02 | are called here, hold down the
Shift key if you want to keep the same
| | 02:06 | proportion of height to width.
| | 02:08 | Again, let go with the mouse first. And
at different times in using these shapes
| | 02:12 | for different reasons,
when you're creating them.
| | 02:15 | Suppose we want to add another one here.
I'll go out once again to the Layout tab,
| | 02:19 | Insert > Shapes, just
pick another shape for an example.
| | 02:25 | Once again hold down Shift
to make it a perfect shape.
| | 02:28 | If you're holding down the Ctrl key by
the way, it creates a shape from wherever
| | 02:31 | you started outward.
| | 02:33 | So that has a different effect too.
| | 02:36 | So any number these could be used and
quite a few of the. Aagain, I think it's
| | 02:39 | nobody's objective here to try and use
all of them but quite a few of them here.
| | 02:43 | And in a later movie, we'll show you
how to put text inside these boxes as well.
| | 02:47 | So here and there, use for
annotation purposes. You can add lines, boxes,
| | 02:51 | arrows and other capabilities from
this huge menu on the Shapes button on the Layout tab.
| | Collapse this transcript |
| Adding floating text and text boxes| 00:00 | If you need to add an explanation to a
chart to explain why a value is so high
| | 00:04 | or so low or you want to raise a
question or simply add your own free-floating text,
| | 00:09 | you can do this on
any chart from the Layout tab.
| | 00:13 | I am going to click on the chart just to
the right of the data here, Layout tab.
| | 00:18 | You will see Text Box.
| | 00:20 | This is one option. Let's click this
and then on the chart and no particular
| | 00:24 | location maybe, just click here.
| | 00:26 | We want to put out a word of
congratulations to Jim here who is the manager
| | 00:30 | of this group for example and say
"Good job, Jim" or something like that.
| | 00:37 | simply click outside that.
| | 00:38 | Now, it really is associated with
a box even though we don't see it.
| | 00:42 | Now, if we happen to click
back there, you will see the text.
| | 00:44 | I think one thing we would probably
want to do here is click the border of it
| | 00:48 | and then on the Home tab for
example, we can make the text bigger.
| | 00:51 | As I am sliding over the numbers here,
you see what's happening to the text.
| | 00:54 | Possibly that and we can move this around of
course and we can resize this to look like that.
| | 01:01 | If we want the actual box to have a
background in it, we can click on the Format tab.
| | 01:06 | It's a Drawing Tool Ribbon addition up there.
| | 01:09 | You might fill in the background with
this color or that color or whatever.
| | 01:12 | Easy to do and fast.
| | 01:14 | And you can also do this with other
symbols found on the Layout tab over in the
| | 01:19 | Insert group under Shapes.
| | 01:22 | And pick any of these
shapes that encompass some space.
| | 01:24 | Here is a pentagon. Pop it in there,
right click on it, it says Edit Text even
| | 01:30 | though there is nothing there yet, and
maybe we want to say Outstanding totals
| | 01:40 | for June, something like that.
| | 01:44 | There it looks a little bit strange
because of the word wrapping. Make it bigger or
| | 01:47 | smaller or whatever.
| | 01:48 | And once again, when you select the
border here, you have a number of tools
| | 01:52 | available on the Home tab. Quite a few
on the Layout tab as well. Probably want
| | 01:56 | it centered this way, put it in the top,
bottom, that sort of thing, all kinds
| | 02:00 | of different things.
| | 02:01 | Maybe we want the color of the
box to be different or translucent.
| | 02:04 | Move it around, associate it with arrows as
well if you wish, you want to add those too.
| | 02:10 | Either for explanations or just putting
in comments like this, you can do these
| | 02:13 | on any of the charts here.
| | 02:15 | And again, the process began
simply on the Layout tab, starting with
| | 02:20 | Shapes and/or Text Box.
| | 02:22 | The text as I mentioned
too could be free-floating.
| | 02:24 | So, just one more quick time on this
here Text Box. Just click and start
| | 02:28 | typing anything you want.
| | 02:30 | We don't necessarily need to put it in a box.
| | 02:32 | So we might just say here Regional Sales.
| | 02:37 | In fact, in a certain sense, you could use
this in place of a main title if you wish.
| | 02:40 | Now, this is probably a little bit
more work to make that work properly for
| | 02:43 | that feature but temporarily here I am
using it for that reason. But we could
| | 02:47 | make it larger and do all the
things you are familiar with doing and
| | 02:50 | manipulating text off the Home tab.
| | 02:52 | So, good quick ways to add boxes along
with other arrows and features to a chart
| | 02:57 | for explanatory or congratulatory reasons.
| | Collapse this transcript |
|
|
5. Layout Tab Options: Adding Titles, Labels, and LegendsAdding, editing, and removing chart titles| 00:00 | At different times when working with
charts, you need to add titles, you need
| | 00:03 | to edit existing titles, or
sometimes even remove titles.
| | 00:07 | Probably the best way to deal with
titles is just after you've created the chart,
| | 00:11 | on the Design tab explore
some of the options under Chart Layouts.
| | 00:16 | Now, sometimes you don't use these
because some of them just don't fit your
| | 00:19 | particular needs or sometimes you
inherit a chart or you have copied a chart.
| | 00:24 | How do you add a title strictly focusing on
the title and placing it where you want it to be?
| | 00:29 | Use the Layout tab in the Ribbon, Chart Title.
| | 00:33 | First two options, once we try one or
the other, immediately we can see the
| | 00:37 | difference. Centered Overlay Title.
| | 00:39 | In other words, it doesn't
move the plot area at all.
| | 00:42 | The title will overlap the top.
| | 00:44 | That might be what you want.
| | 00:45 | How about the other choice?
| | 00:47 | Above Chart, possibly here.
| | 00:50 | In either case, no matter which one you have
chosen, now is the time to type in the title.
| | 00:55 | So, it doesn't necessarily have to
reflect what's on the worksheet but
| | 00:59 | presumably it's a description of the data.
| | 01:01 | So, maybe this is going to say, for
example, 2011-annual sales or sales for
| | 01:08 | certain region, that sort of thing.
| | 01:09 | As you're typing, you will see the data
in the formula bar, but you don't see it
| | 01:14 | on this chart until you press
Enter. So it looks like that.
| | 01:17 | Now, it is in a box. You can format
that if you wish off the Format tab or
| | 01:21 | possibly go to the Home tab and
choose a different font size if you wish.
| | 01:26 | See what's happening here.
You can certainly adjust that.
| | 01:28 | Choose a different font, all those many,
many choices out there you can explore
| | 01:32 | just as easily, if you
want to make changes there.
| | 01:36 | Click outside of it.
| | 01:37 | At a later time or maybe it's the
first time you're seeing it, sometimes you
| | 01:41 | want to change the content.
| | 01:42 | So, we might just click here.
| | 01:44 | Maybe we want to indicate that this is
sales throughout the world or international.
| | 01:50 | Click in front of the S for example.
I'll type in worldwide or total whatever.
| | 01:59 | Click outside and make both Ws
capitalized, whatever it is you want to do there.
| | 02:03 | If you want it to be a two line, three
line title, as you click here and then
| | 02:07 | click again, you could press
Enter at a certain point here.
| | 02:10 | That may not be the best display but you
have that option. How about after the W
| | 02:14 | up there, take out the hyphens?
You can do that as you wish.
| | 02:18 | So, you're in complete control of
the title, to move it anywhere you want.
| | 02:23 | And at any time when you want to get rid of
it of course, just click on it and press Delete.
| | 02:27 | So, you do have control over the main title.
| | 02:29 | Again, keep in mind that idea that if
you have just created the chart, it might
| | 02:33 | be a bit faster if you become familiar
with the chart layout types to go down
| | 02:37 | that way to position your title. But
certainly at different times, you will need
| | 02:41 | to go to the Layout tab and use
some of the Chart Title options.
| | Collapse this transcript |
| Adding horizontal and vertical titles| 00:00 | You can add vertical and horizontal
titles located on the left and bottom sides
| | 00:04 | of a chart by using the multiple options
available from the Axis Title button on the Layout tab.
| | 00:10 | So, on this particular chart, it's
practically mandatory to have a title
| | 00:13 | down the left-hand side that explains
what these numbers mean and although
| | 00:17 | less important, sometimes you want
to have a horizontal title across the
| | 00:20 | bottom of the chart.
| | 00:22 | Suppose we want to explore both of these.
| | 00:24 | The chart has been selected. On the
Layout tab in the Ribbon in the Labels
| | 00:28 | group here, Axis Titles.
| | 00:31 | Let's say for the moment we are
thinking about the left-hand side there.
| | 00:34 | That's a vertical axis.
| | 00:36 | We have got a few options here.
| | 00:38 | Before putting in the text,
let's just explore the options.
| | 00:40 | How about a Rotated Title?
| | 00:42 | This is probably the most
common of the three choices.
| | 00:45 | We want this to say in effect, Sales
in Millions or Millions of Dollars,
| | 00:49 | something like that, and you could
imagine how the text would appear.
| | 00:53 | Another option here under Axis Titles
Vertical, Vertical Title and this is not a
| | 00:59 | favorite of most people.
| | 01:00 | If the text is short, that's probably
okay but longer text, often it doesn't fit
| | 01:06 | and it is more difficult to read.
| | 01:07 | And a third choice up here, Axis
Titles Vertical, Horizontal Title, even
| | 01:13 | though it's on the vertical axis, and
this too isn't going to work so well,
| | 01:17 | particularly if the text is very long.
But it does have its uses here and there.
| | 01:21 | I am going to go back to Axis Titles
and choose under Primary Vertical Axis,
| | 01:26 | Rotated Title and just put in
for example, Millions of Dollars.
| | 01:30 | Now, presumably, I might have the words
sales either in the main chart title or
| | 01:35 | in the horizontal title at the bottom.
| | 01:37 | So I can put it in here if I wanted to.
| | 01:41 | And because I chose the Rotated option,
as I press Enter here we see what's
| | 01:45 | happened to the text here on the left
hand side on the Primary Vertical Axis.
| | 01:50 | How about data below the chart?
| | 01:52 | Probably not often as necessary as
this one is likely to be but certainly at
| | 01:56 | times you want that.
| | 01:57 | Axis Titles Primary Horizontal and only
one real choice here, Title Below Axis.
| | 02:04 | And maybe we just want to put in for
example the year, something that we could
| | 02:08 | have put in and might want to put in a
chart title. But you can choose to put
| | 02:12 | whatever titles you want and
where you want to put them.
| | 02:14 | So, maybe here we want to
put in 2011 - Sales by Month.
| | 02:20 | Perhaps that's obvious but still,
sometimes we say the obvious in out titles and
| | 02:24 | at some point press Enter
and we see the title there.
| | 02:26 | And in both situations here of course,
at different times you might want to get
| | 02:30 | rid of these and start over or edit them.
| | 02:33 | And so, if we want to edit this for
example, the lower title here, might just
| | 02:36 | click in there, click in front of
Month and put in the current month,
| | 02:41 | something like that.
| | 02:42 | In other words, just the idea that
you can edit this text as you wish.
| | 02:46 | On both of these choices too, you might
have noticed another choice in using the
| | 02:50 | Axis Titles options, say on Horizontal,
both here and on Vertical title.
| | 02:56 | If you make these choices, More Primary
Horizontal Axis Title Options or in the other case,
| | 03:01 | More Primary Vertical, this simply
leads you into some formatting choices,
| | 03:06 | which you might want to explore as well,
things that you possibly might get to
| | 03:09 | off the Home tab as well.
| | 03:11 | So, two different ways here to
approach the idea of adding titles, sometimes
| | 03:16 | horizontal titles across the bottom
and sometimes vertical titles along the
| | 03:22 | vertical axis on the left-hand side of a chart.
| | Collapse this transcript |
| Linking titles to content| 00:00 | Instead of typing say a main title or
any other title on this particular chart,
| | 00:05 | you can link a chart title
directly to cells in the worksheet.
| | 00:10 | Let's imagine here that the main chart
title here might as well be the same data
| | 00:13 | that we see in that cell A1,
Two Trees Olive Oil Company.
| | 00:18 | That's a merged cell but that's okay.
| | 00:20 | So how do we add a title?
| | 00:22 | After selecting the Chart, Layout tab,
in the Labels group Chart Title, how
| | 00:29 | about a title above the chart,
and there we see the text.
| | 00:33 | Now without actually clicking in the
formula bar, watch what happens as I type
| | 00:38 | equal and you see the
equal sign in the formula bar.
| | 00:40 | And now I'm going to click on cell A1
and simply press Enter, and so that data
| | 00:45 | is about to appear as the chart title.
| | 00:48 | There it is right there.
| | 00:50 | And in similar fashion, I might
want to put in a title for the Primary
| | 00:53 | Vertical Axis here.
| | 00:56 | So on the Axis Titles button in that
Labels group on the Layout tab, Primary
| | 01:02 | Vertical Axis Title, Rotated Title, this
time equal, click on cell A2, and press Enter.
| | 01:11 | So we're picking up data from the
worksheet, saving us some typing and getting
| | 01:17 | the appropriate information
that we want in the chart.
| | 01:20 | I don't have any other data on this
chart that I might want to put in the
| | 01:23 | title below this, so perhaps we'll use it
there but you can see how easy this is to do.
| | 01:29 | And if you make editing choices here
in either of these cells of course you
| | 01:33 | might expect the label here to change.
| | 01:35 | So if we change this from Worldwide to
International or Total, something like that,
| | 01:39 | as soon as I make the change here
and press Enter, the chart will react and
| | 01:44 | you can see the title here.
| | 01:46 | It has changed also.
| | 01:47 | So we can have our titles
directly tied to worksheet data.
| | Collapse this transcript |
| Showing numbers of different scales| 00:00 | With mixed data containing widely
divergent values, sometimes charts can appear
| | 00:04 | distorted or even illegible.
| | 00:06 | There is an adjustment that allows you
to have a different vertical axis on the
| | 00:11 | left side than you do on the right side.
| | 00:13 | And I think this is best shown by an example.
| | 00:16 | Let's take a look at the data here
on this sheet called Column-line.
| | 00:19 | Perhaps this represents some
vehicles that you have in your organization.
| | 00:23 | You're tracking miles traveled per
month and also miles per gallon.
| | 00:27 | If we simply click on any one cell in
here and press Alt+F1, we've got a chart.
| | 00:34 | But I think almost immediately we see
that something is a little bit off here.
| | 00:37 | We can see the miles here being
plotted and we can see how it matches up with
| | 00:41 | the data in column B, but
how about miles per gallon?
| | 00:45 | It's in the legend, the red box,
but where is the red box?
| | 00:49 | As you look at the numbers, you
begin to think what's happening here, those
| | 00:52 | miles per gallon numbers are what 10,
20, 30 in that general range, and the
| | 00:57 | first horizontal gridline we have
above zero here is the thousand. How are we
| | 01:01 | supposed to see numbers like that?
| | 01:03 | So the miles per gallon are actually
on this chart but we can't see them.
| | 01:07 | Now, if we could somehow show them
and read off the right axis, which we
| | 01:12 | don't yet have, this is the work around and
the first trick-- and it's not really a trick.
| | 01:17 | It's a technique, We need to be
able to select this series called MPG.
| | 01:22 | So, on the Layout tab, the leftmost
group is called Current Selection and the
| | 01:29 | drop arrow here that might say Chart
Area or Plot Area, depends upon where you've
| | 01:33 | clicked before going here.
| | 01:35 | If we click the drop arrow, we'll see
all selectable elements in this chart and
| | 01:41 | we want to go to and select series MPG.
| | 01:45 | Now as I click here watch column C be
selected. So we've selected the series and
| | 01:51 | when you look on the chart you see
it's selected as well but you really could
| | 01:55 | never have actually clicked
on it because it's so tiny.
| | 01:58 | But once it has been selected then we
can go to the choice just below this,
| | 02:03 | Format Selection, and what we want
to do almost immediately is choose
| | 02:07 | Secondary Axis, there we go, and you
can see already what's happened on the chart.
| | 02:12 | Let's just close this.
| | 02:14 | And we are not quite there yet, but
now it's starting to make a little bit of sense.
| | 02:17 | Unfortunately the columns are
overlapping one another, but the red
| | 02:21 | columns that we're seeing represent
miles per gallon and we are reading that
| | 02:25 | off the right side.
| | 02:26 | Now the best next alternative is to
take that series, which is still currently
| | 02:31 | selected, and turn that series into a
line chart. So you can mix chart types.
| | 02:37 | This is probably the most
common type of mixed chart types.
| | 02:40 | Let's go to the Design tab, change
the chart type, and remember we're not
| | 02:45 | changing the entire chart. We're
changing just this selected range to be a
| | 02:50 | line chart, right here with the
markers. Double-click a little bit faster.
| | 02:55 | Now it's starting to make some real sense.
We can change the colors and spiff it up
| | 02:59 | a little bit, but now with only a brief
explanation necessary, we can say as we
| | 03:04 | look at the data, the blue columns
represent miles driven in any given month and
| | 03:10 | we read that off the left vertical axis.
The lines and the boxes represent miles
| | 03:15 | per gallon for any given month.
We read that off the right axis.
| | 03:19 | Notice that the tick marks don't quite
line up and are little not critical, but we
| | 03:23 | could rescale the right axis here
simply by double-clicking it. And then on
| | 03:28 | the Axis options here, maybe
changing the maximum here to be 50 possibly.
| | 03:34 | It will cause those numbers to
line up a little more evenly.
| | 03:38 | That maybe more than we need to do and
I'll just press Enter after changing the
| | 03:41 | fixed maximum to be 50.
| | 03:44 | So now we see it's something like this.
| | 03:46 | So at this point, there's a direct
ratio between the numbers on the right and
| | 03:50 | the numbers on the left,
even though they're not exact.
| | 03:53 | So, I think you could see how
this can work in your favor.
| | 03:56 | Long time users of Excel charts were a
little bit disappointed in Excel 2007
| | 04:01 | when the term combination chart was no
longer available as a chart type, but in
| | 04:05 | effect you can create your own
combination charts using different types and
| | 04:09 | that's what I did in this example here,
to show you how you can relate numbers of
| | 04:14 | widely divergent scope on the
same chart using multiple axes.
| | Collapse this transcript |
| Specifying the position of tick marks and axis labels| 00:00 | Tick marks are the tiny little
indicators that appear along axes.
| | 00:03 | In this particular column chart here,
we see them between the months across the
| | 00:08 | bottom of the horizontal axis here.
| | 00:10 | I think many, many times, you simply
ignore them. They don't get in the way.
| | 00:14 | Occasionally you do want to
have some control over them.
| | 00:17 | They also appear on vertical axis but in
the example here, they do match up with
| | 00:21 | the gridline so you're hardly aware of them.
| | 00:23 | On the different sheet here that called
Line Chart, on this line chart there is
| | 00:27 | a feature active called Drop Lines,
which is occurred in another movie.
| | 00:32 | However, they match up with the dates, which is
probably what you want. The tick marks don't.
| | 00:37 | So we do have some control over this.
| | 00:39 | Now selecting the axis sometimes is a
little bit tricky. You might click here
| | 00:43 | and-- have we selected the axis?
Well on the Layout tab that leftmost group
| | 00:49 | called Current Selection does have a
drop arrow and we can't quite click on that
| | 00:53 | axis prior to double-
clicking to get into a dialog box.
| | 00:57 | You can click this drop arrow and
choose the axis we're talking about here,
| | 01:02 | the Horizontal Category Axis.
| | 01:04 | And then from there you can jump right
into the Format Selection and you don't
| | 01:07 | have to worry about double-clicking somewhere.
| | 01:10 | And we see some choices in here for tick marks.
| | 01:13 | Right now a major tick mark type,
Outside, let's change that possibly to be Inside
| | 01:18 | Keep your eye on those tick
marks to the left and you'll see how they're
| | 01:21 | now inside the actual plot area. And
the other choice might be Cross and there
| | 01:26 | it crosses, but maybe the more
important to you is the position of the tick
| | 01:31 | marks and the last two choices here, the
two opposite choices. On the tick marks,
| | 01:35 | watch the difference now.
There we see them between tick marks.
| | 01:39 | Of course, if we don't want them at all,
ignore these choices and then come back
| | 01:42 | here and choose None.
| | 01:44 | And in certain kinds of charts, you
might want to have minor tick marks as well.
| | 01:48 | We certainly wouldn't need them here.
| | 01:50 | And when you're looking at different
charts at different times be aware of
| | 01:53 | these possibilities.
| | 01:54 | Now, sometimes when you
change a chart, maybe this one,
| | 01:57 | maybe another one, we might want to
change this to be a bar chart. So let's go
| | 02:01 | to Design > Change Chart Type, how
about a horizontal bar chart here, and we've
| | 02:09 | got tick marks here as well too. But
maybe not so much tick marks are we
| | 02:13 | concerned about but the actual
placement of the data itself.
| | 02:16 | In other words, if we look at the
months as we see them here and look at the data,
| | 02:22 | I think a lot of us just say,
isn't it upside down? Here's January 09
| | 02:26 | at the very bottom.
| | 02:28 | We are seeing every other month, we'll
get to that issue in a bit, but we're also
| | 02:31 | seen these upside down compared with the data.
| | 02:34 | So this time, we want to choose the
vertical axis and on the Layout tab, if we
| | 02:39 | can't quite click on it so easily,
we'll make this choice again.
| | 02:42 | That left arrow there, the Vertical
Category Axis here as it's called, and then
| | 02:47 | Format Selection. And the tick marks,
of course we can adjust those but while
| | 02:52 | we're here let's take a look at this
option called Dates in reverse order.
| | 02:57 | Click this, watch the chart.
| | 02:59 | And now the top-down order here matches
the data that we see over in column A.
| | 03:04 | Now you may or may not like the titles
up top appearing there. Clicking there,
| | 03:10 | the dialog box reacts. The axis
labels and this is next to Axis.
| | 03:16 | Now you would think choosing a low
means what? Put these below the data?
| | 03:20 | It doesn't change it at all.
| | 03:23 | How about high? I think that is exactly
something what we call non-intuitive,
| | 03:28 | maybe even anti-intuitive, but
we see the labels below this.
| | 03:32 | And these are related to the tick marks
that we talked about as well too. You'll
| | 03:36 | see how they're playing out.
| | 03:37 | So even though we introduced this selection
as being something along tick marks and it was,
| | 03:41 | it also includes the whole idea of how
you control the labels that accompany the
| | 03:45 | tick marks in certain charts.
| | Collapse this transcript |
| Changing the numeric format on labels| 00:00 | When the chart axis contains numerical
information like in the example on this
| | 00:04 | column chart right here, in general
the formatting that you see in the chart
| | 00:09 | matches the formatting in the source data.
| | 00:12 | In fact, you might think they're
always in sync, because if we were to make a
| | 00:16 | change to this data, maybe eventually we
will be needing decimal points here as
| | 00:20 | we plug in actual data as
opposed to projected data.
| | 00:24 | I am going to change the format here on
the Home tab by putting in a comma and
| | 00:29 | then decreasing the decimals here and we
already are seeing on the chart how the
| | 00:33 | numbers are changing and
watch them change again.
| | 00:36 | Now the reverse is not true.
| | 00:38 | We can change the actual chart format
and that does not change the source data.
| | 00:45 | And so we wouldn't want to see the
zeros here, so how can we make a change?
| | 00:50 | A couple of different ways. Probably the
fastest way is simply double-click the
| | 00:54 | axis and that brings up the appropriate
dialog box, Format Axis. Choose Number
| | 01:01 | and pick a style here that
doesn't include decimal places.
| | 01:05 | Maybe we will adjust that, put in 0.
| | 01:07 | We haven't reached to 1000 but
eventually we could, so leave that in place perhaps.
| | 01:11 | We are all set here. Close.
| | 01:14 | And so the display is different, and
now if we happened to change these,
| | 01:19 | we wouldn't add dollars signs probably, but if
we did that anyway, you see how this is
| | 01:22 | not causing any change in the
chart. Let me undo that one.
| | 01:26 | Now similarly in other charts, for
example, a line chart here on this
| | 01:30 | different worksheet.
| | 01:31 | Here the issue could be
twofold. One is the dates.
| | 01:35 | Nothing really wrong with these, but
you might want to have full spellings of
| | 01:39 | the months here and yet not
make that change in the data.
| | 01:43 | So once again, double-clicking along
the horizontal axis in the bottom here.
| | 01:47 | Double-click and simply choose in this
Format Axis dialog box, the Number tab
| | 01:53 | and we'll use a Date option here that
uses the full spelling of the month.
| | 01:57 | There we go, right here.
| | 01:59 | And immediately you see
what's happened in the chart.
| | 02:01 | So possibly that's a
choice you might want to make.
| | 02:04 | Sometimes you might be
going in the opposite direction.
| | 02:07 | Now what about the vertical titles
along the left-hand side here? Wouldn't it
| | 02:11 | be nicer if we had 25 and 20 and 15 and 10 and
5 and so on? This would be a lot easier to read.
| | 02:18 | So let's select that and then choose
Layout in the Ribbon and then Axes >
| | 02:25 | Primary Vertical Axis and
we see some choices here.
| | 02:29 | Now unfortunately at the moment it's
overlapping but we will see pretty quickly
| | 02:31 | what's happening here.
| | 02:33 | Show Axis in Millions, there we go.
| | 02:36 | That's a lot more readable.
| | 02:38 | And for this label right here,
we might want to expand it a little bit, say
| | 02:41 | Sales in Millions, Profits in Millions,
whatever it might be, just click in there.
| | 02:46 | Space, in, space. There we go and Enter.
| | 02:49 | And maybe move that around a little bit,
resize it and so on, all the things we
| | 02:52 | might do. Click outside of it,
move it around as needed.
| | 02:56 | So that's more readable and we
are not flooded with zeros up there.
| | 03:00 | Different ways to control the
numeric formatting in a chart.
| | 03:05 | Anytime you need to change the numeric
format of an axis entry in Excel, simply
| | 03:10 | by double-clicking that portion of the
chart, as we saw, it activates the Format
| | 03:17 | Axis dialog box and you can use the
Number option, and there is that special
| | 03:21 | choice for certain millions that we
just saw also after selecting the area on
| | 03:26 | the Layout tab. Choose Axes and
explore some of the choices there.
| | Collapse this transcript |
| Adding, editing, and removing legends| 00:00 | Although most charts should contain
legends to help explain what chart
| | 00:03 | elements refer to, you need to
know how to remove a legend, how to add a
| | 00:08 | legend on those charts that don't have them,
and edit or reposition legends within a chart as well.
| | 00:12 | Pretty easy stuff here and you can achieve
these objectives a couple of different ways.
| | 00:16 | In this chart, there is a legend on
the right-hand side and that's just fine.
| | 00:19 | That's the default location.
| | 00:21 | I think that's where we
are used to seeing a legend.
| | 00:23 | However, if we select this chart and
go to the Layout tab, we can choose the
| | 00:28 | option Legend, and instead of showing it
on the right, show it on top. Sometimes
| | 00:34 | that looks better. Personal choice of
course. On the left, and without going
| | 00:38 | through every single one
of these, you get the idea.
| | 00:40 | You might notice the last two here.
Overlay Legend at Right, certainly not a
| | 00:45 | good choice here. Overlay
on the Left. Maybe more so.
| | 00:49 | No matter where the legend does
end up, you can click and drag it.
| | 00:52 | We could have done that from the
beginning before even going into the menu,
| | 00:55 | but you can click and drag this.
Usually just click on it, drag a border, put
| | 00:59 | it wherever you want.
| | 01:00 | And if you want to leave it there, that's fine.
| | 01:02 | A good follow-up on this would be to
give it a background color, zip out to the
| | 01:06 | to the Format tab perhaps, choose one
of these options, pop a color on it.
| | 01:10 | There we go. And if you have to see
those ahead of time, you can certainly do this.
[00:01:134.15]
Drag it around and explore what
those might look like. So, easy to handle.
| | 01:20 | If the legend is larger, if it
encompasses more entries than this one, sometimes
| | 01:24 | what you will want to do
to possibly is resize this.
| | 01:27 | If we had four entries there it might do
this or stack them up two by two and so on.
| | 01:32 | So you can resize the legend as well.
| | 01:34 | And you can certainly get rid of a legend
just by clicking on it and pressing Delete.
| | 01:38 | Again, most of the time you probably do
want a legend, but there are certain pie
| | 01:41 | charts when you might have applied
data labels, even bar charts or the label
| | 01:45 | that contains all of the
information that the legend might.
| | 01:48 | So it's easily added. And if we simply
want to add legend right after we create
| | 01:53 | a chart, it's not a bad idea to go to
the Design tab and choose one of the
| | 01:58 | Chart Layout options that has legends
in it, and at the same time pick up on
| | 02:02 | the titles as well.
| | 02:03 | Many of these have legends on the
right-hand side. After you get used to this
| | 02:07 | particular feature you will recognize
some of that have the legend on the bottom
| | 02:10 | too, for example this one.
| | 02:12 | So you see different options there,
different ways to control the placement of
| | 02:15 | a legend on a chart.
| | Collapse this transcript |
| Adding and editing data labels| 00:00 | Trying to interpret the value of each
color segment in the stacked column chart
| | 00:04 | here is a little tricky. Not so accurate.
| | 00:07 | We can add to this chart what are
called data labels. The actual values of each
| | 00:12 | piece of these columns could be
displayed right on the columns.
| | 00:16 | These are called data labels. Let's
select the chart here and on the Layout tab
| | 00:22 | in the Labels group, Data Labels.
| | 00:25 | The three choices here, Center, Inside
End, and Inside Base, are really about the
| | 00:29 | positioning of the labels.
| | 00:30 | Let's just choose one of
them, for example Center.
| | 00:33 | I think a lot of people would agree
that's pretty crowded there and do we
| | 00:36 | really need to see all those numbers.
| | 00:38 | Well, here and there maybe you do.
| | 00:40 | And how is this going to play out on
the different chart time, for example, a
| | 00:44 | clustered column chart?
| | 00:46 | Going to the Design tab in the Ribbon,
the leftmost button, Change Chart Type >
| | 00:51 | Clustered Column, and I think here
you would agree that's practically
| | 00:55 | uninterpretable, if that's the word.
We can't see where the numbers apply.
| | 00:59 | A not so obvious alternative to this,
and you would have to have the need to
| | 01:03 | actually do this, is to have the data
labels be applied to only one series.
| | 01:08 | So if we were to click just the blue
columns here and go back to the Layout tab
| | 01:14 | and choose Data Labels, we could first
choose None and by choosing None we take
| | 01:20 | the labels off of just that series.
We would have to click the other series and
| | 01:24 | choose None and then the third series.
| | 01:26 | Now I could've done that more
efficiently by not having selected any series but
| | 01:30 | we are back to this stage.
| | 01:31 | So once again, clicking the blue columns
here, Data Labels. How about Inside End?
| | 01:36 | Different approach there. Or another
alternative there, Outside End, probably
| | 01:41 | better in this example here.
| | 01:42 | So we're showing this only for the
blue series, the domestic series.
| | 01:47 | Another approach here is to say I only want
these numbers on two or three of the columns.
| | 01:52 | So let's remove the Data Labels, choose None,
and then pick for example this highest one.
| | 01:58 | Now we have to click the series first.
In other words click one of them.
| | 02:02 | The entire series is selected. Pause for
just about a second, click that one again,
| | 02:06 | and we selected only a single point.
| | 02:09 | And by the way, for reinforcement on
that you will notice in the current
| | 02:12 | selection group on the Layout tab,
the left button will say Series Domestic
| | 02:17 | Point or Europe or Asia in this example here.
| | 02:20 | So we have selected only one point.
Come back to Data Labels and maybe we will
| | 02:24 | put a label for that location only.
| | 02:27 | And of course, we could do this
for other columns as well one by one.
| | 02:31 | So that's another option to consider there.
| | 02:33 | Let's remove these.
| | 02:35 | And you might be using
other kinds of charts as well.
| | 02:37 | Possibly a line chart, and then if we
went to a line chart, I think here too you
| | 02:42 | might have some issues regarding how
clearly these labels are going to appear
| | 02:46 | here and I think you can sense ahead
of time if we apply data labels to every
| | 02:50 | point here, it's going
to be a bit of a disaster.
| | 02:54 | So on the Layout tab, choosing Data
Labels and let's just choose Above.
| | 02:59 | That seems like it would be the best
choice perhaps, but you see what's going on
| | 03:02 | there. Not a great choice.
| | 03:04 | And here too, you might either want to
show fewer months in the example or just
| | 03:08 | apply this to one series only.
| | 03:11 | And how might this look if we
switch the chart type to a pie chart?
| | 03:16 | There is the 2D pie right here. This is
a reminder too why we sometimes frown on
| | 03:22 | using pie charts when they have
more than five or six sectors.
| | 03:25 | It's going to be a bit tricky to read.
| | 03:27 | Would the data labels help here?
| | 03:28 | Well, yes they should.
| | 03:31 | On the Layout tab, back to Data Labels.
| | 03:34 | Maybe in the center.
| | 03:36 | And you can explore where
you want to put them here.
| | 03:39 | I think may be that might be a better choice.
Or possibly Outside the End is another choice.
| | 03:44 | And similarly if we change the design
of this to a 3D pie chart, and of course,
| | 03:51 | we can make that be more 3D. We can rotate.
| | 03:53 | We can do some other things with it here.
| | 03:55 | But let's look at some of the options
here because in this example here, what
| | 03:59 | you might want to do with these is
include percentages, even if they're not part
| | 04:03 | of the actual data displayed
to the left of the chart there.
| | 04:06 | In this case Layout tab, Data Labels,
and again the choices here are about the
| | 04:12 | positioning, but let's go to More Data
Label Options and this brings up this dialog box.
| | 04:17 | Would you want to add the
Percentage? Well you might and at the same
| | 04:21 | time you might want to not show Value.
| | 04:23 | That's certainly an option.
| | 04:24 | And you also have the choice for
showing the Category Name and in this case,
| | 04:28 | this is likely to be in the legend.
One option is to say, well let's do this and
| | 04:32 | then let's get rid of the legend.
Probably wouldn't want them both.
| | 04:35 | The series name, you will see immediately
is redundant, so you wouldn't want that.
| | 04:39 | So it's a question of which combination of
these do you want and do you want the legend.
| | 04:44 | You will also notice a choice that's
there by default, Show Leader Lines, and
| | 04:48 | here we see them. But you don't
always see them. And you might say well why
| | 04:52 | don't we have one for
November? Well it's not needed.
| | 04:54 | But if you take for example just
November here and drag it out like this,
| | 04:58 | you will see leader lines.
| | 05:00 | Now that looks like a make-work project
for some people, but here and there when
| | 05:04 | you're using pie charts by
manipulating these choices that we have just seen,
| | 05:09 | you are going to have some
good labeling potential here.
| | 05:11 | But again, pie charts are
somewhat difficult to read.
| | 05:15 | If we do click away from the chart
and then back on it again, another
| | 05:18 | possibility here is to go into some of
the 3-D Format choices, 3-D Rotation, this
| | 05:23 | sort of thing, change the Perspective
on it, and again, every time you do that
| | 05:27 | the labels start moving around.
| | 05:29 | So I think a better approach here is
decide how you want the actual pie to look
| | 05:34 | and then apply the labels.
| | 05:36 | But there's no shortage of options
here for applying labels to any different
| | 05:39 | kind of chart that you might be using.
| | Collapse this transcript |
| Showing the source of a chart's data| 00:00 | When you're working with the chart
that's on its own sheet or when you are
| | 00:03 | working with a chart that's far removed
from the data on the current worksheet
| | 00:07 | you might want to add to your chart
a feature called a data table, which
| | 00:11 | essentially shows the source data.
| | 00:13 | Let's apply one to this chart here
and I think you probably agree that you
| | 00:16 | wouldn't really need it here because
the data is right next to the chart.
| | 00:20 | Selecting the chart, then going to the
Layout tab, the feature is called Data Table.
| | 00:25 | And we can show it with legend
or without. Let's show the two examples here.
| | 00:28 | Show Data Table and
we see what's happening.
| | 00:31 | And you could imagine with certain size
numbers this may not be so good, unless
| | 00:35 | you make it wider or in
some cases maybe really wide.
| | 00:38 | Now if I make this narrower, because
maybe that was original chart size, you see
| | 00:42 | what's happening to the
data table. Not so good there.
| | 00:45 | So you should experiment with this feature.
| | 00:47 | Now if we have other charts on this
worksheet and may be this chart is far off
| | 00:51 | to the right and not near the data,
perhaps this makes some sense and
| | 00:55 | certainly if this chart is by itself
on another sheet, at any time you can
| | 00:59 | right-click on a chart and move it,
and sometimes it does make sense to work
| | 01:04 | with a chart on its own sheet.
| | 01:06 | I will put it on a new sheet here.
| | 01:08 | And here since we don't have the data
nearby, it might make sense to have a
| | 01:12 | data table on this.
| | 01:13 | You might be printing this or using it
during a demonstration and it would be
| | 01:17 | helpful to see those values there.
| | 01:19 | It's a bit tough to read for an
audience from certain distances.
| | 01:22 | I am going to move this back again
just as a reminder that you can do that.
| | 01:25 | Right-click and Move the Chart and put
it back on that sheet that it came from.
| | 01:30 | YearData, there we go, so we moved it back here.
| | 01:34 | Now this works on many chart types but not all.
| | 01:37 | If we were to switch the design here
of this chart to a pie chart, either pie
| | 01:43 | chart, it's not available there and if
you click the Layout tab you'll see that
| | 01:47 | it's not selectable.
| | 01:49 | But on line charts, bar charts, xolumn
charts, area charts, and a number of other
| | 01:53 | types you will have the ability
to add what's called a data table.
| | 01:57 | And here and there with certain large
amounts as we suggested, this is not very
| | 02:01 | viable, but it does makes sense in
some situations. You can easily add what's
| | 02:05 | called a data table to emphasize the
chart source data, show the data right with the chart.
| | Collapse this transcript |
|
|
6. Layout Tab Options: Using Axes and GridlinesModifying axis scaling| 00:00 | When you create a chart Excel uses what is
called automatic scaling for the axes values.
| | 00:05 | On this chart we see that the scale goes to 350.
| | 00:09 | Now I think most users most of the
time simply don't care to change that, but
| | 00:13 | you can change it and for
a variety of different reasons.
| | 00:16 | One reason might simply be you want
more open space above the columns there just
| | 00:21 | to give it a more open look. You
can certainly do that at any time.
| | 00:25 | You also have control over the spacing
between the gridlines that you might want
| | 00:28 | to show on the chart as well. Or you
want to start the base entry at something
| | 00:33 | above zero, although we will show you
why you might not want to do that as well.
| | 00:37 | I am going to make just a
small change to the data here.
| | 00:40 | The June value is 330. I am
going to change it to be 335.
| | 00:43 | And as I press Enter, watch the
axis on the left side of the chart.
| | 00:49 | It's currently topping out
at 350. Now it goes to 400.
| | 00:53 | Now if I don't like that, what I
might want to do is just double-click here
| | 00:58 | or another approach, go to the Layout tab and
actually take a look at the Axes options here.
| | 01:05 | This is the Primary Vertical axis.
| | 01:08 | Now here we could use other kinds of
features, but I think really what we want
| | 01:12 | to do here is go right into these choices here.
| | 01:15 | So it certainly would have been
faster to do a double-click, but it is a
| | 01:18 | reminder there are some settings here, and by
clicking here we'll come to this dialog box.
| | 01:24 | And you'll notice that the
Axes Options are set to Auto.
| | 01:28 | Let's change the Maximum here to be
Fixed and I want it to go to 350, the way it
| | 01:33 | was before, because the
highest value is below that.
| | 01:37 | Now it will change the look of the
charts and let's just say I have decided
| | 01:40 | that's going to look good.
| | 01:41 | And by the way, if you don't want to
close the dialog box, maybe just click on
| | 01:44 | another option here.
| | 01:45 | I will just click up here on the
word Auto above this and now we see the
| | 01:48 | changes. The dialog box stays open.
| | 01:51 | Now some people say well, you
know I want it to go to 375.
| | 01:54 | Here and there when you choose certain
numbers, you will get different kinds of
| | 01:58 | scaling that might surprise you and
the intervals might be different as well.
| | 02:03 | And you see what's happening here.
| | 02:05 | It shows every 50, but it starts at -25.
| | 02:08 | I think most people probably wouldn't want this.
| | 02:11 | That's a little bit unorthodox.
| | 02:12 | I think you would have your audience,
if they are watching this, scratching
| | 02:15 | their heads saying, why did you do that?
| | 02:17 | So kick this around a little bit and I
think what makes sense here is the choice
| | 02:22 | either 350 or 400, if you
want to set it manually.
| | 02:25 | Now remember it went to 400
automatically so you can simply set it back to Auto.
| | 02:30 | You see what's happening on the chart.
| | 02:32 | So again, you have your needs.
| | 02:33 | Now something I hinted at and I want to
show you why you might do it and on the
| | 02:37 | other hand why you shouldn't do this.
| | 02:40 | I have seen this down with the column
charts and it's particularly bad, because
| | 02:43 | it gives a very misleading message.
| | 02:45 | But here's the rationale
behind what I had suggested.
| | 02:48 | Suppose you're looking at this chart
and you are saying, these columns, they all
| | 02:52 | start well above a hundred. Why don't
we just make the chart start at 100?
| | 02:57 | So for the minimum here I am going to
click on Fixed and change that value to be 100.
| | 03:03 | Click on the word Auto below it over here.
| | 03:06 | Now look at the chart. Now you can
always make the fallback excuse that says
| | 03:10 | well, can't you read the numbers on the
left-hand side? But particularly if you
| | 03:14 | are looking at March, you're eye is
telling you that the sales, the blue column,
| | 03:20 | is three times as large as the expenses.
| | 03:23 | You can see that column is exactly
three times higher, but how about the value?
| | 03:28 | That's 250 and this is 150.
Obviously that isn't three times as big.
| | 03:34 | And I have seen this done at different times.
| | 03:36 | The general rule would be column
charts should never start above 0 and let me
| | 03:42 | show you another example of how you
might have used this and here it even might
| | 03:46 | make some more sense, but
again, it's not the thing to do.
| | 03:49 | In this chart right here you could again
make the case for saying, well we can't
| | 03:54 | read the differences that well, so why
don't we just start the chart at 700?
| | 03:59 | So it's simply double-clicking on the
axis here and I will move this over so we
| | 04:03 | can see what's about to happen.
| | 04:05 | I am going to change the
Minimum value here to be 700.
| | 04:12 | We can still see all the columns.
| | 04:14 | So it does delineate or express or
show the differences perhaps a bit better,
| | 04:19 | but again, we have this oddity. Look at March.
Is this entry three times bigger than this?
| | 04:25 | The yellow column is 850, the green is 750.
| | 04:29 | So our eye is telling us one thing and
the actual data means something else.
| | 04:34 | Now strangely enough, if you turn this into
a line chart, it seems not to be as awkward.
| | 04:40 | In fact, you could make a case for
saying it's okay. But I think you do owe your
| | 04:44 | audience an explanation when you do this.
| | 04:46 | This doesn't start at 0, but for
whatever reason it doesn't seem to suggest
| | 04:50 | volume in quite the same way
that at a column chart does.
| | 04:53 | We are looking at the same data, and
it is starting above 0, but you see how
| | 04:58 | this is reading out.
| | 04:59 | It does accentuate the change more
so than had we used a scale of zero.
| | 05:05 | So if I were the double-click the left
axis here and change the Minimum to be
| | 05:10 | Automatic, you see what
happens when it starts at 0.
| | 05:12 | It makes for a pretty boring chart and
it looks like sales and expenses are real
| | 05:16 | close together. It doesn't do a
good job of showing the differences.
| | 05:20 | So I think you can see how easy it
might be to want to show the scaling start
| | 05:24 | above 0-- and as I press Ctrl+Z here
to undo, close this dialog box first,
| | 05:29 | press Ctrl+Z-- I think you can see how
in a line may be that's acceptable, but
| | 05:34 | going back again with Ctrl+Z, on a column chart
is not acceptable because of the way we read these.
| | 05:40 | There will be times too when you are
creating charts, a scatter chart like
| | 05:44 | the example we see here, times
might get involved and I think you can see
| | 05:48 | based on the data here that starts in the
morning at 8 AM roughly and ends at around 6 PM,
| | 05:54 | we have got sale amounts made
throughout the day. Maybe this company sells
| | 05:59 | large items but not that many, and we
are trying to track sales activity here.
| | 06:04 | So what do we need to do
with the lower axis here?
| | 06:06 | It has times of day on it.
| | 06:08 | If you were to double-click this axis as
I am about to do right here, you'll see
| | 06:13 | some funny looking numbers and
potential choices out here. It says Auto.
| | 06:18 | Well digressing slightly here,
| | 06:21 | you would have to know and recognize
the times of day, no matter how they are
| | 06:25 | displayed, are actually values between 0 and 1,
and 8 AM is roughly a third of the day in.
| | 06:33 | So if we change the Minimum here, to be
about a third, I am going to 0.3 here,
| | 06:40 | and I will click on the word Auto
below it so you can watch the chart change,
| | 06:43 | now it starts at 8 AM
| | 06:45 | And if this data here ends around 6 PM,
that's about three quarters of the way
| | 06:49 | through the day. Let's change the
Maximum here to be 0.75. 0.75 represents three
| | 06:57 | quarters of the day.
| | 06:58 | I will click on the word Auto here.
There we go and we are seeing this.
| | 07:02 | Now what happened here, I didn't
really mean to change it back to Auto, so I'll
| | 07:04 | change it to Fixed.
| | 07:06 | So now we see the range between 8
AM and the display here of 1800.
| | 07:11 | And if it makes more sense to you with
this point to change this to be AM/PM,
| | 07:15 | we can certainly double-click down here
and then activate the Number tab in the
| | 07:20 | Format Axis dialog box and instead of
choosing this kind of time how about the
| | 07:24 | one with the AM/PM in
it? And we will do a Close.
| | 07:26 | There we go and that's adjusted.
| | 07:29 | And similarly, sometimes you might have
to adjust this top and bottom too, but in
| | 07:33 | this case it looks okay.
| | 07:34 | So now after adjusting the axis here we
can read what's happened here throughout the day.
| | 07:41 | At different times of day we
have made sales. We see how that breaks up
| | 07:44 | by time and dollar amount.
| | 07:47 | So for a variety of reasons you might
need to adjust the axis scaling in a chart.
| | 07:51 | Excel gives us
numerous ways to approach this.
| | Collapse this transcript |
| Working with gridlines| 00:00 | Gridlines are designed to improve
the readability of values in charts.
| | 00:04 | Sometimes you might want to enhance
them by including minor gridlines as well or
| | 00:09 | possibly changing the thickness of them.
| | 00:11 | In the example we are seeing here on
this worksheet called Profits I think the
| | 00:15 | gridlines are too prominent, too dark, and at
different times you might want to make changes.
| | 00:20 | If that's the only change to be made
here a quick fix is simply the double-click
| | 00:24 | on one of the gridlines and although
that's a little tricky sometimes to select,
| | 00:28 | if it is, just select the chart and
then in the Ribbon choose Layout and
| | 00:33 | the leftmost button has its drop arrow.
You can select the major gridlines from there.
| | 00:39 | At that point click Format Selection.
| | 00:41 | Now certainly if we had double-
clicked we could get here a lot faster.
| | 00:45 | What are we talking about here?
| | 00:46 | The possibility of a different color.
| | 00:48 | The black is fairly strong there.
| | 00:50 | How about a dark gray?
| | 00:51 | How is that going to show up?
| | 00:52 | You see what's happening on the chart.
| | 00:53 | Not quite dark enough?
| | 00:55 | How about another gray, and so on.
| | 00:58 | Don't make them too obvious, but at the
same time you want to make them workable.
| | 01:02 | Now if you don't have any gridlines
at all or you might want to have minor
| | 01:05 | gridlines, on the Layout tab you have a
choice called Gridlines in the Axes group
| | 01:11 | here with some choices.
| | 01:13 | We are talking about the
horizontal gridlines here.
| | 01:15 | Major lines are already there. Minor ones?
| | 01:18 | We could add those like this.
| | 01:20 | It doesn't look so good, does it?
| | 01:21 | You can't differentiate which lines are which.
| | 01:24 | Let's go back here again on the
Primary Horizontal Gridlines and choose Major
| | 01:29 | and Minor Gridlines.
| | 01:30 | That doesn't change it either.
| | 01:32 | But if we take the gridlines off and
then come back to gridlines, Primary
| | 01:38 | Horizontal Gridlines, Major
and Minor, we do see both sets.
| | 01:44 | If you had done this from the
beginning you would see a differentiation here.
| | 01:47 | So what might we want to do now?
| | 01:49 | Let's simply double-click one of the major ones.
| | 01:51 | So one option is to double-click one of the
major ones and make that be slightly darker.
| | 01:56 | Now that's just dark gray and not so
dark gray in the background and you could
| | 02:01 | imagine the reverse of this would be.
| | 02:02 | I am going to press Ctrl+
Z to undo for the moment.
| | 02:06 | We could take one of the minor
gridlines here, double-click it, and then make
| | 02:12 | that line a solid line, a lighter gray.
| | 02:16 | I think you get the idea here how.
You want to work back and forth with major
| | 02:21 | and minor gridlines.
| | 02:22 | Of course, the real purpose of this is
you are trying to read the values as you
| | 02:26 | look at the different points.
| | 02:28 | Now on a different chart on different
sheet here, CitySales, you will encounter of
| | 02:32 | course bar charts from time to
time and perhaps you use them heavily.
| | 02:36 | Sometimes you're thinking about the
lines, but also about the breakpoints here,
| | 02:40 | which may or may not make
sense in the example being shown.
| | 02:43 | So in this particular chart let's
choose Layout and then go to Gridlines.
| | 02:49 | This time we are talking about
vertical gridlines in this kind of a chart.
| | 02:52 | Maybe we will add both.
| | 02:54 | Now that might be more
lines that we actually need.
| | 02:57 | It doesn't make any difference
here if we change the points.
| | 03:00 | We have control over these breakpoints here.
| | 03:03 | Here they're shown every 2500. Is
that too many or is that not enough, is
| | 03:08 | that too wide of a gap?
| | 03:10 | A good choice here might be
let's get rid of the minor gridlines.
| | 03:13 | So Gridlines here, Primary Vertical Gridlines.
| | 03:17 | Maybe choose Major only
and the minor ones are gone.
| | 03:21 | Then if we want to control the actual
spacing here you can double-click on
| | 03:26 | simply the values outside here, double-click
here, and let's just change the major unit.
| | 03:32 | It's currently 2500.
| | 03:35 | Maybe make it 5,000 and see how that looks.
| | 03:38 | We could click outside here or just
happen to click the word Fixed again.
| | 03:42 | We see what's happened to the chart now.
| | 03:44 | Maybe that's easier. If we want to put
in minor gridlines we could escape from
| | 03:49 | here, come out, add those under
Vertical, and for the moment again we've got no
| | 03:55 | differentiation between the two.
The same thing we would do with the pie chart
| | 03:58 | you might want to do now.
| | 03:59 | But just remember you have got
control over the placement of the gridlines,
| | 04:03 | their thickness and their colors, and
in varying degrees you are trying to make
| | 04:07 | charts more readable by
sensible use of gridlines.
| | Collapse this transcript |
|
|
7. Layout Tab Options: Using the Analysis ToolsAnalyzing existing and future data with trendlines| 00:00 | A significant analytical tool that
comes with charting is your ability to
| | 00:04 | create in a sense something that is
brand new: a trendline that shows not only
| | 00:09 | what this data is doing, but where it
might be headed if you extend it into
| | 00:14 | future time frames.
| | 00:16 | The trendline can be applied to
a variety of different charts.
| | 00:19 | It's often applied to a
line chart or a column chart.
| | 00:23 | One example of a trendline
could be a Moving Average.
| | 00:26 | In the example here we are
looking at sales entries.
| | 00:29 | You probably have seen stock charts
that show a lot of up and down activity.
| | 00:33 | A Moving Average perhaps starting at
the third point and showing an average of
| | 00:38 | every three months might make sense here.
| | 00:40 | At any point on the line here
right-click and Add Trendline.
| | 00:46 | What we see in the background before
we even make a choice is what's called
| | 00:50 | a linear trendline.
| | 00:51 | Now, for this to ultimately make some
sense you would have to have a little
| | 00:55 | bit of knowledge of what a regression line
is, and for the moment I will hold off on that.
| | 01:00 | Do you recognize that one of the
trendline options is called a Moving Average
| | 01:04 | and the default is set up at 2.
| | 01:05 | Let's take a look at what we see
there on the chart. We see a new line.
| | 01:10 | We could certainly make it
thicker or make changes to it.
| | 01:13 | It actually begins at the second point.
| | 01:15 | Now, early on it doesn't seem to be
doing much, but at any given point here,
| | 01:19 | say the one right here for June-09,
this point here is the average of the
| | 01:24 | previous month and the current month.
| | 01:26 | So, it's the average of the point above it
and the one that precedes it to the left.
| | 01:30 | The idea behind a Moving Average is
that it smoothes out the curve and perhaps
| | 01:35 | is a better representation
of what's happened over time.
| | 01:38 | Back in the dialog box here, if I
make the 2 become 3, see what's happening.
| | 01:43 | Of course, as I raise this value, the
starting point of the chart moves rightward.
| | 01:48 | Now it's starting at the third point.
| | 01:50 | The more we raise this number, the
smoother the line gets, and you have seen
| | 01:55 | perhaps charts like this that cover a
huge amount of time, maybe years even, and
| | 01:59 | this makes some sense here and
there for certain kinds of trendlines.
| | 02:03 | Now, going back to Linear again.
| | 02:05 | Associated with this
regression line is an R-squared value.
| | 02:09 | I am hard pressed to give you a full
explanation of this, but let's just say
| | 02:13 | that the R-squared value if it
approaches 1-- and in the example here, it's 0.8.
| | 02:20 | The more it approaches 1, the more
presumably the data is consistent or closer
| | 02:26 | to the straight line.
| | 02:27 | I know this is a loose definition
for those of you who are statisticians.
| | 02:30 | You probably have a better
sense of what this means.
| | 02:33 | But in trying to support where this
data might be going we can extend this.
| | 02:38 | The closer this value is to 1,
the better argument you have for using
| | 02:42 | that particular feature.
| | 02:44 | So, I am going to jump back here again
and double-click on the line here to come
| | 02:48 | back into the Trendline Options.
| | 02:50 | Let's extend this forward. And by the way,
displaying the equation on a chart is
| | 02:54 | again something that you might use if
you're familiar with regression analysis
| | 02:58 | and you know how to use that formula.
| | 03:00 | I am going to uncheck it, but just
recognizing that's a possibility as well too.
| | 03:03 | How about a Forecast?
| | 03:05 | Let's forecast this forward 6 periods
and as soon as I click somewhere else
| | 03:09 | here, you see what's happened.
| | 03:11 | Now, there is no points associated with
it, but at least we can see where that's
| | 03:14 | going based on the existing data. How about 10?
| | 03:17 | We'll see that.
| | 03:20 | A different kind regression line, one
based on a different kind of formula, is
| | 03:24 | Exponential and as I click that, and
this is typically a curve line, although
| | 03:29 | not necessarily, and you
will see what's happening there.
| | 03:32 | The R-squared factor there not
quite as good as the other one is, 0.84.
| | 03:36 | But here too you have that ability to
extend it forward, in this case 10 periods.
| | 03:40 | So, you do have to know a little bit
about regression analysis and how to
| | 03:43 | use that capability.
| | 03:45 | If you are not too familiar with those
terms, Moving Average I think makes sense
| | 03:49 | for a number of people.
| | 03:50 | This doesn't give us a
projection into the future.
| | 03:53 | Let's close this and for the moment
we will leave it at Moving Average and
| | 03:57 | recognize too that sometimes you
might want to use a column chart.
| | 04:01 | So, I am going to switch the design here,
change the chart type to be a column chart.
| | 04:06 | And some people prefer this with
trendlines, because there is the obvious
| | 04:10 | difference in the chart type.
| | 04:12 | We have a line and columns together
here and maybe that makes it more readable.
| | 04:16 | Another option here too is if you're
familiar with a function called Trend, you
| | 04:21 | could show a linear line here manually.
| | 04:25 | What I am going to be doing here in
Column C is highlight this data here and I
| | 04:30 | have already previously made sure
that this data will go into the chart.
| | 04:35 | So, I've highlighted this data.
| | 04:37 | I am going to type =trend( and select
all of the sales data right here and this
| | 04:44 | is a special kind of function.
| | 04:47 | It's an array function.
| | 04:48 | I'm going to press not
simply Enter but Ctrl+Shift+Enter.
| | 04:53 | Now, it puts the data in as a column
here and I want to change that to a
| | 04:56 | line, right there it is.
| | 04:59 | And Design > Change Chart Type. Make
that be a line, possibly with the points,
| | 05:04 | and we see what's happening.
| | 05:05 | There is the linear line
being added to the chart.
| | 05:08 | If we are going to keep that, we
probably want to change its color, but we also
| | 05:11 | have our Moving Average there and
we have this trendline for Linear.
| | 05:15 | One other type that has its own
function is Exponential and the term here is
| | 05:20 | called Growth and similar to what I did
with Trend, I am going to highlight this
| | 05:24 | data here and type =growth(
and select the sales data again.
| | 05:34 | This time press Ctrl+Shift+Enter and
that data goes into a chart and there too
| | 05:39 | we want to change that into by using
the Design tab. Leftmost button, Change
| | 05:44 | Chart Type, we will use a line
chart here as well with markers.
| | 05:49 | If we want to keep all three of those,
and of course that's making it look pretty
| | 05:52 | crowded, we would want to
change the color and so on.
| | 05:54 | But different ways to add these trendlines.
| | 05:57 | Again the manual way of doing
this makes sense for most people.
| | 05:59 | What I did here with the functions
is just to point out that there is
| | 06:02 | another approach to that.
| | 06:03 | So we can get the actual
points out there as well.
| | 06:06 | But it is an analytical tool, the
trendline, that gives us the possibility of
| | 06:10 | analyzing the data that we see in a chart.
| | Collapse this transcript |
| Adding drop lines | 00:00 | For line charts and area charts
sometimes it's difficult to read points because
| | 00:04 | the points on line or the area
are far removed from natural axis.
| | 00:09 | What point is this?
| | 00:10 | We can probably figure that out that
looks like March and of course the popup
| | 00:13 | helps us too, but at a glance maybe
it's little bit more difficult to read.
| | 00:17 | Let's add what are called drop lines.
| | 00:20 | So the chart is selected. Layout
tab in the Ribbon, Lines, Drop Lines.
| | 00:26 | A lot easier to read here.
| | 00:29 | Similarly, with an area chart let's
make a change here on the Design tab in the
| | 00:34 | Ribbon, leftmost button, Change Chart
Type, and this time we'll switch it to one
| | 00:40 | of the area chart options, say the first
one right here, and now we have an area chart.
| | 00:46 | Sometimes when you do this
you have to add the lines again.
| | 00:49 | If you double-click on the lines, you
can certainly make some changes there
| | 00:52 | regarding the colors if you want.
| | 00:54 | That's probably not
necessary in the example here.
| | 00:57 | I think you're going to find this more
useful when you have even more points in this.
| | 01:01 | Not that it's not useful here, but
the more points you have the more useful
| | 01:04 | this feature might be.
| | 01:06 | It's called drop lines. You can use
them only on line charts and area charts.
| | Collapse this transcript |
| Adding high-low lines and up-down bars| 00:00 | In line charts you might want to
compare the difference between two different
| | 00:03 | series by connecting the high and low
points for each specific time period with
| | 00:08 | lines or with bars and both of these
features I think you have to actually see
| | 00:13 | what we are talking about here
rather than trying to explain this.
| | 00:16 | They are available on the
Layout tab in the Ribbon.
| | 00:19 | In this particular situation here
we're comparing domestic and Latin American
| | 00:23 | values for a six-month period.
| | 00:25 | Selecting the chart and then going to
the Layout tab, the lines are found on the
| | 00:31 | Lines button in the Analysis group.
| | 00:34 | High-Low Lines. Show High-
Low Lines on a 2D Line Chart.
| | 00:40 | It simply accentuates the fact that not
only are there differences every month,
| | 00:44 | but in certain months the differences
are substantially larger and the lines
| | 00:48 | just allow us to see this a bit
better than we might otherwise.
| | 00:51 | Another approach to showing this
difference has a little more variation on
| | 00:55 | it and it does give us the added
capability of showing negatives versus
| | 00:59 | positive differences.
| | 01:01 | That's called Up/Down Bars.
The choice naturally Up/Down Bars.
| | 01:07 | You'll notice that there is a
difference in the colors here.
| | 01:09 | The blacks are those situations where the
domestic value is higher than Latin America.
| | 01:15 | The whites are in the opposite direction.
| | 01:17 | If for whatever reason you don't care
for the colors, what you can do here is
| | 01:21 | simply change the series order. And you
can do that by going to the Design tab
| | 01:27 | and the fourth button from the left.
| | 01:29 | It's in the Data group.
| | 01:30 | It's called Select Data.
| | 01:31 | It brings up this dialog box here and
over on the left-hand side simply change
| | 01:36 | the order of either one of
these by clicking the arrow here.
| | 01:39 | So as I click the arrow here, watch the
four black boxes here turning white,
| | 01:44 | the two white boxes turn black.
| | 01:45 | We're simply changing the order of them.
| | 01:48 | So this feature too, like the High-Low
Lines, accentuates the difference, but in
| | 01:53 | this case we actually see color
differences between two different series on a
| | 01:56 | two-dimensional line chart.
| | Collapse this transcript |
| Adding error bars| 00:00 | In some charts you might want to show
additional values above or below, possibly
| | 00:05 | both or either, certain points on a chart series.
| | 00:09 | You might want to put in vertical bars
based on percentages or standard error or
| | 00:13 | fixed values, standard
deviation, or your own calculations.
| | 00:17 | These tend to work best when we're
dealing with just a small amount of data and
| | 00:22 | you can use them on column charts, line
charts, area charts, bar charts, scatter
| | 00:26 | charts, and bubble charts,
but not any of the other types.
| | 00:29 | Let's just apply some of these to the
lower chart here which shows us single
| | 00:33 | line for domestic sales projected.
| | 00:36 | Selecting the chart and then going to
the Layout tab in the Ribbon. Error Bars.
| | 00:42 | See some choices here.
| | 00:44 | Error Bars with Standard Error.
Displays the bars for the selected chart using
| | 00:48 | standard error and you have to know
what that means statistically and you see
| | 00:52 | what's happened on the chart there.
| | 00:54 | That might be one option to explore.
| | 00:57 | Back to Error Bars.
| | 00:59 | Error Bars with Percentage.
Displays Error Bars for the selected chart
| | 01:03 | series with 5% value.
| | 01:06 | Notice how on the January point,
because the values is lower, we can hardly see
| | 01:10 | the 5% difference on either side.
| | 01:12 | So I think that's one you
might want to consider changing.
| | 01:15 | Another option under Error Bars,
Error Bars with Standard Deviation.
| | 01:20 | So in this example the
standard deviation will be calculated.
| | 01:23 | We don't see that on the screen, but
we will see bars that fall between.
| | 01:27 | So that's the standard deviation for the
entire range of data that we're looking
| | 01:31 | at there and the January and June
points are outside the standard deviation,
| | 01:36 | The other four are within it.
| | 01:38 | Now for more detail on this and for
more adjustment let's go back to Error Bars
| | 01:42 | and More Error Bar Options.
| | 01:45 | So for the moment since we just chose
Standard Deviation we're seeing that there.
| | 01:49 | Let's move this over so we can
see our data along with this.
| | 01:52 | Maybe we want to explore that
percentage option here and earlier we saw how
| | 01:56 | 5% was the default.
| | 01:58 | Maybe 10% is what we want
here. Let's change that.
| | 02:02 | Click somewhere else, come back
again, and see what's happening there.
| | 02:05 | It's going to be different.
| | 02:06 | Maybe 25% is what we want to try here.
And you can see what's happening there.
| | 02:12 | So maybe that's the choice
that we would like to make here.
| | 02:15 | Notice also up above you've got control
over whether you want these error bars to
| | 02:19 | appear Both above and below the
points or just below or just above.
| | 02:24 | Then some choices on the design of the
endpoints here, the Caps and No Caps and
| | 02:28 | so on, and some other
choices out here as well too.
| | 02:31 | So let's suppose we leave it at
25 and it might look like that.
| | 02:34 | Now what you might also want to do to
accompany this, and you would have to use
| | 02:38 | the math behind what we're trying to do here.
| | 02:41 | Right now these error bars represent
in both high and low positions here 25%
| | 02:46 | above and then 25% below the given point.
| | 02:50 | So over here on the worksheet itself,
for example in cell B10, here is the
| | 02:54 | formula is simply 75%.
| | 02:57 | In other words, just 25% lower than the
January value of 80. And formulas are put
| | 03:02 | in all across here and here
are the ones that are above that.
| | 03:05 | So 25% above the 80 naturally is
100 and these are multiplied by 1.25.
| | 03:11 | Both of these could be set up in
different ways so you could simply put in a
| | 03:15 | percent out here and work with that.
| | 03:16 | If you're familiar with functions and
formulas that probably be a little bit
| | 03:19 | more efficient, but nevertheless these
items are here and we could if we want to
| | 03:24 | copy these into the chart.
| | 03:26 | So simply highlighting this data and
right-clicking we can choose Copy or Ctrl+C
| | 03:31 | of course and then on the charts select
the chart itself, click in the chart area,
| | 03:36 | right-click and Paste.
| | 03:40 | Now we see those very lines based on the
data in rows 10 and 11 also portrayed here.
| | 03:45 | So sometimes you might want to accompany those
error bars with actual lines that appear here.
| | 03:51 | Now if you try this on other kinds of
charts-- and you're suddenly encouraged to
| | 03:54 | do that. I think you'll see how
it's likely to introduce some clutter.
| | 03:57 | But you can do these on
column charts and line charts.
| | 04:00 | Maybe we'll just put it on one
this series only and here too.
| | 04:04 | Layout tab, Error Bars, and we'll just
use Percentage here like we did before.
| | 04:10 | Percentage and we see what's happing.
| | 04:12 | There too from that point we might want
to go Error Bars More Error Bar Options,
| | 04:17 | and although we don't always have to
use 25, just use that to correspond with
| | 04:21 | our previous example to see how that looks.
| | 04:24 | So sometimes you can use them this way too.
| | 04:26 | Notice how it adjusts the
scaling in the process too.
| | 04:29 | So I think it might work a
little bit better on line charts.
| | 04:31 | You can certainly use these
on column charts as well too.
| | Collapse this transcript |
|
|
8. Format Tab Options: Adding Shapes and WordArt StylesSelecting shape fill and outline| 00:00 | To improve readability or simply to
spice up a chart you might want to select a
| | 00:04 | specific color and shading effect for a
column, a bar, a line, an axis line, a
| | 00:10 | pie wedge, practically any element in a
chart, including plot area and chart area
| | 00:15 | itself, you can change the color
background and a lot of associated features
| | 00:19 | that come with that.
| | 00:20 | In this column chart to the right of the
data, click on it select just the blue columns.
| | 00:25 | I want to use a different color here.
| | 00:27 | The Format tab in the Ribbon is activated.
| | 00:31 | Under Shape Styles we have some
choices and not just the seven choices we see here,
| | 00:36 | but the drop arrow
gives us many, many more.
| | 00:38 | As I'm sliding over these, keep an eye
on those columns. You can see what is
| | 00:42 | about to happen and perhaps a lot more
choices than we ever would have dreamed of.
| | 00:47 | The so-called Live Preview capability
of Excel at least let's us explore this a
| | 00:51 | lot faster than on some of the older
versions of Excel where you actually had
| | 00:54 | to make the choice then decide whether you
liked it and come back in here and so on.
| | 00:58 | So we can certainly explore this.
| | 01:00 | I wouldn't say it's necessary here, but
maybe I'll just use this color instead.
| | 01:03 | Click, there we go.
| | 01:04 | Lots of choices there.
| | 01:06 | If this is a line chart we might want to
go down that path. Design > Change Chart
| | 01:11 | Type and make it be a line chart.
| | 01:14 | Similarly here. Now notice what
happens though when you switch types.
| | 01:17 | Sometimes your previous choice
doesn't necessarily work so well with a
| | 01:22 | different chart type.
| | 01:23 | So if I click the series only and then
go to the Format tab and explore some
| | 01:28 | of these choices here.
| | 01:30 | Not so obvious what might be
happening here, but as you see as I slide over
| | 01:34 | these different choices.
| | 01:36 | So you might have to go back here
in some cases and redesign the chart.
| | 01:40 | Maybe it would be better here to get out
of here simply by pressing Ctrl+Z a few times,
| | 01:44 | go back to this color,
and then switch it to a line chart.
| | 01:48 | Design, Change Chart Type, Line chart
come back here, select the line, then go
| | 01:56 | into Format and explore some of these options.
| | 01:59 | Now without suggesting that doing
these things is always fun or is it really
| | 02:04 | necessary, you can see how you can
get sidetracked so easily on this.
| | 02:07 | What we're doing here potentially is
changing the look of this line to make
| | 02:11 | it be a lot thinner.
| | 02:12 | By the way, notice on this last set down
here this doesn't show any line at all.
| | 02:17 | None of these across the bottom do.
| | 02:20 | The ones above that do show the line
and the ones above that do and the ones up
| | 02:25 | here, because there is a
white border, shows a white line.
| | 02:28 | If you were to choose one of these
and say yeah, but I want the line to be
| | 02:31 | thicker, the next approach then will be
to double-click on the line itself and
| | 02:36 | that takes us into another dialog box
where we can talk about the line color,
| | 02:41 | make a change there again, and then line style.
| | 02:44 | So that's bit bewildering as to how you
might approach making all these changes.
| | 02:48 | That's why sometimes we say don't get too
sidetracked in all of the visuals here.
| | 02:52 | But let me point out just a few more here.
| | 02:54 | Once again I'll press Ctrl+Z a few times and
perhaps leave it right there for the moment.
| | 02:58 | You might have a pie chart like
we see in the lower left corner.
| | 03:02 | I want to click on the pie chart.
| | 03:03 | Maybe I want to change the
color of just one of the wedges.
| | 03:06 | Maybe it's the green one I want to change.
| | 03:08 | So I'll click on that one after
having clicked the pie itself.
| | 03:11 | Now the green wedge only is selected.
| | 03:14 | We'll go into the Format tab in the
Ribbon up above and now we have shape
| | 03:19 | styles applied to this.
| | 03:21 | And as we slide over this,
| | 03:22 | I think you would have expected that
and no big surprises as you make some
| | 03:25 | choices here possibly.
| | 03:27 | Choose a different one that we saw before.
| | 03:30 | Go down that path . And if you click
the outer area that's the chart area,
| | 03:34 | sometimes just a quick fix here.
| | 03:36 | Go right under Shape Styles,
light blue on the outer area.
| | 03:40 | Inner area, if you want a different color
there, go there. How about this, and so on.
| | 03:45 | You can certainly make some bad
color choices along the way too.
| | 03:47 | Here is the legend itself. Click on it.
| | 03:49 | That could be a different
color. And the lines themselves.
| | 03:54 | How about the axis line on the left-hand side?
| | 03:56 | I am going to click the axis line and keep
an eye on the shape styles up above as I do.
| | 04:01 | Now the choices have to do with the
thickness of the line. And I wouldn't say
| | 04:05 | that's the great choice.
| | 04:06 | I'm going to press Ctrl+Z, but
I think you get the idea here.
| | 04:09 | All the different elements of a chart.
| | 04:11 | One more here, the title itself.
| | 04:13 | Maybe that will look better in its own
little background and you can see what
| | 04:16 | could happen there as well.
| | 04:18 | So for any number of reasons you can
apply different color and shading effects
| | 04:22 | to various chart elements by a way of
the Format tab in the Ribbon, Shape Styles.
| | Collapse this transcript |
| Adding shape effects | 00:00 | To provide more variety and visual
impact, you can use the many shape fill,
| | 00:04 | shape outline, and shape effects such
as Glow, 3D, Soft Edges, Bevel, etcetera.
| | 00:09 | These are options on the Format tab
and you can apply these to various
| | 00:13 | selected chart elements.
| | 00:15 | Let's imagine in this particular chart
here to the right we have got a title.
| | 00:19 | Let's explore some of the options
we might want to use on the title.
| | 00:22 | Now, it's not in the box.
| | 00:23 | It has a different color.
| | 00:24 | So what do we see here
when we go to the Format tab?
| | 00:27 | Some shape effects. nd what you will
notice sometimes are some effects that
| | 00:31 | appear to be selectable and
you can't do anything with them.
| | 00:34 | Now, here is one for Shadow.
| | 00:35 | But do recognize here as we drag over,
and keep an eye on that title there off
| | 00:39 | to the right. You can see what's
happening here with some of these shadows, not
| | 00:42 | too well on some of them but
others, better based on the placement.
| | 00:45 | You can see how the shadow will appear
there, even though we don't really see a box,
| | 00:49 | and you have an Inner
Shadow down here as well too.
| | 00:52 | And on these, you don't see a preview,
so that's a little bit confusing.
| | 00:55 | Why aren't we seeing a preview here?
| | 00:57 | So sometimes what you need to do is
to actually as a precursor here or as a
| | 01:02 | forerunner to this, take that box
itself or select the title, again it's a
| | 01:07 | better way to say it, and
then go to Shape Styles.
| | 01:09 | Maybe provide a background color.
| | 01:11 | I don't think this is at all intuitive,
but once you get the idea here, once we
| | 01:15 | have given this a color background
let's say, in other words we've applied a
| | 01:19 | shape style, now we can go to Shape
Effects and now I see these shadows here but
| | 01:24 | the Inner Shadows as well. You see
what those are doing to that title if you
| | 01:27 | like that sort of thing.
| | 01:29 | You will also see Glow,
and as we slide over this,
| | 01:32 | you probably never thought about really
needing this, but there it is and you
| | 01:35 | can see that title
glowing in different ways there.
| | 01:37 | So we see it that way.
| | 01:39 | Now, if I undo some of these features
with Ctrl+Z repeatedly, meaning undo...
| | 01:44 | Go back once again to where it's just a title.
| | 01:46 | And again, a reminder here, if you did
like that Glow effect because you had
| | 01:51 | seen it in the past, you might forget if
you don't use it a lot, you will come
| | 01:54 | back and use Shape Effects
and try and apply a Glow here.
| | 01:56 | And as I am sliding over these, we're
not seeing any glow and once again the
| | 02:01 | reason is that we are not really seeing the box.
| | 02:04 | In other words we have to apply a color
to the box, one of these choices here.
| | 02:09 | I've got a different one this time.
| | 02:11 | Now, maybe we can come back to Format
> Shape Effects and take a look at the
| | 02:15 | Glow now and you see how that works.
| | 02:17 | So we didn't see the glow unless we had an
actual box that was filled in with color.
| | 02:22 | Kind of hard to say all that in one
sentence, but I think you get the idea there.
| | 02:25 | Occasionally, you might want to apply a
Box feature before proceeding with some
| | 02:29 | of the shape effects.
| | 02:30 | It is misleading because the shape
effects appear to be selectable yet
| | 02:34 | they don't do anything.
| | 02:35 | Here is a legend too. Same general idea.
| | 02:37 | Let's apply a shadow to that.
| | 02:39 | Maybe Inner Shadow.
| | 02:40 | Of course, we can't see that right now
and of course many times you will have to
| | 02:43 | scroll around a little bit as you apply these.
| | 02:47 | So we will try that there.
| | 02:48 | There is a glow effect.
| | 02:49 | Here too, same situation. We
didn't put that legend in a box.
| | 02:53 | Let's quickly do that, the choice up there.
| | 02:55 | Then we can come back and try some
of these glow effects and you can see
| | 02:59 | what's happening there.
| | 03:00 | lots of possibilities there.
| | 03:03 | We can do this to the chart area.
| | 03:04 | That's the outer area of a chart.
| | 03:06 | You might want to put up shadow on that too.
| | 03:08 | And again, without exploring all
these because it would take forever, you
| | 03:11 | get some ideas here.
| | 03:13 | Make the entire chart have a
shadow around it, something like this.
| | 03:16 | And you can see in the
background what's about to happen.
| | 03:18 | As I click here and get out here and
move around a little bit, scroll the screen
| | 03:22 | leftward, we can see how the outer
area of that chart has a shadow on it and
| | 03:28 | that's because we selected chart area
before going to Format and Shape Effects.
| | 03:34 | One choice in there you might have
seen too that doesn't seem to be selectable at
| | 03:37 | all is called Reflection.
| | 03:38 | Now, you can get to this in other ways,
and I'm not suggesting you really need this,
| | 03:42 | but it is one of those
effects that some people find appealing.
| | 03:45 | And as far as I know when charting,
the only way this will work, and it
| | 03:48 | doesn't work on legend.
| | 03:50 | It doesn't work on titles or
any of the major areas here.
| | 03:53 | But if you do insert a text box, if we go
to the Insert tab here and choose Text Box.
| | 04:01 | Now, it doesn't really have to be in a
box for all the features, but how about
| | 04:04 | for the actual reflection itself?
| | 04:06 | You do see the temporary box there
and maybe we want to say Preliminary
| | 04:11 | Projections or something like that,
just another bit of text here.
| | 04:14 | It doesn't make any difference what it is for
the moment just for the sake of an example.
| | 04:21 | If we click the border here and then go
back into Format, we now have some Shape
| | 04:26 | Effects options here and there's Reflection.
| | 04:29 | Again, I am not making a strong
case for saying you need this.
| | 04:32 | And we are not quite seeing anything
yet. We don't get the live preview here
| | 04:35 | because it's not in box but click
there and then click outside and again,
| | 04:40 | we're not seeing it.
| | 04:42 | In this case, let's put a box on it.
| | 04:45 | Any of these colors here, doesn't
make any difference which one, solid or
| | 04:48 | different kind of borders
and so on. Possibly that one.
| | 04:51 | Reshape this and let's come back in here again,
under Format > Shape Effects > Reflection.
| | 04:59 | Now, you can begin to see what's happening there.
| | 05:01 | Pretty cool effect if you like that
idea and I've used it occasionally, but it
| | 05:05 | always raises questions like how did you
do that, and after a while maybe it will get old.
| | 05:09 | But just again, one of the many
different features we can use, but in this
| | 05:13 | particular case the Reflection example
only works within a text box and then
| | 05:18 | you get your own separate formatting.
| | 05:20 | So the other tools that we used are on the
Format tab, under Shape Effects, and Shape Styles.
| | 05:26 | so quite a few different variations
just to add a little bit of flare to that
| | 05:29 | chart to maybe was lacking in
oomph or presentation power.
| | Collapse this transcript |
| Applying WordArt styles| 00:00 | If you've worked with the WordArt, a
feature common not only to Excel, but also
| | 00:04 | to Word and PowerPoint, you might
want to consider using it for some
| | 00:07 | specialized title needs.
| | 00:10 | Now, there is a full-fledged version
of WordArt available when you work with
| | 00:13 | text data outside of a chart.
| | 00:15 | Inside of a chart, it's a bit more limited.
| | 00:17 | In this particular column chart right
here, we might want to make the title be a
| | 00:21 | little fancier perhaps.
Just click the title itself.
| | 00:24 | And then on the Format tab in the
Ribbon, possibly consider some of these
| | 00:28 | WordArt styles here.
| | 00:30 | You can see enough of the title there
to left and imagine what could happen if
| | 00:33 | we make these choices.
| | 00:34 | You see the various letter types here.
| | 00:36 | So we can begin to see some options there.
| | 00:38 | So these by the way are quite blurry looking.
| | 00:40 | It's not in the transmission of this.
| | 00:42 | They are just blurry looking, that's all.
| | 00:44 | And that is a certain look that you
might even want from time to time.
| | 00:47 | So I've chosen this one and possibly
then you might want to choose some text
| | 00:51 | effects, some shadows here, this sort
of thing, and usually on these the so
| | 00:55 | called Live Preview of Excel is in
effect. And as we slide over some of these
| | 01:00 | choices, we see what's happening to
that title just to left of these choices.
| | 01:04 | There is Reflection, which we had
seen in an earlier movie, and that's
| | 01:07 | another possibility too.
| | 01:09 | Again, a special effect for a title.
And let's not forget Glow and again the
| | 01:14 | many options here to apply
these to various text entries.
| | 01:18 | You can certainly apply these not only
to title areas, but also to some of the
| | 01:22 | other text items that we have on a chart.
| | 01:25 | So maybe I'll just go with this one.
| | 01:28 | Notice that some of these choices, ones
that we would normally see under Word
| | 01:31 | or ones applied to worksheet data, are
not available here. Bevel and 3D Rotation
| | 01:37 | and Transform and so on.
| | 01:38 | So again, items to explore here if
you're looking for that special effect.
| | 01:43 | I wouldn't say that's such a great option here.
| | 01:45 | In fact, I am going to go back and
change it. Again just to recommend the idea
| | 01:49 | that there are certainly some things
you might want to explore here but on the
| | 01:52 | other hand this feature probably works
best from my perspective as a regular
| | 01:57 | worksheet feature as opposed to
something you want to try on charts.
| | Collapse this transcript |
|
|
9. Using Other Formatting ToolsFormatting lines and borders| 00:00 | Chart areas, plot areas, columns, bars,
and many other chart elements have
| | 00:05 | borders and you sometimes want to change them.
| | 00:08 | You can adjust the color, the
thickness, and the type or style of border.
| | 00:13 | Let's start with the title here in this
chart on the left side of the screen here.
| | 00:17 | By way of the Format tab in the Ribbon
rather than filling in the shape, how
| | 00:21 | about a shape outline
here of one of these colors?
| | 00:25 | Possibly one of these here. Pick one.
| | 00:28 | If you want to change the style you
might also notice here under Shape Outline,
| | 00:32 | the Weight here or Dashes.
| | 00:35 | Now, you can get to these in another way
too and some people prefer this approach.
| | 00:39 | If we simply double-click the border of
the title here, the Format Chart Title
| | 00:45 | dialog box appears and here's the
Border Color. Again, we could have started
| | 00:50 | here as well too by the way, and
possibly we're going to change the color or
| | 00:55 | the gradient line, a line that varies in its
color. I doubt if most people would want that.
| | 01:01 | Let's go back here and choose Border Styles.
| | 01:03 | How about the Dash type, large dashes like this?
| | 01:08 | How about the width of this
possibly at the same time?
| | 01:10 | We can make some changes here.
| | 01:12 | Now unfortunately, when you make some
of these changes, you're not seeing them
| | 01:15 | very clearly in the preview until you
actually close, come back here, and now we
| | 01:20 | can see what's happened here.
| | 01:21 | It looks like the gradient got
applied there. I wasn't paying attention as
| | 01:24 | closely as I might have been maybe,
but let's double-click back here again on
| | 01:28 | the border to resurrect the dialog
box. Use a solid line. There we go!
| | 01:34 | And maybe a different color while we're at it.
| | 01:37 | You can see how sometimes part of
the confusion in using some of these
| | 01:41 | features in Excel is you're not quite
sure of where did I start the last time
| | 01:44 | and how did I get there?
| | 01:46 | And the point is you can get to
some of these features through
| | 01:48 | different techniques.
| | 01:50 | As a general rule in Excel 2010, when
you work with charts, if you double-click
| | 01:54 | a chart element, you're likely
to get the appropriate dialog box.
| | 01:59 | But you can also of course, as the
features we're seeing here, get to these
| | 02:03 | features by way of the Format tab in
the Ribbon. So different places to get
| | 02:07 | to the same location.
| | 02:08 | So, once again, once we're in here,
Border Styles, we're could again experiment
| | 02:12 | with some of the different dash
types if somehow we thought that was
| | 02:15 | appropriate, and the width and some
other choices here as well. Click outside.
| | 02:21 | And we can do this, and I wouldn't say
we need to do this at all on the outer
| | 02:24 | chart border, the chart area.
| | 02:27 | Once again we'll do it the faster way.
Double-click and we've got a border color here.
| | 02:31 | Make it a solid line.
| | 02:34 | Pick the color we might want.
| | 02:35 | How about dark blue there?
| | 02:37 | And then Border Styles.
| | 02:39 | Make it substantially thicker maybe
and you can see that one happening in the
| | 02:43 | actual chart to the left, and once again,
not that we need to make a difference,
| | 02:47 | but we can explore some of these as well too.
| | 02:50 | So that's easily handled.
| | 02:51 | We can do the same thing on
the plot area, the inner area.
| | 02:54 | I don't think you'd want to apply
these to the columns themselves, but we
| | 02:57 | could go down that path as well.
Double-click and you want to be careful not
| | 03:01 | to double-click on just one of them
but all of them so they're all selected.
| | 03:05 | And here too, Border Color, probably
not a great choice here, but we can go
| | 03:09 | down this path as well.
| | 03:10 | You see what might be happening here.
Probably you can't see that too clearly
| | 03:14 | on the column but I might choose
Orange here and then go to the styles and
| | 03:18 | make that thicker as well too, and you can
begin to see what's happening to those columns.
| | 03:22 | That's not particularly attractive from my
perspective, so I wouldn't want to keep that.
| | 03:25 | But nevertheless, the fact that you can
do that or sometimes you see the effect
| | 03:29 | and you want to copy it. Invariably we
start by double-clicking and then pursue
| | 03:34 | some of those options
available on the dialog boxes.
| | 03:36 | So you do have control over the color,
and the thickness, and the style of
| | 03:41 | borders on the various
chart elements in Excel charts.
| | Collapse this transcript |
| Filling an area with a color gradient| 00:00 | Applying color shading effects to
various areas of charts, particularly the
| | 00:04 | larger elements like chart area, plot
area, and some larger titles, is something
| | 00:09 | that I would recommend doing by using
the Format button. And some of the choices
| | 00:14 | you might already have seen. But you do
have some fine-tuning capability here.
| | 00:19 | And part of the reason you might want
to know this is that if you are dealing
| | 00:23 | with charts created in Excel 2003 and
prior versions, you might be trying to
| | 00:27 | duplicate a particular color capability there,
| | 00:30 | some of things we're about to see will
allow you to in effect create some color
| | 00:34 | choices the way you might have
done it in prior versions of Excel.
| | 00:38 | But I would think say in this chart
here if you want to change the chart area
| | 00:42 | right here, just click on the Format
tab, go to Shape Styles as you might
| | 00:46 | have learned in another movie, pick
the style you want, and quite a few
| | 00:49 | choices there, and if you don't like them,
change the theme. But you can get there pretty fast.
| | 00:54 | It didn't take you much time.
| | 00:55 | If you have other needs like I suggested
here, and particularly if you're trying
| | 00:59 | to come up with some of those color
combinations that you might have seen in the past,
| | 01:02 | you can attack these by way
of changing the gradient colors.
| | 01:07 | The fastest way to get there is simply
to double-click the particular element
| | 01:12 | that you're interested in. Say the
outer chart area here, we'll just
| | 01:15 | double-click, and there is that
dialog box with all kinds of choices here
| | 01:20 | under Fill, Solid fill.
| | 01:22 | That's pretty obvious what that's doing
and we see the preview off to the left.
| | 01:27 | But here we go with Gradient Fill, and
sometimes you don't see anything at first
| | 01:30 | here but we do have some preset colors
and you may recognize some of these from
| | 01:37 | older versions of Excel.
| | 01:38 | Maybe we'll use one of the rainbow options here.
| | 01:41 | And if you have used that in prior
versions, you also recall that you could have
| | 01:45 | tilted the colors,
slanted them in different ways.
| | 01:48 | Well, those choices are available here
under Type. Instead of Linear, how about Radial?
| | 01:53 | And we see some choices there. And then
Direction and you can imagine all the
| | 01:57 | possibilities you might
explore this way. How about Path?
| | 02:01 | Here is another one, and Rectangular,
see some choices there, and different
| | 02:08 | effects that way too, and you can
use picture and texture gills as well.
| | 02:12 | Back to Solid gill possibly or
No gill or better yet, Close.
| | 02:17 | I didn't necessarily mean you'd want to
use this one, but here's an example of
| | 02:21 | the chart area having No fill in it.
| | 02:23 | Think of it as if it's on a pane of glass.
We can see through it. We can see the
| | 02:26 | data in the background.
| | 02:28 | Don't think most people would want that.
| | 02:30 | I'm going to press Ctrl+Z few times
here to undo the previous settings and
| | 02:35 | go through this cycle relatively quickly with a
series of Ctrl+Zs to go back to here possibly.
| | 02:41 | So it's not that you
necessarily need these capabilities.
| | 02:44 | Again, I think it's best to go to the
Format tab and make your color choices this way.
| | 02:48 | But on any of these chart elements,
including columns for example, I'm going to
| | 02:53 | double-click on the blue columns here,
double-click, selecting all of them, and
| | 02:57 | here too in the Format Data Series
dialog box we can go to Fill, and once
| | 03:02 | again we see gradient fills and because we
used rainbow last, that's what we're seeing here.
| | 03:08 | I think you can understand and see
what could happen here if you're not too
| | 03:11 | careful. You've got time on your hands.
| | 03:13 | Could be spending way too much time here.
And we can also get the colors this way
| | 03:17 | too for solid colors, if you
haven't explored that option as well.
| | 03:21 | So I think for many people it's the
alternative to the standard approach for
| | 03:24 | adding colors to chart elements in Excel 2010.
| | 03:27 | But do recognize you have that
capability by way of double-clicking and using
| | 03:32 | the various fill options that
are present in this dialog box.
| | Collapse this transcript |
| Specifying line style, color, and weight| 00:00 | If you want to control the appearance
of axes lines or gridlines, you can do so
| | 00:04 | by going to special features
not found on the Format tab.
| | 00:08 | I would recommend, however, if you're
working with a chart like the one we're
| | 00:12 | seeing on the left side of the screen
here, a column chart, if you want these
| | 00:16 | gridlines to look different,
you might first select them.
| | 00:19 | And if you are having trouble
particularly on charts where there are many lines,
| | 00:23 | if you're having trouble selecting a line,
I would recommend going to the Layout tab
| | 00:27 | and then the leftmost button.
| | 00:29 | If you have not yet been able to click
on the line, click the drop-arrow here
| | 00:33 | and all selectable elements of a chart
are available here, so possibly you'll
| | 00:37 | click the line this way. And from there,
you could just click Format Selection.
| | 00:42 | A slightly faster way to have done all that
would have been to double-click the line itself.
| | 00:47 | But it does take us into this dialog
box here and possibly under Line Color,
| | 00:53 | you might want to make a change here,
to Solid Line, change the color if you wish,
| | 00:56 | that sort of thing.
| | 00:58 | Line Style gives you the option of
changing the dash type, this sort of thing,
| | 01:02 | and the thickness, and
some other choices as well.
| | 01:05 | So as I'm making this thicker and wider
and so on. You begin to see what's happening.
| | 01:09 | There is a tendency with some users
to make gridlines more dominant than
| | 01:13 | they really need to be.
| | 01:14 | In this example right now, if I were
to close this, that's not a great look.
| | 01:18 | It tends to draw attention
away from the actual data.
| | 01:21 | So I'd suggest keep the width somewhat
narrower and don't necessarily emphasize
| | 01:26 | it with dashing, but although here and
there that can make some sense too, but
| | 01:29 | some personal choice there.
| | 01:31 | Maybe that draws a little
more attention to the lines.
| | 01:34 | So experiment with these ideas at least.
| | 01:37 | Now, keep in mind too that if you want
to change the chart, a standard way to do
| | 01:41 | this without getting into some of
these fine-tuning features is to actually
| | 01:44 | click on a line, for example here, and
then on the Format tab under Shape Styles,
| | 01:50 | you've got these options.
| | 01:52 | I wouldn't recommend most of these
because they're fairly dark and thick and
| | 01:56 | again I think you're in a situation
where maybe you're overwhelming the data.
| | 02:00 | But when you need those fine-tuning
tools, you can just double-click or take
| | 02:04 | that other approach I
suggested by going to the Layout tab.
| | 02:07 | You might want to do this
with axes as well occasionally.
| | 02:10 | So if I click on the axis
here, here too. There we go!
| | 02:14 | Click on the axis.
| | 02:15 | Immediately, if the Format tab is
active, you'll see the difference in the
| | 02:18 | Shape Styles and we can go through
that process again too. Or again, simply
| | 02:23 | double-clicking the axis over there
takes us into the dialog box, Format Axis.
| | 02:29 | And we can go to Fill and Line Color
and again explore some of the choices here.
| | 02:34 | For some people they might need these
options because they're working with
| | 02:37 | charts that were created in Excel
2003 and in prior versions, and some of
| | 02:42 | these fine-tuning capabilities I would
suggest you try and ignore and not try and match up.
| | 02:47 | But if you need them, here's how to
get to them, by way of double-clicking and
| | 02:51 | then going into the Format dialog
box to make some of these choices like you've just seen.
| | Collapse this transcript |
| Working with chart text| 00:00 | You can control the font style and the
size of all chart text at once or simply
| | 00:05 | portions of a chart as you wish.
| | 00:08 | The features we're about to show you
here are not available on the Format tab
| | 00:12 | of the Ribbon. They're actually available
on the Home tab or by way of right-clicking.
| | 00:17 | Now, if you click in the chart area
of a chart, I'm about to do that here.
| | 00:21 | Remember the chart area is the outer
area, sometimes called the perimeter area
| | 00:25 | of a chart. Not the inner
area, which is called the plot area.
| | 00:28 | If you click on the chart area and then
go to the Home tab, if you make a change
| | 00:33 | to the font size for example here, 10,
I'm going to slide over these, you'll
| | 00:38 | notice as I'm sliding over these that
all of the text on the chart is changing
| | 00:43 | at the same time. And I doubt
that I'd ever make it this big of course, but
| | 00:47 | sometimes this is an approach,
particularly if you're getting a chart from
| | 00:50 | another source and you just want to
readjust all the titles, you might start by
| | 00:55 | clicking in the chart area, make them
all be the same size, say a reasonable size,
| | 00:59 | and then where you want to
make it different, if you want to make a
| | 01:02 | different font size somewhere, click there.
| | 01:05 | So I want the title to be different.
| | 01:07 | I'll click here and make
that be 16 or 18 or whatever.
| | 01:11 | So I'm changing only the text there.
| | 01:13 | Similarly, if I want to make a change to
the legend over here, I come over here,
| | 01:19 | slide the mouse around,
usually there's a box but not always.
| | 01:22 | Just click so that the box is active
and now we could go to on the Home tab
| | 01:27 | the drop-arrow for the Size. Make it
change there if that's what we wanted.
| | 01:31 | We can do that with the different
portions of a chart that contain text.
| | 01:36 | Another approach to this
is simply to right-click.
| | 01:39 | Slight advantage here could be,
suppose as I right-click here on the border that is,
| | 01:43 | the mini toolbar appears. A tad
closer perhaps. You can make some changes here.
| | 01:48 | Maybe I'll shrink this a bit too.
| | 01:51 | Also, whether adjusting all of the chart
text entries at once or just a portion,
| | 01:56 | you certainly do have the option
also changing the font style itself.
| | 02:01 | So I might want to use Arial Black.
| | 02:03 | Maybe I used that recently, or
certainly all of these other many choices.
| | 02:07 | And even though we can't see the
text along the left side of the chart,
| | 02:10 | it too is potentially about to be
changed to some of these other choices here.
| | 02:15 | You can see what's going on here too.
| | 02:16 | So sometimes you want to make
wholesale changes to all elements of the chart
| | 02:21 | that contain text, sometimes just
this one or that one separately.
| | 02:24 | So once again, beginning with chart
area possibly, then using some of the other
| | 02:28 | tools available on the Home tab.
| | 02:30 | Remember the alternate way to get
there too is simply to right-click on the
| | 02:34 | appropriate location, on its actual
border, and possibly use at this point the
| | 02:39 | mini toolbar as well.
| | 02:40 | Different ways to adjust fonts, not
by way of the Format tab, but by way of
| | 02:44 | choices on the Home tab.
| | Collapse this transcript |
| Changing the rotation of chart text| 00:00 | You might want to rotate text either
to accommodate more explanatory text or
| | 00:04 | possibly to improve readability.
| | 00:06 | Here too, a feature that is not going
to be reached by going to the Format tab
| | 00:11 | and choosing any of the options there.
| | 00:13 | Best way to get to slanting text capability
is simply to go to the Home tab in the Ribbon.
| | 00:20 | In other words, we're using features that
are available outside the realm of charting.
| | 00:24 | It's strongly advisable not to tilt your
main title but if you want to go there,
| | 00:28 | that's available and it is acceptable.
| | 00:31 | The Angle Text option here under Alignment,
you can certainly do that if you wanted to.
| | 00:36 | Maybe here and there for
special effects with shorter words.
| | 00:39 | It might make some sense.
| | 00:40 | Now if you click on the legend you'll
see immediately that box is unselectable.
| | 00:44 | And probably with a good reason.
| | 00:46 | Who would want a slanted legend?
| | 00:48 | If you try this on the left-hand side here
on this text, you could go down that way too.
| | 00:53 | What about at the bottom here?
| | 00:54 | This is a more likely use of
this, underneath the columns here.
| | 00:58 | Now, we probably don't really need to
do that here but we could use this button
| | 01:02 | again, if that's the need.
| | 01:05 | Recognize here too that some of
this might happen automatically.
| | 01:08 | This chart is reflective of the
data over in columns A through D.
| | 01:13 | If I put-in the word January right
here instead of Jan, and I'll just edit
| | 01:17 | and type that onto the end of it,
as soon as I press Enter watch the chart here.
| | 01:22 | January appears there.
| | 01:24 | But what if I take the January entry?
| | 01:26 | And a lot of you know this
Auto Fill technique here.
| | 01:28 | I'm going to convert all the rest
of the months to full spellings just
| | 01:31 | by double-clicking.
| | 01:32 | Keep your eye on the chart as I
double-click that lower left-hand corner.
| | 01:35 | You see what's happening now.
| | 01:38 | Now sometimes you'll do that and it
actually will slant the text for you and
| | 01:42 | sometimes it doesn't. I'm hard-
pressed to sometimes describe why that
| | 01:45 | happens because I'm not really sure.
| | 01:47 | But what we would want
to do here is change this.
| | 01:49 | In addition to using that button, which
probably is the fastest way, another way
| | 01:54 | that we frequently make changes in Excel
2010 to different elements in the chart
| | 01:58 | is simply to double-click.
| | 02:00 | So if we double-click one of the
labels down below here, any of these
| | 02:03 | months, double-click, we
immediately see a dialog box whose last choice
| | 02:08 | includes Alignment.
| | 02:09 | But you'll notice on these the text
direction is limited. Horizontal, 90 degrees?
| | 02:15 | Well, that could be okay.
Perhaps the other way might be better.
| | 02:18 | But where's that angle?
| | 02:21 | Well, how about Stacked? Is that going to work?
| | 02:23 | That's a terrible choice.
| | 02:25 | We're not getting the choice here
that says Custom Angle unless we choose
| | 02:29 | Horizontal and then 45, and I'm
certainly not claiming this is faster or better
| | 02:35 | than what we just saw. And
you see what's happened here.
| | 02:37 | That may not be the direction
you want, so how about a -45 there?
| | 02:41 | How is that going to look? There we go!
| | 02:44 | So that's a little bit harder
to get to than you might think.
| | 02:47 | So another way to do this as I
suggested, but since we've already done it we
| | 02:50 | don't need to, select the Labels region,
then go to the Home tab, possibly make
| | 02:55 | your choices this way.
| | 02:57 | So different ways to get there, but
there will be times and you're much
| | 03:00 | more likely to apply this feature to
the axis across the bottom of a chart
| | 03:04 | than anywhere else.
| | Collapse this transcript |
|
|
10. Adjusting Specific Chart TypesUsing column and bar charts| 00:00 | For most Excel users, column and bar
charts are the most popular chart types.
| | 00:05 | They have a lot of features in common,
but a couple you might not be aware of
| | 00:10 | are called Gap Width and Overlap.
| | 00:13 | I'll be starting with some examples
with column charts and then show you some
| | 00:16 | similar examples with bar charts and
also give you a stronger rationale as to
| | 00:20 | why sometimes a bar chart
might be better for your uses.
| | 00:24 | As we look at the three
charts here closest to the data,
| | 00:28 | a reminder here what these
chart types are actually called.
| | 00:31 | The one below the data is
probably the most common type.
| | 00:34 | It's a clustered column chart.
| | 00:36 | It's the default chart type unless you
have made a change to that setting in Excel.
| | 00:41 | The chart to the right of the data
here, the one with a lavender background, is
| | 00:44 | called a stacked column chart.
| | 00:47 | Remember, its big advantage is we can tell
at a glance what the total happens to be.
| | 00:51 | We can see that April is bigger than
February, but only slightly. A bit difficult to
| | 00:56 | discern if we are looking at the
clustered column chart below the data.
| | 01:00 | Less commonly used, but occasionally
used and possibly as an antidote to
| | 01:04 | shortcomings of a pie chart is the
chart here below the stacked column. This is
| | 01:09 | called 100% stacked column.
| | 01:12 | And here the various sizes don't
represent proportional size, but in each case
| | 01:19 | the column adds up to 100%.
| | 01:21 | The January column here represents
a total of 290, these four segments,
| | 01:28 | whereas the June total represents a
total of 490, and we can see those numbers
| | 01:33 | over in the data in row 10.
| | 01:34 | So if we are looking at the two green
areas here for example in January and
| | 01:39 | February, the January green
looks substantially larger.
| | 01:42 | It's 110 and here we see 120.
| | 01:45 | It's actually larger, but it looks
smaller. But of course proportionally Asia
| | 01:49 | has a bigger share of the total in January.
| | 01:52 | So what we are showing here month
by month is a percentage breakout.
| | 01:56 | But as you might be noticing
though, where is the actual percent?
| | 01:59 | We don't see it here.
| | 02:01 | So if you are using charts like this,
you might want to consider showing this in
| | 02:04 | the actual data which is nearby.
| | 02:06 | But it does give us a quick read
on what's happening proportionally.
| | 02:09 | For whatever reason Asia, which had a
large share in January, that share began to
| | 02:14 | shrink as we look at other months and
as we move out to June, substantially
| | 02:18 | smaller at least percentage-wise.
| | 02:20 | Something else you might notice in this
chart too. There is a lot of white space
| | 02:24 | between the columns.
| | 02:26 | We see that up above in the stacked
column chart, not so much over in the
| | 02:31 | clustered column chart.
| | 02:32 | But no matter what the chart type is, if
you double-click on one of the columns,
| | 02:37 | I will do it here on the
stacked column chart, double-click,
| | 02:40 | in the dialog box Format Data Series
you will see a choice called Gap Width.
| | 02:46 | Possibly it's set here.
| | 02:47 | Move this aside just slightly. Keep an
eye on that chart to the left. I am going
| | 02:50 | to slide this leftward,
and see what's happening?
| | 02:53 | The gap between the clusters for
each month is very narrow and now it's
| | 02:57 | disappeared completely.
| | 02:59 | For some chart types maybe that makes
sense. Drag it anywhere you want.
| | 03:03 | Increase the Gap Width here and there
if you think that looks better.
| | 03:06 | So you've got some choices there.
And we can try this on other charts too.
| | 03:11 | Here is the stacked column chart to the
right of the data. Click one of those there.
| | 03:15 | How about the Gap Width here?
| | 03:17 | You see what's happening.
| | 03:19 | The white space between.
| | 03:20 | Same thing would happen down below
on the other chart here and you see
| | 03:25 | what's going on there.
| | 03:27 | Now you will also see a
choice called Series Overlap.
| | 03:31 | I am going to try that on the chart
below the data, the clustered column chart again.
| | 03:35 | Click one of the columns and
we see what's happening here, and as I
| | 03:39 | slide rightward, they overlap a little bit.
| | 03:42 | I don't think this is
necessarily a desirable feature.
| | 03:44 | Maybe here and there to
reduce a little bit of clutter.
| | 03:47 | If you carry this too far, you
essentially are taking the column in front
| | 03:51 | and giving it more attention than it's due.
But at least it's there from time to time.
| | 03:56 | You might want to try this on a stacked
chart. You are going to be surprised at
| | 03:59 | what happens here unless
you've seen this feature before.
| | 04:02 | Be sure to click on one of the series
and then slide this back-and-forth a
| | 04:06 | little bit to see what's happening here.
| | 04:09 | You might have to do some explaining as
to how to read that, but I think possibly
| | 04:13 | here and there that has some merit.
| | 04:16 | And similarly in the chart down below
here too, if we change that, you see
| | 04:20 | what's happening there too.
| | 04:21 | I think you're perhaps less likely to use
the feature here but at least it's available.
| | 04:26 | Now, in all cases here, if we switch
any or all these charts to a bar chart,
| | 04:31 | I will just click on the one below the
data and then go to the Design tab in the
| | 04:35 | Ribbon up above, and that leftmost
button, Change Chart Type, we could certainly
| | 04:39 | turn these into bar charts.
| | 04:41 | Pretty much the same approach here,
same data,. Everything is rotated at 90 degrees.
| | 04:46 | Sometimes you have to do some label adjustments.
| | 04:49 | But the white space idea is going to work in
the same way and here too, the overlap idea.
| | 04:54 | So let's say we'd be repeating ourselves here.
| | 04:56 | But on a 3D chart, there is a
difference and with a column 3D chart, at least
| | 05:01 | this kind of 3D chart, there's
another issue that we need to deal with.
| | 05:05 | If you look closely at the columns
here and also look at the order of the
| | 05:10 | legend, the order of the legend will
match the order of the data unless you make
| | 05:14 | a change. And sometimes you might need
to make a change, as we might need here.
| | 05:19 | As you look at the data the red
columns are behind the green ones.
| | 05:23 | Red represents Europe, green represents Asia.
| | 05:26 | It would be a lot easier to see those if
we could somehow move that series forward.
| | 05:32 | So the way we get here is to just
click here and you can do it two ways.
| | 05:35 | From the Ribbon on the Design tab,
fourth button from the left, select Data.
| | 05:42 | And in this dialog box, even though we don't
really see the words, we do see arrows here.
| | 05:47 | So I am going to click Europe. And
first time around you will probably use the
| | 05:50 | wrong arrow. I do half the time.
| | 05:52 | I am going to click the drop-arrow
here and watch the Europe set of columns,
| | 05:56 | the red ones in this 3D column chart, move
forward to appear to be in front of the green.
| | 06:02 | Also, watch the legend order change, but
the original data in rows 6 and 7 is not
| | 06:08 | going to change at all.
| | 06:09 | So I will click the drop-arrow
right here in this dialog box.
| | 06:12 | So see what's happened to that chart.
The legend has adjusted; the actual
| | 06:16 | data has not changed.
| | 06:17 | Sometimes you need to do this.
| | 06:19 | You can do this with other charts as
well. I certainly wouldn't say it's
| | 06:23 | necessary in the chart below the data.
| | 06:25 | That's a bar chart.
| | 06:26 | We needed to close this first. Click OK.
| | 06:29 | But in this chart here if somehow or
other you wanted the order of this series
| | 06:33 | to be different, we can just click on
anyone of them and then go to Select Data.
| | 06:39 | Again it's on the Design tab and here
just by playing with the arrows a little bit,
| | 06:43 | see what's happening, see how
we're moving the blue bar upward and maybe
| | 06:47 | Asia typically, second place.
| | 06:49 | So I will move it up also. Actually moving
it down the list here but up on the chart.
| | 06:54 | I am going to take Europe
and do one more of those.
| | 06:56 | So maybe that's a slightly better look.
| | 06:59 | But some people would suggest don't do
this at all because by changing the
| | 07:03 | order here you are not always matching up
with the order that appears on the worksheet.
| | 07:07 | Sometimes you do care about that.
| | 07:10 | Now, many, many times it's a
tossup between row and column.
| | 07:14 | It's either personal preference or
sometimes it's just because other charts you
| | 07:19 | created in previous years maybe were
columns so you'll continue to use those.
| | 07:23 | But here is the situation where you
might think either chart choice is going to
| | 07:26 | work the same, but maybe a
bar chart might be better here.
| | 07:29 | Here is a different set of data with
some department names and some salaries.
| | 07:33 | To create a chart with this data,
because the data is contiguous and is
| | 07:37 | surrounded by empty cells, we can
simply click on a single cell here.
| | 07:40 | If we are in a hurry, press Alt+F1,
we will get the default chart type, and
| | 07:45 | it's a column chart.
| | 07:46 | I think you can see here we have
difficulty reading the labels. They are slanted
| | 07:49 | and we are not seeing them all,
unless we make it a lot wider.
| | 07:53 | I wouldn't say it's a horrible chart,
but we do have this problem of reading
| | 07:57 | the labels perhaps.
| | 07:58 | Let's convert this into, by way of the
Design tab in the Ribbon and the leftmost
| | 08:03 | button Change Chart Type, change it
into a bar chart, a clustered bar chart.
| | 08:11 | We see what's happening.
| | 08:11 | Now we will have to make this taller.
Probably don't want to overlap the data.
| | 08:15 | We are seeing every other title here.
| | 08:17 | We will make it taller from the bottom
and sometimes you will have to do that by
| | 08:22 | way of a font change as well.
| | 08:24 | So now we can read the titles more
easily than we might have in a column chart.
| | 08:29 | The other thing you might want to do
here, particularly if it's important, if
| | 08:31 | you notice the order here is the upside down
order of what we're seeing out in column A, D.
| | 08:38 | So a quick approach here would be to
double-click this axis and in the choices
| | 08:43 | that we have here, Categories in
reverse order, and you see what's happening
| | 08:47 | lately in the background there
too and so that's an improvement.
| | 08:50 | So I think sometimes, at least when you have
long labels, bar chart might be a better choice.
| | 08:55 | I have a tendency to favor column
charts. I think a lot of people do.
| | 08:58 | But sometimes they use bar
chart just for variation.
| | 09:01 | They both work pretty much the same way
and all the things we've talked about in
| | 09:05 | terms of gap width and overlap are
going to be very similar in the two chart
| | 09:10 | types for columns and bars.
| | Collapse this transcript |
| Using line charts| 00:00 | Line charts are generally considered
the best charts to use when you're trying
| | 00:03 | to show a trend and nearly always
they are associated with dates and times,
| | 00:07 | and sometimes hours and minutes even.
| | 00:09 | As we look at this chart here, I think
one choice we might want to make is the
| | 00:13 | size of the actual points. Remember a
quick double-click usually is the way in
| | 00:18 | Excel 2010 to make adjustments
like that, formatting changes.
| | 00:22 | If you double-click the actual line,
you would have quite a few choices
| | 00:26 | available under Format Data
Series and under Marker Options.
| | 00:30 | I think a recommend choice might be here
to click Built-in and possibly reduce the
| | 00:34 | size of those markers.
| | 00:36 | And that happens immediately as
you watch the lines here to left.
| | 00:38 | Sometimes you might want to change the
actual line style to change the width of that.
| | 00:44 | Sometimes that works a little
bit better too with a narrower line.
| | 00:46 | You will notice in the chart to the right here
| | 00:49 | that one of the lines looks a little
different and it's actually smooth whereas
| | 00:53 | the other lines, like the line
in the previous chart, are jagged.
| | 00:58 | The options that we are talking about
those are called Smooth. We will use it on
| | 01:01 | the chart to the left for example.
Double-click the line and then under Line Style
| | 01:07 | you see an option called
Smoothed Line. Click here and you see what's
| | 01:11 | happened to the line, and if you click
outside, you would see it even better.
| | 01:14 | So sometimes that's appropriate.
It gives a slightly different look and
| | 01:17 | different feel, perhaps
it accentuates the trend.
| | 01:21 | So on the chart on the right, similarly
if we double-click one of the lines and
| | 01:24 | we can do the same sort of thing there.
Under Line Style, Smoothed Line, get the
| | 01:30 | idea and it looks like this.
| | 01:31 | So we could do with this other ones as
well. We will do them all of course but
| | 01:35 | probably if you're during one, you'd do them all.
| | 01:37 | Line Style, Smoothed Line, and on the
green one too, Line Style, Smoothed Line.
| | 01:45 | And for certain charts
that provides a better look.
| | 01:48 | Now, I had mentioned earlier that line
charts are best for showing trends over time.
| | 01:53 | If you change the orientation of
a chart, and I do recommend that you try
| | 01:57 | that on various charts, particularly
with column and bar charts, when you try
| | 02:01 | this with line charts, nearly always
there's an obvious first choice between the two.
| | 02:06 | So on this particular chart here, I am
going to go to the Design tab and then
| | 02:10 | the third button from the left, the Switch Row
Column, that changes orientation. Look what happens.
| | 02:15 | And I think almost anybody would
agree it just doesn't read well.
| | 02:19 | Now you could say well the data is
there accurately but line chart when not
| | 02:23 | associated with time,
| | 02:24 | in other words as we move from left
to right here, we are actually moving
| | 02:28 | to different regions.
| | 02:29 | So the idea of somehow connecting them,
as if the data were flowing from one to
| | 02:33 | another, really doesn't make a lot of sense.
| | 02:36 | And so when you do see line charts
without an axis reference to a date or time frame,
| | 02:42 | switch it by changing the
orientation, on the Design tab, third button
| | 02:47 | from left, Switch Row/Column.
| | 02:49 | Now there are some other things
about line charts you want to be aware of too
| | 02:53 | and I think almost as soon as
you see these images to the right, we
| | 02:56 | recognize what's going on.
| | 02:57 | Let me point out that these three
charts here are all based on the same data,
| | 03:03 | the data that's available right here.
| | 03:05 | It is pretty obvious if you are using
one chart or another and particularly maybe
| | 03:10 | the tall one on the left or the
wide one on the bottom, even though we are
| | 03:14 | talking about the same data, the
visual tells a different story.
| | 03:19 | I think in both cases, we could say
they're both exaggerated. Well that's true,
| | 03:23 | but if we were to alter them a little
bit, changing the height to width. That's
| | 03:27 | called the Aspect Ratio. Maybe like that.
| | 03:29 | That doesn't look as eccentric as it
did and that shows a distinct rise in the
| | 03:34 | data and maybe you are trying to emphasize that.
| | 03:36 | The lower chart says okay there
has been change but not a whole lot.
| | 03:40 | That too may be a bit exaggerated here.
| | 03:43 | We will make it more like this. There
is a more of a slope there and this chart
| | 03:47 | too perhaps is misleading.
| | 03:49 | Now, usually you are not going to see
the two next to each other but remember
| | 03:52 | during a presentation, when you do see
line charts that are unusually wide or
| | 03:57 | unusually tall, you might try and
visualize the data a little bit differently.
| | 04:01 | Again we are seeing a different story,
at least visually, from the data here just
| | 04:05 | based on the shape of the chart itself.
| | 04:09 | And in the case here of the chart that
is attempting to accentuate the change,
| | 04:13 | let me make this a little narrower again.
| | 04:15 | Something else you could do here
if you're going down that path, by
| | 04:18 | double-clicking the left axis here you
can change the Minimum Axis option, make
| | 04:24 | the starting point be 80 and possibly
at the high end too. Do something similar
| | 04:29 | and make the high end, go to 180.
You saw immediately what the 80 did and the
| | 04:34 | 180 will even add more.
| | 04:36 | In other words make this be even
steeper. And that's considered more acceptable
| | 04:41 | in line charts than that is on a column charts.
| | 04:43 | So again this really accentuates,
despite the fact that we had some dips in there,
| | 04:48 | huge change in our sales.
We are really moving along well here.
| | 04:52 | We are selling a lot more than we used to.
| | 04:54 | And this chart to the right, it's
improved as we move through the year but not
| | 04:58 | that much. Now maybe you'll be using
this style with different data where you are
| | 05:01 | trying to hide a trend that shows
increased absenteeism or something like that,
| | 05:05 | but just beware of this idea that
resizing a line chart has an impact on the display.
| | 05:11 | And in our previous examples, we saw
situations where we wanted to make changes
| | 05:15 | to the line chart by smoothing the
line or in some cases changing the
| | 05:19 | orientation of a chart.
| | 05:21 | These are the kinds of things you
are looking for when you're using line charts in Excel.
| | Collapse this transcript |
| Using pie charts| 00:00 | Pie charts are widely used and although
appropriate for some data, certainly not
| | 00:04 | appropriate for all.
| | 00:05 | And there are some oddities about
charting, not only about the selection of data,
| | 00:09 | but also about how you want to
manipulate these pies and whether they
| | 00:13 | are 2-D pies like the one we see
to the right of the data here or a
| | 00:17 | three-dimensional pie below the data,
there certainly are some similar
| | 00:21 | adjustments we would like to make.
| | 00:23 | You will notice here I just clicked on
the 3D pie, that the data that selected
| | 00:27 | shows four regions and yet only one turns up.
| | 00:30 | If you haven't worked with pie
charts, that's a bit of a surprise.
| | 00:33 | A pie chart only deals with one row or
column and so in either case and I will
| | 00:38 | click on the 2D pie as well over
here. You will see the same idea.
| | 00:42 | So if you'd like to show Europe and Asia
and Latin America, you will either need
| | 00:46 | to create additional pie charts or
perhaps use a stacked column chart or
| | 00:51 | possibly a doughnut chart.
| | 00:52 | Once you have a pie chart you also
probably at different times might consider
| | 00:57 | rotating the pie chart, make certain
wedges appear more toward the front or to
| | 01:01 | one side. You might want to tilt a 3D
pie chart, and in both cases you might
| | 01:06 | want to extract one wedge or
another to draw attention to it.
| | 01:10 | And anybody who has worked with pie
charts has done this at least the first time around.
| | 01:14 | I want to pull out a wedge here.
| | 01:15 | So on the 2D pie chart, I will just
point here, click and drag and all the
| | 01:20 | wedges come out. And that's probably
not what you want, although here and
| | 01:23 | there maybe that's okay.
| | 01:25 | But the idea here is after we
pulled these back-- and sometimes that's a
| | 01:29 | little tedious. It might be better here to
press Ctrl+Z, a couple of undos here, there we go.
| | 01:35 | To pull out one wedge, simply click the
pie, pause just for a half a second or so,
| | 01:39 | click again and then
pull out a single wedge.
| | 01:43 | Same thing with the 3D pie. Click the
pie, pause for a second or so, click one wedge,
| | 01:48 | that wedge is selected, pull out
that. Or you can certainly do this with
| | 01:52 | more than one pie as well to.
| | 01:54 | So that's easy and fast. No
special commands required in either case.
| | 01:58 | With a 3D pie chart, you might want to
tilt the chart. Thinking that you would
| | 02:03 | double click on the pie
chart, that makes good sense.
| | 02:06 | Throughout Excel 2010, you can often
get to feature simply by double-clicking
| | 02:10 | rather than trying to track
down a command in the menu system.
| | 02:14 | When you double click here, you will see
something else here. Rotation, No Rotation.
| | 02:20 | Right now it says 0. We can drag this
and we saw what happened on the pie there.
| | 02:25 | And I would suggest most people
don't need this most of the time.
| | 02:29 | It does rotate the pie in such a way
that January is now off to the right.
| | 02:33 | The initial starting point here is I move
this back as the January starts at the
| | 02:37 | 12 o'clock position.
| | 02:38 | So unless this is absolutely critical,
I would say don't bother with this.
| | 02:43 | Now if you're trying to tilt the pie,
you would've thought that well, by
| | 02:46 | double-clicking that's going to get you
there. And how about this 3D format here?
| | 02:50 | And yet the adjustments here are fine-tuning
adjustments that have nothing to do with this.
| | 02:55 | So here's the little oddity. If you want
to adjust the perspective on the pie or
| | 02:59 | you want it tilt it more so that
you are looking straight on at it,
| | 03:02 | you double-click but not the pie itself.
You double-click either in the plot area
| | 03:06 | or the chart area. You
would never call that intuitive.
| | 03:09 | So, I am going to double-click the plot
area here and then this dialog box comes
| | 03:14 | up and 3d format as what we just saw.
Wasn't that helpful. How about 3D rotation?
| | 03:19 | Right away you are thinking, well
wait a minute, we had rotation before the
| | 03:23 | different dialog box. So it
could be a little bit confusing.
| | 03:26 | Why would we go here?
| | 03:27 | As I click the arrow next to
the X here watch the pie chart.
| | 03:31 | Now that's something we
saw in a previous dialog box.
| | 03:35 | So, possibly could come here and to
do this, but how about the Y here?
| | 03:39 | For want of a better word perhaps we
would see the word tilt nearby. Here and
| | 03:43 | there this makes some sense.
| | 03:44 | It makes the pie chart perhaps more
readable and doesn't distort it as much.
| | 03:49 | And there's some perspective
buttons down here as well too.
| | 03:52 | I think for a lot of people these are
just fun and games. I wouldn't recommend
| | 03:55 | these perspective buttons either.
| | 03:57 | Notice as I am doing this for example,
the green there representing March and
| | 04:02 | the gold in the
background there representing June.
| | 04:05 | What's the value of the green?
| | 04:06 | You see it right there on the pop-
up 125. The value of the gold, 180.
| | 04:11 | So I would say don't use the
perspective unless you are just interested in
| | 04:15 | distorting the view of things here.
| | 04:18 | You won't get that similar
feature course on a 2D pie chart.
| | 04:22 | Another thing you might want to do
with the 3D pie, the height as percent of base,
| | 04:27 | the arrow down here. I am going to
click the down arrow and this is making
| | 04:31 | the pie flatter and flatter and flatter.
| | 04:33 | Now if the nature of the data here
happens to be something related to the volume
| | 04:38 | of a certain kind of item you're
selling, maybe here and there adjustment of
| | 04:42 | this height has some relevance there.
Well I kind of doubt that, but still just
| | 04:46 | a different look and
sometimes you want control over this.
| | 04:50 | Recognize a couple of other
deficiencies here with pie charts as well too.
| | 04:54 | Now I'm going to change January
here to be -80. Suppose what we are
| | 04:59 | tabulating here are profits and for
whatever reason things went bad in
| | 05:03 | January. I want to press enter.
| | 05:06 | As you watch both pie charts you
would expect them to change, but they
| | 05:10 | didn't change that all.
| | 05:12 | Now we had labels on them they would,
we would see something different, but
| | 05:16 | recognize that we are dealing with
negative and positive data and the very
| | 05:20 | concept of a pie chart is
the pieces add up to the whole.
| | 05:25 | So this is extremely misleading and
I would say anytime you've got a negative
| | 05:30 | don't use it in a pie chart.
| | 05:32 | Now if someone suggested to me once,
if all of the numbers were negative,
| | 05:35 | perhaps we could get away
with this. Well, maybe so.
| | 05:37 | I am not sure what that data would
be, but possibly that's an out here.
| | 05:41 | I like to use the line too sometimes,
there's no such thing as a negative piece of pie.
| | 05:45 | So stay away from negative data.
| | 05:47 | Now the one major thing missing here
and it is covered in another movie and
| | 05:50 | that's how to apply data labels.
| | 05:52 | Probably the best way here is just to
click on this chart or that chart and then
| | 05:56 | go into the Ribbon, choose Layout and
then Data Labels. Pick a location here.
| | 06:01 | Maybe Outside End, something like that.
There we do see the -80 and on the other
| | 06:06 | chart too, similarly the 2D chart,
Data Labels and we will put them on a
| | 06:10 | different location. How about Inside End?
Sometimes you want to put them inside.
| | 06:14 | So that's the only recognition that
there is negative data and I would say don't
| | 06:17 | use that kind of data.
| | 06:19 | So there are lots of different
things we want to do with pie charts and
| | 06:22 | certainly in some situations they are
totally appropriate for data at hand, but I
| | 06:27 | think you will recognize
some real limitations as well.
| | Collapse this transcript |
| Using area, stock, and XY charts| 00:00 | Although less commonly needed by most
Excel Chart users, area, stock, and XY
| | 00:05 | charts, sometimes called scatter
charts, are specialized chart types ideal
| | 00:10 | for some sets of data.
| | 00:12 | Looking at this data here that
covers information for about a
| | 00:15 | year-and-a-half, two-year period here,
let's create a line chart off of this
| | 00:20 | first simply by clicking the Insert
tab in the Ribbon, choosing Line, this
| | 00:25 | choice right here. Click it.
| | 00:27 | Not bad. It could be refined a bit.
| | 00:29 | We could make the symbol smaller and so on.
| | 00:31 | But an area chart presents this
information in similar fashion yet it shows the
| | 00:36 | information as volume.
| | 00:38 | So I'm going to change the
chart type here to be an area chart.
| | 00:43 | And there are six variations on area
charts, very similar to the kinds of
| | 00:46 | variations you see with line and column charts.
| | 00:49 | A simple area chart, stacked, 100% stacked,
and then three of these in 3D versions too.
| | 00:55 | Here's the simple area chart. Double-click it.
| | 00:59 | And the red which is representing
Other Sales is in front of the US Sales.
| | 01:04 | So we're seeing the information this way.
| | 01:06 | This might be a little clear if we
change the style here to be this one, sort of
| | 01:11 | a pseudo-three-dimensional.
| | 01:12 | And to convey the total amount here we
might want to use a stacked area chart.
| | 01:17 | So let's go on the Design tab,
leftmost button, Change Chart Type, we'll make
| | 01:23 | this be a Stacked Area Chart.
| | 01:24 | Clicking like that.
| | 01:26 | And now the height of these, for
example this point here, represents the total
| | 01:30 | of the Other Sales and the US Sales.
| | 01:33 | So one of our lower values down
there somewhere, probably one of these two
| | 01:36 | here somewhere in there.
| | 01:37 | We're talking about the total into the
30 million category, in terms of totals.
| | 01:41 | And another way to display this data
too in an area chart, and this would
| | 01:45 | definitely be better than showing it in
a line chart, the design change to the
| | 01:51 | chart type, stacked, the 100%.
| | 01:54 | Now these do require a little bit of
interpretation and explanation perhaps, but
| | 01:57 | for any given monthly period here, we
can see for every one of them US Sales
| | 02:02 | comprised more than half.
| | 02:04 | We're not seeing the actual
percentage here, but we do see the percentage
| | 02:07 | markers on the left-hand side.
| | 02:09 | So overall, it looks as if US Sales are
averaging out about 60%-65%, although it
| | 02:15 | varies from month to month.
| | 02:16 | So in any given month, for example
this one here, US Sales has a higher
| | 02:20 | proportion looks like it does
here than it does in other months.
| | 02:23 | The totals add up to 100%.
| | 02:25 | So area charts are just variations on
line charts, sometimes more colorful, and
| | 02:29 | they do tend to suggest
volume by their appearance.
| | 02:33 | Stock data, a specialized kind of chart,
and there are six variations on this.
| | 02:38 | Notice the data here and I've
segregated it a little bit. We've got data by
| | 02:42 | Date, we've got High, Low, Close values
for a given stock, also Volume and Open.
| | 02:49 | Now you might want to consider moving
these around, but you won't really do that
| | 02:53 | until you see some of the possibilities.
| | 02:55 | And I've got data here that
covers quite a large timeframe.
| | 02:58 | In fact, it could be covering years and years.
| | 03:02 | But I think these charts are likely to
work best for just a short timeframe.
| | 03:05 | Let's go to the Insert tab
here and choose Other Charts.
| | 03:11 | Now there are four variations on Stock.
| | 03:12 | As I slide over the first one,
note that it says High-Low-Close.
| | 03:16 | And if we want this to work, we must
have them in that order as you see them
| | 03:20 | over in the left-hand side, Columns B, C, and D.
| | 03:23 | The second kind of stock
chart is Open-High-Low-Close.
| | 03:27 | requires four series of values in
order Open, High, Low, and Close.
| | 03:32 | If I were interested in this kind of
chart, I'd have to move the Column G data
| | 03:36 | over and put it between A and B.
| | 03:38 | Next type here, Volume-High-Low-Close.
| | 03:40 | I think you get the idea.
| | 03:43 | These are the kinds of things you might
want to be measuring for a given stock.
| | 03:47 | And if you've been able to download
that data from some source, one or more of
| | 03:51 | these is likely to be of value to you.
| | 03:53 | And this last one here is Volume-
Open-High-Low-Close, all of these.
| | 03:57 | I simply want to use the first one and
so I do have the data lined up already.
| | 04:01 | It's High, Low, and Close.
| | 04:03 | So simply by selecting this data, and
I have put a gap in down below so that
| | 04:07 | we're only about to cover one month.
| | 04:09 | In other words, this data right here.
| | 04:11 | And a lot of you know, if you simply
click in a cell and create a chart, Excel
| | 04:15 | picks up the surrounding data.
| | 04:17 | It won't go beyond the empty
Column E there or the empty Row 23.
| | 04:21 | So we'll just click in here and go to
Other Charts, make this first choice right
| | 04:27 | here, and there's our chart.
| | 04:29 | And probably not the most exciting
chart in the world, not the easiest to read.
| | 04:33 | We might want to make some changes here.
| | 04:35 | You'll notice a couple things we
might want to adjust almost immediately.
| | 04:38 | If you look at it for bit, you'll say well,
there are some gaps in here, aren't they?
| | 04:41 | There's a gap there.
| | 04:43 | The stock market tends to be open, what,
just five days a week and few were on
| | 04:47 | a holiday, so we've got some gaps here.
| | 04:49 | This is a minor point, but the way
we could change this isn't so obvious.
| | 04:53 | If you double-click the Lower Axis
here, that activates a dialog box and I
| | 04:59 | don't think you'd automatically
gravitate toward this choice. Text axis.
| | 05:03 | Let me move this over a little bit.
As I click Text axis, and watch the lower
| | 05:08 | axis and watch the chart change.
| | 05:10 | The weekend gaps are eliminated,
so you'd probably want to try that.
| | 05:14 | Perhaps more important is
the way these lines look.
| | 05:17 | Now if you look at the legend, the
High, Low, you can sort of figure out
| | 05:20 | what does that mean.
| | 05:21 | The height of the bar.
| | 05:23 | Maybe the more important item here
to check is the Close, but how can you
| | 05:28 | actually get in there and click on it?
| | 05:30 | It's going to be a little tricky
maybe. You might click there, you might
| | 05:32 | click there, and so on.
| | 05:34 | So if you would like to select the Close
item, then go to the Layout tab in the
| | 05:40 | Current Selection on the left-hand side.
| | 05:43 | We're not trying to select the plot area.
| | 05:45 | Let's click the drop arrow. We're
trying to click the Close series.
| | 05:50 | And once that has been selected,
then the choice right below it, Format
| | 05:54 | Selection. Takes a little
bit of time to get here.
| | 05:57 | Then in the Format Data Series dialog
box, let's choose Marker Options and
| | 06:03 | increase the size of this.
| | 06:05 | I'm just clicking up arrow here and I
bet you'll see how that green marker out
| | 06:08 | there is getting bigger.
| | 06:09 | Maybe a black marker would be better.
| | 06:11 | You can make your choice there if
you wish and see what's happening.
| | 06:14 | So I think that's something
you'd probably want to do as well.
| | 06:17 | Now for some people will say, all
I care about is the closing price.
| | 06:21 | Well, maybe a stock chart
isn't the one you want anyway.
| | 06:23 | Maybe it's just a line chart.
| | 06:24 | So you can certainly work with this
data with other charts, but if you do have
| | 06:29 | High, Low, Close, Volume, Open
data or most of those, you have some
| | 06:34 | possibilities here in using
what's called a stock chart.
| | 06:37 | Another kind of chart is called an XY chart.
| | 06:40 | That's its official name, but it's
frequently referred to as a scatter chart.
| | 06:44 | In this data here, we have results of a race.
| | 06:47 | Now they happen to be sorted by age,
but that doesn't have to be the case.
| | 06:51 | I could click in Column B here and
go to Data and sort them by Times.
| | 06:56 | It doesn't make any difference.
They don't even have to be sorted.
| | 06:58 | We're trying to see if there's any
relationship between the age of the runners
| | 07:02 | and the time that it took
to finish this 10K race.
| | 07:05 | And we got about 100
entries here, so 99 it looks like.
| | 07:08 | So we'll click somewhere in the data,
and then in the Ribbon choose Insert >
| | 07:14 | Scatter, the first choice here.
| | 07:17 | And first thought is well,
that's not so good, is it?
| | 07:20 | What are we seeing here?
| | 07:21 | And we can get rid of the time legend on
the right-hand side. Just press Delete.
| | 07:25 | The first thing you might want to do
here is say well, we don't have anybody
| | 07:27 | under 20 in this race, why don't
we adjust the scaling on the bottom?
| | 07:31 | Simply double-click the Scale on the
bottom and let's change the Minimum here
| | 07:36 | from Automatic to be 20.
| | 07:40 | And if you simply click on word
Fixed, you'll see that it has adjusted.
| | 07:43 | Let me move this over a little bit
temporarily so that you can see what's happening.
| | 07:46 | And at the high end, looks like that's raised it
| | 07:47 | I think the oldest
runner in this is 62-63.
| | 07:49 | That's looking okay.
| | 07:52 | But the other axis we might want to change
as well, and that gets a little trickier.
| | 07:56 | it looks like the fastest
time here is about 45 minutes.
| | 08:00 | Maybe a little bit under, but by double
-clicking the left axis and you might
| | 08:03 | have to guess at this a little bit,
let's change the Minimum here.
| | 08:07 | In other words, don't do the automatic
scaling. Turn it into 45, and you can type 0:45.
| | 08:14 | That should do it, and I'll
just click the word Fixed here.
| | 08:17 | And that makes a reasonable adjustment there.
| | 08:19 | Now it's a lot trickier and you have to
work with it to get the intervals, or in
| | 08:23 | other words, make the
gridlines be a little bit different.
| | 08:25 | But let's say we've done enough here at
least to get the idea across that when
| | 08:29 | you're trying to associate two
different sets of numerical values, a scatter
| | 08:33 | chart makes some sense.
| | 08:35 | You might try this also with
situations where you've got perhaps the age of
| | 08:39 | people and their salaries. You're trying to
see if there's a correlation there as well too.
| | 08:43 | With the variety of different data
types, you can use a scatter chart.
| | 08:46 | So we've seen quick examples here of an area
chart, a stock data chart, and a scatter chart.
| | 08:52 | A little bit unusual for some
people, but they do have their uses.
| | Collapse this transcript |
| Using doughnut, bubble, and radar charts| 00:00 | To overcome some of the limitations
of a pie chart, you might try using and
| | 00:04 | experimenting with a doughnut chart.
And if a scatter chart isn't giving
| | 00:07 | everything you might need, a
bubble chart is worth exploring.
| | 00:11 | A third kind of chart, a radar
chart, you are unlikely to use.
| | 00:13 | But just for the sake of
experimentation that too is worth a quick look.
| | 00:17 | This particular data here, if we
wanted to show the breakout by month for
| | 00:22 | domestic, Europe and Asia, first
thought might be well, let's create some
| | 00:25 | pie charts out of this.
| | 00:27 | If you haven't used pie charts a lot, you
are likely to be encountering a surprise here.
| | 00:32 | If I highlight this data and go to the
Insert tab in the Ribbon and choose Pie
| | 00:37 | and I'll just use a simple 2D pie,
| | 00:40 | it might have you scratching your head
a little bit because it says Domestic
| | 00:44 | and even though we selected Europe and
Asia, this only shows a single series,
| | 00:50 | the Domestic series.
| | 00:52 | In other words the pie isn't layered
and by shrinking this, this hasn't changed
| | 00:56 | anything at all. We are still only
showing Domestic, as you might expect by doing
| | 01:00 | this, but that's a limitation.
| | 01:03 | An alternative approach to this
is what's called a doughnut chart.
| | 01:06 | Let's start with this data highlighted
and this time we'll go to the Insert tab
| | 01:10 | again, Other Charts, start with
this Doughnut chart right here.
| | 01:15 | Display the contribution of each value
to a total, like a pie chart, but it can
| | 01:20 | contain multiple entries. There we go.
| | 01:23 | That's a bit tough to read, isn't it?
| | 01:25 | Let's change the chart style. That might help.
| | 01:29 | There we go, but we've got a lot of
labeling to do and/or drawing arrows or
| | 01:34 | somehow to explain this.
| | 01:35 | I wouldn't say this is unworkable. We
might want to show fewer regions, in other
| | 01:41 | words just Domestic and Europe.
| | 01:42 | But it's not even clear
which region is which here.
| | 01:45 | So, one approach to this might be on
the Layout tab in the Ribbon, under the
| | 01:51 | Chart Tools Ribbon to top there, Data Labels.
| | 01:55 | Let's show the labels.
| | 01:57 | And right away, you are thinking well
that could be helpful but on the other hand,
| | 02:01 | we have to keep going back to
the data to figure out what's what.
| | 02:05 | So what might we try here?
| | 02:07 | Let's double click on one of the series,
say the outer series, just any one of
| | 02:10 | these, just double-click.
| | 02:12 | And immediately we see Format Data
Labels, so maybe we could have the series name.
| | 02:16 | And I think as soon as you see
that you might be saying well yeah, but
| | 02:20 | there is Asia down there
every single time, isn't there?
| | 02:23 | There is no good way to put these labels
out here except by doing them manually.
| | 02:27 | Think that's an oversight
here but that's the way it is.
| | 02:30 | Now, we might reconsider for the
moment here. Close this dialog box.
| | 02:35 | What if we were to show
this data for only six months?
| | 02:38 | You might be saying well yes,
but I want to show the whole year.
| | 02:41 | Well at least try this to
see how that might look.
| | 02:44 | I think at least we are getting into
something that could be more readable.
| | 02:47 | I'm sure you wanted to show Asia in
this but maybe you don't for the moment and
| | 02:51 | you can see what's happening here.
| | 02:52 | So we do have some possibilities here,
but we are sort of setting this up as a
| | 02:56 | solution and then sort of
reneging a little bit perhaps.
| | 03:00 | I think you want to experiment with this.
Double-click again one of the rings.
| | 03:04 | Actually, it's best to click outside
and then come in and double-click so we
| | 03:08 | get these choices here.
| | 03:09 | The Series Name, at least explore that.
Category Name is redundant if you have
| | 03:14 | a legend as we do here, but possibly
you could get rid of the legend and then
| | 03:18 | keep these indicators.
| | 03:20 | But now we still don't know based on
what we are seeing here which ring is which,
| | 03:24 | which is Domestic, which is
Europe. And then you notice this oddity.
| | 03:29 | The blue value here that I'm circling
is a 60 and the inner value is an 80.
| | 03:34 | Well, obviously 80 is bigger than 60
but it surely doesn't look it here.
| | 03:39 | So I think you are going to
need to experiment with this.
| | 03:41 | You might want to change the order of
the series. Some of you know how to do
| | 03:44 | that to accommodate this and there are
certainly some other approaches here.
| | 03:48 | But at least it's an attempt to try and
show information by way of pie chart in
| | 03:53 | a similar kind of
arrangement, in other words circular.
| | 03:57 | But I think you'll have
to experiment with it a bit.
| | 03:59 | A third alternative here that some
people prefer is simply 100% stacked column chart,
| | 04:04 | so that's another alternative as well.
| | 04:07 | Another kind of chart is a bubble chart
and this is an unusual chart and this is
| | 04:11 | one of those charts where you
always have to explain how to read it.
| | 04:15 | Think of it as a scatter chart
with another dimension in it.
| | 04:18 | In this example here we've got the ages
of people, their salaries, and let's say
| | 04:22 | a job rating and so turning this into a
chat, we choose Insert > Other Charts.
| | 04:30 | Now what's the advantage of bubble,
what's the description? Resembles a scatter
| | 04:33 | but compares sets of
three values instead of two.
| | 04:38 | The third value determines the size of the
bubble marker which is displayed with a 3-D effect.
| | 04:43 | That's because I am choosing the second option.
| | 04:45 | This option over here is two-dimensional.
| | 04:47 | Let's try this 3-D and I think
when you see this you might be
| | 04:50 | pretty disappointed.
| | 04:52 | What's going on here?
| | 04:53 | Well first of all, let's make
the chart a little bit bigger.
| | 04:55 | It's going to help.
| | 04:56 | And the first-order business here
would be to shrink the size of the bubbles.
| | 05:00 | Let's just double-click.
| | 05:02 | Notice that they are different sizes
and although it's not obvious yet they are
| | 05:06 | reflecting the entries in
column C, the job ratings.
| | 05:10 | When you double-click on a bubble,
the Format Data Series dialog box opens.
| | 05:14 | Scale bubble to, and maybe we'll make
this be 50% and then simply close it.
| | 05:21 | That's going to be helpful.
| | 05:23 | We don't need the legend here.
| | 05:24 | Let's get rid of that. And furthermore the
indicators across the bottom of the screen here,
| | 05:29 | they start at 20 and end at about 55 or so.
| | 05:32 | So double-click the bottom axis
here and make the Minimum here,be 20.
| | 05:40 | And for the Maximum how about 55 or thereabouts.
| | 05:45 | That's going to be helpful a lot.
| | 05:47 | And with regard to the salaries on
the left-hand side looks like the bottom
| | 05:50 | this should be around 20,000 so we'll
double-click the left-hand vertical axis
| | 05:53 | there and the change the minimum,
in other words override the automatic
| | 05:57 | setting, make it be 20,000.
| | 06:02 | Now you may have noticed an odd thing
about the data here and also about the
| | 06:06 | fact that nothing across the bottom
indicates age. And the word age isn't here.
| | 06:12 | And I am going to go to cell A1 and type
in age and the chart becomes worthless.
| | 06:18 | I'm not sure why that happens, but the
workaround would be if you truly want to
| | 06:22 | indicate this, you can put in your own
label and the axis underneath the data
| | 06:27 | here to indicate that it is age.
| | 06:29 | But when the word age
appears there over in cell A1,
| | 06:31 | it does not allow us to
create the Bubble chart properly.
| | 06:35 | So how did we read this and I did
indicate that you do have to do some explaining.
| | 06:38 | Think of it as a scatter chart
representing various people in the organization,
| | 06:43 | their ages and their salaries by simply
looking at the grid, but the size of a
| | 06:49 | bubble represents the job performance.
| | 06:51 | Now some people might question why we
are mixing those in with the scatter chart
| | 06:55 | information but that's what we are doing here.
| | 06:56 | This person here, whoever it is, has
an age about 46, 47 someone there.
| | 07:01 | As you slide the mouse over you can
momentarily see what that represents.
| | 07:05 | The age is 46, the salary 64,000, you
read that off the left axis, and the job
| | 07:10 | performance is 9, which is relatively good.
| | 07:13 | Here someone here who has a relatively
higher salary compared with some people
| | 07:17 | and yet has a job performance of 2.
| | 07:19 | In other words,
reflecting the size of the bubble.
| | 07:22 | One thing that has been pointed out
about this too, our eyes don't necessarily
| | 07:26 | read these bubble sizes that well.
| | 07:28 | Just here for example, that's a size 4.
| | 07:31 | This is a size 2. What's this one here?
| | 07:33 | A 6? Does it look that much bigger?
| | 07:35 | It looks like the 2 is pretty small.
| | 07:38 | But at least there is some merit here
and if you don't like this style you might
| | 07:41 | switch it to the two-dimensional style
and again if we simply click in the chart,
| | 07:45 | go to the Design tab, and then the
second button from the left, possibly the
| | 07:51 | two-dimensional bubble.
| | 07:52 | Maybe that works better.
| | 07:54 | Not as interesting as the other one though.
| | 07:56 | And a third kind you are unlikely
to see, but occasionally you might.
| | 07:59 | It's called a radar chart and we
don't have it setup here just yet.
| | 08:03 | And this kind of chart measures
information and you can use multiple ranges.
| | 08:08 | I want to say it's a
variation on a line chart. But not really.
| | 08:11 | Let's show it rather than trying to define it.
| | 08:14 | Insert > Other Charts > Radar.
And there are three options here.
| | 08:20 | Data relative to a center point.
We got a couple of options here.
| | 08:23 | All of them are related to the center point.
| | 08:25 | But it's the shading
differences that we're talking about.
| | 08:27 | I use the first one here.
| | 08:29 | And here too, I think you are
going to have to do some explaining.
| | 08:33 | I can't readily admit that I've
seen one of these used very often.
| | 08:37 | In fact it's been about six
years since I actually saw one.
| | 08:40 | In that situation we merely turned
into a column chart and it was much,
| | 08:44 | much more readable.
| | 08:45 | But here and there perhaps it has
some use and I haven't found a really
| | 08:49 | great example anywhere.
| | 08:51 | And in this particular situation too
because of the number of points being shown
| | 08:56 | here, at first glance that
might be an optical illusion.
| | 08:59 | It might look like a cube.
| | 09:00 | Don't think of it as a
cube; think of it as a web.
| | 09:02 | Maybe a spider web. And if we're only
showing information for five days here, it
| | 09:08 | will look like that.
| | 09:09 | What it's attempting to show and I think
you can see it in the data, is that the
| | 09:13 | blue line represents the average
orders that we get on different days of the
| | 09:16 | week and red brings out the average
number of shipments we do or possibly the total.
| | 09:22 | We don't see that in data
necessarily but you see what's happening.
| | 09:24 | And the oddity I assume would be the
fact that on everyday except Wednesday you
| | 09:29 | know the orders exceed the shipments.
| | 09:31 | So it's a different way to display
a data, but it's not widely used.
| | 09:35 | So you've seen three different
kind of pie charts in this movie.
| | 09:38 | The radar chart preceded by the bubble
chart, which is a variation on scatter
| | 09:42 | charts, and preceded by a doughnut chart
which is an attempt to handle multiple
| | 09:47 | ranges like a pie chart.
| | Collapse this transcript |
|
|
11. Changing a Chart's Data SourcePasting new data into a chart| 00:00 | Sometimes you need to expand a chart to
include data that you didn't originally
| | 00:04 | need or maybe just wasn't available.
| | 00:06 | Standard copy/paste techniques
often provide a quick remedy here.
| | 00:10 | Let's suppose looking at this data
here that we want to create a chart that
| | 00:14 | includes maybe just Domestic and Europe.
| | 00:17 | Or maybe Domestic and Asia. If we did,
in that case remember we can highlight
| | 00:20 | the data here, let go of the left
mouse-button, highlight Asia, and we can
| | 00:25 | create a chart this way.
| | 00:26 | If we want to use the default type,
we'll simply press Alt+F1 to get the chart
| | 00:30 | immediately on the same worksheet.
| | 00:33 | Resize a little bit, maybe apply a
chart style, or that sort of thing.
| | 00:37 | Okay, looking pretty good here. We're
making a presentation, we're about to print
| | 00:41 | it, put in some labels and so on.
| | 00:43 | Then we decide that maybe we
do want to include Europe now.
| | 00:47 | Highlight the data.
| | 00:48 | It's too bad that one of the features
out of the past versions of Excel we can't
| | 00:52 | do anymore. We used to be
able to drag this onto a chart.
| | 00:54 | But sure enough we can press
Ctrl+C to copy and a surprise here.
| | 01:00 | To get the menu choice for pasting,
don't right-click on the plot area.
| | 01:04 | Right-click on the chart area.
| | 01:06 | When you right-click on the
plot area here, you don't see Copy.
| | 01:09 | Right-click on the chart area you do see
Copy, and we'll simply paste, there we go.
| | 01:15 | So now Europe is in there.
| | 01:17 | If the order bothers you, you
could change the order of the series.
| | 01:20 | Let's not worry about that right now.
| | 01:22 | So the data is looking like this.
| | 01:24 | Now we might also want to pull in maybe
at a later time or we're looking at it
| | 01:27 | right now, we've got some
average data. Can we put this in here?
| | 01:31 | Sure, how about a Ctrl+C, click out here.
Ctrl+V also works. I just did a paste.
| | 01:38 | What you might want to do in this
kind of a situation is change the Average
| | 01:42 | column here because it is different
in nature than the other ones. Change
| | 01:46 | that into a line chart.
| | 01:47 | So by selecting a single series here
we can go to the Design tab, left button,
| | 01:52 | Change Chart Type, and simply make
it be a line chart. Double-click.
| | 01:56 | So sometimes you'll mix the charts that way.
| | 01:59 | But it is easy to copy and paste data
onto a chart and sometimes you'll do the
| | 02:04 | reverse too. Change your mind again
here and maybe you didn't want to show the
| | 02:08 | average or one of the series.
| | 02:10 | By the way if you were to take out one
of the series you probably should also
| | 02:13 | take out the average.
| | 02:15 | But let's say you've changed
your mind. It's a bit later.
| | 02:17 | It's too late to do an Undo.
| | 02:18 | You don't want the average.
| | 02:20 | You don't want a series in a chart.
| | 02:21 | Simply click there, and
press Delete, there we go.
| | 02:24 | So you can remove a series as well.
| | 02:27 | So a quick easy way is to copy-
paste data into a chart and occasionally
| | 02:32 | delete data from a chart.
| | Collapse this transcript |
| Creating charts from multiple data sources | 00:00 | You can certainly base a chart on data
from nonadjacent ranges on the same worksheet.
| | 00:05 | But you can also get source data from
different worksheets and even different
| | 00:10 | workbooks to create a chart.
| | 00:12 | Let's create a chart for example on this
worksheet that's related to California sales.
| | 00:18 | But we've also got an Arizona
sheet here. Same layout and everything.
| | 00:22 | That's not a prerequisite by the way.
| | 00:24 | And also an Oregon sheet.
| | 00:26 | Let's create a chart.
| | 00:27 | Now the idea might be let's show sales
from all three states here on a single chart.
| | 00:32 | And arbitrarily we'll just start
with California here and create the chart
| | 00:37 | from here simply by highlighting this
data here. Press Alt+F1 to get the default
| | 00:42 | Clustered column chart and the legend
for the moment isn't very helpful but
| | 00:47 | we'll leave it there.
| | 00:48 | Let's now go get data from Arizona.
| | 00:51 | So I'm going to click on Arizona
here and highlight this data here.
| | 00:55 | Press Ctrl+C or right-click, Copy,
any of the different copy methods
| | 00:59 | you're familiar with.
| | 01:00 | Then come back to California and right-
click on the chart area and paste, there we go.
| | 01:06 | Notice that legend here isn't
shaping up so smoothly, is it?
| | 01:10 | Let's go to Oregon here too and pickup
a similar range of data right there and
| | 01:17 | we'll Ctrl+C again, come back to
California, click on the chart area here,
| | 01:20 | press Ctrl+V to paste.
| | 01:22 | So we do have the data in place but
the labeling isn't correct and we really
| | 01:26 | wouldn't stand for that.
| | 01:27 | Now one option of course would be to
create your own text entries and you
| | 01:31 | could go to the Layout tab and create an
axis title. You just type-in whatever you want.
| | 01:35 | In other words create your legend on
the fly but a better way would be to
| | 01:39 | actually make this data be related properly.
| | 01:42 | So with the chart selected, let's go
to the Design tab and choose Select Data.
| | 01:49 | And here we see the words Sales three times.
| | 01:52 | What we're trying to get at is the
idea that we want the three sales here to
| | 01:56 | turn up in our legend as
California, Arizona and Oregon.
| | 02:00 | So for the very first sales entry
we'll click Edit and you see what it's
| | 02:04 | referring to, California Cell A4.
| | 02:08 | We want it simply to say California.
| | 02:10 | So let's just take this portion of the
address and get rid of it and put in a
| | 02:15 | double quote on the right-side and
similarly on the left-side a double quote.
| | 02:20 | So that says ="California".
| | 02:24 | Click OK and you see what's happened
to the legend. And of course we'll do
| | 02:27 | this two more times.
| | 02:28 | Second one, edit sales, and of course
here we're going to put in Arizona.
| | 02:33 | It's already there. Just put in the
double quotes, get rid of the other stuff
| | 02:38 | that's there and OK, and of
course one more time here with Oregon.
| | 02:42 | So it does take a little bit of work
to get here for the labels but in other
| | 02:45 | respects the copying and pasting is
pretty straightforward and relatively easy
| | 02:49 | to do, so there we go.
| | 02:52 | Of course as you would expect, this
chart is reflective of changes made on the
| | 02:57 | different sheets and so even though
this is on the California sheet, if we went
| | 03:01 | to Oregon here and for the very first
entry for sales maybe that was a mistake,
| | 03:05 | instead of 55 if it's 100, we'll make a
change there and even though we're not
| | 03:10 | looking at it at the moment, at some
point go back to the California sheet where
| | 03:14 | the chart resides and the Oregon value
here, as we see here, it's 100 not the 55
| | 03:19 | that it had been. And
probably no big surprise of that.
| | 03:22 | So you certainly can get data not only from
other worksheets but also from other workbooks.
| | 03:28 | A little bit of adjustment
there on the legend but otherwise
| | 03:31 | fairly straightforward.
| | Collapse this transcript |
| Adding new data using a table| 00:00 | If you want a chart to reflect
frequent additions of new information to the
| | 00:04 | source data and you don't want to
manually re-adjust the chart settings
| | 00:08 | every single time you add new data,
you could convert the source data into
| | 00:13 | what we call a table.
| | 00:14 | A table is a new feature introduced
in Excel 2007, also available in 2010.
| | 00:20 | It's reminiscent of an older
concept called a list in Excel.
| | 00:24 | Now this line chart, which just is
easily could have been a column chart,
| | 00:28 | reflects the data we see to the left.
| | 00:31 | But we might want to continue this
chart into the next year, the year after
| | 00:35 | that even possibly.
| | 00:37 | So what do we do here?
| | 00:38 | We get some new data.
| | 00:39 | The January 2011 data has been recorded
to us. We can just highlight these two
| | 00:43 | cells and drag downward. That makes it a
little bit faster and we'll put in the
| | 00:47 | January 2011 number, which happens to be
210, press Enter, and of course nothing
| | 00:52 | is happening to the chart, because we
haven't indicated in any way that the
| | 00:56 | source of the chart data has to be expanded.
| | 00:59 | Now if you are doing this once a month
maybe just continue to do this manually.
| | 01:03 | What do we do here? Simply drag the
lower right-hand corner and as soon as we
| | 01:07 | let go the chart has been
expanded. So not so bad.
| | 01:11 | But you might have a situation where
you were updating daily, you have to
| | 01:14 | do this everyday, and I've seen
charts like this that pick up hourly
| | 01:18 | information every so often.
| | 01:20 | So that could be a faster way to handle this.
| | 01:22 | One way to do this is to take the
original set of data here and convert it into
| | 01:26 | what we call a table.
| | 01:28 | There are four ways to do this.
| | 01:30 | By a way of the menu system you can
go to the Insert tab and choose Table.
| | 01:35 | If you're on the Home tab you can
choose Format as Table or two keystrokes shortcuts.
| | 01:40 | Ctrl+T, T for Table. Ctrl+L,
remember the old list, possibly that's
| | 01:45 | the way to remember it.
| | 01:46 | I want to choose this option this
option here Format as Table and it first
| | 01:50 | gives us all these different
formatting choices. I'll just pick one here and
| | 01:54 | then it asks us to confirm the
contiguous range and this is nearly always
| | 01:58 | correct. Just click OK.
| | 02:00 | Now in addition to giving us some
formatting capabilities here what we also have
| | 02:05 | is this ability whereby if we add new
information here, so I'm just going to
| | 02:09 | type 2/1/11, as soon as I tab or move
into another cell and watch the chart
| | 02:14 | change, we see February represented there.
| | 02:17 | Of course no data yet so we'll plug in
the data here. Maybe it's 250 and as soon
| | 02:21 | as we type that of
course we see it in the chart.
| | 02:24 | And maybe for the next month's and you
can do it anywhere, if you want.
| | 02:27 | Next month's number is 300, and then over
here what happened here is a change the
| | 02:31 | scaling of it. And again you can do this
a couple of different ways. Type it in,
| | 02:34 | drag it down this way. You see what's happening.
| | 02:37 | That has some merit to it.
| | 02:39 | Another approach to these kinds of
situations could be what if we just had data
| | 02:43 | like this here all set up before we
even had recorded any sales information.
| | 02:47 | Imagine it's back in January of 2010.
You're anticipating keeping a chart here
| | 02:52 | for two, maybe three years.
| | 02:54 | We could just start here. I'm going
to press Alt+F1 to start the chart.
| | 02:58 | It looks a little funny because we
don't have any data, so ignore that for the
| | 03:02 | moment, but suppose we click here in
cell P2. I'm just going to type in the
| | 03:07 | number there, 82, and you see
what's happening to the chart.
| | 03:11 | So if you set it up this way, you don't
have to work with the table or work with
| | 03:14 | that concept, not that that's bad, but
you just set up the months this way ahead
| | 03:18 | of time and then the next number
comes in, so we'll type in 125, and so on.
| | 03:23 | But notice here that when I do this it
will change the scaling of the chart, and
| | 03:27 | you would expect that. What I did see
in one situation once is that someone
| | 03:31 | every month wanted to paste the latest
chart up on the wall, but realized after
| | 03:35 | a few months that the charts weren't
similar because the scaling kept changing
| | 03:39 | with larger numbers.
| | 03:41 | But the table concept
might have some merit for you.
| | 03:44 | It's easy, it's fast, you get some
other formatting features with it, and it
| | 03:48 | does simplify automatic
updating of certain kinds of charts.
| | Collapse this transcript |
|
|
12. Printing and Sharing ChartsPrinting charts | 00:00 | As you work with charts there certainly
will be times when you want to print a chart.
| | 00:04 | Sometimes charts are
located on a worksheet with data.
| | 00:07 | That's increasingly more
common these days, it seems like.
| | 00:10 | Sometimes you have a chart
on it's own separate sheet.
| | 00:13 | In either case you might want to print.
| | 00:15 | A quick reminder here, when you do have
a chart on a worksheet and would prefer
| | 00:19 | to have it on a separate sheet,
remember all you have to got to do is to
| | 00:22 | right-click in the chart area and move
the chart, if that's what you want to do,
| | 00:26 | and put it on a new sheet.
| | 00:28 | Click here. It will disappear from this
worksheet, the blue chart up above here. Click OK.
| | 00:34 | It's going to be on a new sheet
and sometimes you want to do that.
| | 00:38 | And the reverse is true, just as quickly and
easily. I want this chart to be on the worksheet.
| | 00:43 | Right-click in the chart
area, choose Move Chart.
| | 00:46 | I don't necessarily have to put it on
the same sheet that it came from or put
| | 00:50 | it on the sheet that's already
got charts. Put it where you want.
| | 00:52 | Okay, I'll put it here.
| | 00:55 | And there it is. Move it around and so on.
| | 00:57 | But the issue of printing comes up.
| | 00:59 | Let's deal with a chart that's on
a separate sheet. Chart one here,
| | 01:03 | a separate sheet, has a chart on it.
| | 01:06 | Remember the advantage of working with
a chart in its own sheet is you've got
| | 01:10 | only it to focus on. There is no data nearby.
| | 01:13 | You can worry about all the details,
the colors, the designs that you sometimes
| | 01:16 | want to focus on, without dealing
with other aspects of the worksheet.
| | 01:21 | Now a couple of quick ways for getting a
print preview, which I do recommend and
| | 01:25 | always have recommended
anytime you want to print.
| | 01:28 | Your Quick Access Toolbar on the upper
left corner usually, or it could be below
| | 01:32 | the Ribbon, I recommend strongly
right-clicking on this at least once and
| | 01:37 | customizing it and add to
it a Print Preview button.
| | 01:41 | When you customize the Quick Access
Toolbar under Popular Commands you will
| | 01:46 | see a choice here, and you'd only do this once
by the way, Print Preview and Print. Click Add.
| | 01:51 | We're adding this button
to the Quick Access Toolbar.
| | 01:55 | Once we do this, and provided our next
exit from Excel is a normal one,
| | 02:00 | this button is here all of the time.
| | 02:01 | So when you're working with data like
this, why not click that button? And there
| | 02:05 | is our Print Preview and we have only
the chart to deal with here, because we're
| | 02:10 | on a chart sheet. And maybe it looks pretty good.
| | 02:12 | Maybe not, but you can change the
orientation for certain kinds of charts.
| | 02:16 | Unlikely when the chart is on its own
sheet, but you might want to explore that
| | 02:19 | a little bit and you can go
down this path to adjust margins.
| | 02:23 | Pick Normal, Wide, Narrow, or Custom Margins.
A real time waster here if you are not careful.
| | 02:28 | So if it looks pretty reasonable, you're
ready to print, click Print and you're all done.
| | 02:33 | To get out of the Print Preview, maybe
you saw something in the preview that
| | 02:37 | caused you to say well, I am going
to make a change on the design,
| | 02:40 | just press Escape and you would be
right back onto the chart itself.
| | 02:44 | Now when you're looking at a
worksheet that contains charts, sometimes the
| | 02:49 | thought is well, I want to
print all the charts and the data.
| | 02:52 | At other times maybe well the data and
some of the charts or maybe even just a chart.
| | 02:57 | If you want to get a quick Print
Preview when working with a worksheet, simply
| | 03:02 | press Ctrl+F2 and we're in
this Print Preview mode here.
| | 03:06 | And so what we see here, it doesn't
always read so clearly and whenever you do
| | 03:10 | get a Print Preview off of a worksheet
always look at the bottom of the screen
| | 03:14 | first that says Page 1 of 2, indicating
we've probably got a little bit of work
| | 03:19 | here to do in order to make this sensible.
| | 03:21 | Do we want to include more rows, more
columns, should we shrink these charts and so on?
| | 03:25 | Do we have other options available here?
| | 03:28 | And you see lots of choices over here as well.
| | 03:30 | So let's press Escape here.
| | 03:33 | Let's imagine that we want to print the
data over in columns A through I and the
| | 03:39 | chart to the right, the chart below it.
| | 03:41 | We want to do some resizing.
| | 03:43 | Quick reminder on resizing. Typically
if you drag the chart from the corner you
| | 03:47 | can take care of two edges at once.
That's fine, looking something like that
| | 03:50 | maybe. Adjust this one.
| | 03:52 | To make this a little neater and tidier,
some people like to hold down the Alt key.
| | 03:56 | And when you do hold down the Alt
key and drag an edge, it only lets the
| | 04:00 | edges of the chart line up
perfectly with cell boundaries.
| | 04:04 | Sometimes you don't see that so
clearly until you click outside of it.
| | 04:06 | So on this blue chart here I'm going
to drag the lower right-hand corner, but
| | 04:10 | hold down the Alt key if I want it to
be just perfect that way, and then the
| | 04:14 | upper left corner. And of course you can
do that on all the sides as well if you wish,
| | 04:18 | but if you do two corners it's
going to make it a little bit faster.
| | 04:22 | Maybe we don't want to include
this pie chart here at this time.
| | 04:25 | So take the chart below the
data and do the same kind of thing.
| | 04:28 | And again when you're in a hurry, just
take a little bit of time. You just have
| | 04:31 | to be patient with it, if
this idea is important to you.
| | 04:34 | Again its purpose is simply to let you
quickly line up the edges of the chart
| | 04:39 | with the cell boundaries.
| | 04:41 | And one more here, possibly this one. Drag this.
| | 04:45 | So a quick Ctrl+F2 will get his back here.
| | 04:48 | Click on a worksheet cell, Ctrl+F2, there we go.
| | 04:51 | That still says Page 1 of 2.
| | 04:53 | Now a different approach to this and
it's a reminder that Excel has so many ways
| | 04:58 | to achieve certain objectives and
sometimes it's a little annoying.
| | 05:02 | A different approach to printing
can be from the status bar, a lower
| | 05:06 | right-hand corner, the third button over, Page
break preview. In this dialog box just click OK.
| | 05:14 | Now what's a little bit difficult to see
here is you will see a blue border here.
| | 05:18 | Now it's tough to see in this example.
When you slide the mouse over it, at some
| | 05:21 | point here you will see that and you
might have to do this a little bit.
| | 05:24 | There is that blue border and I think
what's revealing here is that the dotted
| | 05:28 | line that we're seeing right here,
indicating that's going to be on Page 2, so
| | 05:33 | why not drag this rightward?
| | 05:36 | There we go and then we can get
this chart back in shape again.
| | 05:38 | Again, a little bit of tedious dragging
here to get things looking just so, so.
| | 05:44 | A quick Ctrl+F2 or if you have added
that button in your Quick Access Toolbar in
| | 05:49 | the upper left corner, Print Preview.
| | 05:51 | How is that looking?
| | 05:52 | Now it's Page 1 of 1.
| | 05:54 | Some people will see this and say, you
know, that doesn't look so good, because we
| | 05:57 | have a lots of white space here.
| | 05:59 | Well, then you'll just have to press
Escape and go back and start redesigning
| | 06:03 | this shape of the chart. But the main
idea here is you sometimes do want to
| | 06:07 | print data along with certain charts.
| | 06:10 | Now there are other times when you say,
I just want to print this one chart.
| | 06:14 | Maybe you want to print the
green chart that's below the data.
| | 06:17 | Click on that chart.
| | 06:18 | Now Ctrl+F2 won't work here, but the
Print Preview button will and this will
| | 06:23 | show us only the chart, not
any of the data around it.
| | 06:27 | So that's an option as well.
| | 06:28 | So we've got different
approaches to approaching printing.
| | 06:31 | You can print a single chart by itself,
you can print a chart with the data, or
| | 06:36 | if a chart is on its own sheet you
can simply print that chart by itself.
| | Collapse this transcript |
| Copying and linking charts with Word and PowerPoint| 00:00 | There are times when you might want to
copy an Excel chart into a Word document
| | 00:05 | or a PowerPoint presentation. And
sometimes you want to do that as if it was just
| | 00:08 | a static picture, a copy/paste.
| | 00:11 | At other times you might want to
set up what's called a link. In other words,
| | 00:15 | make a copy of a chart, paste
it as a link, and then as the data in
| | 00:20 | the chart gets updated in Excel, you
would like the PowerPoint presentation
| | 00:25 | that shows that chart also to be
updated, and similar in a Word document
| | 00:28 | too if you had pasted it there.
| | 00:30 | So there is a difference between paste
and paste link and there is a difference
| | 00:34 | in Excel 2010 as to how to approach this.
| | 00:37 | Now I do have a PowerPoint
presentation ready and here's the idea.
| | 00:41 | This blue chart here, I want to make a
copy of this simply as a static image.
| | 00:45 | Right-click and Copy.
| | 00:48 | Using Alt+Tab, I am going to jump
into PowerPoint here. I've got my
| | 00:52 | presentations set up and on this sheet
right here I want to paste that chart
| | 00:57 | and here's where the surprise might come in.
| | 00:58 | I am going to right click and Paste.
| | 01:00 | Some kind of a message here about
open dialog boxes and editing mode.
| | 01:05 | It's not going to work in 2010
the way it has in prior versions.
| | 01:08 | Let's go back to Excel here.
| | 01:11 | This time the chart is selected and on
the Home tab in the Ribbon, not just a
| | 01:16 | simple copy, but let's click the drop
arrow, copy this as a picture and in this
| | 01:21 | dialog box simply click OK.
| | 01:24 | We'll jump over to
PowerPoint now and right-click, Paste.
| | 01:28 | This would be pasted as a static image,
but I want to make it larger and do the
| | 01:33 | other kinds of things you might want
to do in PowerPoint to manipulate it.
| | 01:37 | All right, I have got another slide here
and let's by way of contrast jump back
| | 01:41 | into Excel and then come back
here and paste that chart as a link.
| | 01:45 | And we use a different chart this time,
the chart that's below the dat.
| | 01:49 | This time right-click in the chart area
and do a straight copy, then jump over to
| | 01:55 | PowerPoint, and on this slide here,
we'll use the arrow under the Paste
| | 02:00 | button on the Home tab, Paste Special
and then Paste link. It creates a shortcut
| | 02:08 | to the source file.
| | 02:09 | So the changes to the source file
will be reflected in your presentation.
| | 02:14 | So I'll click OK. Different chart, but
it's going to look very similar to the
| | 02:17 | first kind of chart that we pasted.
But this one is a link. Here too, I might
| | 02:21 | want to make it larger.
| | 02:22 | Even though the charts are different in
the way that data is disorganized, they
| | 02:26 | are both showing the same columns. And
notice that the Domestic for January here
| | 02:31 | is 100 and in the previous chart
here Domestic is 100 for January.
| | 02:37 | So let's jump back to Excel and show
the difference when we change the data.
| | 02:42 | Instead of that being 100,
maybe we make an adjustment here.
| | 02:44 | It's 150 and here in Excel of course
as I press Enter, all these various
| | 02:50 | charts here that are linked to this
data are going to change and so there's
| | 02:54 | the 150 right there.
| | 02:56 | The chart to the left which
has been copied, it's 150.
| | 02:58 | So is our line charts and the others
that we have an output in the PowerPoint.
| | 03:02 | So we are going to jump back into
PowerPoint and see what's happened.
| | 03:05 | In this first slide here, it's
actually on slide 2, the change to 150 is not
| | 03:10 | reflected at all, but how about the
next slide? There it has been reflected.
| | 03:16 | In other words this is linked.
| | 03:18 | It is reacting to the fact
that they are changes in Excel.
| | 03:22 | And similarly, what if we close this
presentation or better yet Save and Close?
| | 03:28 | I'll just do a quick Save and then
from the button here, just do a Close.
| | 03:32 | Okay, so it's closed.
| | 03:33 | Meanwhile in Excel our file is still open,
we are making some adjustments to the
| | 03:37 | data, and probably in the real life
situation the data wouldn't change this way,
| | 03:41 | Let's make that January Domestic entry
again different. Make it be 200 right now
| | 03:46 | And as you would expect,
the charts here will change.
| | 03:49 | The PowerPoint file is closed, but
let's go back into PowerPoint, perhaps at a
| | 03:53 | later time and open that file. Do it
from the Office button and just choose the
| | 03:59 | name of it. It's Presentation1,
Update the Links, there we go.
| | 04:02 | So here is that first chart,
this is the one that was the paste.
| | 04:06 | It is not reflecting any change, not
only to the 150, nor to the 200. How about
| | 04:11 | the other chart here?
| | 04:13 | We see the change there.
| | 04:14 | It is reflecting that change of 200.
| | 04:16 | So you see the difference here
between a Paste and a Paste link and
| | 04:20 | similarly in Microsoft Word, similar
kind of setup and so on, and particularly
| | 04:25 | if you are putting together a whole
presentation that might be involving not
| | 04:29 | only Excel files, but also Word and
PowerPoint files, the whole presentation,
| | 04:34 | you might want to have linked charts or
simply charts as pictures, but we have seen
| | 04:39 | the example here both techniques, for
copying and pasting a chart as a static
| | 04:44 | image or copying and pasting as a dynamic link.
| | Collapse this transcript |
|
|
ConclusionNext steps| 00:00 | Upon completion of this course, you
might want to check out some additional
| | 00:03 | Excel 2010 courses at lynda.com.
| | 00:07 | If you're making a leap from Excel 2003
to Excel 2010, be sure to check out the
| | 00:12 | course Migrating from Excel 2003 to Excel 2010.
| | 00:16 | For review all the basics, go
to Excel 2010 Essential Training.
| | 00:20 | If you want to focus on just the new
features of Excel 2010, Excel 2010 New
| | 00:24 | Features course, and you might want to
check out my Excel 2010 Power Shortcuts,
| | 00:29 | suitable for any user in Excel who wants
to improve his or her efficiency and use
| | 00:34 | with this powerful software, and there
are additional courses there as well and
| | 00:38 | check back from time to time
for updates and new courses.
| | 00:41 | Thank you for watching.
| | Collapse this transcript |
|
|
