IntroductionWelcome| 00:04 | Hi! I'm Dennis Taylor, and welcome
to Excel 2007: Charts in Depth.
| | 00:09 | Creating charts is one of Excel's
most powerful yet easy-to-use features.
| | 00:14 | You'll see huge improvements in the
visual quality of charts, and have a vast
| | 00:18 | array of color and design choices not
available in older versions of Excel.
| | 00:23 | In this course, we'll be looking at the
concepts underlying charts and give
| | 00:27 | you the tools to implement those concepts.
| | 00:29 | I'll show you how to create standard,
useful charts very quickly, and then how
| | 00:34 | to fine-tune those charts with
a variety of tools and features.
| | 00:38 | We'll see how to give greater
impact to your charts with pictures and
| | 00:41 | shapes and then examine the most
effective use of titles, labels, legends,
| | 00:46 | axes, and gridlines.
| | 00:48 | We'll look at analytic tools that
help you make sense of the chart's data.
| | 00:52 | If you're not using charts then you're
missing out one of the most popular and
| | 00:56 | easy-to-use features of Excel.
| | 00:58 | So join me in Excel 2007: 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:05 | you're watching this tutorial on a
DVD-ROM, you have access to the exercise
| | 00:10 | files used throughout this title.
| | 00:12 | In opening the files, you'll recognize
that there is one file per chapter--for
| | 00:17 | example, Ch_01_Chart_Concepts.
| | 00:21 | And like many of the files here,
you'll see multiple worksheets, and these are
| | 00:25 | often referred to in the course of the training.
| | 00:28 | Some of them are not referred to.
| | 00:30 | They have charts on them.
Feel free to use them as you wish.
| | 00:33 | If you are a Monthly subscriber or
an Annual subscriber to lynda.com, you
| | 00:38 | don't have access to the exercise files,
but you can follow along from scratch
| | 00:43 | with your own files.
| | 00:44 | Let's get started.
| | Collapse this transcript |
|
|
1. Understanding Excel Chart ConceptsIdentifying chart elements like plot area, chart area, gridlines, and legends| 00:00 | When you work with Excel charts you
will need to become familiar with certain
| | 00:03 | terms that you might not
have seen or heard before.
| | 00:06 | And although it's best not to memorize
the various parts of a chart, knowing the
| | 00:11 | names of what are called chart
elements will increase the value, not only of
| | 00:14 | this course, but any kind of
instruction material related to Excel charts.
| | 00:18 | In this particular worksheet there are
a number of charts associated with the
| | 00:23 | data, different ways to display
the same data by way of a chart.
| | 00:27 | To the right of the data, a green
border chart. I'm going to click on it here,
| | 00:31 | and as I slide the mouse around into
this portion of it, you might see here and
| | 00:36 | there words pop up, like Plot
Area like we're seeing right now.
| | 00:39 | If I slide upward into the green area,
we'll see Chart Area. On all charts
| | 00:44 | we'll see these terms.
| | 00:46 | I'm going to slide the
mouse over the box right here.
| | 00:49 | You see the word Legend.
| | 00:50 | You've probably heard that before,
and certainly you've see these on maps,
| | 00:53 | a legend that explains what
lines mean, what symbols mean.
| | 00:57 | We get use to these terms. And not only
where you see some familiar terms like Legend,
| | 01:02 | here's a line here. What's the line?
| | 01:04 | It's a gridline, and to be more
precise, a Vertical Axis Major Gridline
| | 01:09 | That suggests there might
be Minor Gridline somewhere.
| | 01:12 | So on different charts here and there
we'll see different kinds of chart elements.
| | 01:16 | How about the bottom of the chart here?
| | 01:18 | Let's slide into the bottom of the green chart.
| | 01:20 | That's the Horizontal Axis.
| | 01:22 | Well, sure enough, there's a Vertical
Axis over on the left-hand side as well.
| | 01:26 | And as I've said, don't memorize these,
but the more you work with charts, the
| | 01:30 | more you'll need to know what these are.
| | 01:33 | Another term is going to come up, and
it's a little bit illusive at first but
| | 01:36 | you'll become very familiar with it--
as I click on one the lines in this
| | 01:40 | line-chart--you see the term Series.
| | 01:43 | In an example here it says Latin America.
I should point to Jun there. But if you
| | 01:48 | click the line itself or point to
the line itself after having moved to
| | 01:51 | somewhere else, you'll see it's the
Series Latin America. Or up here, this the
| | 01:57 | series Europe, and off to the
left, you can see the data as well.
| | 02:00 | In fact, I click on it, the data will
highlight too. You've learned to kind to keep an
| | 02:04 | eye on that, and you sort of absorb the
meaning of these terms without really
| | 02:09 | making a studied list of them.
| | 02:11 | There is a pie chart to
the right, a 3D pie chart.
| | 02:14 | Let's click on the pie chart,
and once again we see that word Series.
| | 02:19 | Often a series will refer to a row
of data, as it appears to be in these
| | 02:23 | examples, but there is
another chart off to the left.
| | 02:26 | This is a column chart of a different
nature that's clustering data a little bit
| | 02:30 | differently than you
might see in another charts.
| | 02:32 | I'll click on one of the columns here,
| | 02:34 | slide the mouse over it.
That's June and it's part of a series for Jun.
| | 02:38 | Move away from it. Slide over it again.
| | 02:41 | June. There the series refers to
something vertical, part of a column, and here
| | 02:48 | is a 3D chart here.
| | 02:51 | I'm sliding the mouse over the chart
area. The chart area nearly always refers
| | 02:55 | to the outer perimeter area of a chart.
The inner part that holds the actual image is
| | 03:00 | generally referred to as the
plot area, we see right there.
| | 03:04 | In this 3D chart--I'm pointing to
the left-hand side there--Sidewall.
| | 03:09 | It's a term we have not seen in the other charts.
| | 03:12 | Back here a Back Wall, and you've probably
figured out by now, what do we see at the bottom here?
| | 03:16 | This is called Floor.
| | 03:18 | And there's another chart down here
below the data, a different chart related
| | 03:23 | to different data, and there is a gridline.
Clicking on the chart first before you see these.
| | 03:29 | That's a major gridline and here
are one of those minor gridlines.
| | 03:33 | Another thing you recognize too and it is
important to start picking up on the names,
| | 03:38 | there will be times when you want to
change one of these chart elements,
| | 03:42 | maybe make a line thicker or
change its color, and many times you will
| | 03:47 | right-click on the choice to do that.
| | 03:50 | Sometimes though you will have
difficulty and actually picking or selecting.
| | 03:55 | This chart here at the bottom here that I'm
moving the mouse around has some problems with it.
| | 04:00 | It's not quite ready to be printed.
| | 04:02 | And one of the series here, the
miles per gallon represented by a red, is
| | 04:07 | difficult to click on.
| | 04:08 | So here's something else you'll note.
| | 04:10 | I'm going to click in
this chart on the chart area.
| | 04:13 | In the Ribbon at the top of the screen,
here's a Layout tab, and the left-most
| | 04:18 | portion of this called Current Selection,
| | 04:20 | you see the term Chart Area.
| | 04:22 | That's the same one we saw
when we clicked on the chart.
| | 04:24 | I'm going to click the drop arrow here.
| | 04:27 | Here are all possible elements within
this chart, and if I really need to get to
| | 04:32 | that MPG series, I'll click it
right here, and now it's selected.
| | 04:37 | So right below this it says Format Selection.
| | 04:40 | I could click there and make some changes.
| | 04:42 | So as you use charts, you'll see these
terms all the time, and again, without
| | 04:47 | really making a studied effort to
memorize them all, you'll slowly pick up on
| | 04:51 | them, and it will help you understand
charts, and it'll help immensely as you use
| | 04:56 | the various chart tools when you want to
make changes to them throughout the use
| | 05:00 | of charts in Excel 2007.
| | Collapse this transcript |
| Selecting the right chart type| 00:00 | Excel has quite a few different chart types.
| | 00:03 | Get familiar with that term.
You'll hear that a lot.
| | 00:06 | Certainly the most common for many,
many people are column charts.
| | 00:09 | There is one below the data we see on
this worksheet to the left. A line chart
| | 00:14 | is to the right of it.
| | 00:15 | Not all chart types are equally effective
in showing the data in a meaningful way.
| | 00:20 | Most Excel users agree that a line chart,
for example like the one we're seeing
| | 00:23 | here to the right of the data
is best for showing a trend,
| | 00:27 | although sometimes a column chart
might work equally well for you.
| | 00:31 | Usually column and bar charts are
probably better for depicting volumes
| | 00:34 | or amounts of data.
| | 00:36 | Other chart types might have special
uses that make them effective with certain
| | 00:40 | data collections, but despite the over
70 choices of charts available, I think
| | 00:45 | it's really best to experiment with
different kinds of charts until you get a
| | 00:49 | little more familiar with the
idea and what works best for you.
| | 00:52 | On this worksheet of course, you do
see quite a few different chart types,
| | 00:56 | and most people, with myself included, wouldn't
be using this many different chart types here.
| | 01:01 | The chart to the lower
right here is a 3D column chart.
| | 01:04 | Those have some initial visual appeal,
but they are certainly not the best for
| | 01:08 | actually getting a good read on the data.
| | 01:10 | Even if I zoom in on this a
little bit and scroll around and make
| | 01:14 | that a little more obvious,
| | 01:16 | it's pretty hard, as I point for example,
to this red value, to know what this
| | 01:19 | value is until we actually point
to it and it tells it that it's 100.
| | 01:23 | Would you read that by seeing
the gridlines in the background?
| | 01:27 | The value of a chart like this might
be that it shows the relative difference
| | 01:32 | of the various column heights, and
similarly, other kinds of charts have some
| | 01:36 | visual appeal as well.
| | 01:37 | The pie chart to the right, if we
are trying to show, for example, what's
| | 01:42 | happened in the first six months,
| | 01:44 | we do get a quick read here that
suggests, as we look back and forth with a
| | 01:48 | numbers and the pie there,
| | 01:49 | that April was a substantial chunk.
But is it bigger than June? Can we tell that?
| | 01:53 | Well, you look at the numbers and I
think we're seeing one of the deficiencies
| | 01:57 | of this kind of chart.
| | 01:59 | Now, one by one, as we talked about, pie
charts, column charts, line charts, bar
| | 02:03 | charts, and a lot of the other major types here,
| | 02:06 | I think you can begin to see
some advantages and disadvantages.
| | 02:09 | The more I look at this, the more I say,
well, that purple wedge in front to me
| | 02:13 | still looks bigger than that yellow or
gold wedge, whatever you want to call it,
| | 02:17 | back there that represents June.
| | 02:19 | And yet the purple wedge upfront is 130
and that June value in the back is 180.
| | 02:25 | So here and there, as we look at
different chart types--and again, experiment
| | 02:29 | freely and let's point out right now,
even though perhaps you haven't created
| | 02:33 | too many charts or maybe none at all,
anytime you do click a chart, for example
| | 02:38 | I just clicked this pie chart,
| | 02:40 | you will see in the menu system there
is a Design tab up top. Click that and as
| | 02:46 | you do, the leftmost button amongst
those many choices, Change Chart Type.
| | 02:52 | And here are 73 variations, and they are
in groups: column charts, line charts,
| | 02:58 | pie charts, and quite a
few variations within each.
| | 03:01 | 73 altogether. Nobody is really counting.
But maybe this will look better as a column chart,
| | 03:06 | so I'll just double-click this. That's slightly faster and
we're seeing the data this way now. Is that better?
| | 03:12 | I'm not necessarily going to say it is, but
at least we can see at a glance, we're
| | 03:16 | not seeing the proportion so much, but
we do see clearly that June is a lot
| | 03:20 | bigger than the others.
| | 03:22 | So on the next sheet over is a sheet
called ColumnChart, and here are some
| | 03:26 | variations on column charts.
| | 03:28 | I am going to zoom in a
little bit on this one too.
| | 03:30 | We will in other movies talk more about
the different kinds of column charts and
| | 03:34 | why you might want them.
| | 03:36 | I do want to say that it's a safe choice
because it is the default selection in
| | 03:41 | Excel when you create a chart. If you
don't do anything unusual or different or
| | 03:45 | if you haven't changed the default, you
will get a column chart, and there are
| | 03:50 | number of variations on
column charts, as we see them here.
| | 03:53 | Almost the same as this but rotated 90
degrees is a bar chart, and here is a sheet
| | 03:59 | with some bar charts on them.
| | 04:01 | And sometimes it's just a question of
your own personal preference. One advantage
| | 04:05 | to a bar chart is if the labels are
wider or longer--in this case they're
| | 04:10 | not--but that's one reason
to show this kind of a chart.
| | 04:13 | Here we see the months down the side.
And in a later movie when we talk about
| | 04:17 | bar charts, we pull that feature out
or emphasize that point a little more
| | 04:21 | strongly. But sometimes it's
just a question of personal choice.
| | 04:24 | Does this tell the story properly?
| | 04:26 | Is it making the data clear to people?
| | 04:28 | And that's the main focus, or should be
the main thrust, as we talk about charts.
| | 04:34 | Do you have to spend a lot of time
explaining how to read the chart?
| | 04:37 | If you do, perhaps it's not the best chart.
| | 04:40 | And again, we can change these so quickly.
| | 04:43 | You say maybe I like that, but I
might like it better as a column chart.
| | 04:47 | We'll go right to the Design tab, click
Change Chart Type, pick whichever chart
| | 04:52 | type we might want,
| | 04:53 | one of those sub-choices under Column,
maybe this one. Double-click it and now
| | 04:58 | it's a column chart, and maybe it is better.
| | 05:00 | There is lot of subjectivity in all this too.
| | 05:03 | Sometimes we see charts that we don't see as
often as others, for example an AreaChart.
| | 05:06 | Now this might be just as good as a
line chart, but if we're trying to suggest
| | 05:11 | volume perhaps, maybe this is a
better. Maybe it's a bit more dramatic.
| | 05:16 | Let's change it to a line chart. Compare it.
| | 05:18 | Again, never hesitate to make a change,
because you can easily change it back or
| | 05:23 | change it to another type.
| | 05:24 | Back to the Design type. Change Chart Type.
| | 05:28 | This time we'll make it a line.
| | 05:30 | Whenever you are using lines, my first
choice usually and I recommend it for
| | 05:34 | you, Line with Markers.
| | 05:36 | Let's try this. Double-click.
| | 05:38 | You might want to change the size of
the markers, but there is a different
| | 05:41 | approach to showing this data.
| | 05:43 | The drop lines, by the way,
don't come automatically with charts.
| | 05:46 | They were added in these two cases.
| | 05:48 | So that's another option, too,
which we'll get into it a bit later.
| | 05:51 | So don't worry about changing a chart.
Don't worry about coming across as
| | 05:55 | somewhat indecisive.
| | 05:56 | You want to see the
different ways to display data.
| | 05:58 | You also might encounter a scatter chart.
| | 06:02 | This example here is comparing various
times for a race along with the age of the
| | 06:06 | participants, and you'll see this kind
of chart from time to time as well too.
| | 06:10 | It's called a scatter chart.
| | 06:12 | It's one of the many
different choices we have in Excel.
| | 06:16 | And again without constantly harping on
it, anytime you click on a chart with the
| | 06:20 | Design tab, always take a
look at Change Chart Type.
| | 06:23 | Consider what the chart might look like if
you change it to a different kind of chart.
| | 06:29 | One thing about pie charts
I meant to mention here, too--
| | 06:31 | I'll go back to one of our line charts
here. How would this look as a pie chart?
| | 06:35 | I think almost immediately you can
say that's not going to look so good.
| | 06:37 | Well, you're right.
| | 06:38 | Let's just do it anyway to point
out an aspect of that type as well.
| | 06:43 | Design, top of the screen there left
button, Change Chart Type, and we'll make it
| | 06:49 | a pie. 3D pie may be more exciting.
We'll try that. And I think you can see
| | 06:54 | what's going on there.
| | 06:55 | And even if we put labels on them,
the general rule--and it varies a little
| | 06:59 | bit--but I think on pie charts as soon
as you get over a five or six wedges, or
| | 07:03 | items and data that you're trying to
show, some pie charts are just not going
| | 07:06 | to be very readable.
| | 07:07 | And we can tilt this.
We can do other things with it.
| | 07:10 | We could make it be 2D.
| | 07:11 | It might be a little bit
better there, instead of 3D.
| | 07:14 | Change Chart Type again, left button,
this time a 2D pie, a little better, but
| | 07:20 | still, a lot to be desired.
Not a great choice at all.
| | 07:24 | So as we got some obvious choices here
and there. Not so obvious at different
| | 07:27 | times, and lot of subjectivity, but just
keep in mind the idea that you're trying
| | 07:32 | to depict numerical information in a
very clear visual way, and Excel gives us
| | 07:37 | quite a few starting points, 73
different chart types to start from.
| | 07:41 | If you relatively new, again
stick to column, line, pie, and bar.
| | 07:45 | For most people, those are
likely to be the best choices.
| | 07:49 | The ease with which you can change
a chart type is one of Excel's great
| | 07:52 | features, and not only is it easy to
use, I actually would encourage you to
| | 07:56 | use it frequently, just to get a
different sense of how data can appear more or
| | 08:00 | less effectively.
| | Collapse this transcript |
| Understanding chart terminology| 00:00 | Column and bar charts comprise the
bulk of chart types for many Excel users.
| | 00:05 | Understanding the differences between
the terms clustered, stacked, and 100%
| | 00:10 | stacked helps you understand
how best to use these chart types.
| | 00:14 | The default chart type in Excel, unless
you have changed it, is what's called a
| | 00:18 | clustered column chart.
| | 00:20 | I am not sure if that's a term that
everybody uses there freely, but this chart
| | 00:24 | to the right of the data here on this
worksheet called Column Chart is called a
| | 00:28 | clustered column chart. It is the default.
| | 00:31 | And many, many times it's a good choice.
| | 00:34 | We see clearly what the height of each
column, what it refers to. We have got a
| | 00:38 | legend that refers back to the data.
We can look there if it's on the same sheet
| | 00:42 | as it is here. Easy to read.
But sometimes what we're aiming for is to show the
| | 00:47 | data perhaps not only in a simpler,
cleaner look, but also to get information that
| | 00:53 | shows us the total for each month.
| | 00:55 | Now can you tell for sure, would you bet
the ranch, is April bigger than February?
| | 00:59 | Yeah, I think it is.
| | 01:01 | It looks like it, but how much, and how clear is it?
| | 01:03 | The chart to the left here, below the
data itself, is what's called a stacked
| | 01:08 | column chart and we can see at a
glance that April is bigger than February.
| | 01:12 | It's like April and May are about the same.
We could go back to the data to check that out.
| | 01:17 | But the advantage, certainly one of the
advantages of a stacked chart, as this
| | 01:21 | example here is, we do see
the grand total properly.
| | 01:25 | The downside, for sure, is
reading the specific pieces.
| | 01:29 | Now the blue, the example here,
represents domestic, and we can compare those
| | 01:34 | columns, their height, pretty
readily. We can see the values clearly.
| | 01:38 | But try comparing the greens from
month to month, particularly the months
| | 01:42 | that are not next to each other.
| | 01:43 | Is the January green? That's for Asia entry.
| | 01:46 | Is it bigger than June?
| | 01:47 | Can we tell that clearly?
| | 01:49 | Once we have clicked the chart, we can
hover the mouse over it and see that
| | 01:52 | that's 110 for January, and then
go slide the green over into June.
| | 01:58 | There is 130, but was that so obvious
until we saw the numbers? And over here
| | 02:04 | back on the previous chart, when it's
clustered, we do see that breakout.
| | 02:07 | But at different times you're trying to show
different kinds of information in the chart.
| | 02:12 | And a third type of chart--and by
the way, you will see these not only for
| | 02:17 | column, but you'll also see them for
line charts and bar charts, and also
| | 02:21 | area charts, the term clustered again
for the basic type, stacked over here to
| | 02:26 | the left and a third type called 100% stacked.
| | 02:30 | I myself don't use this very often.
| | 02:32 | I don't see them being used a whole lot,
but they certainly do have their place,
| | 02:36 | and in many situations they take
the place of multiple pie charts.
| | 02:41 | So as you probably could glean from
the data here--and let me make this a
| | 02:44 | little clear by actually dragging this chart
upward, so that we can see it next to the data.
| | 02:50 | So for the moment, it's overlapping
another chart, and now we have got our data
| | 02:54 | off to the left here.
| | 02:55 | So for January, just a glance here,
| | 02:58 | you can see, based on the colors, that
Asia has the biggest percent of choice, and
| | 03:02 | we don't necessarily know that number
till we point to it and what happens on
| | 03:07 | the chart itself when we point to the green.
| | 03:09 | It gives us the value but not the percent.
| | 03:12 | In this case, we actually have
to look at the chart itself.
| | 03:15 | Now, did I misspeak there or what?
| | 03:17 | Can we see that percent?
| | 03:19 | It's not in the data, so we don't
even know what the percentage is.
| | 03:22 | But at least we get some idea of the breakout.
| | 03:25 | So all the January data, which totals 290,
this is the breakout percentage-wise.
| | 03:31 | The February total is larger.
| | 03:33 | It's 390 and so the breakout is by percent.
| | 03:37 | So it's not correct to compare, for
example, these two green bars here and say
| | 03:43 | automatically that the January entry is bigger.
| | 03:45 | This is 110 for January, right there, and then
to the right of it, this is February. That's 120.
| | 03:52 | So I think you can see the downside of
this, and yet at the same time, for any
| | 03:56 | given month by itself, a
stacked 100% chart makes sense.
| | 04:01 | So when you are changing chart types, in
other words when you selected a chart
| | 04:06 | and you go to the design tab in that
left button for changing chart types,
| | 04:11 | you will see, for example, under
Column, first choice is called Clustered,
| | 04:16 | second one is called Stacked, third one
is called the 100%. Under Line, we see a
| | 04:22 | line chart then we see what, Stacked
line, and then we see 100% Stacked line.
| | 04:28 | And with bar chart, as you might
imagine, it's very similar to column.
| | 04:32 | I'm going to go to clustered and stacked,
and 100%. And similarly with
| | 04:39 | area: Area chart, Stacked Area, 100% Stacked Area.
| | 04:46 | Another type of chart that you'll see
from time to time is 3D, and you recognize
| | 04:50 | a slight difference here.
| | 04:52 | There is something called a 3D clustered column.
| | 04:55 | I'll double-click to make this change
right now, and you'll see that look.
| | 05:00 | And there is another choice under Design >
| | 05:04 | Chart Type called simply 3D
Column. Watch the difference here.
| | 05:10 | And I think this is the one that has
for some people some initial visual appeal
| | 05:14 | because it's kind of dramatic, and I'm
going to press Ctrl+Z to undo, so we can
| | 05:17 | see the previous one and then Ctrl+Y to
go back and forth here a little bit, but
| | 05:22 | you can again begin to see why one of
these is better than the other for most
| | 05:26 | people, but not necessarily everybody.
| | 05:29 | Back and forth here a little bit.
| | 05:30 | I'd stay away from, not this 3D
chart, but the other 3D chart. I think from
| | 05:34 | time to time that's the one
that's not as clear as it could be.
| | 05:38 | But do recognize the standard terminology
here that we see in different chart types.
| | 05:43 | If we do switch this to a bar chart,
you would think that we might have two
| | 05:46 | corresponding similarities there when
it comes to 3D, but there is actually
| | 05:51 | only one that makes sense here.
| | 05:53 | On bar chart you will see, for
example, 3D Clustered, but you won't see
| | 05:57 | so-called plain 3D bar chart.
So we see this one. That's the only 3D
| | 06:03 | variation for bar charts.
| | 06:05 | So without letting of your knowledge of
all these different chart types become
| | 06:09 | too cumbersome here,
| | 06:10 | just do recognize the terms
Clustered, Stacked, 100% Stacked,
| | 06:15 | you'll see them from time to time when
using column and bar charts--less so with
| | 06:20 | the line charts and area charts,
but you'll see them there as well.
| | 06:24 | So gaining a clear understanding of
these terms really helps to master the many
| | 06:27 | uses, particularly of
column and bar charts, in Excel.
| | Collapse this transcript |
| Understanding the Ribbon and the Design, Layout, and Format tabs| 00:00 | One of the problems that some people
have in working with Excel charts is the
| | 00:04 | tremendous wealth of
possibilities for altering charts.
| | 00:08 | There are so many options, and nobody
really counts, but it's in the millions.
| | 00:12 | And one of the ways that you will begin
to understand charts a bit better, even
| | 00:17 | before you created them, suppose
you're looking at a worksheet like the one
| | 00:20 | here called chart data.
| | 00:21 | When you click on a chart the Ribbon
menu system at the top of the screen,
| | 00:26 | brand-new in Excel 2007,
changes in a significant way.
| | 00:30 | As I click this middle chart on the
screen here, the one just to the right of
| | 00:34 | the data, keep your eye on the Ribbon
up above and what happens? We see--and the
| | 00:40 | terminology here, by the way, will
vary if you're looking this Excel book or
| | 00:44 | that book. It does vary somewhat.
| | 00:45 | A lot of books will say there is
now a new Ribbon called Chart tools.
| | 00:50 | I think it's little more accurate to
say there are three new tabs on the
| | 00:54 | existing Ribbon: Design, Layout, and Format.
| | 00:59 | And in this course, we'll be using
many, many different features from each
| | 01:03 | of those tabs, and it can be a bit
overwhelming at first. Think of it sort
| | 01:08 | of in this way, though.
| | 01:09 | The most important choices related to
charts that you'll see are on the Design
| | 01:14 | tab, and as I click this, all the
choices that we see here, particularly the one
| | 01:19 | on the left for changing chart type, and
as we'll see some of the others here, are
| | 01:23 | very important in the way that you use charts.
| | 01:27 | Once you click Layout, you'll begin
to see, this is where a lot of the fine-
| | 01:31 | tuning is going to be done
| | 01:33 | when you need those features. And the
Format tab, perhaps less important most of
| | 01:38 | the times in Layout, has a lot more to
do with the actual visuals, the colors.
| | 01:43 | And again, we're not going to go
through every single option here.
| | 01:46 | But every time you click on a chart,
you will see the term Chart Tools above
| | 01:51 | the Ribbon and then these three tabs:
Design, Layout, and Format. And the very
| | 01:57 | structure of this course focuses on
these three tabs, in that order, too.
| | 02:03 | And anytime you're creating charts,
keep in mind, you will see these. If I
| | 02:07 | click outside of a chart, back on the
worksheet, watch the Ribbon change into
| | 02:11 | its more typical look, where we have the Home,
Insert Page, Layout tabs that we normally see.
| | 02:17 | Click on the chart, we see the other three.
| | 02:19 | So by recognizing the major kinds of
grouping that occur on the Design, Layout,
| | 02:24 | and Format tabs, this is going to
simplify and give some sense of order to the
| | 02:28 | way that you handle charts.
| | Collapse this transcript |
|
|
2. Creating Basic Charts QuicklySelecting data to display as a chart| 00:00 | Many Excel users are surprised, after
creating their first chart, how easy it was,
| | 00:05 | and if you haven't created a chart
before, I think you're going to be surprised.
| | 00:08 | There are some great shortcuts.
| | 00:10 | There are some easy ways to do this.
| | 00:12 | But no matter how create a chart,
the first step is always thinking about
| | 00:16 | which data it is you'd like to see
portrayed in the chart and then actually
| | 00:21 | selecting that data.
| | 00:23 | As we look at the two sets of data
here on this worksheet called Chart Data,
| | 00:27 | actually it's the same
data, just aligned differently.
| | 00:30 | Let's focus on the top
portion rows 4 through 11.
| | 00:34 | I've highlighted in green, not
necessarily the required area, but let's say the
| | 00:39 | area that we'd be most interested in,
and right away you might be thinking, "Well,
| | 00:42 | that's a mix of numbers and text.
How is this all going to work."
| | 00:46 | You certainly can create a chart by
first selecting any data you want. But if I
| | 00:50 | were to select just this data here,
and right away you might be thinking, "Well,
| | 00:55 | why didn't I choose the
Total? How about the Percent?"
| | 00:58 | Let's just say that many, many times, in
most charts, mixing the actual detailed
| | 01:03 | data with summary data doesn't work so well.
| | 01:06 | We'll show you that in a bit
as to why it's not so great.
| | 01:09 | But suppose you were thinking about
well I guess Excel is all about, you know,
| | 01:12 | showing numbers and I've been told
that, so let's highlight these numbers.
| | 01:16 | But nearly every time you work with numbers,
you typically have a row on top of the data.
| | 01:22 | You usually have a column on the
left-hand side that goes with that data.
| | 01:26 | Select it all together.
| | 01:28 | This will simplify your use of creating charts.
| | 01:30 | It's going to be a lot simpler if you do that.
| | 01:33 | And as a general rule, as I suggested, leave
off totals along with the detail.
| | 01:36 | Don't do them together.
| | 01:38 | Many, many times highlighting the
data, just like this, is the first step.
| | 01:42 | Now on the next sheet over
called Line-Chart, if we wanted to create a
| | 01:48 | line chart, maybe something like this
one or maybe working with the data over
| | 01:52 | on the left-hand side, we might just
simply click and drag across these two columns.
| | 01:57 | In the example here, the data
goes down to about row 30 or so.
| | 02:01 | If we wanted all that data to appear in
the chart, along with the headings, we might as
| | 02:05 | well drag across the
columns themselves, so either way.
| | 02:08 | Now going back to that first sheet
again. Suppose, for example, you wanted to
| | 02:12 | create a chart for presentation and said,
"Well, in this particular example, we do
| | 02:17 | want to show the sales for Domestic and
Europe and Latin America but not in Asia."
| | 02:22 | Sometimes what you will do is
highlight non-contiguous data,
| | 02:26 | in other words highlight a portion of
the data that you want and then let go
| | 02:30 | over the mouse, use the Ctrl key, and
highlight another portion. And although the
| | 02:34 | highlighting for the moment isn't quite
as clear as we might want it to be, I've
| | 02:38 | selected data in rows four, five,
and six, only columns A through G, and then in
| | 02:44 | row eight, column A through G, but not row seven.
| | 02:48 | Now, we are going to get into the
mechanics of creating a chart, but let's simply
| | 02:51 | create a chart here quickly to show
what will happen. And as you would expect
| | 02:55 | based on what I've said, we
will not get Asia in this mix.
| | 02:59 | So the standard way to create a chart
from the Ribbon at the top of the screen,
| | 03:02 | click Insert, and then we'll just click
Column first time around and pick the
| | 03:08 | first choice, and there we are.
| | 03:11 | And you'll notice in the example here
that Asia is not selected, and so you do
| | 03:16 | have a choice when you're selecting data.
| | 03:17 | I think almost 98% of the time,
when you select data, it's going to be
| | 03:21 | contiguous. In other words highlight
all of this, and sure enough, if we were
| | 03:25 | able to go to the Insert tab now and
choose chart, Column again like we did the
| | 03:29 | first time around, we will get a chart
that does include Asia this time around.
| | 03:35 | Now with certain clusters of data, for
example, on the next sheet over called
| | 03:39 | Line-Chart, if you simply put the
mouse within data and then create a chart,
| | 03:45 | Excel automatically picks
up the surrounding data.
| | 03:48 | Sometimes you'll hear the term 'current region'.
| | 03:51 | So without highlighting the data here
and clicking on only one cell in this
| | 03:56 | range here and then going to Insert
and choose Column again, click this, and
| | 04:03 | there we see all the data represent
this way. And again you can tell by the
| | 04:07 | highlighting, we haven't explained that
just yet, but you see the way the data
| | 04:11 | to the left is selected automatically?
| | 04:13 | As I scroll downward, it
only goes down to the extent of the data.
| | 04:17 | So sometimes you're in a hurry and you'll
forget to select the data you want, and
| | 04:21 | Excel might do a good job of
selecting everything that you intended anyway.
| | 04:25 | Now going back to that first sheet again,
if I were let's say a little careless
| | 04:30 | or in a hurry and wasn't paying
attention here, if I simply click in this data
| | 04:35 | here and create a chart, what will
Excel do? It'll attempt to use all of this
| | 04:39 | contiguous data right here.
| | 04:41 | So this is not going to be a great
choice this time. Of course we can easily get
| | 04:45 | rid of this, but I'll just simply
create a chart this way, as we've been doing,
| | 04:49 | and we see what's happening there.
| | 04:51 | And I think you can see clearly that
the percentages just don't work at all on
| | 04:55 | this kind of a chart along with the
other data, and the totals don't either, and
| | 04:59 | that's why earlier I had suggested
keep totals and details out of each other's
| | 05:03 | way so to speak--in other words,
don't put them in the same kind of chart.
| | 05:07 | Now you could easily imagine someone
saying, "Well I do want to see a chart
| | 05:11 | showing just the totals, but I do want
the labels." And so if I were to highlight
| | 05:18 | just the data in column A--and notice
that I'm picking up cell A4 here as I do
| | 05:22 | this--as well as the data in column H,
so I'll let go the mouse, hold down the
| | 05:27 | Ctrl key, highlight these cells here.
| | 05:31 | So I've got the five cells
highlighted over in column A and the five cells
| | 05:35 | highlighted over in column H, and this
would be perhaps a good candidate for a
| | 05:39 | pie chart. We'll go to
Insert > Pie > 2-D pie this time.
| | 05:45 | There we go, and there is our chart
| | 05:47 | showing the percentage breakout.
Obviously, it needs labels and some other
| | 05:50 | things, but it does suggest we are
selecting noncontiguous areas, column A to
| | 05:56 | pick up the actual text data for the
chart and column H for the actual numbers
| | 06:00 | that are being represented
by the pieces of the pie.
| | 06:03 | So by specifically highlighting cells
or using cells in what Excel sometimes
| | 06:07 | calls the current regions, just by
clicking in one of those, or by selecting
| | 06:12 | noncontiguous cells, you can quickly
start the whole chart-creation process.
| | 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:05 | keystroke shortcuts, one of them new in
Excel 2007, that let you create charts instantly.
| | 00:10 | I sometimes refer to these as
show-off kinds of features because when you
| | 00:14 | are demonstrating, or showing to
others, how easy it is to create a chart,
| | 00:18 | sometimes people are amazed.
| | 00:20 | Let's imagine on the set of data that
we're looking at here I'm going to be
| | 00:23 | highlighting this data
right here. I'm in a hurry.
| | 00:26 | If you want to create a chart and have
it on a separate worksheet from the data--
| | 00:31 | and sometimes you might want to. I
think its more common these days and more
| | 00:36 | likely to want to put the chart on the
same worksheet, but if you would like to
| | 00:40 | put this on a separate
worksheet, simply press the F11 key.
| | 00:44 | Now you notice at the bottom of
this screen we are currently looking at a
| | 00:47 | sheet called ChartData. I have highlighted
the date in question from cells A4 to G8.
| | 00:54 | I'm going to press the F11 key, and
here's the chart on a new sheet, and you'll
| | 00:59 | notice at the bottom of the screen
it's called Chart1. It's to the left of the
| | 01:03 | data. And if we go back to the original
chart datasheet and create another one,
| | 01:07 | it'll be called Chart2, and it'll be
to the left to the data, and so on.
| | 01:12 | One advantage of working with charts on
there own sheet is you're not looking at
| | 01:16 | the data. You might have a ton of it and
the chart might be pretty busy. You're seeing it
| | 01:21 | full screen. You can print it easily,
and you can focus strictly on the chart
| | 01:26 | itself as you work with it.
| | 01:28 | So that's one advantage to
working with data on the separate sheet.
| | 01:31 | The F11 shortcut has been around for
quite some time. But a new shortcut in
| | 01:35 | Excel 2007, as we go back to the
ChartData sheet, is, after having your data
| | 01:41 | highlighted, if you would like it chart
right on the worksheet next to the data,
| | 01:46 | it's simply Alt+F1.
There we go. It's right there.
| | 01:51 | Now something to point out here
that will at times be a little confusing.
| | 01:55 | The data here came up
clustered in a certain way here.
| | 01:59 | Now what if I were to
highlight just the first three months?
| | 02:03 | You could imagine, for example, the
chart to the right not showing April, May,
| | 02:07 | and June, but looking pretty much the same.
| | 02:09 | Let me move this down and over a little bit,
shrink it a little bit from the corner.
| | 02:14 | So we're looking at the data here.
I'm going to highlight just this data right
| | 02:18 | here, and I want to create a new chart
right here on the worksheet, Alt+F1.
| | 02:23 | And notice the chart is oriented quite a bit
differently, and you're saying, "What's going on there?
| | 02:28 | Isn't that strange?
| | 02:29 | It's not grouping these by month any anymore."
| | 02:32 | Based on the number of columns and rows
in the data that you select, Excel makes
| | 02:37 | an arbitrary choice, but in the second
example here, we had more rows, we had four
| | 02:42 | rows of data, than we had
columns, three columns of data.
| | 02:46 | First time around when we highlighted this
data, we had four rows of data and six columns.
| | 02:52 | So depending upon what's more, rows or
columns, you can get the different chart
| | 02:56 | type. So what's going to
happen if we choose April?
| | 02:59 | Now this is an unlikely combination, but
you could choose this. I'm going to press
| | 03:02 | Alt+F1, and you see what's happing there.
| | 03:04 | That's more like the first
chart, which is to our lower left.
| | 03:08 | In this case, it's a tie,
so Excel goes one way or the other.
| | 03:12 | So you will find that confusing at
times, and one of the buttons that we'll be
| | 03:16 | using at different times is anytime
you do see a chart that's on the Design
| | 03:21 | tab in the Ribbon, Switch Row/Column,
and then you'll be this way, so you'll sense
| | 03:26 | that from time to time.
| | 03:28 | But getting back to the shortcuts.
Anytime you want to show data, for example a
| | 03:32 | different sheet here like on the
Line Chart sheet, here since our data is
| | 03:36 | contiguous and everything in column A
and B is something that we might want to
| | 03:40 | show in the chart, we get everything there.
| | 03:43 | When you all ready to create a chart,
remember you don't highlight all the data
| | 03:47 | although that's not wrong. Simply
click on a single cell, and that means don't
| | 03:51 | click on two cells, just a single cell
within this cluster of cells here, and
| | 03:56 | we'll simply press Alt+F1 again and
we'll get the default type, which is a column
| | 04:01 | chart, and we see the data
that way. So, fast and easy.
| | 04:04 | And there will be times when you are
not really trying to create a chart long
| | 04:08 | term, you just want to see a quick
visual representation of the numbers, and it
| | 04:13 | gives you a quick read on the data and
maybe you will get to the chart later.
| | 04:16 | So charting sometimes is used to
just an exploratory way, and these great
| | 04:20 | shortcuts, F11, for creating a charts
instantly on the new sheet or to create a
| | 04:25 | chart on the current worksheet, Alt+F1--
two great shortcuts. I think you will use them a
| | 04:30 | lot. Instant creation of charts.
| | Collapse this transcript |
| Creating charts with standard menu commands| 00:00 | There is no denying how quick and easy
the keystroke shortcuts are for creating
| | 00:04 | charts instantly in Excel.
| | 00:06 | But the standard way to create a chart
using the Ribbon menu system requires
| | 00:10 | just a few mouse clicks, and it does
give us the added feature of being able to
| | 00:13 | select the appropriate chart
type as we create the chart.
| | 00:17 | So once again we're looking at the data here.
| | 00:19 | Perhaps we're going to be
highlighting this data here.
| | 00:21 | This is what we want to see in the chart.
| | 00:23 | The standard way to create a chart in the
Ribbon menu system, click Insert and you
| | 00:29 | will see the group called Charts.
And we may decide that this is going to look
| | 00:34 | better as a bar chart or line chart.
And if you're not seeing the chart type
| | 00:38 | that you're most interested in here,
you will see a choice called Other Charts.
| | 00:42 | And as we click the arrow here, we do see
some other types that are not represented there.
| | 00:47 | And the other types are certainly
unusual for a lot of us, and it depends upon
| | 00:51 | how you have worked with charts, or what
you're interested in doing, but these are
| | 00:54 | the more obscure type, you might
say, that have their specialized uses.
| | 00:58 | There is also a button at the
bottom All Chart Types. This shows us all,
| | 01:02 | ultimately 73, basic types. There they are.
| | 01:07 | And many, many times when you come to
this step you will not have clicked other
| | 01:10 | charts, because you've
already determined ahead of time,
| | 01:13 | well, this is going to be a bar chart.
And so, for example, if that's the type, we'll
| | 01:16 | click Bar right here, make our choice.
Maybe we want to use a clustered bar chart.
| | 01:22 | We'll just click here, and
there we have our chart.
| | 01:24 | So that's certainly not very
lengthy. It's easy to use.
| | 01:28 | It is considered the
standard way to create a chart.
| | 01:30 | You'll notice here that we didn't say
in any way or indicate where we wanted the
| | 01:34 | chart to be, and so the chart
automatically goes on the worksheet.
| | 01:38 | Interestingly, in prior versions of
Excel, the default location, by way of the key-
| | 01:43 | stroke shortcut, was on a separate worksheet.
| | 01:45 | I think what Microsoft has done here is
to recognize that many people prefer to
| | 01:49 | have charts right on a worksheet along
with the data, and so the process here, we
| | 01:55 | didn't even stop to think about it.
| | 01:56 | We saw no choices when we created the chart.
| | 01:59 | We actually put it here on the
same worksheet automatically.
| | 02:03 | There is an option, as there has been in
the past, with any chart, no matter where
| | 02:07 | it's location, you can easily change
the location of the chart, put it on a
| | 02:11 | separate sheet, or put it on a
worksheet with the data itself.
| | 02:15 | So creating the chart is simple.
| | 02:17 | It's easy using standard techniques.
| | 02:19 | It's a straightforward process,
just a short sequence of commands.
| | 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 you're just a casual chart
| | 00:03 | user, applying the final touches to
the chart requires just a few commands
| | 00:08 | that you can do quickly.
| | 00:09 | So you really don't have to spend
a lot of time tweaking a chart.
| | 00:12 | Now, sometimes you might want to,
| | 00:14 | but let's just review the idea of
creating a chart, making a couple of changes,
| | 00:18 | and in effect say 'this is complete'.
It's good enough for the presentation
| | 00:22 | or the printed page.
| | 00:23 | In the example here I'm going to be
highlighting on this chart datasheet this
| | 00:27 | data right here. I want to create a
chart. We will use the standard technique,
| | 00:32 | Insert from the Ribbon. How about a column
chart this time. Clustered Column. There we are.
| | 00:39 | Now what does these chart lack in
another words for presentation or on paper?
| | 00:44 | We see numbers. We see the colors. We see a
legend. It looks pretty good, but what do
| | 00:49 | the numbers mean? And what year is
this? What company is this? And so on.
| | 00:54 | So I think a quick suggestion here in
terms of what we need would be a title on
| | 00:58 | top, something to explain the
numbers, and maybe nothing else.
| | 01:03 | The title might include a year.
There are a lot of different approaches here.
| | 01:08 | We're ignoring for the moment the idea
that maybe we don't like the colors, or
| | 01:12 | maybe the line should be darker, or
maybe they shouldn't be there. There are
| | 01:16 | all kinds of things we could do with
the chart. But let's just say after creating
| | 01:20 | the chart, we could have done this with
the keystroke shortcut. I happened to use
| | 01:24 | in this case the menu approach, but
in both cases we've got some shortcomings.
| | 01:29 | Let's deal with them directly.
| | 01:31 | Probably the fastest way, and
we'll see this in more detail later,
| | 01:34 | is simply to say let's add titles.
| | 01:38 | The Layout tab offers us one way to
do this, and we see Chart Title. There it
| | 01:43 | is, Chart Title, Centered Overlay
Title, Above Chart, either way. How about
| | 01:49 | Above Chart, right there?
| | 01:51 | Obviously, that's not the exact
title we want. Why don't we just, for
| | 01:54 | example, say Sales-2010. There it is.
| | 01:59 | How about a title on the left-hand side?
| | 02:01 | Is that an obvious choice?
| | 02:03 | Well, maybe not obvious,
but Axis Titles. Click it.
| | 02:07 | The Vertical Axis Title and rotate it.
That probably would make sense. Rotate it
| | 02:12 | down on the left-hand side.
| | 02:13 | Now it doesn't say all that,
but that's what we want to do.
| | 02:16 | Rotated Title and we see Axis Title.
And more or less what we want to say is what
| | 02:20 | we're seeing in row two. And how about
Millions of Dollars, or Sales in Millions?
| | 02:25 | Something like that. Or just Millions
of Dollars. Enter. There is the data.
| | 02:32 | Although there are many other things
we could do with this chart, for a lot
| | 02:34 | of us, that's perfect.
| | 02:36 | That just says what we want to say.
| | 02:37 | We will worry about that colors later and
all those kinds of changes that we could make.
| | 02:42 | The chart shows us the information.
The legend clearly defines what the color means.
| | 02:47 | We see that's it in million of dollars.
It's for the year 2010. What else do we need to know?
| | 02:52 | If it's within the same company, we
would only mean to name the company.
| | 02:55 | We could have a title below the month
at the bottom of the screen. Probably
| | 02:59 | not necessary here.
| | 03:01 | So despite all the numerous adjustments
you can make to a chart, making a chart
| | 03:05 | "complete" only takes a few steps.
| | Collapse this transcript |
|
|
3. Fine-Tuning Charts with Design Tab ChoicesSwitching rows and columns for a different view of the data| 00:00 | Of all the many buttons available to
you on the Design tab once you've selected a
| | 00:04 | chart is a not-so-obvious button, third
from the left, called Switch Row/Column.
| | 00:10 | This has to do with what we
call the orientation of the chart.
| | 00:14 | The chart that's highlighted, the one
roughly across columns H through N here, is
| | 00:18 | based on the data that we see over in
columns A, B, C, and D. And nothing wrong
| | 00:23 | with the chart, really. It lacks a few
items of explanatory information, and it's a
| | 00:27 | typical-looking chart that we see when
we highlight data and, whether we create a
| | 00:32 | quickly or from the menu, a column chart.
| | 00:34 | Now what does Switch Row/Column do?
| | 00:36 | Let's click it, and you'll see what happens.
| | 00:38 | The data is oriented quite a bit
differently in this example here.
| | 00:42 | Let me switch it again.
| | 00:43 | You can go back and forth; it's a toggle switch.
| | 00:46 | The initial display shows each cluster
of columns in this group representing a
| | 00:50 | different month, and of course we're
talking about Domestic, Europe, and Asia
| | 00:53 | for each little cluster.
| | 00:54 | And I think for many people this would be
an ideal way to display this information.
| | 00:59 | Why Switch Row/Column?
Well, perhaps you never thought of this kind of display.
| | 01:03 | Now I'm not necessarily saying its better.
| | 01:06 | In fact, I would probably say it's
not as good because the legend on the
| | 01:09 | right-hand side has
twelve different colors in it.
| | 01:12 | But it does point out with certain
kinds of data, changing the orientation
| | 01:16 | by way of the Switch Row/Column button does
give us a different perspective on this data.
| | 01:22 | I recommend it as something you do all
the time, even though many times you'll
| | 01:26 | see a display that's not very enticing
or very much better, and sometimes a lot
| | 01:29 | worse that what you already have.
| | 01:31 | But now when we look at the data, we
see what's happening. For example, under
| | 01:35 | Domestic, we see the trend going up and
down. Same thing in Europe, maybe a little
| | 01:39 | more consistent. Maybe in Asia even
more consistent. That tells us something.
| | 01:43 | And once again not necessarily better that
what we saw before. And as you try this
| | 01:48 | with different charts, and once you begin to get
used to it, there is another chart down here.
| | 01:52 | This is a stacked column chart.
| | 01:54 | I don't think this is going to look
better, but let's try this as well.
| | 01:57 | Switch Row/Column and we
see what's happening this way.
| | 02:00 | This does tells us at a glance--which
we probably already knew anyway--that
| | 02:03 | Domestic has a larger share than the
other two regions, but once again we have
| | 02:07 | the issue of 12 separate colors.
| | 02:10 | Let me change the nature of this chart
though by suggesting that maybe instead
| | 02:14 | of, as I click the chart here and go back
to the data, suppose we're only showing
| | 02:18 | the first six months here.
| | 02:20 | So I'm going to simply make this show
the first six months, and we see the result.
| | 02:25 | And of course we are just seeing
that first half the year there.
| | 02:28 | Switch Row/Column. With only six
items in the legend, this isn't quite so
| | 02:33 | unrevealing as the previous chart was.
So as we look at the data here, we see the breakup.
| | 02:39 | Here is a Line chart and here--
nearly always when you try this for the line chart
| | 02:43 | you'll see usually not
an acceptable alternative,
| | 02:45 | Particularly on a line chart where we
have gotten used to the general idea that
| | 02:49 | usually we're talking about data
flowing over a period of months or days or
| | 02:54 | hours, weeks, whatever, any time period.
This doesn't really make a lot of sense
| | 02:58 | and not a good choice.
| | 02:59 | But again, use this button freely.
| | 03:01 | It's so easy to use.
| | 03:02 | It gives you a different, and sometimes
more revealing, perspective on the source
| | 03:07 | data that you're trying to show on a chart.
| | Collapse this transcript |
| Setting a default chart type and creating a template| 00:00 | If the default clustered column chart,
like the one we're seeing here on the
| | 00:04 | screen to the right of the data, is not the
kind of chart you're likely to need most often,
| | 00:09 | you can change the default
chart type to another type.
| | 00:12 | Suppose after working with charting for
a while, you begin to recognize that the
| | 00:16 | chart type like we see below the
data here--this is a stacked bar chart--
| | 00:21 | suppose, more often than not,
this is a kind of chart you need.
| | 00:24 | Anytime you press Alt+F1 on selected
data, or the F11 key, you will get a chart
| | 00:30 | instantly and it'll be the default type.
But if it's not this type, you have to
| | 00:35 | change it every time.
| | 00:36 | So let's make this be the default type,
because this is the one, for example,
| | 00:40 | that you have decide this is the chart
you'd like to see more often than not.
| | 00:43 | So we've got it selected.
| | 00:45 | On the Design tab, change Chart type.
| | 00:48 | Now we're really not trying to change it
right now, so that it might seem little strange.
| | 00:52 | We come into the dialog box here, and
that type of chart is selected. We'll simply
| | 00:57 | click below here, set as Default Chart. Click OK.
| | 01:03 | So what does that mean?
| | 01:04 | From now on, when we select data--and
it certainly doesn't have to be this data,
| | 01:09 | or the same amount of data. Maybe I
just want to show this data right here.
| | 01:13 | I'm going to press Alt+F1.
| | 01:15 | Instead of this being a clustered
column chart, it's going to be a stacked bar
| | 01:20 | chart as I press Alt+F1, because
that's the default that I changed it to, and
| | 01:25 | from now on, that's the way it's going to be.
| | 01:27 | Now similarly, after working with charts,
you might also decide that certain chart
| | 01:33 | types, and maybe it's a clustered column
chart type with certain design features,
| | 01:37 | is something that you'd like
to have handy and available.
| | 01:41 | So on this particular chart here, I'm
going to change the style of it to look
| | 01:46 | like this. I'm going to give it a
different background color maybe with a format
| | 01:50 | button, make a quick change here, maybe
we make an interchange, and so on, because
| | 01:54 | I just love the color combination,
it's ideal, and I want to have this at my
| | 01:58 | fingertips at different times.
| | 02:01 | So with this in place, I'll click the
Design tab and the second button from the
| | 02:07 | left, Save As Template. And I might have
others out there already, so maybe I'll
| | 02:12 | call this BlueStacked, something like that,
| | 02:16 | some way for me to remember it. I can get
more specific called Background, Plot Area,
| | 02:21 | whatever. I'm just going to
call it BlueStackedbar, and Enter.
| | 02:26 | Now in the future, anytime I'm either
creating a chart from scratch--say over here
| | 02:32 | with this data, I want to create a chart
using the Insert tab > Other Charts > All
| | 02:38 | ChartTypes, I see templates listed here.
| | 02:41 | I have got three or four
of them already perhaps.
| | 02:44 | Do I want this one? Do I want that one?
And I see the names that presumably
| | 02:47 | are helping me remember which
one I want, say this one here.
| | 02:50 | I will click it, and OK, and there it is;
one of my favorite chart types is available.
| | 02:56 | And when working with existing
charts, like this one right here,
| | 03:00 | I might want to change that to one of
my favorites. So from here, Change Chart
| | 03:05 | Type, choice at the top,
Templates. Maybe I'll choose this one,
| | 03:10 | BlueBackgroundBar. Make it a little
bit different, maybe this one here,
| | 03:13 | GoldBackgroundStackedBar, another one I
created a while ago. Double-click it.
| | 03:18 | There we go, and that resurrects another favorite.
| | 03:21 | So you get quite a few of these set up.
| | 03:24 | So we can change the default chart
type in Excel so that every time we use
| | 03:29 | Alt+F1 or F11 to create a
chart it will be of that type.
| | 03:33 | The example we saw previously was a bar
chart, and we can save our own favorite
| | 03:38 | charts that have certain look and style
features, and we can get to them at any
| | 03:41 | time, either when we create new charts
or when we want to alter existing charts.
| | Collapse this transcript |
| Dealing with empty and hidden cells | 00:00 | Selecting data for a chart can
include hidden rows or hidden columns.
| | 00:05 | Also, it might contain zero values, as
we see in this line chart here. But do you
| | 00:11 | want the data in this chart to be
depicted regardless of its hidden status, and
| | 00:15 | what do you want to do
with those zeroes at times?
| | 00:18 | This chart perhaps is a little bit
disconcerting because of the gaps.
| | 00:22 | How might it look if it were column chart?
| | 00:24 | Will that make any differences?
Let's check this out here.
| | 00:26 | Click on the Chart, Design
tab, change the Chart Type,
| | 00:30 | how about to a clustered column?
| | 00:32 | Double-click that first choice.
| | 00:34 | We still see the gaps.
| | 00:36 | For some reason, to me that's not as
obviously un-appealing as the previous
| | 00:40 | example was. As I press Ctrl+Z
here to undo, it looks like that.
| | 00:43 | So maybe that's a question of personal taste.
| | 00:46 | What if we were to hide the columns?
| | 00:48 | Now looking at the data, you would
imagine eventually that this missing data
| | 00:52 | should be reported and put into the
data, and everything is going to be cleaned
| | 00:55 | up, but for the moment it's not.
| | 00:57 | What if these rows were hidden?
| | 00:59 | And you can imagine, for example, the
same kind of data, maybe if it's covering
| | 01:03 | day by day, over a time
period. You've got missing days.
| | 01:06 | Maybe that's a more likely scenario, but
let's select the rows that have missing data.
| | 01:11 | Row 4 and using the Ctrl key, row 11,
and then row 18, and then right-click and
| | 01:19 | Hide, and you see how the chart is changed.
| | 01:23 | Now, not the world's best solution, but
it does show us that the hidden data, or
| | 01:28 | the hidden rows in this
case, are not being displayed.
| | 01:32 | Now, let's right-click one of these.
Or if we choose the Design tab, the
| | 01:37 | choice called Select Data,
| | 01:40 | we do have some control over
both hidden and empty cells.
| | 01:44 | It's in the bottom of this dialog box
called the Select Data Source dialog box. Remember,
| | 01:49 | we got here from the Select
Data button on the Design tab.
| | 01:54 | We also could have right-clicked on
one of the lines and chosen Select Data.
| | 01:59 | So hidden and empty cells.
Show empty cells as gaps.
| | 02:04 | Show data in hidden rows and columns,
| | 02:06 | what if we were to do this
first? Make this choice.
| | 02:09 | Show data in hidden rows and columns.
| | 02:11 | You see what's happening here.
| | 02:13 | Now would you want to show the empty
cells as zero? And you might be misled here.
| | 02:18 | Click OK and then OK.
| | 02:21 | And I doubt if you would make that choice.
| | 02:23 | Now you could, of course. It does show
the data for those hidden months, which we
| | 02:28 | are not seeing. For example, here is October.
We don't see that in the list over there.
| | 02:32 | It is in the hidden row 11.
| | 02:34 | It displays as a zero.
| | 02:34 | I think that's not such a good
choice. Same thing with May here.
| | 02:38 | Now another way to get into this
dialog box, as I mentioned, is to right-click
| | 02:42 | one of lines here and choose Select Data, and
here we are back again, Hidden and Empty Cells.
| | 02:50 | And Connect the data points
with line, we could do that.
| | 02:54 | Now we are still showing the data in hidden
rows, which in this case is zero value data.
| | 02:59 | Click OK and for example,
keep your eye on the May here.
| | 03:02 | It's going to connect the April and June.
| | 03:05 | And we don't necessarily know what
really would've occurred there when we
| | 03:09 | get the real data, but it's actually going
to connect the dots as if it were continuous.
| | 03:13 | So we click OK. So, you see what happened there.
| | 03:15 | So it's up to you to decide what to use
here, but you do have some control over
| | 03:19 | how you want these to be displayed.
| | 03:21 | Now once again, switching
this back to a column chart,
| | 03:25 | on the Design tab,
leftmost button Change Chart Type.
| | 03:29 | We'll make it a column chart,
the first choice, clustered column.
| | 03:32 | We see what's happening here.
| | 03:34 | If we were to right-click the column
here or go through Select Data up on the
| | 03:38 | Design tab, same place,
| | 03:40 | hidden and empty cells.
The third choice here, Connect the data points with
| | 03:44 | lines, doesn't exist.
| | 03:46 | So even though we could choose a zero
here, we cannot really connect them with a
| | 03:51 | line here because we have no lines.
| | 03:53 | So that option is not selectable if
it's a column or bar or any other chart
| | 03:59 | other than a line chart here.
| | 04:01 | So we have some options, and you've
got control over the way Excel shows or
| | 04:05 | hides a chart's source data that might
be in hidden rows or columns, or if you
| | 04:10 | encounter 0 values.
| | 04:12 | Remember distinct differences, the way
this works between line and column or
| | 04:17 | bar charts.
| | 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:06 | often the critical step too, and the
Chart Layout options provide you with quick
| | 00:10 | techniques to complete this vital phase.
| | 00:13 | Remember, the term is Chart Layout.
| | 00:15 | As I click on this chart here to the
right of the data, even though we are on
| | 00:19 | the Design tab, the key phrase Chart
Layout and the associated boxes that we
| | 00:25 | are about to see are located on the Design tab.
| | 00:28 | When you've created a chart and you're
in a bit of a hurry and want to make
| | 00:33 | sure that it has got its titles and
legend where you want them to be and you
| | 00:36 | want the appropriate explanatory
information in place, the drop arrow right
| | 00:40 | here in the Chart Layout group--this is on
the Design tab--will give you some choices.
| | 00:46 | And the number of different
variations will change with the chart type.
| | 00:51 | So currently, it's a clustered column
chart, and I'm going to click the drop arrow.
| | 00:55 | And my first thought when I saw this
years ago was, why aren't these bigger?
| | 01:00 | And I think the rationale is that if
they were bigger then it would overlap
| | 01:04 | perhaps the chart that we're looking at.
| | 01:06 | Now I think it could be a still
little bit better, but what do we see here?
| | 01:10 | I don't think it's very obvious at first.
But as you look at these 11 choices--and
| | 01:14 | notice how they have names like
Layout 6, Layout 5, et cetera--
| | 01:16 | get used to the idea that many of them
have a stripe across the top, meaning it
| | 01:21 | will give you space to put
in a title and a box for it.
| | 01:25 | A number of them will give you a similar
place for a title down the left-hand side.
| | 01:29 | You'll see a bar there on many of these.
| | 01:32 | You also see the legend most often on
the right, but in some cases, not obvious
| | 01:37 | at first, but below the data, or
in some cases not here at all.
| | 01:41 | So you'll have to
experiment with these a little bit.
| | 01:44 | Some of them have gridlines. Some of them don't.
| | 01:46 | Some of them appear to have labels
on them. And without necessarily taking
| | 01:50 | the time to explore all nine of them or 11 of them or 13
of them, depending upon the chart type, maybe pick one.
| | 01:57 | That one doesn't appeal to me.
| | 01:58 | I'm seeing those numbers off to the right.
| | 01:59 | I am going to try another one.
| | 02:01 | So again, up here, click the drop
arrow. Maybe I'll go to this one right here.
| | 02:05 | Now notice what happened there: It
made the columns all wider so there are
| | 02:08 | no gaps between them.
| | 02:10 | It did give us room for a title on the
top, which we will need to change, also
| | 02:14 | down the left-hand side
and underneath, but no legend.
| | 02:18 | Now you can manually add
the legend but maybe better,
| | 02:21 | let's go back to other chart layouts.
| | 02:23 | So the idea is I think you'll quickly
develop a favorite or two here.
| | 02:27 | Mine happens to be this one
right here, Layout number 9.
| | 02:30 | I'll click this one.
| | 02:32 | It does put the legend on the right-hand side.
| | 02:34 | It provides room for a title on
the bottom, top, and left side.
| | 02:38 | By the way, you don't need to use all
these titles if you don't want them.
| | 02:41 | So I am going to click the title below this.
| | 02:43 | I don't need that one.
| | 02:45 | Just click its border and press Delete.
| | 02:47 | But I do want a title up top, so click there,
| | 02:51 | highlight the text, and type in 'Two
Trees Olive Oil', the name of the company,
| | 02:54 | or something. Or maybe put in the year
along with it, or maybe as row 2 suggests, Sales.
| | 03:00 | We can actually put the 'millions of
dollars' perhaps down the left-hand side.
| | 03:04 | So maybe we'll just put in the year, name
of the company, whatever we want to do here.
| | 03:09 | How about just 'Sales'?
| | 03:10 | Maybe everything is within the company anyway,
| | 03:12 | 'Sales-2011'. Good enough.
| | 03:15 | Click over on the other title;
| | 03:17 | Axis title here. Instead of that,
how about 'Millions of Dollars'?
| | 03:21 | It's up to you to decide
how explanatory that is.
| | 03:25 | As I press Enter here, we see it that way.
| | 03:27 | So you can enter it different
ways. And maybe that's enough.
| | 03:31 | Now again, I would encourage you to
experiment with chart layouts and if, for
| | 03:35 | example, I were to change this to a bar chart--
| | 03:37 | Change Chart Type and I'll choose Bar, this one--
| | 03:41 | it looks like this.
| | 03:43 | Maybe I am not too wild about the
look, although it is very similar.
| | 03:47 | This time I'll go to Chart Layouts and
instead of having 11 choices, we have only 10.
| | 03:51 | I am not sure why, but nevertheless,
some different layout choices here.
| | 03:55 | Maybe I'll try this one.
| | 03:56 | I don't like that one.
| | 03:57 | Maybe we'll try this one.
| | 03:59 | That puts what's called
a data table underneath it.
| | 04:02 | So this does give you this quick
ability to choose a standard kind of
| | 04:06 | layout, like it or don't.
| | 04:08 | Later as you learn to tweak different
parts of a chart, you might start with
| | 04:12 | one of these, and then make certain
adjustments that you'd make normally and
| | 04:16 | then proceed from there.
| | 04:17 | So I think it's a good starting point,
a good step, too, after creating the chart,
| | 04:22 | to clean it up, put in the titles
you need, and then take it from there.
| | 04:25 | It's easy to use and easy to get to.
| | 04:28 | After creating a chart, you can
quickly complete many of the missing
| | 04:31 | features via this technique.
| | Collapse this transcript |
| Choosing a chart style from 48 colorful variations| 00:00 | The colors that we see in the column
chart to the right of the data here come
| | 00:03 | automatically, and for many of us, myself
included much of the time, they're just fine.
| | 00:09 | I don't worry about them too much.
| | 00:10 | Keep in mind that if you'd like to
fine-tune a chart, you could easily change any
| | 00:15 | one of these simply by right-clicking
on any of these columns here, Format Data
| | 00:18 | Series, and way too many
choices for a lot of us.
| | 00:21 | Let's do talk about a feature though
that's readily available and for some
| | 00:25 | people is ideal, particularly if they don't
have the time to make a lot of design changes.
| | 00:30 | These are actually called style adjustments.
| | 00:33 | Anytime you have selected a chart, if
you go to the Design tab, you will see a
| | 00:38 | large group called Chart Styles.
| | 00:41 | There is drop arrow on the
right-hand side. 48 choices.
| | 00:46 | If you are an indecisive person, you
are going to have some problems with this.
| | 00:49 | A lot of choices here.
| | 00:51 | The default is right here, the upper row here.
| | 00:54 | It's the second choice.
| | 00:56 | And either experiment--I like this one.
| | 00:58 | It's sort of pseudo-three dimensional.
| | 00:59 | Got some surface to it. I like that.
| | 01:02 | And I kind of question why they have
some these. If all these bars have the same
| | 01:05 | color, almost the same color here,
| | 01:09 | for any of us who have difficulty in
discerning the differences between colors,
| | 01:12 | I don't think that's such a great
chart. But they're here and depending upon
| | 01:16 | chart type, you will see similar choices.
| | 01:19 | So if, for example here, on the Design
tab if I change the chart type here to
| | 01:23 | be anything else--and we mean anything else,
| | 01:26 | maybe I'll make it a
stacked bar here, click OK--
| | 01:29 | we see what's happened there and go to the
choices, and now there are all our chart choices.
| | 01:34 | Click there, and so on.
| | 01:36 | But it is quick, it's fast, it's easy.
And one thing that will change the way
| | 01:40 | you will proceed with this could be
your choice of what's called a theme.
| | 01:45 | Now that's perhaps the stuff of a
different course, although it is related
| | 01:49 | to Excel formatting.
| | 01:50 | But let's just point out
here that on this worksheet--
| | 01:53 | let me zoom back a little bit--
| | 01:54 | there are some other charts. And if we
were to change the theme by going to the
| | 01:59 | Page Layout tab in the Ribbon--
left-hand side, choose Themes--
| | 02:05 | as we slide over some of these choices, you
can begin to see some changes in the background.
| | 02:10 | I think to see this a little bit better
here, I'll zoom in a bit more so we can
| | 02:14 | keep our eye on some of these charts.
| | 02:15 | We don't have to see them all
necessarily, but once again, on the Page Layout
| | 02:20 | tab, choose Themes, and now I have the
mouse hovering over Equity and then Flow
| | 02:26 | and then Foundry and so on and so on.
| | 02:30 | We see what's happening here.
| | 02:31 | I kind of like this one maybe.
| | 02:33 | I don't like that one.
| | 02:34 | I like that one and so on.
| | 02:36 | Now notice how all the charts are changing.
| | 02:39 | So this has some impact.
| | 02:40 | If I go with Solstice here, not only
am I making a change to the colors, but
| | 02:45 | notice the font, particularly
in the chart just to the right.
| | 02:48 | It's a different font than we're used
to seeing too, and look at the data itself.
| | 02:52 | So that has worksheet-wide
implications here as we make these changes.
| | 02:57 | If I go back to the chart now and
then on the Design tab look at the Chart
| | 03:01 | Styles, we see what's happening here.
| | 03:04 | And once again, we are seeing a similar
kind of layout but obviously different
| | 03:07 | colors than we saw before.
| | 03:09 | So you can imagine the possibilities
here and for some people, they get lost
| | 03:13 | in a hopeless tangent here because they can
never decide what exactly it is they need.
| | 03:17 | Easy to get to, 48 different style
options, and it does depend upon what you
| | 03:22 | have chosen as a theme.
| | 03:24 | If you stick with the standard theme, I
am going to go back to that since I do
| | 03:27 | prefer it and it raises few
questions during presentations
| | 03:31 | anyway. Come back to the
Office theme, and there we are.
| | Collapse this transcript |
| Changing the location of a chart| 00:00 | When you create a chart with the
so-called quick method, the function key F11
| | 00:04 | that creates a chart, it
puts it on a separate sheet.
| | 00:08 | You may want to move that chart from
a separate sheet back to, or onto, a
| | 00:12 | different worksheet.
| | 00:14 | And similarly, in reverse order, if
you have a chart on a worksheet, like the
| | 00:18 | example here to the right of the data,
if you'd like to remove that chart from
| | 00:22 | here and put it on its own
separate sheet, you can do that as well.
| | 00:26 | So at anytime you can make a change.
| | 00:28 | At different times you may change your
attitude about where a chart really belongs.
| | 00:33 | Let's suppose we select some data here
and quickly create a chart on another
| | 00:36 | sheet with the shortcut F11.
| | 00:39 | If you keep your eye on the sheet tabs at the bottom,
| | 00:41 | after pressing F11, we will see a new
sheet to the left of this. There it is.
| | 00:46 | It's called Chart1, and there is that chart.
| | 00:49 | And again, the advantage of working with
a chart on its own sheet is that you're
| | 00:52 | strictly focused on the chart.
| | 00:54 | You're not looking at the data.
And perhaps it's just easier to see because
| | 00:58 | typically it's larger than it
might appear on a worksheet.
| | 01:01 | So suppose you've changed your
mind about this, its location.
| | 01:05 | On the Design tab, you see the rightmost
button called Move Chart. We can simply click that.
| | 01:11 | And do we want this on a new sheet?
| | 01:14 | We could put in on different
sheet, although it's already there.
| | 01:17 | The term here may throw you a
little bit. It says, "Object in."
| | 01:21 | Well, that's the terminology meaning the
chart is an object. Let's put it in--
| | 01:25 | I almost want to
interrupt and say, "Let's put it on."
| | 01:28 | It will go if we wish it to go,
to the sheet called YearData.
| | 01:32 | Now there are other sheets out there.
| | 01:33 | We can certainly click the drop
arrow and decide where we want to put it.
| | 01:37 | Occasionally, you will even see a
chart on a worksheet that doesn't have
| | 01:41 | the source data in it.
| | 01:42 | But let's say logically here it
might go on the YearData sheet.
| | 01:46 | Before doing that though, let's make a
change to it so that we don't confuse it
| | 01:50 | with any existing charts that might be there.
| | 01:52 | Maybe we will just change the chart type of it here.
| | 01:55 | They could be 100% Stacked Column,
a choice not too many people use that
| | 02:00 | often, but I'll use it here.
| | 02:02 | And let's simply click the Move Chart
button and switch this to be an object on
| | 02:08 | the sheet called YearData.
| | 02:10 | And as we click OK, something else you
will notice at the bottom left of the
| | 02:14 | screen, the sheet that's actually
called Chart1 we're now looking at will
| | 02:18 | disappear as we click OK. And on the
YearData sheet--this is where we are--
| | 02:24 | here is that very chart that we moved.
| | 02:27 | So that's pretty easy and fast.
| | 02:29 | I'm going to get rid of it.
| | 02:30 | We don't really need it. Press Delete.
| | 02:33 | But sometimes we do go to the opposite
direction and not only can we use that
| | 02:37 | button that's on the right-hand side of
the Design tab called Move Chart, we can
| | 02:41 | also do this by way of a right-click.
| | 02:44 | So on this example here, I've decided
that this chart really belongs on its
| | 02:47 | own separate sheet.
| | 02:49 | Let's remove it from here and at
the same time create a new sheet.
| | 02:53 | Right-click on the chart area, usually
near the perimeter. And by the way, you
| | 02:57 | can also do this on the right border,
left border, any of the borders as well.
| | 03:01 | Sometimes when you do this if you
happen to right-click in a certain spot,
| | 03:05 | you don't see the choice that says
Move. That's what we're looking for.
| | 03:09 | So if we right-click out here in
Chart area, we see quite a few choices.
| | 03:13 | Move Chart, and let's
just put it on a new sheet.
| | 03:17 | We're going to put it on the
sheet called Chart2. Click OK.
| | 03:21 | So you can easily move a chart to a
different location in the same workbook, or
| | 03:27 | you can even move it from its own
separate sheet onto a worksheet.
| | Collapse this transcript |
| Moving and resizing a chart| 00:00 | When you're using a chart that's on a
worksheet, or possibly many charts on a
| | 00:04 | worksheet as we see in this example
in this particular worksheet called
| | 00:07 | YearData, there are four different charts here.
| | 00:10 | Now you've got complete control
over the placement of the charts.
| | 00:13 | You can move a chart. Any of these
charts can be moved to any position you want
| | 00:18 | simply by dragging a border.
| | 00:20 | You can resize a chart.
| | 00:21 | You can make a copy of a chart.
All these things we need to be able to do.
| | 00:26 | These are actually dragging choices
that we don't actually use a menu on.
| | 00:30 | So let's just click, for example, in
this chart here, as I just did. I want to
| | 00:34 | move this chart to the right of the pie chart.
| | 00:36 | Now you can drag from anywhere in the
chart area, but sometimes when you're
| | 00:40 | doing this you happen
to click on the wrong spot.
| | 00:43 | So if I happened to click here and drag--
maybe I am kind of in hurry when I do this--
| | 00:46 | I'm moving the Plot area. Or maybe if I
did this, I'm moving the legend or something.
| | 00:50 | You can do that, and sometimes you want
to do that. But if you want to move the
| | 00:53 | chart--let me press Ctrl+Z a
couple of times to undo those actions.
| | 00:58 | It's probably easiest to get in the
habit, after selecting the chart, to simply
| | 01:01 | drag one of the borders, and you drag it
right, left, down, upright, wherever you want to.
| | 01:07 | I want to move the pie chart leftward.
| | 01:08 | I'll drag its border. And you
can do it in one fluid action.
| | 01:11 | You can both select it and drag
it at the same time if you wish.
| | 01:15 | Sometimes you want to resize a chart.
| | 01:18 | For example, I might want to move this
chart over here a bit and then make it
| | 01:21 | larger, because I don't think I can see
the detail enough. And maybe for whatever
| | 01:26 | reason as I make it
larger, how do we do that?
| | 01:29 | We can drag any corner--
| | 01:30 | when you put the mouse on a corner
you'll see a diagonal arrow--or a side--you
| | 01:35 | either see a horizontal arrow
this way, vertical arrow down here.
| | 01:39 | We can make this chart taller, shorter.
| | 01:41 | We can make it wider or narrower
or drag it from the corner.
| | 01:45 | As we drag from the corner, we could
possibly make it wider or taller or
| | 01:49 | and so on.
| | 01:51 | And if you worked with objects at all in
either Excel, Word, or PowerPoint, these
| | 01:56 | are familiar techniques that
you've probably used already.
| | 01:58 | If you want to maintain the same
ratio of height to width, it's called the
| | 02:03 | aspect ratio, as you drag one of the
corners, you can also hold down the Shift key.
| | 02:07 | So as I'm dragging this corner here
back and forth, I cannot make it wider
| | 02:11 | without it getting taller, or
narrower without it getting shorter.
| | 02:15 | So I'm holding down Shift as I'm
dragging the corner and when I let go of the
| | 02:20 | mouse it will have got larger or
smaller, but the proportion of height to width
| | 02:24 | will have stayed the same.
| | 02:26 | Now rarely but occasionally you might
want to make a chart larger or smaller and
| | 02:30 | keep it around the same center.
| | 02:32 | I think you're more likely to want to do that
with objects, but if we drag any edge or
| | 02:36 | corner here and we're holding down the
Ctrl key, we're going to be making this
| | 02:41 | taller, smaller, but the same center is here.
| | 02:44 | Now I'm just moving the mouse here
like I've got nothing else to do, but I'm
| | 02:47 | holding down the Ctrl key, and
you see what could be happening.
| | 02:49 | If I let go with the mouse before the
keyboard, I will have resized this in
| | 02:54 | whatever way I want to.
| | 02:55 | So sometimes that's relevant. Perhaps
more relevant though is the idea that
| | 03:00 | if you have a certain idea that this
should look a little neater and tidier
| | 03:03 | in terms of layout,
| | 03:05 | you might want to have the edges
of the chart line up with the cell
| | 03:08 | boundaries in the worksheet.
| | 03:10 | I'd suggest doing this on corners
rather than sides, but you can do it either way.
| | 03:15 | As I drag this corner here, I can take
it anywhere I want. But if I hold down the
| | 03:19 | Alt key while doing this, it only lets
the borders line up with cell boundaries.
| | 03:25 | So I did it there. Click outside
this; you'll see what it has done.
| | 03:28 | You'll see that it's just perfect.
| | 03:29 | I'll do the lower-left
corner as well, maybe right here.
| | 03:33 | Move this around anywhere I want, but if
I'm holding down the Alt key, it's going
| | 03:36 | to line up perfectly this way.
| | 03:38 | So maybe I want to do that,
or maybe take it down to here.
| | 03:42 | I'm thinking of the other charts, and so on.
| | 03:45 | This is little tedious maybe, but it
does give you a quick way to do that. Maybe
| | 03:48 | I'll do it this way.
| | 03:49 | So I'm dragging an edge, any edge or
corner, holding down the Alt key, makes
| | 03:55 | the edges of the chart line up
perfectly with the cell boundaries. And any of
| | 03:59 | these techniques that we use with the
Alt key, Ctrl key, or Shift key be sure
| | 04:04 | to let go with the mouse first before the
actual key itself for the feature to in effect stay.
| | 04:10 | And one other feature here, too, is
we've already talked about moving, but
| | 04:13 | sometimes you want to make a copy of a
chart, and once you have become familiar
| | 04:18 | with how easy it is to change a chart
type, sometimes you say well, okay I
| | 04:22 | like this chart, but I'd also like
to have a column chart next to it.
| | 04:26 | Why don't we just drag the edge of this
chart over here, but this time be holding
| | 04:30 | down the Ctrl key, as I'm doing
right now. Drag it over here.
| | 04:33 | Let go with the mouse. There's a copy.
| | 04:35 | We don't want to keep two identical
charts here, so we'll simply change, by way
| | 04:40 | of the Design Tab, change the Chart
Type of this one to maybe something
| | 04:44 | completely different, maybe one of
these here, which I don't really like, but
| | 04:48 | the idea is you can easily copy a
chart and possibly change the chart type.
| | 04:53 | And if the chart that you drag that
did have a lot of labels on it already,
| | 04:57 | a lot of text information, that's going
to go with it as it did here, although
| | 05:00 | we didn't have a lot going on at that point,
but still, that's going to happen as well.
| | 05:04 | So no shortage of techniques,
generally dragging techniques they are called
| | 05:08 | for copying charts, moving charts,
and/or adjusting the size of charts on a
| | 05:13 | worksheet.
| | Collapse this transcript |
|
|
4. Layout Tab Options: Inserting Pictures, Shapes, and Text BoxesUsing pictures as chart elements| 00:00 | A well-designed chart is, by its very
nature, colorful, easy to read--a lot
| | 00:06 | easier than the data for many of us.
But you still might want to enliven it even
| | 00:10 | further by including a clip art image
or a photograph as part of a chart, not
| | 00:15 | necessarily of the perimeter or the
plotting area, but the actual columns or bars
| | 00:20 | or even wedges of a pie.
| | 00:21 | Let's take a look at how this works.
| | 00:23 | The process begins with, for example,
in the lower-left corner, suppose we'd
| | 00:27 | like to see instead of the columns,
or one of the blue set of columns, how
| | 00:32 | about an image here?
| | 00:34 | Maybe this particular data is about
vehicles or cars, and maybe we want to introduce a
| | 00:39 | little bit of whimsy as well.
| | 00:41 | Let's click one of the blue
columns and click in the chart.
| | 00:44 | You see how all three of
them are selected at once.
| | 00:46 | Now on the Layout tab in the Ribbon,
if you choose Picture, and this seems
| | 00:52 | like we're going in the right direction,
possibly you could use a photograph like this.
| | 00:56 | Now these are the standard ones
available, and you could certainly find others,
| | 00:59 | depending on what you might have on a
flash drive or on your C drive, whatever.
| | 01:03 | You can easily choose one of these.
| | 01:06 | However, if you do choose one, I'm
choosing this right now, the Chrysanthemum,
| | 01:10 | you choose Insert, just do a simple Insert,
| | 01:13 | it covers the entire chart.
| | 01:15 | That's not what we want to do.
| | 01:16 | Ctrl+Z. Strangely enough,
we're going to get there faster
| | 01:20 | if we simply right-click on the blue
columns and go to Format Data Series.
| | 01:25 | And we can fill that series with a solid
color, a gradient color, lots of choices here.
| | 01:33 | How about Picture or texture fill?
| | 01:36 | And the last time this was used,
apparently, on this particular PC, someone did
| | 01:40 | fill in some colors, of
light tan, so that's what we see.
| | 01:44 | That's simply the last one that had been used.
| | 01:47 | We could go to file and choose something here.
| | 01:49 | Why don't we choose the
Chrysanthemum and see how that's going to look?
| | 01:52 | And Insert. That's probably not a great
choice here, and besides I suggest that
| | 01:57 | maybe we want to use a car here.
| | 02:00 | Now there could be a picture of a car on a file.
| | 02:02 | I'm not sure how good that would look.
| | 02:03 | I don't think the penguins would look
so good here either. We could try that
| | 02:06 | one. Well, maybe, depending
on the nature of the content.
| | 02:08 | But let's jump into clip art and
see what we could find--and of course,
| | 02:13 | thousands of choices out here.
| | 02:15 | Presumably, you're looking for something
that's pertinent to the particular kind of data.
| | 02:19 | I'm going to just type in the
word 'car' here, and there's sort of a
| | 02:23 | cartoonish car right here.
| | 02:24 | Maybe choose this and OK.
| | 02:29 | Now that's all squat and doesn't look
very good, so what might we do with this?
| | 02:33 | We could certainly leave it
that way, but I don't think so.
| | 02:35 | Stack, let's try Stack
right here. It stacks the car.
| | 02:38 | If the proportions aren't the way you
would like them be, you can stack and
| | 02:42 | scale them in different ways, but
let's say that doesn't look too bad.
| | 02:44 | And I think you just have to decide
whether that tells the story. Are you
| | 02:49 | going to be taken less
seriously if you use an image like this?
| | 02:52 | Is it too off-the-wall? Is it too non-
standard, too non-businesslike? But at
| | 02:56 | least you can see how that could be done.
| | 02:59 | You see a lot of these kinds of charts,
for example, in the lower left-hand
| | 03:02 | corner of typically USA Today
newspaper. Americans are eating more potatoes,
| | 03:07 | you'll see a stack of
potatoes, that sort of thing.
| | 03:09 | So you can insert clip art
and/or images within chart elements.
| | 03:13 | I doubt if the bar chart here in the
middle is a good candidate for those
| | 03:17 | because there are just too many bars there.
| | 03:20 | Pie chart maybe not so good either, but
let's, after selecting one of the wedges,
| | 03:23 | right-click, and maybe format that.
| | 03:27 | This time I'll try using that
chrysanthemum. Maybe it will look okay there.
| | 03:30 | Now again, I'd be a little bit hard-
pressed to tell you why that's a great choice.
| | 03:34 | You can see what's happened automatically here.
| | 03:36 | The car, since that was last used, that
probably doesn't make much sense there either.
| | 03:41 | How will this chrysanthemum look? There we go!
| | 03:44 | So again, in context with the right kind of
data here and there, that might make some sense.
| | 03:50 | It's a relatively easy way to add a
little bit of flair and pizzazz to
| | 03:53 | certain kinds of charts.
| | Collapse this transcript |
| Adding shapes and arrows | 00:00 | To accentuate a certain portion of a
chart, or to draw attention or shift the
| | 00:04 | focus to a particular chart element,
you can put in a special label.
| | 00:08 | You can add an arrow.
| | 00:09 | You can add symbols to provide emphasis
or to do what's called chart annotation.
| | 00:14 | In the bar chart to the right of the
data here, the April data for Europe is
| | 00:19 | substantially larger than any
other entry for that particular region.
| | 00:23 | And you might just want to draw
additional attention to it, although it
| | 00:26 | certainly draws a lot of
attention to itself already.
| | 00:28 | Let's put an arrow, for example,
pointing to this particular bar.
| | 00:33 | So having selected this chart, on the
Layout tab, you do see a choice called Shapes.
| | 00:40 | You wouldn't exactly suspect that an
arrow is found there too, but there are lots
| | 00:44 | of choices here, including, for
example, a simple arrow right here.
| | 00:49 | Click this. Draw it anywhere we want.
| | 00:51 | You start from the tail of the arrow,
point it toward wherever you want, and of
| | 00:55 | course, we can move either end if we
don't like that positioning, but that's easy
| | 00:59 | to get to of course that way.
| | 01:01 | Notice that there is a new tab in the
Ribbon. We're temporarily looking at
| | 01:05 | Drawing Tools > Format tab,
and we can certainly make some changes to
| | 01:10 | this arrow here under Shape Effects,
Shape Outline, that sort of thing, and
| | 01:14 | quite a few styles here, as if we
don't have enough to do already, but
| | 01:18 | perhaps you'll like that idea.
| | 01:20 | And we can put in additional arrows like that.
| | 01:22 | Another kind of arrow, or a different
kind of arrow that you might want to put in
| | 01:25 | instead of this, is on the Layout tab,
under Shapes, a thick arrow. Here's one
| | 01:32 | here, and there's quite a few choices
here, quite a few different designs.
| | 01:36 | We'll just click and drag an arrow.
| | 01:38 | And that may not be pointing the way
we want it to, but the green dot on top
| | 01:43 | allows us to rotate this. Minor point here,
| | 01:46 | if you hold down the Shift key,
you can only rotate this in 15-degree increments.
| | 01:50 | That may or may not be
necessary, but that could be helpful.
| | 01:53 | We're pointing it in the right
direction but haven't got it lined up yet.
| | 01:57 | Drag one of the borders.
Maybe put it right there.
| | 02:00 | The advantage to this approach could
be we could actually add text to it
| | 02:04 | if you happen to right-click on
this and you have to right-click on the
| | 02:09 | object. And then Edit Text,
| | 02:12 | now there's no text there, but there could be.
| | 02:13 | Let's Edit Text, and let's put in 'Great job'.
| | 02:18 | And the text may not be looking the way you
want it to just yet, so let's press Enter here.
| | 02:24 | And then right-click again this time on
the blue arrow and choose Format Shape.
| | 02:30 | And this time we will have some choice
here with regard to the actual text box.
| | 02:36 | Text layout, Vertical alignment,
Text direction. It's Horizontal.
| | 02:41 | Let's make it be this way.
| | 02:42 | And you see what's happening there.
| | 02:44 | You might, as I'm doing here,
play with this different ways.
| | 02:47 | Stacked is not the choice
I think most of us would want.
| | 02:49 | How about this one? We're getting there.
| | 02:51 | We can begin to see what's happening with this.
| | 02:53 | That may take some working, kicking around
back and forth to get exactly what you want.
| | 02:58 | Also, if you happened to click there, too,
you could go to the Home tab, make it
| | 03:02 | be bold. Some of those
choices work as well, and italic.
| | 03:06 | And you can change the color of the
font and on and on and on, if you wish.
| | 03:09 | So you can add text that way as well.
| | 03:12 | Use that kind of an arrow
as opposed to the other kind.
| | 03:15 | So there are lots of choices we can use
here using arrows, and we can add any of
| | 03:19 | those other shapes that
might be appropriate, too.
| | 03:21 | So once again, back on the Layout tab,
we can go to Shapes, and any of these
| | 03:27 | other choices that might make
sense to you. And quite a few,
| | 03:30 | Stars and Banners that sort of the thing,
things that you might be familiar with
| | 03:34 | in your use of PowerPoint and/or Microsoft Word.
| | Collapse this transcript |
| Adding floating text and text boxes| 00:00 | In addition to using arrows with text
in them or simple arrows to point out
| | 00:05 | certain features of a chart to draw
attention to them, you can actually
| | 00:08 | explain certain parts of a chart with
text boxes or use free-floating text
| | 00:14 | anywhere within a chart.
| | 00:16 | And we start with these features after
having selected the chart from the Layout
| | 00:21 | tab again. And we see a choice called Text Box.
| | 00:25 | Now even though this says Text Box and that
would lead you to believe that we'll see a box,
| | 00:30 | I'm going to click and drag a box.
| | 00:33 | But if we don't do anything with the
box in terms of its border or shading, in
| | 00:38 | effect, we're putting in free-form text.
| | 00:40 | So the idea of putting in text is
we'll put in the text, and then maybe we'll
| | 00:44 | decide after the fact whether
it's going to be in a box or not.
| | 00:47 | So either way, but you can start with Text Box.
| | 00:50 | Maybe we're going to say that 'Data
is Preliminary' or something like that.
| | 00:55 | You can imagine a variety of different
things that you might be saying here to
| | 00:58 | explain what's going on in this chart.
| | 00:59 | Data is Preliminary.
| | 01:00 | And that may not look so good in
terms of the layout, so we could drag the
| | 01:04 | border of the box to make it
like this. Put it like there.
| | 01:07 | And while we're here, maybe we'll click
the border of the box, and on the Home
| | 01:11 | tab, make it be bold.
| | 01:13 | If we do wanted to have the image of a
box, we could choose, on the Fill Bucket
| | 01:19 | option, to put a color behind it like
this or that, and we see Excel's so-called
| | 01:24 | Live Preview highlighting the box this way.
| | 01:27 | And if you'd like to center that left
or right, here's the centering choice.
| | 01:30 | So many of the buttons here are available
on the actual Home tab Formatting Options
| | 01:34 | are available that way.
| | 01:35 | You just click the box to put it in the
center, meaning top, down, center, as well
| | 01:39 | as left, right, center.
| | 01:40 | So a lot of choices there too, and maybe
use a bigger font here. We'll use font 14.
| | 01:46 | That'll cause us to have to resize
the box possibly. There we go!
| | 01:50 | And you got to know how to spell
preliminary, don't you, if you want to make
| | 01:54 | this really meaningful.
| | 01:55 | And once again, by using
different-sized text, we see what's happening there.
| | 01:59 | So you see how that option could be played out.
| | 02:01 | If we didn't want this to actually
appear to be in a box, what we could have done
| | 02:06 | here is have not included the Fill option.
| | 02:08 | Now, for the moment, going back to the idea
of a box though, how about a border on the box?
| | 02:14 | Recognize that when we click here, as
opposed to clicking outside of it, click
| | 02:18 | here on the Ribbon, right now there
is no choice for the actual object.
| | 02:24 | This format here refers to the chart
itself. But click the box and now we'll
| | 02:28 | see Format, and this is related
to the actual box that we're using.
| | 02:32 | So from here we could use Shape Styles
and fill in a color that way if we wish.
| | 02:37 | Maybe that or how about the outline of this?
| | 02:41 | We can change the color of the outline.
| | 02:42 | You see what's happening there.
| | 02:44 | And you can imagine if it's a busy
day, or let's say if it's a rainy day
| | 02:48 | depending upon the circumstances, you
either have plenty of time to do these
| | 02:51 | things, or you don't.
| | 02:53 | Quite a few options out
here for this sort of thing.
| | 02:55 | How about Shape Fill? No Fill.
| | 02:58 | You can see what's happening that way.
| | 03:01 | So I'm going to press Ctrl+Z a couple of times
to go back a few steps. And remember,
| | 03:05 | you can go back a hundred steps with
Ctrl+Z, maybe back to here. There we go!
| | 03:09 | And what if we don't want
this to be in a box at all?
| | 03:13 | I could do a series of Undos more, but
come back here and choose No Fill, and
| | 03:18 | we see no box at all.
| | 03:20 | To drag this, we need to drag the box,
but depending upon where we're placing
| | 03:23 | this, we can see how that can go anywhere.
| | 03:26 | And this, too, is in a box.
It could be rotated.
| | 03:29 | I wouldn't suggest that's a
necessity either, but that just may give it a
| | 03:32 | certain look that you like.
| | 03:33 | You put it anywhere you want, click
outside of the box, and it looks like that.
| | 03:37 | So quite a few options here, quite a few
variations on the idea that you can add
| | 03:41 | text anywhere within a chart,
any kind of chart you wish,
| | 03:44 | and you can either keep it in a box or
keep it in one of those shapes that we
| | 03:48 | saw, or simply have the appearance of
being free-floating on the chart itself.
| | Collapse this transcript |
|
|
5. Layout Tab Options: Adding Titles, Labels, and LegendsAdding, editing, and removing chart titles| 00:00 | If you didn't add a title to a chart at
the time you created it, you can use the
| | 00:04 | title options available on
the Layout tab in the Ribbon.
| | 00:08 | As a reminder, when you do create a
chart and particularly if you've done it
| | 00:11 | using one of the quick methods--
| | 00:12 | for example, this chart was
created with a keystroke shortcut Alt+F--
| | 00:16 | The chart has most of the features needed;
| | 00:19 | it certainly does not have,
however, a chart title.
| | 00:23 | On the Layout tab we can make
a choice called Chart Title.
| | 00:28 | If you go to the Design tab and use
Chart Layouts, possibly you might want to
| | 00:32 | take care of other titles as well
at the same time, and legends as well.
| | 00:37 | Ironically, those choices called
Chart Layout are on the Design tab.
| | 00:41 | The Layout tab itself
has the choice Chart Title.
| | 00:44 | A quick look at these.
| | 00:45 | Two choices, the description of them
is fairly obvious. Above the Chart,
| | 00:49 | I think this is what a lot of people would want.
| | 00:51 | The other choice here under
Chart Title, Centered Overly Title.
| | 00:56 | It does not resize the chart.
| | 00:57 | The title will look there.
| | 00:58 | This is plenty of white space in the chart,
| | 01:00 | so that would be viable option here.
| | 01:02 | Maybe we'll just leave it this way.
| | 01:04 | Now we don't have to actually select this
or drag across, but that you can do that.
| | 01:09 | I'm sure your chart titles will not
contain those two words most of the time.
| | 01:13 | So that's not really necessary.
| | 01:14 | So as soon as we see Chart Title,
what we could do is simply start typing.
| | 01:19 | In other words, when we first created it, we see this.
| | 01:22 | I'm simply going to type in a description
of the chart, namely 'Sales - 2011'. Enter.
| | 01:28 | As I'm typing this, you see this in the
formula bars. As soon as I press Enter, of course
| | 01:34 | it finds its place on the title.
| | 01:36 | A minor point here, but occasionally useful.
| | 01:38 | Sometimes we want more
information in this title.
| | 01:42 | I'm clicking the formula bar now, and
I could've done this earlier before
| | 01:45 | pressing Enter, but I would like to
have on the line below this, 'preliminary' or
| | 01:50 | 'projected' or something like that.
| | 01:52 | So after the 2011, I'm going to press Alt+Enter.
| | 01:55 | Some of you may know that's the way to
do a line break, and now I'm going to type
| | 01:59 | in parentheses 'Projected' and then
Enter and we see what happens this way.
| | 02:05 | So you can make some changes and have
multiple line entries this way as well.
| | 02:09 | Now suppose this is the
title, you've used it a while,
| | 02:13 | you want to make a change to it, or
you want something completely different.
| | 02:16 | If we simply clicked near the title,
or I don't have to be right on it, the
| | 02:20 | box is highlighted.
| | 02:21 | We could click in the formula bar and
type something completely different or
| | 02:25 | alter this if we want to.
| | 02:26 | Maybe I'll put in 'Sales-2011 Final'
| | 02:30 | this time on the same line.
| | 02:32 | So without really editing what's there,
I'll just type this and press Enter and
| | 02:36 | we see the title there.
| | 02:37 | If we want to change the location of it,
Chart Title is centered above chart this way.
| | 02:42 | We simply have adjusted it that way as well too.
| | 02:44 | You can also do formatting on it also.
| | 02:47 | A recommended approach there might be
simply right-click on the border and then
| | 02:51 | go to Format Chart Title and explore
some of those options with regard to
| | 02:55 | filling in the color.
| | 02:57 | Not necessary, but for some people you
might want to pursue that as well, too.
| | 03:00 | So it's easy to put in a title or to
make a change to it or to replace it, adding
| | 03:05 | a title or adjusting any existing
title, to simply use the title options
| | 03:09 | available on the Chart Title
option in the Layout tab in the Ribbon.
| | 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
| | 00:09 | the Layout tab in the Ribbon.
| | 00:12 | So in this particular chart let's imagine
we have not yet done the main chart title.
| | 00:16 | Let's say we tackle the titles down
the left-hand side and across the bottom.
| | 00:20 | Now neither one of these is a requirement,
but in many, many cases--particularly the
| | 00:24 | vertical axis on the left side--we
definitely want to have that here to explain
| | 00:28 | what these numbers mean.
| | 00:30 | It's not to say that you couldn't put it
in the main title, but typically it goes
| | 00:34 | along the left-hand side. Look at the
options that we have here under Axis Titles.
| | 00:39 | This is in the Labels group.
| | 00:41 | It's on the Layout tab of the Ribbon.
| | 00:43 | Primary Horizontal,
| | 00:45 | in this case Primary Vertical Axis Title.
| | 00:48 | Rotated Title, probably the most common choice.
| | 00:51 | As soon as I click this,
you'll see what we're about to do.
| | 00:53 | Let me show you the other
options before completing this entry.
| | 00:56 | Back to Axis Titles.
| | 00:58 | Less likely here is the choice Vertical Title,
| | 01:01 | although if the words are short, that
would make sense in some situations.
| | 01:06 | Typically, this is harder to read, and the
third choice here might be Horizontal Title.
| | 01:12 | This eats up a lot of chart space, but
there, too, that might be appropriate for
| | 01:16 | certain kinds of short titles.
| | 01:18 | In the example here I want to indicate
that these numbers represent millions of
| | 01:22 | dollars just the way we see it
over there in row 2 above the data.
| | 01:26 | So Axis Titles here > Primary Vertical Axis
Title > Rotated Title, and we see it that way.
| | 01:34 | I simply want to say 'Sales-Millions'
of Dollars, but if I'm going to put
| | 01:38 | sales maybe in the main title or
below the data, maybe I'll just say
| | 01:41 | 'Millions of Dollars'.
| | 01:44 | As soon as press Enter,
we see the information there.
| | 01:48 | If you want to change the size of that
font, by the way, you can go to the Home
| | 01:51 | tab here, and if it's size 10, for
example, just slide over this, and you can
| | 01:54 | say it growing as I slide over these,
without even making a click just yet.
| | 01:58 | So we can certainly adjust the size
of that quickly, easily if you wish.
| | 02:02 | Now sometimes you do one information
below. Maybe we're going to put a main title
| | 02:08 | up above that possibly names the
company or the year, and sometimes it's just a
| | 02:12 | tossup of what works better for you.
| | 02:15 | In many, many charts you will not
see a title below the data, but why not?
| | 02:19 | In the example here, once again, if we go
to the Layout tab > Axis Title, this time
| | 02:25 | Primary Horizontal Axis Title,
| | 02:27 | fewer choices here. Title Below Axis or None.
| | 02:31 | Well, we do want a title let's say right here.
| | 02:34 | Maybe we want to put something in about
the company name or indicate that maybe
| | 02:39 | this represents three regions, or three
different parts of the world, whatever
| | 02:43 | it is that seems appropriate for us.
Maybe I'll just put in the example here
| | 02:46 | '2011 - Total Sales'.
| | 02:52 | Enter, and we see the information here.
| | 02:55 | At a later time if we come back and
decide to either get rid of this, of course we
| | 02:59 | can just click on that
and press Delete like that.
| | 03:01 | I'm going to press Ctrl+Z to undo that.
| | 03:04 | At other times maybe we simply want to
change the wording, and many times it's
| | 03:08 | going to be faster, rather than
trying to edit this--but you certainly can.
| | 03:12 | For example, I can click down here and
type in the word Volume right in front of
| | 03:16 | Sales, something like that maybe.
That's appropriate. Or if not, another way if I
| | 03:21 | want to retype all of this, simply
come here, click on it, and jump into the
| | 03:26 | formula bar and start typing something.
| | 03:28 | Actually, you don't even have to go into
the formula bar. You can just start retyping.
| | 03:32 | So maybe I want to reverse the order of this.
| | 03:34 | I just want to say 'Sales 2011',
Enter, and we see how that works.
| | 03:40 | So, quite a few different ways to adjust titles.
| | 03:43 | They are important here and there,
| | 03:44 | the horizontal and vertical titles on the chart.
| | 03:46 | You can add them, remove them, or
alter them either by the typing method you
| | 03:50 | just saw or primarily through the
Layout tab, the choice called Axis Titles.
| | Collapse this transcript |
| Linking titles to content| 00:00 | When you add titles to a chart, whether
it's the main title or any other titles
| | 00:04 | on the left-hand side of the plot
area, or below the plot area, instead of
| | 00:08 | typing the various titles that you need,
you can link chart titles directly to
| | 00:13 | specific worksheet cells.
| | 00:15 | Maybe for the main title on this
particular chart we want to use the title
| | 00:18 | that we see in cell A1.
| | 00:20 | It's a centered entry that
says 'Two Trees Olive Oil Company'.
| | 00:24 | So let's suppose we want to add a title here.
| | 00:27 | Chart Title on the Layout tab, we'll
click the option and choose Centered
| | 00:33 | Overlay Title like this. And at this
point simply type equal--and you'll see this
| | 00:39 | in the formula bar, by the way--click
on cell A1 or anywhere on the panel up
| | 00:43 | there since that's a centered title--
click here, for example--and simply press
| | 00:47 | Enter, and the title goes
right into place there.
| | 00:51 | Similarly with other titles that we
might need. We need to explain what these
| | 00:54 | numbers are; someone's
looking at the chart only.
| | 00:57 | So similarly, on the Layout tab in the
Ribbon, we'll choose Axis Titles > the
| | 01:02 | Primary Vertical Axis Title, perhaps rotated.
| | 01:05 | That's the most appropriate one here.
And similarly, equal, and this time we'll click on
| | 01:11 | cell A2 to pick up that information and simply
press Enter, and we see what's happening here.
| | 01:17 | So that's a good quick fix in both
examples for getting titles into charts
| | 01:21 | without to having to use any typing whatsoever.
| | 01:24 | Now I'm going to press Ctrl+Z a couple
of times to take out these titles and
| | 01:29 | remind you that also you can create
titles from the Design tab by choosing one
| | 01:35 | of the chart layout
options that contain titles.
| | 01:38 | So, for example, this choice has a main title.
| | 01:41 | This one has a main title
and a title on the left.
| | 01:43 | Perhaps, we'll go with this one.
It has some other features as well.
| | 01:46 | So if you were to choose this option,
you would have the same situation.
| | 01:49 | So how do we add the chart titles this
way using the technique just demonstrated?
| | 01:53 | This time simply click near or on
Chart Title and type =, click cell A1,
| | 02:01 | Enter, and we see the title appear there.
| | 02:04 | Similarly, on the left-hand side, click
Axis Titles, type =, click on cell
| | 02:10 | A2, and press Enter, and we
have the entries going in this way.
| | 02:15 | That one probably needs to be
dragged around a bit to be adjusted, but the
| | 02:18 | main idea here of course is we don't
type the entries, we simply pick them up
| | 02:23 | from cell references.
| | 02:25 | So you can save a good deal of typing
time and improve accuracy by linking
| | 02:29 | titles to worksheet cells.
| | Collapse this transcript |
| Showing numbers of different scales| 00:00 | With mixed data containing widely
divergent values, like we see here, charts can
| | 00:05 | appear distorted and illegible. Now we
haven't seen the chart just yet, but take
| | 00:09 | a look at those numbers in columns B
and C. They're pretty far apart and trying
| | 00:13 | to show both of those in the same charts is
going to be a little strange unless we adjust this.
| | 00:18 | They're used to be a feature in prior
versions of Excel called a Combination
| | 00:22 | Chart and what we're about to see
is in fact a replacement of it.
| | 00:26 | Let's imagine you're in a hurry.
| | 00:27 | You wanted to see this
information depicted as a chart.
| | 00:30 | It's all contiguous.
| | 00:31 | There is no other information nearby.
We could simply click on a single cell in
| | 00:35 | here and press Alt+F1, and we're going to
get a default column chart. There it is.
| | 00:40 | Right away of course you would be a
little dismayed. If you look very, very
| | 00:43 | closely--and it's perhaps not
viewable as you're watching it--
| | 00:46 | the miles per gallon in this chart are
showing up in very faint red columns at
| | 00:51 | the bottom, just above the months here.
| | 00:53 | We can't see them, and of course when
you look at the numbers you begin to think
| | 00:57 | about it, well, that makes sense.
| | 00:58 | We've got columns here representing
numbers almost as high as 9,000, and the
| | 01:03 | miles per gallon are the
numbers in the 20s and 30s, and so on.
| | 01:07 | So what do we need to do here?
| | 01:09 | Ideally, we like to change those so
that they're taller columns, but come to
| | 01:13 | think of it, how can we read those?
| | 01:15 | What we actually have to do
is to select the other range.
| | 01:19 | Now this is one of the rare times when
you can't click a range to make a change.
| | 01:23 | The general rule of thumb throughout
Excel is if you want to change something,
| | 01:27 | you click it, and then you make a menu
choice that will allow you to change it.
| | 01:31 | So, if we click on this column, of course
we could then explore a lot of different
| | 01:35 | Layout options, Design options, Format
options, but how do we click on miles per gallon?
| | 01:39 | We might click down here and not quite get there.
| | 01:42 | So this is one of those rare times
when we need to go to the Layout tab, the
| | 01:46 | extreme left button. The group is called
Current Selection and you'll see a drop
| | 01:52 | arrow, and it may or may not say Plot Area.
| | 01:54 | It could say something else, but when
you click the drop arrow, you will, in
| | 01:58 | effect, see all selectable elements in this
chart, and what we're looking for this series MPG,
| | 02:05 | so let's click it. And now it looks a
little funny here, we've selected that series.
| | 02:10 | So what do we want to do with this now?
| | 02:13 | We want to format the current selection.
| | 02:16 | So on the left-hand side there, under
Current Selection, we've chosen the MPG.
| | 02:21 | Let's do Format Selection and now--and
maybe by default, but maybe not so obvious
| | 02:27 | the first time around--we want to
plot this series on a secondary axis.
| | 02:31 | Let me move this over a little bit.
| | 02:32 | We're talking about on the right-hand
side of the chart to have a different axis
| | 02:37 | that'll allow us to read the miles per gallon.
| | 02:39 | Secondary axis, and as soon as we do
that, we see what's happening on the screen there.
| | 02:44 | The miles per gallon are now
represented by red columns, and we read those off
| | 02:49 | the secondary axis, which
is on the right-hand side.
| | 02:53 | But I think it's pretty apparent
| | 02:54 | this isn't quite where we want to be just yet.
| | 02:56 | What happened in October, for
example, November, December?
| | 02:59 | We can read the miles per gallon,
but we can't read the miles driven.
| | 03:04 | So, the next step here is to actually take
the miles per gallon series, which is
| | 03:09 | currently selected, and let's change
the chart type to be a line chart for
| | 03:15 | the miles per gallon.
| | 03:17 | So on the Design tab in the Ribbon,
leftmost choice, Change Chart Type, and let's
| | 03:24 | simply choose Line with markers,
this one right here.
| | 03:27 | You can double-click this to make
it slightly faster. There we go.
| | 03:30 | Now we're pretty much in shape.
| | 03:32 | We don't need the Format
Chart Area dialog box anymore.
| | 03:35 | It may require just a brief explanation,
but we can see now for this set of data
| | 03:40 | here the miles driven each month, which
presumably represents cars, trucks,
| | 03:44 | deliver trucks, whatever.
| | 03:45 | We see that being represented by the
blue columns, and we are reading off the
| | 03:49 | left-hand primary vertical axis.
| | 03:52 | If we're trying to figure out what the
miles per gallon are for a given month,
| | 03:56 | we read the red markers that are
associated with the red line, miles per
| | 04:00 | gallon, but we're reading off the right
side for the secondary vertical axis and
| | 04:05 | we see in the legend on the
right-hand side what's happening.
| | 04:07 | There will be times when you need to
track data of widely different scope, as we
| | 04:12 | see here, and this is one way to achieve this.
| | 04:14 | Now, you had been using the Combination
Choice in prior versions of Excel. This is
| | 04:19 | in effect its replacement.
| | 04:20 | So we don't see the word 'combination'
here at all anymore, but the technique we
| | 04:24 | went through here is fairly fast and
efficient, and you can mix different kinds
| | 04:27 | of chart types as well, too.
| | 04:29 | I think the most common kind of mixing
of chart types within a single chart is
| | 04:34 | the mix of column and
line the way we see it here.
| | Collapse this transcript |
| Specifying the position of tick marks and axis labels| 00:00 | When tick marks--now these are the little
marks that we see on various axes in charts.
| | 00:05 | In this particular chart, on the horizontal axis
at the bottom of the screen we see tick marks.
| | 00:10 | I think many of us probably ignore
these most of the time, but sometimes they
| | 00:14 | obscure chart elements, or they're not
prominent enough. You can choose from a
| | 00:18 | large set of tick-mark
placement choices from the Layout tab.
| | 00:22 | So let's select this particular axis
here, and if you're having trouble clicking
| | 00:27 | on it--and sometimes is that happens with charts,
| | 00:29 | they're a little bit tricky as to
how you might actually select an item--
| | 00:33 | on the Layout tab, in the Current
Selection group on the left-hand side
| | 00:37 | remember there is a drop arrow here,
Horizontal Axis if you haven't chosen it
| | 00:42 | manually. Then choose that.
| | 00:44 | Then let's go right below it
in and choose Format Selection.
| | 00:47 | A lot of choices in this dialog box,
but let's focus on Major tick mark type.
| | 00:54 | Currently it's Cross, meaning
the tick marks across the axis.
| | 00:58 | Another choice is Inside.
| | 01:00 | You see how the tick marks appear this way,
and another choice of course Outside.
| | 01:05 | We will see them below this.
| | 01:07 | Now many times I think it doesn't make
a lot of difference, but suppose we go
| | 01:10 | back to Cross. Sometimes the way
these line up or don't line up with points
| | 01:15 | above them, you might want to
consider making a different choice.
| | 01:19 | Position Axis, On tick
marks, Between tick marks.
| | 01:23 | Many times we don't care, but
certainly one time when you might is if, for
| | 01:27 | example, in this chart here
you were to add drop lines.
| | 01:30 | Now that's covered in another movie,
but I just want to point out that
| | 01:33 | sometimes in line charts and in area
charts it makes sense to show under
| | 01:38 | analysis Lines > Drop Lines.
| | 01:41 | So I'll just throw this in right here.
| | 01:43 | You would probably want those
to line up with the tick marks.
| | 01:45 | So in that situation going back to
Format Data Series here if we simply clicked
| | 01:50 | that Horizontal Axis again,
these choices come back.
| | 01:54 | Then Position Axis at the bottom On tick marks.
| | 01:58 | Click here and we see what's happening there.
| | 02:00 | Now you also have tick
marks on the vertical axis too.
| | 02:02 | In the example here in this particular
worksheet these don't really make a lot
| | 02:06 | of choices, but we see tick
marks over here as well, too.
| | 02:10 | Here they are accompanied along with gridlines.
| | 02:12 | So that's a different choice.
| | 02:14 | So here the option doesn't really
make any difference at all if you have
| | 02:17 | gridlines. You see how the Inside
Outside is having slight impact on the
| | 02:21 | Inside, but not on Outside at all.
| | 02:24 | In other words, we see them
there and we see them with Cross.
| | 02:26 | Those two are identical.
| | 02:28 | So anytime you're working with
charts that have axes and the tick marks
| | 02:32 | do become important to you related to
other features that you might have in a chart,
| | 02:36 | you do have options both for horizontal
and vertical axes to control tick marks.
| | Collapse this transcript |
| Changing the numeric format on labels| 00:00 | As you look at the chart on this
worksheet, you would recognize almost
| | 00:03 | immediately that the numbers in column B,
which are in the millions, are reflected
| | 00:08 | in the chart along the
vertical axis on the left-hand side.
| | 00:11 | As a general rule, the numbers on an
axis in an Excel chart are in sync with the
| | 00:17 | values, but maybe not quite in
the way that you might think.
| | 00:20 | There are times when you want a
different format on the chart than you actually
| | 00:23 | see within the data.
| | 00:25 | Now, let me point out if I do change the data,
| | 00:28 | for example if I click column B here
right now in this worksheet and on the
| | 00:32 | Home tab if I simply apply the Dollar
sign, it's going to change not only the
| | 00:37 | format of the data in column B, but also
on the chart, and we see what's happened.
| | 00:42 | We see Dollar signs and decimal
places in column B as well as on the chart.
| | 00:47 | But you can't make the reverse assumption.
| | 00:49 | In other words, we can and we
sometimes want to have a different format on a
| | 00:53 | chart than we do in the actual data.
| | 00:56 | Now I'm not a big fan of the
formatting in column B and you might not be
| | 00:59 | either, but let's say that we do have that
format and we would like to change the actual chart.
| | 01:05 | Let's just click on the chart axis here.
| | 01:07 | On the Layout tab in the Ribbon, if you
have chosen it that way or possibly you
| | 01:11 | clicked the drop arrow here to make
the choice, the Vertical Axis choice,
| | 01:15 | you can then go right into Format Selection--
| | 01:17 | this is one of a few ways to get here--
and the Format Axis dialog box pops up.
| | 01:23 | One of the choices over on
left-hand side is Number.
| | 01:26 | Now maybe we're not sure what we want, but
let's say that we don't want decimal places.
| | 01:30 | Maybe we'll just stick with Accounting
here but not have 2 decimals. How about 0?
| | 01:35 | As I close this, you see what happens.
| | 01:39 | We don't have decimal places, but we
still have that in the original data.
| | 01:42 | So you could say in a certain sense this
is no longer in sync with the data, but
| | 01:46 | it certainly is reflective
of the information at hand.
| | 01:49 | I think you could easily say, "Well yeah,
but do we really need to see all the zeros?
| | 01:54 | What if we insert some text eventually
that says these are in millions of dollars?
| | 01:58 | Why don't we display this
simply as 25, 20, 15, et cetera?"
| | 02:02 | Skip the Dollar sign or not.
We will decide on that possibly.
| | 02:06 | Another way to get into formatting here
is simply to right-click the selection.
| | 02:11 | So we have selected the axis already, so
right-click > Format Axis, and we are
| | 02:18 | in the same dialog box we saw before.
| | 02:20 | A third way to get here, as I close this, is
simply from a selected axis, press Ctrl+1.
| | 02:26 | Now normally within an Excel worksheet
that activates the Format dialog box.
| | 02:32 | So it does here too, in a different sense.
| | 02:35 | So here too we want to go to the
Number tab within the Format Axis dialog box,
| | 02:41 | and let's choose a custom format.
| | 02:43 | Now you might or might not know that
there is a way to display values in millions.
| | 02:47 | By the way, when you first come in
here sometimes you'll get some bizarre
| | 02:51 | temporary format changes that in this
case of course we're not going to keep, but
| | 02:55 | we're seeing dates over
there that looks strange.
| | 02:57 | But let's choose, for starters here,
Number, or possibly Currency or
| | 03:02 | Accounting, and then shift over to Custom.
| | 03:06 | Now we can start almost anywhere here.
| | 03:08 | I'm going to choose one
without Dollar signs, say this one.
| | 03:11 | If we are never going to be using
negatives, we can simply get rid of
| | 03:14 | this portion of it.
| | 03:15 | You don't have to be a formatting wiz to
necessarily know how to do this, but the
| | 03:19 | next step is certainly an unusual one.
| | 03:21 | To display numbers in thousands, you
either end the display in 0 or .0 and then
| | 03:27 | put in one trailing comma.
| | 03:29 | So if we were to add this as a custom
format, we will be seeing the numbers on
| | 03:33 | the left-hand side in that
vertical axis, 25,000, 20,000, et cetera.
| | 03:38 | That's not what we want. We want millions,
| | 03:40 | so we put in another comma.
| | 03:42 | So each comma suppresses the display of
three characters to the left of the decimal.
| | 03:47 | Now that's, again, a far cry from
being intuitive, but this will work.
| | 03:52 | We need to add this, and you see
immediately what's happened. Click close.
| | 03:56 | Now you might have wanted
the Dollar sign in there.
| | 03:58 | I might put a Dollar sign
in front of this right here.
| | 04:00 | You can certainly do that.
| | 04:02 | If that's important, add that.
| | 04:04 | We see what's happening. Click Close.
| | 04:06 | Of course, what you might also want to
do is to, by way of Axis Title, put in a
| | 04:10 | title there to accompany that
so that would make sense too.
| | 04:13 | But don't forget this axis here as well.
| | 04:16 | Right now, for the moment, we're
displaying exactly what we're seeing in column A,
| | 04:19 | and that might be fine, and I'm not
suggesting that we always change these, but
| | 04:22 | keep in mind here, too, by simply right-
clicking here, that might be as fast as
| | 04:27 | any way as we suggested. Go to Format
Axis, Number, and of course this time the
| | 04:32 | focus would be on one of the Date options.
| | 04:34 | So you pick the one that makes sense to
you, and there are quite a few choices there.
| | 04:37 | And here too you might jump into Custom
and if you're familiar with how to use
| | 04:42 | Ms and Ds and Ys, there are any
number of different kinds of formats,
| | 04:46 | particularly if you start with Date
here and then shift over to Custom.
| | 04:50 | You can make up your own if you
wanted to, something like this.
| | 04:53 | So maybe we will put in 3 m's, and again
I'm not suggesting in this case it would
| | 04:56 | be as necessary as it might have been.
| | 04:58 | If you want to put in the first day
of each month, how about mmm-d- and if
| | 05:04 | you want 4-digit years, four y's.
| | 05:08 | You see what's happening in the display.
| | 05:10 | So quite a few different ways to
approach this, but remember, even though in
| | 05:14 | general we think of the values on
the axes in a chart to be reflective of
| | 05:19 | the data, and they are,
| | 05:20 | they're not always exactly the same format.
| | 05:22 | And you can change the format of the
data on the axes in its chart simply by
| | 05:26 | right-clicking and going to Format
Axis and making the change that way,
| | 05:30 | independent of the data in the worksheet.
| | Collapse this transcript |
| Adding, editing, and removing legends| 00:00 | Although most charts should contain
legends to help explain what the chart
| | 00:04 | elements refer to, there are times when
you want to remove a legend, particularly if
| | 00:08 | there is only one series--you might have
that same information embedded in a title.
| | 00:13 | You also need to know how to edit
a legend, how to move it around, or
| | 00:16 | reposition it anywhere on the chart you might
wish, or possibly put it under or above the chart.
| | 00:21 | There are a number of different ways to do this.
| | 00:23 | You do have control over the legend.
| | 00:25 | In the chart that we are seeing right
here, a legend is in it's most customary
| | 00:29 | position on the right-hand side of a chart.
| | 00:31 | Now remember, when you do select a
chart you have an option here are on the
| | 00:36 | Layout tab, if you go to Legend--
| | 00:39 | it's in the Labels group here--to
reposition the legend at various locations here.
| | 00:44 | This is certainly one
approach. Let's put it on top.
| | 00:46 | See how that looks.
Maybe that looks pretty good,
| | 00:48 | so we'll come back up here again,
| | 00:50 | Show Legend at left.
| | 00:53 | Less common by far, Bottom, and
Overlay legend, don't forget these two.
| | 00:59 | I think most of the times you're not
going to want these, but you can easily make
| | 01:02 | sense out of what these choices give us.
| | 01:04 | I am going to put it on top this time. There.
| | 01:07 | Now no matter where it is, there
certainly are times we say, well, I kind of like
| | 01:11 | it there, but maybe I want to put a
title on top of it. Can we move this?
| | 01:14 | As you point at any of the edges, you
can just--well, I will drag here maybe.
| | 01:18 | Now you see that's overlapping the
gridlines and when you click outside of it,
| | 01:21 | maybe that's not so bad, but while
we are here you could possibly also
| | 01:25 | right-click on it and then format the legend.
| | 01:29 | Possibly give it a background color.
| | 01:31 | You could fill it there.
| | 01:32 | Lots of options here.
| | 01:33 | We could choose maybe just a solid
fill. Maybe that's a good color, maybe
| | 01:37 | not, but you can certainly make some
other choices here and proceed with the
| | 01:41 | legend looking like that.
| | 01:42 | We don't always need to reformat.
| | 01:45 | Also, you can resize this by
dragging one of its corners.
| | 01:48 | Now again, I am not saying this is better,
but if we make it narrower, it's going
| | 01:51 | to wrap the legend this way or that way.
| | 01:54 | Sometimes either you mistakenly got
rid of a legend or for whatever reason
| | 01:58 | maybe you did want to get rid of it
temporarily or something like that.
| | 02:01 | You can easily bring back the legend
not only by a way of the Legend button
| | 02:05 | here in this Labels group on the
Layout tab, but also on the Design tab of the
| | 02:11 | Ribbon, Chart Layouts.
| | 02:13 | So here's a legend on the right.
| | 02:15 | This technique does give us the
capability of setting up other features as
| | 02:18 | well, primarily titles.
| | 02:20 | So here is a legend with
a title on top, and so on.
| | 02:22 | Quite a few choices here and five of
these, at least five, are showing a legend on
| | 02:26 | the right-hand side.
| | 02:28 | Again, the most common choice, but if
you like this one with the legend on top--
| | 02:32 | actually this one doesn't have a legend.
It has it buried on the right with text.
| | 02:35 | That's not the one we want.
| | 02:37 | If you're familiar with this and have
used this, of course this is a way to go.
| | 02:41 | Maybe we will start here.
| | 02:42 | So quite a few different choices to
get the legend in place, and remember no
| | 02:45 | matter where it is, you can
move it around your own way.
| | 02:48 | If you do move it as I'm doing it here,
do not expect the plot area to change.
| | 02:53 | You could certainly leave
this open space on the right.
| | 02:56 | If you click inside the plot area here,
which is a little tricky to do with
| | 03:00 | all these grid lines, and
| | 03:01 | you might have to go to the
Layout tab to do this, Plot area,
| | 03:05 | you could resize the plot area manually.
| | 03:08 | So I am not suggesting
| | 03:09 | this is a make-work project, but you do
have control over the placement of the legend.
| | 03:13 | You can put it anywhere you want,
and it's easy to add it, take it out,
| | 03:16 | reposition it, as necessary.
| | Collapse this transcript |
| Adding and editing data labels| 00:00 | In certain charts, for example the
stacked column chart that we are viewing here,
| | 00:04 | it might be important to see the
actual values of each segment of the columns
| | 00:09 | that we are looking at here.
| | 00:11 | This green they we're seeing for August,
| | 00:13 | now the data is nearby, we
could certainly figure this out.
| | 00:15 | But would it be valuable to
see the numbers right here?
| | 00:17 | What we are talking about is
a feature called data labels.
| | 00:21 | And you might want to add data labels
for each point within a line or column or
| | 00:27 | bar or each sector in a pie chart even.
| | 00:30 | You can do this for an entire series.
| | 00:32 | We could do this for all the green bars.
| | 00:34 | We could do this for all of the bars
here, the greens, the reds, and the blues.
| | 00:38 | I think that would be a little crowded here.
| | 00:41 | Or we can do this for
selected points here and there.
| | 00:43 | So it all depends upon your needs and
whether we are talking about bar, columns.
| | 00:47 | It works on nearly all the
different chart types within Excel.
| | 00:50 | So let's try these data labels here
and without selecting any particular
| | 00:55 | portion of the columns, let's simply
go to the Layout tab in the Ribbon and
| | 01:00 | choose Data Labels.
| | 01:01 | We are not seeing choices that
relate to the actual series of data.
| | 01:05 | We simply see Center,
Inside End, and Inside Base.
| | 01:08 | Let's just try one of these, for
example, Center. Watch the chart.
| | 01:12 | I think for a lot of us maybe that's
too much information, but there's no doubt
| | 01:16 | as to what each color segment
means in terms of its value.
| | 01:19 | Let's try a variation on this with Data Labels.
| | 01:22 | Inside End, slight shifting of the data,
and Inside Base, shifting the data downward.
| | 01:30 | And let me, with these features in
place, change the design of this particular
| | 01:34 | chart here to the Chart Type on the
Design tab, left button, Chart Type.
| | 01:40 | Let's make it a standard
clustered column. Double-click.
| | 01:43 | And of course totally unacceptable.
Maybe in this example here we'll go back to
| | 01:47 | the Layout tab, choose Data Labels, and
choose Center, and I think you can see
| | 01:53 | that's pretty bad too.
| | 01:55 | Now if we had only 6 months of data--
let's quickly change that over on the left-hand side--
| | 02:00 | this might look a little better.
| | 02:01 | Now obviously if you want to see all
your data, this isn't acceptable either.
| | 02:05 | But if we were showing this amount, maybe that
would be okay with only 3 months. That's not so bad.
| | 02:11 | So depending upon how much data and the
nature of its display and the different
| | 02:15 | charts, you might want to add these data labels.
| | 02:19 | Now let's remove them, and maybe we
want these just on the blue columns.
| | 02:24 | Now whether it's a stacked column or a
clustered column it wouldn't make any difference.
| | 02:28 | But if we simply want these on the
blue columns, like I'll click one of the
| | 02:32 | blues here, all of them will be selected,
| | 02:34 | the entire domestic series.
| | 02:37 | You can see the highlighting
over in the data has been selected.
| | 02:40 | Let's go to Data Labels now, and again
we are not seeing any changing in the
| | 02:43 | wording here, but we'll choose
Center and just see what happens.
| | 02:47 | Maybe that's not the best
choice here, although it's not bad.
| | 02:50 | How about Outside End.
| | 02:52 | Maybe that's a little bit better.
| | 02:54 | And I think you know what
happened if we tried this with the green.
| | 02:57 | We'd see them on top of each green bar.
| | 02:58 | It's going to get bit too crowded.
| | 03:00 | Now even this might be more than you
want. Let me go back here and well I'll
| | 03:04 | simply choose None.
| | 03:06 | I could choose Ctrl+Z in
this case to undo. Choose None.
| | 03:10 | Maybe you want to have
numbers on just certain columns.
| | 03:13 | Now you have to do these one at a time.
| | 03:15 | Maybe the large one here at the
end, we'll click this single point.
| | 03:18 | Now, for the moment every
element in this range is selected.
| | 03:21 | So pausing, clicking again--
| | 03:24 | there we go--we've selected only this point.
| | 03:26 | By the way, when you are doing this
too, if you are not sure that you've
| | 03:29 | selected just the one, on the Layout
tab, the extreme left choice would say
| | 03:33 | Series Domestic Point.
| | 03:36 | Now watch what happens if I click outside of
this and click on a blue bar again. What do we see?
| | 03:41 | The entire series. After a while it
becomes real obvious, but use that as
| | 03:46 | verification when you're selecting
one column, two columns, or a series.
| | 03:50 | I want to click one column again,
pause, click it again, so only the
| | 03:55 | December column is chosen.
| | 03:57 | Data Labels just for that one
possibly, and I'll choose Outside End.
| | 04:01 | And while we are here, would you
like that to be a bigger font?
| | 04:04 | Let's click this, and we could do this a
couple of different ways but maybe on the
| | 04:08 | Home tab, just jump over there.
| | 04:10 | And in the Font group here maybe we'll
make this a bigger font, 12, 14 whatever.
| | 04:14 | And make it bold, or you know, the
things you might want to do with it.
| | 04:18 | You can manually move this around, but
once again this could seem something
| | 04:21 | like a work project.
| | 04:23 | But if you are only doing one or two of
these occasionally, some of these manual
| | 04:26 | movements might make sense.
| | 04:28 | But there is a lot we can do from the
menu, and it certainly is going to make
| | 04:32 | sense at times to add data labels to
help explain or to accentuate chart data.
| | 04:37 | There's just a ton of options for doing this.
| | Collapse this transcript |
| Showing the source of a chart's data| 00:00 | Often when you're working with a
chart in Excel the data is nearby.
| | 00:04 | You do however have the capability of
creating a chart on a separate sheet.
| | 00:08 | And when you do and need to see the
data as well, you might consider adding
| | 00:12 | what's called a data table.
| | 00:14 | So, a couple of different options here.
| | 00:16 | There will be times with certain kinds of
charts, say that one we're looking at here,
| | 00:20 | adding a data table here, which I'm
about to do, probably doesn't help a lot.
| | 00:24 | I'll show you how to add it.
| | 00:26 | Simply select the chart and then on the
Layout tab in the Ribbon, let's choose
| | 00:32 | Data Table > Show Data Table. We can
show with or without the Legend keys.
| | 00:36 | Let's show with the Legend keys, since
for the moment in this chart we don't
| | 00:40 | have a legend, and you'll see what happens.
| | 00:42 | Now because the data is nearby, this
isn't providing us with you know a feature
| | 00:46 | that's absolutely required here.
| | 00:49 | But let's take a look at
another example, a line chart sheet.
| | 00:52 | There is one chart already here.
| | 00:53 | Maybe we want to take this data here and
create another chart. Put it under this.
| | 00:58 | And furthermore, even while looking at
this chart, as we scroll up and down--
| | 01:02 | you know we do have to scroll up
and down to see all of the data--
| | 01:05 | is it important to see the
data here and how might this look?
| | 01:08 | Well, let's take a look at it.
| | 01:09 | Click this chart, select the Layout tab
in the Ribbon, choose Data Table > Show
| | 01:16 | the Data Table with Legend Keys.
The legend here is superfluous.
| | 01:19 | We don't really need that.
| | 01:20 | But I think you can see, here the
data table is not a good choice.
| | 01:23 | We're not seeing the
value with the numbers here.
| | 01:26 | And in order to see them, we either
have to use a much smaller font or possibly
| | 01:30 | make this chart a lot wider, and even there,
| | 01:33 | not looking so good here.
| | 01:35 | Data table is not a good
choice for this kind of chart.
| | 01:38 | But there could be times when you
want to add a data table to a chart.
| | 01:41 | Maybe it's further down on the sheet,
and you want to see all the data.
| | 01:45 | With smaller numbers, this might make some sense.
| | 01:47 | Now what if you have a chart
that's on a completely separate sheet?
| | 01:51 | We might start with this data, or maybe I'll
go back to the very first sheet, Year Data.
| | 01:56 | I might want to keep this chart here.
| | 01:59 | I'll decide on the data table later,
but let's, for example, select this data,
| | 02:03 | and maybe we want to have a
chart on a separate sheet.
| | 02:06 | You can get that really
fast simply by pressing F11.
| | 02:10 | We now have this chart on the separate sheet.
| | 02:12 | And no matter what chart type it is, it
might make sense here to say, well, let's
| | 02:16 | have a data table with this chart,
| | 02:18 | so every time we look at this we don't
have to keep constantly going back to the
| | 02:22 | sheet that contains the original data.
| | 02:24 | So for some people using a data table
might be confined only to those charts
| | 02:29 | that are on separate sheets.
| | 02:30 | We'll go to the Layout tab, choose Data
Table > Show Data Table, this time maybe
| | 02:37 | without the Legend Keys since we already
have a legend, and there we see the data
| | 02:41 | table below the data.
| | 02:42 | And the numbers here, because
they're short, can all be seen.
| | 02:46 | If you want the legend to be included
then let's take it off this side. We'll
| | 02:49 | put the Legend on the
right side and press Delete.
| | 02:52 | The chart expands a bit.
| | 02:53 | We'll come back to Data Table again,
| | 02:55 | this time with the Legend Keys,
and you'll see the difference.
| | 02:59 | The colors are in the legend.
| | 03:01 | So there are certainly going to be
times where you want to use a data table.
| | 03:04 | You can emphasize a chart's source
data by showing the data right in the body
| | 03:08 | of the chart.
| | Collapse this transcript |
|
|
6. Layout Tab Options: Using Axes and GridlinesModifying axis scaling| 00:00 | When you're creating a chart,
Excel uses numbers on axes and it scales
| | 00:05 | them automatically.
| | 00:06 | In this particular chart here that shows
data from rows 4 and 5 on the worksheet
| | 00:11 | over between columns B and G, you can
readily see the highest value here; the
| | 00:15 | sales value for June is 330.
| | 00:18 | On the chart the highest value shown is 350.
| | 00:21 | Now if we alter the numbers and we
happen to be using certain larger numbers and
| | 00:25 | it depends upon the value of them,
Excel will automatically re-scale the chart
| | 00:29 | based on the data, and
sometimes the interval as well.
| | 00:32 | The intervals here are 50.
| | 00:34 | That's probably the reasonable interval.
| | 00:35 | It's not too crowded.
| | 00:37 | Makes the chart more readable, and
it's accompanied by gridlines as well.
| | 00:41 | I'm going to change the June value
here from 330 to 335, not a big change.
| | 00:46 | As I press Enter, watch the chart.
| | 00:49 | The interval is still the same, 50,
but now that chart tops off at 400.
| | 00:54 | Now, rather than playing the game to see
when this changes, you can bet that if I
| | 00:58 | change this to what, 390, is it
still going to keep the 400?
| | 01:01 | Well, I think so, and you might want
to experiment with that a little bit.
| | 01:05 | But I'm getting the point that you
sometimes do want to control this yourself.
| | 01:10 | And with 390 it jumps to 450,
maintains the 50 interval.
| | 01:14 | Let me take those numbers back again.
| | 01:16 | Sometimes you have a rationale for
saying, "On my chart I want more white
| | 01:21 | space above the columns."
| | 01:22 | Now you might be doing that because
you want to put a box in here, perhaps
| | 01:26 | a text box that explains something
unusual about the data or something
| | 01:30 | that's important to it.
| | 01:31 | And sometimes you just like to look of
it. Or maybe at other times you're trying
| | 01:35 | to match up with another chart that
shows similar data and its highest point is
| | 01:39 | 450 or 500, something like that.
| | 01:42 | So, if you simply right-click on one of the
axis numbers, you can go right into Format Axis.
| | 01:48 | From the Layout tab, this will take
considerably longer. I just want to point
| | 01:52 | out to you that you can't get to this
by way of a Layout tab, and I'll show you
| | 01:56 | how cumbersome it is for this
particular use of a feature on this tab.
| | 02:00 | The Layout tab > Axes. This is a
Vertical Axis, and although some of these
| | 02:06 | choices occasionally might be what
you need, let's just say right now we're
| | 02:08 | not interested in that.
| | 02:09 | How about More Primary Vertical Axis
Options? And that takes us to this dialog box.
| | 02:16 | Once again getting here faster would
simply be a right-click on these choices
| | 02:21 | here and then Format Axis.
| | 02:23 | So we see a Maximum and a Minimum,
and let's just say that I want the
| | 02:27 | maximum here to be 500.
| | 02:28 | Here is the maximum.
Override the automatic setting.
| | 02:31 | Go to Fixed and let's just make this be 500.
| | 02:34 | Now I could be making other
choices here as well, and this will react.
| | 02:38 | I might jump up here a moment earlier.
| | 02:40 | Click here. Already the chart has reacted.
| | 02:42 | Maybe I'll go back to Auto here.
| | 02:44 | Sometimes you see the choices ahead of time.
| | 02:46 | But if that's all the choice you'd make,
you'd choose Close and see the difference.
| | 02:50 | Now that may or may not
be what you wanted to do.
| | 02:52 | You sometimes might want to change the interval.
| | 02:55 | Maybe I'm thinking well, you know
having these gridlines and numbers being
| | 02:59 | every 100, that would be fine.
| | 03:00 | So maybe another right-click here. I could
have done this first time around of course.
| | 03:04 | Let's just change the Major unit
here to be fixed and make it to be 100.
| | 03:10 | And of course sometimes you'll do
this because you don't like what the
| | 03:13 | automatic scaling is.
| | 03:15 | This time I don't care a whole lot,
but again, you have your reasons, and now we
| | 03:18 | see what's happening here.
| | 03:20 | So you've got control over this.
| | 03:21 | In the horizontal axis in this particular
example, there is no real reason to change that.
| | 03:26 | But similarly, with certain kinds of data,
you want to make changes there as well, too,
| | 03:31 | simply by right-clicking, going to
Format Axis, and you have similar choices
| | 03:35 | there with those kinds of axes, a
different set of choices, and you can control a
| | 03:39 | lot of variations there as well.
| | 03:42 | So there certainly are times, and for
a variety of reasons you might need to
| | 03:45 | adjust the axis scaling in a chart;
Excel provides you with numerous options.
| | Collapse this transcript |
| Working with gridlines| 00:00 | Gridlines are designed to improve
the readability of values in charts.
| | 00:04 | Now you can enhance them.
| | 00:05 | You can use major gridlines as well as
minor gridlines, and you can change the
| | 00:10 | color and/or thickness of lines,
and you should be careful when using gridlines
| | 00:15 | not to let the lines overwhelm the data.
| | 00:17 | It's a common mistake, or misuse of gridlines.
| | 00:20 | In this particular chart here,
major gridlines are in place.
| | 00:24 | Now to control these, you want to click
the Layout tab in the Ribbon and choose,
| | 00:30 | in the Axis group here, Gridlines >
Primary Horizontal Gridlines.
| | 00:35 | We can certainly choose None, which of course
for the moment, we don't have any gridlines.
| | 00:39 | We might want to show just major
gridlines. Showing just minor gridlines
| | 00:45 | probably isn't the choice you want.
| | 00:46 | I'll choose it for the moment just
to show you what that looks like.
| | 00:49 | That's probably more
information than most of us would need.
| | 00:52 | But here and there it might make sense if
it's important for you to read the values.
| | 00:57 | A better choice for that might be to
insert data labels where you see the actual
| | 01:01 | numbers next to the columns,
but this is certainly one option.
| | 01:04 | Let's look at a third option here under
Gridlines > Primary Horizontal > Major &
| | 01:10 | Minor Gridlines. And unlike in prior
versions of Excel charts, this essentially
| | 01:16 | does a differentiation for
us from our first application.
| | 01:20 | So you can see here the major
gridlines are slightly darker.
| | 01:24 | Actually, to be more precise,
they are black lines, whereas the minor
| | 01:27 | gridlines are gray.
| | 01:29 | Now, if that's a bit too strong, you
think they should be lighter, let just
| | 01:32 | right-click one of these minor
gridlines and then Format Gridlines and perhaps
| | 01:39 | choose a different color. And maybe we'll
just use an underline color here, choose
| | 01:44 | a Solid Line, and rather than the
suggested blue here, maybe pick a gray. Is this
| | 01:49 | is too light? We'll see. Pick this gray
and perhaps it is a bit lighter. Maybe
| | 01:54 | you want even want lighter than that.
| | 01:55 | In other words, you can experiment with this.
| | 01:57 | I'll choose even lighter one.
You can probably barely see that.
| | 02:00 | But it's batter to have
these to be not so dominant.
| | 02:04 | And if you think that major gridline
is a bit too strong, click it and the
| | 02:08 | dialog box that currently says
Format Minor Gridlines will change. Now it says
| | 02:13 | Format Major Gridlines.
| | 02:14 | So we could control the line
color there in a similar way.
| | 02:18 | So changing the color of the line
here and there will make some sense.
| | 02:21 | Now you want a slightly
darker gray. Earlier it was black.
| | 02:25 | Maybe that's strong enough.
| | 02:27 | An another option, although I wouldn't
necessarily suggest you need to go here,
| | 02:31 | is if you do pick the Line Style,
you'll see the width of the line.
| | 02:35 | So maybe sometimes simply making
the line a bit thicker might help.
| | 02:39 | Now, I make this a lot thicker, perhaps
that's too strong. But once again you've
| | 02:43 | got some leeway here, and you
can decide what looks best.
| | 02:47 | I would say don't overwhelm your chart
by making these too thick or making the
| | 02:51 | colors too dark, but at least
you have some control over these.
| | 02:55 | So we've changed the look of this chart.
We have minor gridlines. That may be
| | 02:58 | a tough read for you.
| | 02:59 | I can see these pretty clearly,
and you'll have to be the judge of how that
| | 03:02 | looks on paper once you print this.
| | 03:04 | Now in a different kind of chart--and
I'm switching sheets here, going over to
| | 03:08 | the City Sales sheet--it makes sense
sometimes to have vertical gridlines.
| | 03:13 | Now on the previous chart, the
vertical gridlines wouldn't have bought as
| | 03:16 | much, or given as much value, but they
certainly might here, and so in this chart,
| | 03:21 | which is a horizontal bar chart, let's
go to Layout tab > Gridlines > Primary
| | 03:27 | Vertical Gridlines.
| | 03:29 | Maybe Major & Minor, maybe just Major here.
| | 03:32 | Click this and see what's happening.
If it's important that we get a better read
| | 03:36 | on some of those bars that aren't
too close to these vertical lines,
| | 03:39 | then let's go back to Gridlines, Primary
Vertical Gridlines, and choose Major & Minor both.
| | 03:45 | So we're in similar territory as we
were with the previous chart, and it's just
| | 03:49 | the idea of how do we want to control these?
| | 03:51 | If you want the major ones to stand
out more, once again consider widening
| | 03:55 | or changing the major gridlines to a darker
color or possibly taking the minor gridlines.
| | 04:01 | In either case, you can right-click on
the appropriate lines, format, and make the
| | 04:05 | change that makes sense to you.
| | 04:07 | This time I'll use a lighter gray,
perhaps that one. There we go.
| | 04:10 | Now I can see these well. I'm not so
sure that's going to come through on the
| | 04:13 | transmission, but to me that's a lot
better than what we saw previously,
| | 04:17 | all based on the idea that the
gridlines help us read values within a chart.
| | Collapse this transcript |
|
|
7. Layout Tab Options: Using the Analysis ToolsAnalyzing existing and future data with trendlines| 00:00 | Excel has quite a few different
analytical tools, and one that's associated
| | 00:04 | specifically with charts
is what we call a trendline.
| | 00:08 | Now you don't have to be a statistical
expert to understand regression analysis,
| | 00:12 | or anything like that, although we
will get into that territory a bit.
| | 00:15 | If you're trying to analyze the
existing data and possibly even trying to make
| | 00:20 | predictions about where the sales
might head in the future, right-click on the
| | 00:24 | line chart entry--and you can also do
this with column charts as well--and
| | 00:29 | choose Add Trendline, and immediately
you'll see a line there. It's called a
| | 00:34 | linear regression line.
| | 00:36 | Now you probably at this point do need
to know a little bit about regression
| | 00:39 | analysis, and we are not going to be
talking about that in any depth here.
| | 00:42 | But if you're familiar with the idea of
regression, you might choose Exponential.
| | 00:47 | Usually that's a curved line.
| | 00:49 | Associated with this is also an
equation, and if you're familiar with why you
| | 00:54 | have an equation, you might want to
display that on the char. We see this option
| | 00:57 | down here, Display Equation on Chart.
| | 01:00 | Let me move the dialog box over.
| | 01:02 | There is the equation.
| | 01:04 | Sometimes the R-squared, which is a
rough measure of how accurate this might be,
| | 01:10 | and again long explanations as to how
that really works, but for those who are
| | 01:14 | familiar with these concepts, the
ability to create these quickly and easily has
| | 01:18 | some real merit to it.
| | 01:19 | I'm just going to choose Linear here.
| | 01:22 | And let me just suggest--and without
displaying the other characteristics here at
| | 01:26 | the bottom I'll uncheck those--
| | 01:28 | you can also, in addition to creating
this line, extend it into the future.
| | 01:32 | So I'm going to forward this, using the
forward box here, six periods forward, and
| | 01:38 | close. And again if I have the right
statistical background and am familiar enough
| | 01:43 | to suggest what has happened here, I can
also make a prediction as to where these
| | 01:48 | sales figures might be headed in future months.
| | 01:50 | Now, one feature here that doesn't
require a lot of statistical analysis is a
| | 01:56 | different kind of trendline.
| | 01:57 | So I'll again right-click here.
| | 01:59 | I could keep the current trendline
or possibly replace it with another.
| | 02:03 | So maybe we'll right-click on the line
here, format the trendline, and along the
| | 02:09 | way here change it to Moving Average.
| | 02:11 | Immediately we see a different line
here, and you'll see the number 2, so what
| | 02:16 | actually has happened here in the example?
| | 02:18 | A new line is being created.
| | 02:20 | It starts at the second point. And each
point here that we see on this new line
| | 02:25 | is the average of the
current entry and the previous one.
| | 02:29 | So, for example, the one right here
for Jun-09, this point right here is the
| | 02:33 | average of the point above and the
point for May-09 just to the left here.
| | 02:38 | And the idea behind these is that there
are some erratic occurrences in the data
| | 02:43 | here. It bounces up and down a lot.
| | 02:45 | So this line here is more of an average,
not exactly an average of everything,
| | 02:50 | but an average of what's
happening month to month.
| | 02:53 | You see these sometimes with stock quotes.
| | 02:55 | So I am going to change this to be a
three, and each time we do this, as we change
| | 03:00 | this box here, we get a smoother
line, and a smoother line, and so on.
| | 03:04 | So that's going to make some sense,
rather than creating a function or a long
| | 03:08 | formula to do the calculation,
| | 03:10 | you don't have to do this.
| | 03:11 | Now those of you who are
adept with Excel formulas know
| | 03:14 | that's not exactly difficult.
| | 03:15 | You could easily create the data for this.
| | 03:18 | But the trendline does it much faster,
and so creating a Moving Average could be
| | 03:22 | a more popular use of this feature.
| | 03:25 | So this is a feature that has some
sophistication to it. On the other hand, a
| | 03:28 | Moving Average a lot more common for a
lot of people, easy to get to, different
| | 03:33 | ways to create a trendline in working in
Excel. And I did say earlier, it doesn't
| | 03:37 | have to be a line chart. We could
easily change this to a column chart here, and
| | 03:41 | it might be appropriate as
well, and I will do that right now.
| | 03:45 | On the Design tab, Change Chart Type,
I'll make it a clustered column here to
| | 03:50 | see the data that way.
| | 03:51 | In fact, in this case maybe it sets off
the line more readily because we are not
| | 03:54 | looking at two lines,
| | 03:56 | we are looking at a line and a series
of columns. So, different uses of the
| | 03:59 | trendline capability with Excel charts.
| | Collapse this transcript |
| Adding drop lines | 00:00 | For chart series that have many data
points, perhaps like the line charge that
| | 00:04 | we're seeing here, you can add a
feature called drop lines that improve the
| | 00:08 | readability of the values here.
| | 00:10 | If we could drop lines from each
point here down to the axis, we can more
| | 00:14 | readily see which point is
associated with which time period.
| | 00:18 | So you don't even have to select the line;
just select the chart and then on the
| | 00:22 | Layout tab, the choice under Lines >
Drop Lines. We see what's happening there.
| | 00:28 | The more points you have, the
more likely this is to be useful.
| | 00:32 | Now what might be a little
annoying here is that the tick lines don't
| | 00:35 | necessarily match up with those, and
they look like they are sort of obtrusive.
| | 00:39 | You could either get rid of them
or make an adjustment here simply by
| | 00:42 | right-clicking anywhere down
on the horizontal axis here.
| | 00:46 | Right-click, go to Format Axis, and then
for the tick lines, how about Position
| | 00:51 | Axis, On tick marks? Watch the display
change. We'll see what's happening there.
| | 00:56 | In effect that got rid of them, or at
caused them to coincide with the drop lines.
| | 01:00 | So that's likely to be helpful there, too.
| | 01:02 | Now what if we add more data?
| | 01:04 | I'm actually going to go slide down a
bit on the screen here, and maybe we've got
| | 01:07 | data for 12 more months.
| | 01:09 | So I'm simply going to copy this data
or move it over into here, double-click to
| | 01:13 | make the column wider,
| | 01:15 | take this last entry here and with
the right mouse button I'll simply drag
| | 01:18 | downward to cover an additional time period.
| | 01:21 | We'll fill this with months. There we go.
| | 01:24 | And then clicking back up on the
chart, we still haven't expanded this.
| | 01:28 | Now we're already to expand into the new
area, the new set of numbers, and so now
| | 01:33 | we'll have even more values being
displayed up there. And I think you can begin
| | 01:36 | to see that these lines are even more valuable.
| | 01:39 | This feature, by the way, is only
available on two kinds of charts:
| | 01:42 | this kind of chart here, a line
chart, and also an area chart.
| | 01:46 | Let's take a look at an area chart.
| | 01:48 | We'll go to the Design tab in the Ribbon,
leftmost button, Change Chart Type, and
| | 01:54 | this time how about an Area Chart?
And just the first one right here will do
| | 01:58 | just fine. Double-click it
and we see that kind of choice.
| | 02:02 | That might be enhanced by
with different chart style.
| | 02:04 | It doesn't have to be necessarily say, this one.
| | 02:05 | But we can begin to see how that
makes these a bit more readable.
| | 02:09 | Now the values maybe aren't quite
what you want, and that's a different
| | 02:13 | characteristic and a different feature.
| | 02:14 | But the drop arrows here allow us to
associate the actual points with the months.
| | 02:19 | Now as a byproduct of this, and this
happens sometimes, notice how we are getting
| | 02:22 | every other month. That may or
may not be bothersome to you.
| | 02:25 | You can easily figure it out.
| | 02:26 | But sometimes the choice is either to
make this wider. At some point maybe
| | 02:31 | they'll kick in. Or if you didn't want to do
that, maybe we could change the font down here.
| | 02:36 | Just click here and then on the Home tab
use a smaller font. Instead of
| | 02:41 | 10, we'll use, for example, 9 and
then months are visible that way.
| | 02:44 | And a third thing we might do too is
possibly simply slant the text this way
| | 02:50 | counterclockwise. That might help as well.
| | 02:52 | But once again, see how
every other one appears now.
| | 02:55 | So you just have to kick that around
a little bit yourself to decide what
| | 02:57 | works best for you.
| | 02:59 | But the drop arrows certainly help in
certain kinds of charts, certain kinds of
| | 03:02 | line charts, or area charts, where
you're trying to be able to read where the
| | 03:07 | line actually points to.
| | 03:09 | Adding drop lines will improve the
readability of charts the display many
| | 03:13 | data points.
| | Collapse this transcript |
| Adding high-low lines and up-down bars| 00:00 | In two-dimensional line charts you might
want to compare the differences between
| | 00:04 | two separate series by connecting the
high and low points for each specific time
| | 00:09 | period, either with lines or with bars.
| | 00:12 | Both of these features are found in the
Analysis group in the Layout tab of the Ribbon.
| | 00:17 | In the line chart that we are looking
at, we are comparing Domestic and Latin
| | 00:20 | America, a bit erratic
here, the two sets of data.
| | 00:23 | They overlap at different times.
| | 00:24 | Let's point to the Lines button in the
Analysis group and choose High Low Lines
| | 00:31 | and see what happens.
| | 00:32 | So at a glance, you can begin to see
the relative differences in the two.
| | 00:36 | Now they are going in opposite
directions at different times, so we have to view
| | 00:39 | the information closely to see what's
happening, but the gap between the two
| | 00:43 | entries each month varies.
| | 00:45 | In no way would I be suggesting here
that these totals always have to be
| | 00:48 | overlapping in any way. In fact many,
many times, two series are if not parallel,
| | 00:53 | at least approximately parallel, and
sometimes you just want to compare the
| | 00:57 | height of these lines.
| | 00:58 | Another option instead of this--let's
take off the Lines, choose None--is under
| | 01:04 | the heading Up/Down Bars, show
Up/Down Bars on the Line Chart.
| | 01:09 | And you can see almost immediately
what's happening here. The four bars there
| | 01:13 | that are black represent situations
where the Latin American entries below
| | 01:18 | Domestic and the two white ones
represent data going in the opposite direction.
| | 01:22 | So it gives you a quick visual read on
these differences, and many, many times of
| | 01:26 | course when you are using this feature,
the boxes will all be of the same color.
| | 01:30 | So you can use High-Low Lines or
Up/Down Bars to show the differences between
| | 01:35 | series in two-dimensional line charts.
| | Collapse this transcript |
| Adding error bars| 00:00 | To show values above and/or below
points in a chart series, you can insert what
| | 00:06 | are called vertical error bars.
| | 00:08 | They can be based on percentages
or fixed values or even standard
| | 00:11 | deviation calculations.
| | 00:13 | And these work in line charts. We are
probably more likely to see them here, but
| | 00:17 | they also work in column and bar
charts, also in scatter charts and bubble
| | 00:21 | charts, but no other types.
| | 00:23 | So in the lower chart here--and I
think you'll find that these tend to work
| | 00:27 | better when you are working only
with the single series, although you can
| | 00:29 | use multiple series.
| | 00:31 | In this lower chart, let's insert some
error bars, so the chart has been selected.
| | 00:36 | Then in the Layout tab in the Ribbon,
in the Analysis Group, choose Error Bars,
| | 00:42 | and you'll see some options here.
| | 00:44 | Error Bars with Standard Deviation.
| | 00:46 | Now of course, we'd have to know
something about standard deviation to know what
| | 00:49 | this really means here, but the bars
that we see, and they are the same all the
| | 00:53 | way across, represent the
standard deviation of this series.
| | 00:57 | So the January point is outside the
lower standard deviation, and the June entry
| | 01:02 | is above the upper range
of the standard deviation.
| | 01:04 | You see what's happening
in the other entries as well.
| | 01:07 | Now that may not be what you need,
so we could go back to Error Bars and
| | 01:10 | possibly choose None, or maybe
choose a different option here.
| | 01:14 | Error Bars with percentage displays
error bars for the selected chart series
| | 01:19 | with 5% value, and we see what's happening there.
| | 01:22 | Now that's awfully tight,
and you can barely see it.
| | 01:25 | So possibly what we might want to do
here is change that to a different percent.
| | 01:29 | It all depends upon your needs.
| | 01:31 | Let's go back to the Error Bars
and choose More Error Bar Options.
| | 01:37 | This dialog box pops up, and we
see, based on we just chose here, that
| | 01:41 | the percentage is 5%.
| | 01:42 | Let's click this button and maybe what
we would like to see here are error bars
| | 01:47 | that are 25%, 20%, whatever makes sense to you.
| | 01:51 | Change that to 2 there, and just by
clicking somewhere else, maybe up here,
| | 01:56 | changing these, and go in the other
direction, we immediately see what's happening.
| | 02:00 | We could have also chosen Close here as well.
| | 02:02 | So we might want to experiment
with these numbers a little bit.
| | 02:05 | So the entries that we are about to
see--and they look little strange because
| | 02:08 | they are currently selected--
| | 02:09 | we now have a vertical bar here with a top
and bottom, and notice it says Cap or No Cap--
| | 02:14 | you can get rid of those if you
wish. And do we want these to go in both
| | 02:18 | directions or maybe just above?
| | 02:19 | I am going to click Plus, and now
they're just above the line--we see them here--
| | 02:24 | or Minus just below, or possibly both.
| | 02:28 | So at different times you
have the need to show these.
| | 02:30 | As I click Close here, we
will see these Error Bars.
| | 02:33 | And I have to click outside of it too.
| | 02:35 | So in each case--and the reason it's
lower here under January is is that we've
| | 02:40 | got a 25% value above
this entry and then below it.
| | 02:44 | Now some people like to actually
connect these lines here, and what you have to
| | 02:49 | do in cases like that is write
formulas, and these are already written. So in
| | 02:53 | cell B10, for example, right
here, this equal be 5 times .75,
| | 02:58 | this represents the lower value.
| | 03:00 | So it represents the bottom of this
line that we were seeing for January.
| | 03:04 | So by writing the formulas here--and this
is the one for the lower entry and here
| | 03:08 | is for the upper entry.
| | 03:10 | This is 25% above the 80. So that's
going to be actually 100. See how
| | 03:14 | that's matching up here.
| | 03:16 | If we want to show these values here in
this chart as well, that in effect will connect
| | 03:21 | these points. We can simply copy these values.
| | 03:24 | I'll press Ctrl+C here, and then on the
chart, right-click and Paste, and they go
| | 03:31 | right into the chart to match up with those.
| | 03:32 | I wouldn't call that a necessity, but
sometimes you do want to do that as well.
| | 03:36 | So after connecting these lines, you
may want to try some other options here.
| | 03:41 | Under the Error Bars capability,
| | 03:43 | it's on the Layout tab, and we can
explore some other options here or also go to
| | 03:48 | More Error Bars Options.
| | 03:50 | Inserting Error Bars in a series
might be used to convey uncertainty or an
| | 03:54 | amount associated with each point
in the data series as you choose.
| | Collapse this transcript |
|
|
8. Format Tab Options: Adding Shapes and WordArt StylesSelecting shape fill and outline| 00:00 | To improve readability or to spice
up a chart, you might want to select a
| | 00:04 | specific color and shading effect.
| | 00:07 | You can do this for columns and bars and
those kinds of charts, or in pie slices.
| | 00:11 | You can do it to the outer area of a chart,
the chart area, the inner area, the plot area.
| | 00:16 | You can do this for titles. Practically
any portion of a chart that encompasses
| | 00:21 | space possibly can have a shading
effect, and these choices are all found in
| | 00:27 | the Ribbon under Shapes Styles.
| | 00:30 | So, for example, the chart on left
here doesn't look that bad really.
| | 00:34 | The legend is somewhat oddly placed
for the moment because it's not in the
| | 00:37 | box, but at the outer area here, which I
just clicked on, is called the Chart area.
| | 00:42 | And on the Format tab in the Ribbon
there is a section called Shape Styles, and
| | 00:49 | there are choices right here that we see,
seven choices, but click the drop arrow
| | 00:54 | and suddenly we got quite a few.
And Excel's Live Preview is in effect so as I am
| | 00:58 | sliding over these and I am not clicking,
| | 01:00 | you see what's happening to the chart
area there, the outer area of a chart.
| | 01:05 | And as I sometimes say in these situations, if
you are an indecisive person well, good luck.
| | 01:10 | It's going to take your some time here.
| | 01:11 | So maybe we will just apply that color.
| | 01:13 | You can easily see how some people
are going to get sidetracked with all the
| | 01:16 | wealth of choices here.
| | 01:18 | Now that's the chart area.
| | 01:19 | The inner is the plot area.
| | 01:21 | We don't really have to change this, but
if we click the plot area and go up to
| | 01:25 | Shape Styles, we are now on the
path to changing that area as well.
| | 01:29 | Similarly, with a legend, and this one
is somewhat oddly placed, click near the
| | 01:34 | legend or on it and it to
has a rectangle around it
| | 01:38 | that isn't visible unless we put a color on it.
| | 01:41 | And you can see how we could go
overboard. We could do this for title as well,
| | 01:44 | and not to beat this to death, but just
so you get the idea here, maybe that will
| | 01:48 | look better in that kind of situation.
| | 01:50 | The chart to the right, same idea.
Maybe we'll do just the legends there.
| | 01:53 | So these are easy to get to, and the
color choices, by the way, are affected by
| | 01:58 | the theme that's in place
in this particular workbook.
| | 02:02 | So if this set of colors is not to your
liking, or if you are pulling together a
| | 02:07 | presentation and the other parts of
that presentation say in Word or PowerPoint
| | 02:12 | are using a different theme, you might
want to go to the Page Layout tab and
| | 02:17 | choose Themes and possibly pick another
theme. And as I'm sliding over these,
| | 02:22 | I am not clicking. You see what's
happening to various color choices.
| | 02:25 | I think you could easily see here that
you could get sidetracked worrying about
| | 02:30 | this color and that color.
| | 02:31 | Now on a one-by-one basis, you can
change this column, that column, this series,
| | 02:36 | a lot of work potentially here if
you let it happen, but lots of choices.
| | 02:41 | Primarily we're talking about the
choices that appear on the Format tab in
| | 02:45 | Charts, but momentarily here as we look
at these different themes, you could see
| | 02:48 | where this might take us.
| | 02:49 | I want to stick with the Office
theme, and so we're back here again.
| | 02:53 | So, easy choices. In any chart, format,
select the area that you'd like to change
| | 02:59 | and then go to Shape Styles. Lots of choices.
| | Collapse this transcript |
| Adding shape effects | 00:00 | To provide more variety and visual impact,
you can use the many shape outline and
| | 00:05 | shape effects such as glow,
3D, soft edges, reflection.
| | 00:10 | These are options on the Format tab that
you can apply to various chart elements.
| | 00:15 | When the Format tab is active, as it
is right now, if you were to go to Shape
| | 00:19 | Effects, suppose I want to make a
Shape Effect application to the title here.
| | 00:23 | I am going to click here and try one
of these, and I want to point this out
| | 00:27 | because I remember doing this a while ago,
trying to apply something called Glow
| | 00:32 | and I'm trying this and here are some
choices and nothing seems to be happening.
| | 00:37 | The reason it's not is that most of
these features here that we are talking
| | 00:40 | about, Shape Effects, Shape Outline,
are dependent upon the idea that you've
| | 00:44 | already applied a shape style.
| | 00:46 | So, let's click the drop arrow here and
maybe just pick one of these at random, say
| | 00:49 | this one right here.
| | 00:51 | Now, if we go to Shape Effects, we
could apply a glow here and Glow is one of
| | 00:56 | these features where I think a lot of
us thought, "Have we really needed this?
| | 00:59 | Has this been missing from our lives?"
| | 01:00 | Well, we've got lots of choices here, and
you see what's happening as I'm sliding
| | 01:04 | over these various choices here.
| | 01:06 | You see what it's doing to the title.
And used with discretion, and not in every
| | 01:11 | single example, or in every single
body of text, this might make some sense.
| | 01:15 | Again, we are talking about a little
bit of pizzazz here, a little bit of flare
| | 01:18 | that you want to provide on this
chart or that chart, something like that.
| | 01:22 | Clicking outside of this, by the
way, we are not seeing the border.
| | 01:25 | So, the Shape Outline maybe has
something to be said for it too, so that button,
| | 01:30 | Shape Outline, also in the same general
area on the Format tab, right above Shape
| | 01:34 | Effects. We can change the color of the outline.
| | 01:37 | For example, right now, we can see that
the box around the Two Trees Olive Oil
| | 01:41 | Company is sort of orange.
| | 01:43 | You could, then from there, if you
wanted to, probably some of those other
| | 01:46 | choices here, change the thickness of this.
| | 01:49 | Now, without suggesting
that these are all frivolous,
| | 01:51 | again, you can get a little carried away
with this. It's going to take up some time
| | 01:54 | as you try some of these various choices.
| | 01:57 | Keep in mind, some of these might be
applied to the inner plot area or the
| | 02:01 | outer chart area as well.
| | 02:03 | The example here, as I suggested earlier,
choose a Shape Style first and then
| | 02:08 | come in if you want to
explore some of these choices here.
| | 02:11 | Shadow probably wouldn't have much
impact here, and as you make the choices and
| | 02:15 | slide over these, you can see to the
left of where I am pointing right now,
| | 02:19 | look at the perimeter of the plot area.
Right now there is a shadow on all four edges.
| | 02:24 | If you want a shadow just in the upper
left, we will come down here, and you'll
| | 02:28 | see how the image shows the shadow
above and to the left, but not below into
| | 02:31 | the right, and so on.
| | 02:33 | And then there are inner shadows as well.
| | 02:35 | You can see all of these choices.
| | 02:37 | Things that maybe we didn't know we
needed or wanted, but they are here.
| | 02:41 | Depending upon what it is you've chosen,
not every possible choice is available here.
| | 02:46 | If I wanted to explore Reflection a
little bit, maybe I go over here on this
| | 02:50 | chart and choose the title.
| | 02:52 | Again, Reflection is not available to
us, so possibly we will make another
| | 02:57 | change, give it a color maybe. And what
happens now? Do we have--no, still no
| | 03:01 | Reflection available.
| | 03:02 | So you will be surprised here and
there as to when certain choices appear and
| | 03:07 | don't appear, and some of these are
available with different parts of a chart.
| | 03:12 | Shape Effects, Shape Outline effects,
all designed to make the chart more
| | 03:17 | appealing from your perspective.
| | Collapse this transcript |
| Applying WordArt styles| 00:00 | WordArt, which is ideal for
specialized title needs, is available, but in a
| | 00:05 | limited version, within charts.
| | 00:07 | You can use it to control text outlining
and filling, as well as applying special
| | 00:12 | effects like Shadow, Reflection, and Glow.
| | 00:14 | If you have used this feature in
prior versions, you might be a little
| | 00:18 | disappointed in some of
the uses of it in Excel 2007.
| | 00:20 | It's not quite a full-fledged
version as it was in the past.
| | 00:24 | The chart on the right has a title in
it, and it looks okay, but we might want to
| | 00:29 | make some changes to it, and not by way of shape
styles or shape effects, but by a way of WordArt.
| | 00:34 | So, let's just click the box here.
| | 00:36 | In the Ribbon on the Format
tab, we see WordArt Styles.
| | 00:41 | Now, this is one of those features--
and you can almost see it coming--
| | 00:44 | if I am going to press the down arrow
here, the choices are going to overlap
| | 00:48 | what I am seeing below.
| | 00:50 | Now I can see most of it, so maybe we
don't worry about it, but sometimes of
| | 00:53 | course, what we'll need to do is
maybe step back and scroll leftward or
| | 00:56 | rightward to get that chart maybe on
the left side of the screen as we
| | 00:59 | make these choices.
| | 01:00 | But here, as I'm sliding over these,
keep your eye on the text in the
| | 01:03 | background, just to the right there, Two
Trees Olive Company and see what could
| | 01:08 | be happening as I explore
some of these choices here.
| | 01:11 | Some of them, by the way, are not very
readable, and here and there I have my
| | 01:14 | favorites and certainly a lot of non-
favorites as well. And some of them have
| | 01:17 | other effects, like this one has a
Reflection effect, so we can do that.
| | 01:21 | Maybe we'll just pick one here like
this one, and since that doesn't have a
| | 01:25 | Reflection effect and maybe you'd like that idea,
| | 01:27 | it's still selected. Maybe click
the drop arrow right here, Text Effects >
| | 01:33 | Shadow and again, sliding over the
choices here, keep your eye on the text as you
| | 01:37 | explore some of the many, many options.
| | 01:40 | Here is Reflection and nine choices there.
| | 01:44 | I know for a while I was using this,
and then I realized that I was using it
| | 01:47 | way too much and decided I haven't
used it much since, but you have your
| | 01:51 | favorites and you pick this effect, that affect.
| | 01:53 | It's easy to apply, and again those
things that make the chart more appealing.
| | 01:58 | And like some other choices you might
have seen elsewhere, there are also Glow
| | 02:01 | and 24 possibilities there.
| | 02:04 | Some of these choices don't work with text.
| | 02:06 | Notice also that you do have the
ability to highlight a portion of the text, and
| | 02:11 | then when you make some of these choices here
| | 02:13 | you can apply features just to the
highlighted part or to all parts of it.
| | 02:17 | You can clear the WordArt.
| | 02:18 | I mean lots of things to
explore here in terms of effects.
| | 02:22 | Another choice over here.
| | 02:23 | You can go pick your own colors too, as if
you didn't have enough choice already here.
| | 02:28 | So, there are times when you want to
apply special effects to text areas of
| | 02:32 | charts, and I didn't mean to suggest
that you would use this only on main
| | 02:36 | titles, although I think there is, for
a lot of people, that's the main use of
| | 02:39 | WordArt, on the main title of a chart,
but you can use this in other text areas
| | 02:44 | of the chart as well.
| | 02:45 | Once again, it's that extra feature
that sometimes gives it some visual
| | 02:49 | flair that you like.
| | 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 sometimes you want to change them.
| | 00:07 | You can do it by adjusting the color of
the borders, the thickness, and the type
| | 00:12 | of border, whether it's solid or dashed--
| | 00:14 | a wealth of choices here.
| | 00:16 | Now, in this chapter, we will be
talking about quite a few different
| | 00:19 | formatting choices.
| | 00:21 | In general, it's a safe thing to say
these choices are found on the Format
| | 00:26 | button in the Ribbon.
| | 00:28 | However, even though you can start here
and get to a lot of features, many times
| | 00:32 | a quick way to begin formatting in
Excel 2007 charts is to right-click.
| | 00:38 | Now if you have been a chart user in
prior versions, you may have gone by the
| | 00:42 | almost mantra of saying anytime
you double-click a chart element, you
| | 00:46 | immediately get a dialog box.
| | 00:48 | That's not true in Excel 2007, but it
is safe to say anytime you right-click
| | 00:54 | on an element, you have the option to get
into some formatting tools quickly and easily.
| | 00:59 | So there is no box or border on this title here.
| | 01:03 | I might want to add it.
| | 01:04 | I might want to make some other changes to it.
| | 01:06 | Despite all those choices on the
Format tab, let's simply right-click on the
| | 01:11 | title anywhere in here, right-click.
And nearly always when you right-click on an
| | 01:16 | element it's the last choice that you
want, if you're looking for formatting.
| | 01:20 | You'll always see the word 'Format', and
then it's going to be followed by the
| | 01:24 | element that you happen
to have right-clicked upon.
| | 01:27 | Click Format Chart Title and
the relevant dialog box comes up.
| | 01:30 | Now let me close this and show you
another way too, and for some people it's
| | 01:34 | faster, although maybe you've switched
from mouse to keyboard in the process.
| | 01:39 | You might click the element first
and then press Ctrl+1, and you may well
| | 01:44 | know that Ctrl+1 does activate the
formatting dialog box in your standard use of Excel.
| | 01:49 | So here's another way to get here.
| | 01:51 | So in either case, either
by right-clicking on an element and choosing Format
| | 01:56 | followed by the phrase or words for the
element that you're choosing, you will
| | 02:00 | come to this dialog box.
| | 02:02 | Remember the other way to get here.
| | 02:04 | I'm not trying to confuse people, but I
do know that as you work with Excel and
| | 02:08 | you've got all these formatting choices,
| | 02:09 | it is a bit much to suggest there are
so many different ways to get here and
| | 02:13 | trying to come up with one way isn't
really the goal here, but just to alert you
| | 02:17 | to the idea of how many different
ways you can get to the same feature.
| | 02:22 | Another way would be we've chosen the
Format tab, we click the element that
| | 02:27 | we are interested in, and then the left group in
the Format tab, second choice, Format Selection.
| | 02:33 | Now, something else that you might
have seen in prior movies, and I'll
| | 02:37 | emphasize it here too:
| | 02:38 | sometimes you're working with an
element and working with title, so I might
| | 02:43 | want to change, in this case actually
add a border color, perhaps a solid line,
| | 02:49 | black is just fine.
| | 02:50 | The color you see here will be the one
that was used most recently by whoever
| | 02:54 | was using this feature on the
particular computer you're using.
| | 02:57 | So if I put in red here, I'm going to
have a red border, and you can't see that
| | 03:01 | too clearly just yet, but it's going to be red.
| | 03:03 | So I might want to change the Border
Style and make it thicker possibly.
| | 03:08 | As I'm doing this, you can see the
reaction a bit over on the left-hand side.
| | 03:12 | If that's not enough special features,
you can go into Dash type, maybe you
| | 03:16 | want this kind of a border, and on and on and
on, quite a few other choices here as well.
| | 03:21 | Now, maybe that's all you
want to do here for the moment.
| | 03:24 | If we click another element, we don't
need to close the dialog box, so maybe
| | 03:28 | I'll go to the Legend, click the
Legend. And as I click Legend, keep an eye on
| | 03:33 | this dialog box title says Format Chart Title.
| | 03:36 | I'll click the Legend and the dialog box, as
you might expect, switches to Format Legend.
| | 03:42 | So in this example maybe we want to
fill this with a color, maybe a solid
| | 03:47 | fill, a certain color, red, or whatever.
We've got all of these choices here as well, too.
| | 03:51 | So you can begin to see how easy it is.
| | 03:54 | You can control any of the elements
within a chart simply by right-clicking
| | 03:59 | and the appropriate dialog box will
appear after you've chosen the Format
| | 04:04 | choice, usually the last one, and the various
dialog boxes will always be context-sensitive.
| | 04:09 | Remember, if I click another area as
I'm doing now, left side, the dialog box
| | 04:13 | title changes again.
| | 04:15 | So, as you can see, there's any number
of different border color and styles
| | 04:20 | available, with a wealth of
options to control these appearances.
| | Collapse this transcript |
| Filling an area with a color gradient| 00:00 | You can fill columns, bars, pie slices,
chart areas, plot areas, and many other
| | 00:06 | nonlinear chart elements, in other
words those elements that encompass boxes or
| | 00:10 | spaces, with built-in and
your own created color gradients.
| | 00:15 | Once again, we're talking about the
capability of going beyond what you might
| | 00:18 | find in the Format tab in the Ribbon.
| | 00:22 | So, despite all of the choices that we
have here, we might want to change colors
| | 00:26 | based on our own needs.
| | 00:28 | So, for example, on the chart on the left,
maybe I want to change the chart area
| | 00:32 | color, the outer parameter.
| | 00:33 | So I'll simply right-click and go
immediately to Format Chart Area. And we can
| | 00:40 | use fill here, and perhaps a solid color,
lots of choice. Yellow was the last
| | 00:46 | one used by whoever was using this
feature last, but we've got all these choices
| | 00:51 | here and certainly others we can
get too as well under More Colors.
| | 00:55 | So certainly you can go down that path.
| | 00:57 | Another choice might be Gradient fill.
| | 01:01 | Again, the choice that comes up is one that was
used recently. But you can go to preset colors.
| | 01:06 | Once again, things we didn't know we
needed, but if this appeals to you, there it
| | 01:09 | is, and on and on and on with these choices,
| | 01:12 | rainbows of all different
hues and shapes, and so on.
| | 01:15 | Don't stop there necessarily.
| | 01:17 | Once you have done those, you can
control the direction of them, and I'm not
| | 01:21 | making a strong case for saying this is a
great choice here, but we all have our preferences.
| | 01:26 | I would never actually make this choice, but
it does show what's there, quite a few built in.
| | 01:31 | You can do the same thing for the plot area.
| | 01:33 | You can do the same thing for the legend.
| | 01:35 | You can do the same kind of
thing for the title as well:
| | 01:39 | either Solid fill, which is a little
more conservative perhaps, or Gradient fill,
| | 01:43 | as well as pictures and texture fills, which
was covered in another movie in this course.
| | 01:49 | So there are many, many choices here if
you're trying to fill in the colors in
| | 01:54 | any part of a chart that encompasses a
box, or at least a potential box, as we've
| | 01:58 | see in the example of titles, and
legends, chart areas, plot areas, et cetera--
| | 02:03 | lots of choices here for formatting.
| | 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
| | 00:05 | this in a number of different ways.
And in some sense, this illustrates the fact
| | 00:10 | that I think a lot of us, myself
included, become a little bewildered at times as
| | 00:14 | what's the best way to do things, because
they're so many different ways to approach this.
| | 00:18 | In the chart on the left are some
gridlines, and maybe we want to make some changes.
| | 00:23 | Now a lot of you know that in
creating gridlines many times where you would
| | 00:27 | start would be to go to the
Layout tab and choose Gridlines,
| | 00:32 | possibly apply them this way. And along
the way in that process you might come to
| | 00:36 | More Primary Horizontal
Gridline Options, makes some choices.
| | 00:40 | We can control Line Color and Line
Style, so there is certainly that approach.
| | 00:45 | When you created the chart you could
have gone to the Design tab, chosen Chart
| | 00:50 | Layouts, and some of the options
here have gridlines, some don't.
| | 00:55 | You could've start the gridline
process that way. How about format?
| | 00:59 | We could go to Format, and if you have
selected a gridline as I have here, we've got
| | 01:04 | some Shape Styles related to gridlines.
| | 01:06 | And if that weren't enough, if some
how you said I need to change these, or do
| | 01:10 | something to these gridlines in
terms of their appearance, we could
| | 01:13 | right-click on a gridline and go to Format
Gridlines, leading us to a dialog box we just saw.
| | 01:19 | There are so many different
ways to get into this process.
| | 01:22 | So let's just take the example here
where we have gridlines already, possibly.
| | 01:26 | Click this one. We've got some shape
styles, and of course you knew there are
| | 01:30 | going to be more then seven. We've got 21 of
them, and I would suggest the heavy ones
| | 01:35 | don't look good at all.
| | 01:36 | These overwhelm the data substantially.
| | 01:39 | Even these are a bit much.
| | 01:40 | So I would say use these sparingly, but
again, for some situations maybe these are
| | 01:45 | appropriate. You can certainly
make those choices that way.
| | 01:48 | If we were to right-click on these,
we can go to Format Gridlines and make
| | 01:54 | some choice here. Maybe the line style
will look better if we a dash type here,
| | 01:58 | and again these are similar to
features you might have seen previously, coming
| | 02:02 | at them from different angle
perhaps. And after making those choices, maybe
| | 02:06 | we'll say let's change the Vertical Axis on
the left-hand side, make it more dramatic perhaps.
| | 02:11 | We've got Axis Options. How about
Line Color? Solid line maybe, different
| | 02:17 | color, make it red maybe. How about Thickness?
| | 02:20 | Line Style, again features we
might've seen previously. We've got complete
| | 02:25 | control over all these, but the entry
to these can come from a variety of
| | 02:29 | different points, through the Design
tab at the time you are creating these lines,
| | 02:34 | through the Layout tab possibly to
adjust gridlines, through the Format tab or
| | 02:39 | simply by right-clicking. Lots of
different ways to achieve line color style
| | 02:44 | and weight changes.
| | Collapse this transcript |
| Working with chart text| 00:00 | You can control the style of a font,
size of the font, and all chart text within
| | 00:06 | a chart either all at once, in other
words all the text entries on a chart, or
| | 00:10 | simply one portion at a time.
| | 00:13 | Most of these shortcuts are available
by right-clicking, but even faster, much of
| | 00:18 | the time, will be standard tools that you're
familiar with on the Home tab of the Ribbon.
| | 00:22 | In the worksheet on the left, perhaps I
want to change the main title. I'm just
| | 00:25 | going to click on it, and the Home tab
of the Ribbons is active. I want to change
| | 00:30 | the font, possibly. It will overlap the
title, but I can still see here a portion of
| | 00:35 | it, and as I'm sliding over this
choices, you see the title just to the right
| | 00:38 | there changing based on these many,
many different font possibilities.
| | 00:43 | Once again the wealth of choices is
astounding here, and Excel's so-called Live Preview
| | 00:48 | really helps, so I'm not clicking and
we can get through this list pretty fast,
| | 00:51 | although we really don't get
through it, but just to see what some of
| | 00:55 | these choices offer us.
| | 00:56 | If we like this one, that one, we'll
stop, click it, fine. Something like that.
| | 01:01 | Now I changed just the main title,
didn't change the Legend. Possibly we could
| | 01:05 | over there and make it change to the
Legend, maybe something different over there,
| | 01:09 | maybe not. Same general idea of course.
I could change there, and similarly with
| | 01:14 | Size. And I can say this are great choices,
but anyway, just to make the point, if I
| | 01:20 | want the labels across the bottom to
be of different font--and I wouldn't
| | 01:24 | suggest multiple fonts is a good idea.
In fact, most books on Excel charting
| | 01:28 | suggest that you keep the fonts to
at most two variations within a chart,
| | 01:33 | certainly not 3, 4, or 5.
| | 01:35 | Now what if we wanted to change all
text within the chart, and we can then from
| | 01:41 | there change our mind about this
portion of it or that portion of it.
| | 01:45 | If you click in that portion of the
chart called Chart Area--that's the outer
| | 01:49 | portions, just click there--
| | 01:51 | if you make a font change, I'm going to
go back to Calibri here, that changed all
| | 01:56 | the text--top, bottom, left, right--on
this chart. And if I click the drop arrow
| | 02:01 | for the font size here. I'm going to
choose 16. As I'm sliding over these,
| | 02:05 | you'll see what's happening in the background.
| | 02:07 | I'm about to change all of these.
| | 02:10 | So sometimes you might use this as a
starting point. Maybe 16 is not quite right.
| | 02:14 | 14 looks pretty good for most of these.
But on the other hand, you want the main
| | 02:18 | title to be bigger. Click right here,
choose a different font size there.
| | 02:24 | Now you can also right-click to get
into these, but when you do right-click--
| | 02:27 | suppose I want to change the legend over
here on the right-hand inside. I'm going
| | 02:31 | to right-click on the legend and
possibly instead of going to Format Legend,
| | 02:36 | because if you go here you won't see
font size changes, nothing about the size of
| | 02:41 | the font or the font itself, but you may
have noticed when I did that right-click
| | 02:45 | there was another choice called Font.
| | 02:47 | So I could go down this path here, and
you might not have seen this one before.
| | 02:51 | We can change the Size here.
We can make some choices here.
| | 02:55 | Now this doesn't give us the so-called
Live Preview features, so I don't think
| | 02:59 | this is as good. We're not seeing
what the font looks like ahead of time.
| | 03:02 | Add another choice you might have
noticed here, when I right-click here again the
| | 03:07 | Mini tool bar appears, so I've got
some choices here too. And here we see some
| | 03:12 | font choices. Now, on these two we're
not seeing Live Preview, but if you know
| | 03:17 | exactly which font you
want, that's another approach.
| | 03:20 | So did we give you enough ways here to
make text changes? I think so. It's easy.
| | 03:25 | It's fast. I recommend the Home tab
because you do have Live Preview in all cases.
| | 03:30 | It's fast and it's easy.
| | 03:31 | So great ways to make font changes
within different parts of the chart or
| | 03:36 | throughout all text on
the chart at the same time.
| | Collapse this transcript |
| Changing the rotation of chart text| 00:00 | In certain charts, particularly those
where you have a number of items within a
| | 00:04 | series, you need to adjust the text by
rotating it to accommodate either more
| | 00:09 | explanatory information or
simply to see all of the text labels.
| | 00:13 | And the best way to make these
changes usually is by way of the Orientation
| | 00:17 | button on the Home tab.
| | 00:18 | And you can do this to different
parts of a chart, different text entries,
| | 00:23 | although I think it's most commonly done
along the bottom of the chart where the
| | 00:27 | actual labels for the
columns in this chart on left occur.
| | 00:30 | So let's just click on that
section of the chart here,
| | 00:34 | and on the Home tab the
Orientation button is in the Alignment group.
| | 00:38 | We could angle these at
different direction, this way possibly.
| | 00:42 | No real reason to do that here, probably
because we can see the months anyway, but
| | 00:47 | again we have some choices, and I
wouldn't recommend Vertical Text, but some
| | 00:51 | people try that, and that could be
acceptable here and there, but I doubt it, and
| | 00:55 | some other choices as well here too.
| | 00:57 | Let's say that either angle
counterclockwise or maybe just back to the way it
| | 01:00 | had been from the beginning.
| | 01:01 | Strangely enough, you can do this with a
title. It's going to look a little bit
| | 01:04 | strange here, but you can here and
there do that for a special effect. You can't do
| | 01:08 | this with a legend, but you could do it
with a text over on the left-hand side as well.
| | 01:13 | Now you will notice in some charts
the text is angled, even if you didn't
| | 01:19 | make the selection.
| | 01:20 | In the original data that this chart
is based on in columns A through D, I am
| | 01:24 | going to change the A5 entry for January
and actually spell out the month.
| | 01:29 | As I complete this entry, keep an eye on the
chart. We see January here and if I fill
| | 01:35 | in the others, we see what's happening.
| | 01:38 | Now we are seeing every other month,
and so what happens sometimes when you
| | 01:41 | create charts, you will see the angled
text automatically, but if you have been
| | 01:45 | altering them sometimes it's
a little bit unpredictable.
| | 01:48 | So I if click on the chart and just
click one of those labels there and go back
| | 01:53 | to the Home tab and use the
Angle text button, choose this,
| | 01:57 | we'll see that kind of an entry.
And there it would be appropriate, if its
| | 02:01 | important, to see the
full spelling of the months.
| | 02:04 | But you will sometimes see these when
you create a chart after having, for
| | 02:08 | example, gone to the Design tab
if you made a chart layout choice,
| | 02:12 | sometimes you will see these
automatically appearing here.
| | 02:16 | So you do have some control
over it, and it's easy to use.
| | 02:19 | If you happen to right-click, as I
have been suggesting with a lot of other
| | 02:22 | formatting choices, if you then go to
Format Axis, although you do have some
| | 02:27 | control here on the Alignment tab, not
only does it take longer to get here, but
| | 02:32 | you are going to have to put in
custom angles this way. If you choose these
| | 02:36 | boxes, you get a very limited set. That's
not to say these are not workable or
| | 02:40 | usable, but it doesn't have the
fluidity or the ease that the Orientation
| | 02:45 | button on the Home tab has.
| | 02:46 | So here and there you might try this,
but it's not nearly as good as using the
| | 02:51 | Orientation button that we have on the Home tab.
| | 02:53 | So once again we click
here, back to the Home tab.
| | 02:57 | The example currently probably is the way
I would want it. Sometimes you'll see
| | 03:00 | text angled this way as well.
| | 03:02 | But again you can see why it's
necessary here. If I do take this off--
| | 03:06 | remember, if we take off the angle clockwise,
you are going to only see every other month.
| | 03:11 | When these are values sometimes what you
will do to accommodate this is make the
| | 03:14 | chart wider, but we are going to make
this one a lot wider for all the labels, and
| | 03:19 | see, now they appear.
| | 03:20 | So sometimes you will
experiment with that approach as well.
| | 03:24 | So, for those many different times when
you need to adjust the angle of the text,
| | 03:28 | the fastest way is going to be on the
Home tab and the Orientation button found
| | 03:34 | in the Alignment group.
| | Collapse this transcript |
|
|
10. Adjusting Specific Chart TypesUsing column and bar charts| 00:00 | Column charts and bar charts are probably the
most commonly used charts by most Excel users.
| | 00:06 | On the screen here we're seeing
four variations on column charts.
| | 00:09 | Now many of the kinds of adjustments
you might want to make to a column chart
| | 00:14 | also apply to a bar chart,
| | 00:16 | so let's talk about some of the
things they have in common, and eventually
| | 00:19 | I want to point out why you might want to
use a bar chart instead of a column chart.
| | 00:24 | Below the data here, we see
probably the most common kind of chart.
| | 00:28 | It's called a Clustered Column Chart.
| | 00:30 | To the right of it, also fairly
popular, a Stacked Column Chart.
| | 00:35 | Below that, not so commonly used, but
occasionally, 100% Stacked Column Chart.
| | 00:41 | Now, in looking at the data, for example
the Clustered Chart below the data,
| | 00:45 | if there is some explanation that needs to be
added here, we might want to change the axis.
| | 00:50 | That's covered in another movie.
| | 00:51 | That's certainly one of things we want
to change, but also the spacing between
| | 00:55 | the columns here and there
might be something you want to do.
| | 00:58 | And in any of these three charts here,
we can right-click on any series as I'm
| | 01:03 | doing right now, choosing Format Data Series.
| | 01:06 | I'm about to change the Gap Width.
| | 01:09 | If I drag this leftward and keep an on that
chart below the data, see what's happened.
| | 01:14 | There is no gap at all between
the clusters of months there.
| | 01:17 | Dragging it in the other
direction, quite a bit more.
| | 01:20 | And again, half the time you don't even
think about it maybe, but it's there to be
| | 01:23 | adjusted if you wish.
| | 01:25 | If I switch to the regional chart to
the right of the data here and were to
| | 01:30 | right-click on one of those series,
Format Data Series, watch what happens in
| | 01:35 | the gap here. And you can see that.
| | 01:37 | And again, it's your choice.
| | 01:39 | In either chart too, you might want to
consider Overlapped versus Separated.
| | 01:44 | And here you're seeing something
that's a bit unusual, and this will look
| | 01:48 | different under Clustered Charts than it
we're here under Stacked Chart, but that
| | 01:52 | too for somehow bringing out
the data a little bit differently,
| | 01:55 | you've got it your disposal as well too,
so there are some options there.
| | 01:59 | Dragging that back and forth to see how it
looks and adjusting the Gap Width as needed.
| | 02:04 | Going back to the Clustered Chart again,
right-clicking on one of the series.
| | 02:09 | Here Overlap has a different
meaning as we format the data series.
| | 02:13 | Overlapped here I think
you're less likely to want to use.
| | 02:16 | You can see what's happening to the
columns here. Whichever column is in front,
| | 02:21 | is going to get more
attention, probably, than the others.
| | 02:23 | And you wouldn't want this 100%
Overlap because then you couldn't quite see
| | 02:26 | the detail behind it.
| | 02:28 | But those are the changes we want to
make at different times as we work with
| | 02:31 | these chart types as well too.
| | 02:33 | Another possible option that you might
want to use here in a Stacked Chart is
| | 02:39 | comparing two consecutive months,
this piece of Asia for January.
| | 02:43 | Here's another piece.
| | 02:44 | Now if the numbers are nearby, we can
go to look at them, but another option--
| | 02:48 | and you could enter this from the
Design tab, under Chart Layouts--at least
| | 02:54 | one of the options here shows lines
connecting the series, like this, and this
| | 02:58 | may or may not help you.
| | 03:00 | I would possibly get rid of the
gridlines here at the same time.
| | 03:03 | But again, that occasionally might help
you read those pieces a little bit better.
| | 03:07 | As you point to them, you also
see the values too. That helps.
| | 03:11 | The 3D chart to the right
exhibits another problem.
| | 03:14 | If you look at the series order--
| | 03:16 | Domestic, Europe, Asia, Latin America--
it's exactly the way it is in the actual
| | 03:21 | data in rows 5 through 8.
| | 03:23 | And yet on the chart, the Europe
series, which is reddish, is behind the
| | 03:28 | green, which is Asia.
| | 03:29 | We can't see it so well.
| | 03:31 | If you right-click on one of the series,
you might think that you would go to
| | 03:34 | the Format Data Series, but that
won't help. It's Select Data.
| | 03:40 | And here we see over in the
left-hand side, the four entries.
| | 03:44 | The word Order doesn't appear here, but
I'm going to click on Europe, click the
| | 03:48 | drop arrow here, and you see what
happened to the chart. The series of red
| | 03:53 | columns that represent Europe moved forward.
| | 03:56 | And you can begin to experiment with these,
move these back and forth a little bit.
| | 03:59 | I'm using Domestic right now.
| | 04:00 | Obviously, I don't want these choices, but
back and forth, you can see what's happening.
| | 04:05 | Now that in no way alters the original
data, but it changes the legend and the
| | 04:10 | display of the order here.
| | 04:12 | And you can do this on the other
three kinds of charts as well, and it is
| | 04:15 | something you want to be
able to do from time to time.
| | 04:17 | And I think it makes sense now.
| | 04:19 | Now that's much, much more readable.
| | 04:21 | Now, because this is a 3D chart, the
other thing you might want to do at
| | 04:25 | different times--and this could take
some time--is if you right-click on one of
| | 04:29 | the series here and go to Format Data
Series, you'll also see in the dialog
| | 04:34 | box here 3D Format.
| | 04:37 | You see all kinds of variations here as well.
| | 04:39 | If you click on the chart area, you
will see 3D Rotation in that dialog box.
| | 04:46 | And here, by clicking the arrow to
the right of X, this currently says 210,
| | 04:48 | so I'm clicking this arrow.
| | 04:51 | You see what's happening to the
chart, move this way or that way.
| | 04:55 | And this one here, it's hard to
describe this in words, but you can immediately see
| | 04:58 | what's happening to the display of the chart.
| | 05:00 | Once again, 3D column charts are very
appealing, at least initially, but they
| | 05:05 | are difficult to read.
| | 05:07 | They're only good, I think, in showing
relative size, certainly not exact size.
| | 05:11 | What's the height of that column?
| | 05:13 | If you point to it, you can see it, but
would you ever guess what it really is?
| | 05:17 | Try Perspective too. That changes the look of it.
| | 05:20 | So you can certainly change the look
of these in a variety of ways using all
| | 05:23 | these different arrows here.
| | 05:24 | By the way, if you hold down the left
mouse button, you'll go through continuous
| | 05:28 | changes like this and get some
pretty weird things at times too.
| | 05:31 | But at least you can make those adjustments.
| | 05:34 | Now, all the things that we've done here
similarly would apply to bar charts as well.
| | 05:40 | But there are times when a bar chart
might make a little bit more sense.
| | 05:43 | And to the right here,
I've got some different data,
| | 05:46 | say this data right here.
| | 05:48 | I'm going to create a chart quickly
simply by clicking on one of the cells and
| | 05:51 | pressing Alt+F1, and the automatic default
chart type of a Clustered Column will appear.
| | 05:58 | I'll move over a little bit and move the chart.
| | 06:00 | And I think you can see
that that's not so great.
| | 06:03 | We have difficulty reading the labels.
| | 06:05 | We're getting every other one, and
they're angled, not that that's horrible, but
| | 06:09 | eventually by making this
wider, we would see the choices.
| | 06:13 | Now, how might this look as a bar chart?
| | 06:15 | On the Design tab, left
button, Change Chart Type.
| | 06:19 | Let's make this be a clustered bar chart.
| | 06:23 | First choice, double-click.
| | 06:25 | And I think for a lot of people
that's a better choice than what we just saw.
| | 06:28 | I'm going to press Ctrl+Z, meaning Undo, to go
back to the previous choice, and then Ctrl+Y.
| | 06:34 | I think in situations where you have
longer labels, as we do here--some of the
| | 06:38 | names of those departments are large--
| | 06:40 | this might be a better choice
because it's easier to read.
| | 06:43 | And you can certainly tighten
up the Gap Width if you want.
| | 06:45 | That's a different selection, but
I think it makes some sense here.
| | 06:49 | And a lot of times the difference
between column and bar is just personal
| | 06:52 | choice, or maybe you simply have to
match up the design of a chart with the way
| | 06:56 | charts had been done in
the past. So, simple and easy.
| | 06:59 | And with all of these, remember, the
Chart Styles option sometimes gives you
| | 07:03 | a slightly fancier look to some of these too.
Maybe you'd like that a little bit better.
| | 07:07 | And we can go down in that of course
for all the other chart types we've
| | 07:09 | talked about here as well.
| | 07:10 | So column and bar charts widely used
by most Excel users can be modified in a
| | 07:16 | number of different ways.
| | 07:17 | And much of the time, it's simply a
tossup as to which one you like better,
| | 07:21 | except perhaps in the case of longer labels.
| | Collapse this transcript |
| Using line charts| 00:00 | Line charts are invariably mentioned as
the kinds of charts that you would want
| | 00:04 | to use if you're trying to show a
trend, or sometimes the lack of a trend.
| | 00:09 | In the examples that we are seeing here
are two full charts and a portion of a
| | 00:12 | chart to the right, three different kinds of
line charts showing different kinds of data.
| | 00:18 | As you look at line charts, one thing
that might not have occurred to you is
| | 00:22 | that the ratio of height to width can
skew your impression of what actually is
| | 00:26 | happening in the data.
| | 00:28 | We will talk about that in more detail in a bit.
| | 00:30 | We also want to point out the difference,
as I scroll rightward here, that in that
| | 00:34 | purple background chart, the one that
says Domestic Sales, the lines are as they
| | 00:39 | would normally appear. Maybe we would
call them jagged for want of a better
| | 00:42 | term, but the lines connect point to point.
| | 00:45 | That's default setting in Excel.
| | 00:47 | The chart to the right, however,
has the lines looking much smoother.
| | 00:51 | They're curved as we move from point to point.
| | 00:53 | Those points, by the way, are very small.
| | 00:56 | You've got control over the points.
| | 00:58 | You've got control over the curving,
| | 01:00 | it's actually called smoothing, and
also the thickness of lines has some
| | 01:04 | bearing as well too.
| | 01:05 | There are a lot of
different ways to control these.
| | 01:08 | One simple difference on the
middle chart here, Domestic Sales,
| | 01:12 | if I want the lines to be thicker, one
approach could be on the design tab, make
| | 01:17 | one of the many chart style choices.
| | 01:19 | Maybe I like this one.
| | 01:21 | On the other hand, maybe
that's too thick. Maybe it isn't.
| | 01:24 | But we have got our choices here.
Easy to get to. That might be just fine.
| | 01:28 | If you were to right-click here, you
could go to Format Data Series and control
| | 01:32 | the thickness that way.
| | 01:33 | Once again these are features that no
doubt you've seen at different times. This would
| | 01:36 | be under Line Style.
| | 01:38 | You control the thickness.
I will make that bigger.
| | 01:40 | I will just change that 5 to an 8 and as soon
as we press Enter, you will see what happens.
| | 01:44 | So that may or may not be what
you want to do at different times.
| | 01:47 | Now let's talk about the idea here that
you might want this to appear to be smoother.
| | 01:52 | Let's do a right-click again here, and
that may contribute to the idea that maybe
| | 01:56 | we want it to be thinner. Let's do the
smoothing first, but we will change our minds as we try
| | 02:01 | these different features.
| | 02:03 | And we are talking here about the line
style, and there is that choice Smoothed
| | 02:07 | line, and you see what's happening in
the background. And while we are here, if we
| | 02:11 | want to revert to a thinner line, we
will go up to the Width up top and change
| | 02:15 | that to something smaller.
| | 02:16 | As I'm clicking the arrows here,
you can see that getting thinner.
| | 02:19 | That may or may not be what you need to do.
| | 02:21 | While we are here, let's talk about the
Marker Line Style, or the Marker Fill, the
| | 02:26 | Marker Options here.
| | 02:28 | We could go to Built-in here.
| | 02:29 | Here is the diamond that we are
currently seeing that, but you might choose
| | 02:33 | another one and you can fill it in.
| | 02:35 | That's the default. We see that to the
left. And the diamond might be just fine.
| | 02:38 | Some people like the X, but
decide which one you might want there.
| | 02:41 | You see the difference.
| | 02:42 | We will go back to the other one, a diamond,
and you can of course choose different colors.
| | 02:47 | Here's where you adjust
the size. I'll click this.
| | 02:49 | Maybe that will look better if
we choose a smaller size marker.
| | 02:54 | So quite a few choices there too.
| | 02:56 | Now with this chart and the chart to
the left what if we ignore for the moment
| | 03:01 | the title and just imagine
suppose there isn't really Sales.
| | 03:05 | Maybe it has to do with increased
reports of crime or maybe some kind of
| | 03:09 | an absentee report.
| | 03:10 | I know the numbers are quite large, but
if we make this chart a lot wider, now at
| | 03:16 | some point perhaps people are
going to say 'why is it that wide?'
| | 03:19 | so you don't always want
to draw attention to it.
| | 03:21 | But there's no denying that the numbers
here are increasing over time. But as I
| | 03:26 | press Ctrl+Z here and go back to the
previous setting a couple of times, what if
| | 03:30 | I were to make that chart taller?
| | 03:33 | Well, obviously it's going up.
| | 03:35 | It's pretty erratic, some jumps up and down.
| | 03:37 | But that tells a different story
than had we kept the chart this way.
| | 03:42 | Let me get the other one out of the way too.
| | 03:44 | So we are talking about exactly the
same data but a different impression.
| | 03:48 | And maybe on this chart here Domestic Sales--
| | 03:50 | I will move it around a little bit--
watch this one if we make it a lot wider.
| | 03:55 | Now if we were trying to report
something was negative, like maybe that was lack
| | 03:59 | of participation in a certain
program or something, it's going up,
| | 04:03 | so it's a negative kind of trend, even
though it's going up. We might want to
| | 04:06 | hide that a little bit by
flattening out the line charge.
| | 04:10 | On the other hand, if we were trying to
show increased participation or increased
| | 04:14 | sales or profits, let's take that
same chart and make it be a lot taller.
| | 04:17 | Even though it's got some ups and downs in it,
| | 04:20 | I think you can see, as I press
Ctrl+Z to go back to the previous chart,
| | 04:25 | Ctrl+Y back and forth here,
| | 04:27 | you can begin to see how the same
data, when displayed differently in a line
| | 04:31 | chart using a different height-to-width
ratio, sometimes called the aspect ratio,
| | 04:36 | you in effect are telling a different story.
| | 04:38 | If you did want to continue to
accentuate the change here moving upward, you
| | 04:43 | could also right-click on the axis
here and format the axis and start the
| | 04:48 | axis, for example, at 80.
| | 04:50 | Go to the Minimum value here, choose Fixed.
| | 04:53 | The first item in that chart is 80.
| | 04:57 | As I press Close here, watch that chart
to the left--even steeper looking. And we
| | 05:02 | could also clip off the top
too to make it even steeper.
| | 05:05 | So again pressing Ctrl+Z to move back
a bit here, you can see how the same
| | 05:10 | data certainly can look different with a
different display of the aspect ratio in a line chart.
| | 05:17 | The Smoothing, as we mentioned earlier, is
something you may or may not want to use.
| | 05:21 | Perhaps it accentuates the trend.
| | 05:22 | It could have been used on the larger chart here.
| | 05:25 | Right-click on the line, Format Data
Series, and on the Line Style here, Smoothed
| | 05:33 | line, and you see it
happening already. There too.
| | 05:37 | You want to experiment freely with the
aspect ratio and these various approaches
| | 05:41 | to making the line chart look
the way you think it looks best.
| | Collapse this transcript |
| Using pie charts| 00:00 | Pie charts, although ideal for certain
kinds of data, many times are inappropriate.
| | 00:06 | Nevertheless, when you're using pie
charts, you need to make some adjustments to
| | 00:10 | them, and the two pie charts that
we see here certainly are lacking in
| | 00:14 | explanatory information.
| | 00:16 | Let's point out a couple of deficiencies,
particularly with regard to 3D pie charts.
| | 00:22 | As you look at the chart on left here,
which is reflective of the data above it,
| | 00:25 | for the Domestic row, we see the
breakout for the first six months.
| | 00:30 | Now, without looking at the numbers and
looking at the wedges, is the green wedge
| | 00:34 | bigger than the blue wedge to the
left, in other words, this one here,
| | 00:37 | the one over there?
| | 00:38 | The green wedge is March.
| | 00:40 | The blue one over there is May.
| | 00:42 | You look at the numbers:
March is 125, May is 140.
| | 00:47 | Can you see that difference?
| | 00:48 | I pretty much doubt it? Most people can't.
| | 00:51 | And depending upon it the order in
which these colors appear here, you might
| | 00:55 | or might not be giving precedence to one
wedge over another without necessarily knowing it.
| | 01:00 | If you've created a pie chart without
concern for any order, these will be in
| | 01:05 | a circular order moving from
the back in a clockwise order.
| | 01:08 | So the blue that I'm pointing to
here is January and then February.
| | 01:12 | But let's talk about how we can change
the appearance of this, because there are
| | 01:16 | some tools specifically for 3D charts.
| | 01:19 | And the general rule, as we've been
saying throughout this course in terms of
| | 01:23 | Excel 2007, when you want to make a
change to an object or a chart element, begin
| | 01:29 | by right-clicking on it if you have
not found any choices in the menu.
| | 01:33 | So if we were to click this chart and
then right-click on one of the wedges
| | 01:38 | here, go to Format Data Series, we
begin to see some of our choices here.
| | 01:44 | I'm going to focus first on 3D Format and
lots of choices in here and also, as we
| | 01:50 | start to adjust some of these,
you can change the material.
| | 01:54 | These are things that I think a lot of
us never even thought we would ever need,
| | 01:57 | and maybe you still won't, but a lot of
different choices here under 3D Format.
| | 02:01 | We can certainly fill these in
with different kinds of colors.
| | 02:04 | We can change the border color,
the border styles. We've got shadows.
| | 02:10 | Let's change the entire chart.
| | 02:12 | Now, under 3D Format, we're not
seeing any choices here regarding rotation.
| | 02:18 | So let's click back on the chart and
simply, without picking one of the wedges
| | 02:23 | here, just right-click in the plot area and
choose 3D Rotation, and here we have some choices.
| | 02:31 | Best approach here probably now as I'm
clicking these arrows, watch the chart.
| | 02:35 | We see what's happening.
| | 02:37 | If you want to move January more toward the
front, it's the blue that's next to the red,
| | 02:42 | so it's about to move towards--
| | 02:43 | it's on the right side.
| | 02:44 | Now, it's moving more and more forward.
| | 02:45 | So January is in the front.
| | 02:47 | That might or might not be what you want.
| | 02:49 | What happens here with these buttons?
| | 02:51 | You can begin to see how that tilts
the chart in different ways, and we can
| | 02:55 | change the perspective as well too,
either with this or with the arrows.
| | 02:59 | And again, for a lot of
people, this is very distortive.
| | 03:02 | April in the background there, the
purple color, looks a lot smaller than January
| | 03:06 | here, and it's actually a lot bigger.
| | 03:09 | So we've got some problems
with this, but again, it is vivid.
| | 03:14 | It's colorful and if you're trying to
show the breakout, say, for budget type
| | 03:18 | numbers, sometimes this
is the appropriate chart.
| | 03:21 | I know some people who say,
"Don't use 3D pie charts at all.
| | 03:25 | You should use 2D pie charts
like the one we were seeing here."
| | 03:29 | The approach here is because we are
not appearing to be looking at it from an
| | 03:32 | angle, the sizes of the
pieces appear to be more accurate.
| | 03:36 | But in either of these cases, I think it's
appropriate to say let's add some labels.
| | 03:41 | One rule of thumb I've heard is don't
be using these pie charts in either case
| | 03:45 | if you have more than five or six or
seven wedges, and I know this is not quite
| | 03:49 | exact, but it will depend on the
number of wedges you're using.
| | 03:53 | Let's make some adjustments here.
| | 03:54 | Once again, we might start by right-
clicking, and where do we need to go this
| | 03:58 | time if we want to add labels?
| | 04:00 | Add Data Labels. And some people like
the approach of just using these here.
| | 04:05 | Now we've got some other choices
here too. If you go to the Layout tab and
| | 04:10 | choose Data Labels, you can see some choices
here related as to where those labels up here.
| | 04:15 | Some people like them toward the outer edge.
| | 04:17 | Now we got a problem with that
black wedge, don't we? Yes, we do.
| | 04:22 | Inside, Outside, Outside the End, Best Fit.
| | 04:25 | Try it that way too.
| | 04:27 | So we've got quite a few choices here
related to where these labels are going to go.
| | 04:31 | Some people want them this way as well too.
| | 04:33 | So you do have some different choices here.
| | 04:36 | You can also, on a one-by-one basis, take these
labels and drag them off the chart, like this.
| | 04:41 | And you will see, without even trying or
setting anything, what are called leader lines.
| | 04:47 | Now trying to do this on
black might be a little tricky.
| | 04:50 | I did happen to catch it there, but
you don't always do that so easily too.
| | 04:54 | So we can do this here too.
This can get a little old too, can't it?
| | 04:57 | Notice how, depending on where you might drag
this, you don't always see the leader lines.
| | 05:02 | So that might cause you to right-click
here and say, "What's going on?" or, "What can
| | 05:06 | we do with these labels?"
| | 05:07 | Do a right-click here and talk about the
Data Labels, and we can see positions here.
| | 05:14 | Include legend key in
labels, that's another option.
| | 05:17 | Show Leader Lines is always on, but you
will not see them necessarily unless the
| | 05:23 | positioning is at a certain place.
| | 05:25 | And so here and there, sometimes
try as you might, you can't get them
| | 05:28 | disappear, but sometimes they won't be
there, and if you drag it onto the wedge,
| | 05:32 | you see what's happening there.
| | 05:34 | So this is just something you have to
experiment with and come up with your own
| | 05:37 | conclusions as to what works best for you.
| | 05:39 | But I think you can readily see,
| | 05:41 | on pie charts, you will need
to add labels most of the time.
| | 05:45 | And the labels here are the values.
| | 05:47 | You might not always want to use values.
| | 05:49 | So once again in the dialog box there,
you do see Value, but how about Percentage?
| | 05:54 | You might want both.
| | 05:55 | I think a lot of people who would
want only one or the other, but you can
| | 05:58 | certainly do that if you wish, choose both.
| | 06:00 | I'm going to uncheck Value,
and now we see percentages.
| | 06:03 | So there are quite a few choices there.
| | 06:06 | And similarly, we could do the
same thing with a 3D pie chart.
| | 06:11 | So even though there is limited use
here, I think you can see you do need to
| | 06:14 | make some adjustments.
| | 06:16 | And one more point about this too:
| | 06:18 | if you select data here and create a
pie chart, if you haven't worked with pie
| | 06:22 | charts at all, you're going
to be a little bit surprised.
| | 06:24 | For example, if I go to the Insert tab
and choose Pie and whether it's 2D or
| | 06:30 | 3D, it doesn't make any difference,
| | 06:33 | you might be scratching ahead a
little bit saying, "Why does it say Domestic?"
| | 06:37 | A pie chart, unlike most other chart
types, can't handle more than one range.
| | 06:42 | So we're looking at this data automatically.
| | 06:44 | Now if you were dead set on showing a
pie chart with Asia only, you would be
| | 06:49 | selecting this range and then
with the Ctrl key, select this range.
| | 06:54 | And then in the Ribbon,
Insert > Pie, pick either one, you want,
| | 06:59 | maybe 2D pie this time, and
you see what we're choosing here.
| | 07:02 | So regardless of how much data you're
highlighting, Excel can only handle a
| | 07:07 | single portion of a row or column, not
multiple rows and columns, in a pie chart.
| | 07:13 | One more aspect of this too.
| | 07:15 | I'm going to change this
data and make it negative.
| | 07:19 | Now, not just for fun, but just based on the idea,
| | 07:21 | what if the sales adjustment number
here for February, because of returns
| | 07:26 | or whatever, was -10?
| | 07:28 | I'm going to change this number
to -10. That's in cell C2 there.
| | 07:33 | As I press Enter, watch all these charts.
| | 07:36 | Now what will happen in all of them is
that the February wedge will become much
| | 07:40 | smaller, but is there any indication
whatsoever on any of these charts--
| | 07:45 | the Asia chart doesn't
include this we don't see it there--
| | 07:48 | but on the other charts here, the
February wedge is certainly smaller, but is
| | 07:52 | there any indication
whatsoever that it's negative?
| | 07:54 | Now over here, we take a look on this
chart where we actually have titles, and
| | 07:59 | it's a little tricky to move the side.
| | 08:01 | You can see where it's going on there.
| | 08:02 | That would certainly get our attention,
but the concept is totally inappropriate.
| | 08:07 | I guess we could say there is no such
thing as a negative piece of pie. And it
| | 08:11 | is just a little bit strange that Excel
doesn't really prevent you from creating the pie;
| | 08:15 | it just treats the data as
if it were a positive entry.
| | 08:19 | So in wrapping up the whole discussion
here about pie charts, certainly valuable
| | 08:23 | for certain kinds of data.
They definitely have limitations.
| | 08:26 | I would suggest a 2D probably
better than the 3D if you're looking for
| | 08:30 | accuracy and display.
| | 08:32 | And in either case, I think it makes
good sense to put in data labels on
| | 08:35 | pie charts.
| | Collapse this transcript |
| Using area, stock, and XY charts| 00:00 | Although less commonly needed by most
Excel chart users, area charts, stock
| | 00:05 | charts, and XY charts--sometimes called
scattered charts--are specialized chart
| | 00:09 | types ideal for some sets of data.
| | 00:12 | On the screen we are looking
at a sheet called AreaChart.
| | 00:16 | The data represents about 30 months of
data for US Sales and Other Sales, and
| | 00:20 | we would like to share this
information possibly as a column chart, line
| | 00:24 | chart, or an area chart.
| | 00:26 | For some people an area chart
is a variation on the line chart.
| | 00:30 | Let's take a look at how
it might look with this data.
| | 00:33 | We want all the
contiguous data used in the chart,
| | 00:35 | so all we need to do is
click on the single cell here.
| | 00:38 | In the Ribbon, we will click on Insert.
| | 00:41 | There is the choice Area.
| | 00:42 | Insert an area chart, and the first choice,
although it doesn't say clustered, is
| | 00:46 | somewhat like the clustered
charts that we see with column,
| | 00:50 | meaning it's not a Stacked variation.
| | 00:52 | We see that one here and then the 100%.
| | 00:53 | Let's choose the regular area chart right here.
| | 00:56 | Here is the data we see.
| | 00:58 | Think of the data perhaps in layers.
| | 01:00 | We can see all of the data for Other Sales.
| | 01:03 | The blue data representing
US Sales is in the background.
| | 01:05 | So you can just check out a number or two here.
| | 01:09 | The really high entry we see
there for October 2010, 22 million,
| | 01:14 | that's the total for US Sales.
| | 01:16 | In front of it Other Sales, about 13 million.
| | 01:20 | So this might be ideal.
| | 01:21 | A possible visual change here might make
it look little bit better. Under Chart
| | 01:25 | Styles in the Ribbon, on the Design tab,
| | 01:28 | perhaps one of these that
gives us sort of 3D-like look.
| | 01:31 | If you like to see this in its stacked
variation, on the Design tab, left button,
| | 01:35 | change to Chart Type the
option for a Stacked Area chart.
| | 01:39 | Double-clicking looks like this.
| | 01:42 | So now we are seeing the total.
| | 01:43 | So a high point here, for example,
represents the total of 22 million, and
| | 01:47 | almost 14 million, roughly 36 million.
| | 01:50 | We are seeing that total right here,
reflecting the data coming out of row 29 here.
| | 01:56 | So the two are stacked on top of one
another, and that third variation, less
| | 02:00 | likely to be used, but
still it has its merits too,
| | 02:03 | under Change Chart Type again, that left button,
the Area type called Stacked 100% Stacked Area.
| | 02:11 | This, like our other two charts, might
look better, and I think it will apply right
| | 02:15 | now, on the Layout tab.
| | 02:16 | How about some drop lines here?
And we can see what's happening.
| | 02:21 | So for any given point, say January 09
right here on this particular line, we
| | 02:27 | can't see this exactly, but it's roughly
68% of our sales are coming from the US
| | 02:33 | and the other 32% coming from Other Sales.
| | 02:36 | So if we were looking at those numbers,
we are talking about these two right here.
| | 02:40 | What we see when we point here are not the
percentage breakouts, but we can see it in the visual.
| | 02:45 | So overall, as we look at this, we
could say in most months the US Sales are
| | 02:50 | roughly two-thirds, five-eights of the total.
| | 02:53 | Simply 100% percent breakout.
| | 02:55 | So I think this gives a little more
power, a little more oomph to it perhaps
| | 02:59 | than a line short might.
| | 03:00 | The same basic idea though.
| | 03:03 | Now for certain kinds of data related
to stock market activity, this particular
| | 03:07 | sheet which shows Stock data for
portion of April and May of 2011 does have the
| | 03:13 | potential of showing High, Low, Close, and
Open data, and possibly even volume, which
| | 03:19 | is not seen here, but those are the
components of what you might use in a stock chart.
| | 03:24 | In the example here, and if I click on
the chart you will see, we're not showing
| | 03:27 | open data, and off to the
right you do see High, Low, Close.
| | 03:32 | Now, the legend isn't that helpful here,
except for the close part of it, and the
| | 03:37 | green tick marks that we see here,
which perhaps could be more prominent, do
| | 03:41 | represent the closing price
for the stock on a given day.
| | 03:45 | When you do create these--I am going
to go back and highlight the data again--
| | 03:49 | do we want to show all this or do we
just want to show the High, Low, Close, Open?
| | 03:53 | You've got some variations.
| | 03:55 | If you are selecting all of the data, as
I am right now, you can go to the Insert
| | 03:59 | tab, choose Other Charts right
here, and we do come to Stock,
| | 04:04 | notice that the first one is High-Low-Close.
| | 04:05 | The second one is Open-High-Low-Close.
| | 04:10 | So if I did want to show that, I'd click it
right now, and we see that data shown this way.
| | 04:15 | It's up to you to decide
which of these is most useful.
| | 04:18 | When you do make these choices,
initially you're likely to see a gap here, which
| | 04:23 | represents the fact that the stock
market is closed on Saturday and Sunday.
| | 04:26 | So the dates are still represented.
| | 04:29 | If that somehow isn't what you want to
see, you could right-click here on the
| | 04:33 | lower axis, format it, and
| | 04:35 | then simply display this is as a text axis,
and then it removes those weekend gaps.
| | 04:41 | In the previous chart that was already
created, those gaps are missing already.
| | 04:45 | If you want to control the size of the
tick marks or the thickness of lines,
| | 04:49 | that's all doable by way of the Layout tab.
| | 04:52 | The idea here might be if you want to
close marks to be more dominant, the easiest way
| | 04:56 | to do this is rather than trying to
click on them, go to the Layout tab in the
| | 05:01 | Ribbon and then on the far left, under
Current Selection, click the drop arrow
| | 05:05 | next to whatever might be
appearing here, and choose a Series Close.
| | 05:10 | Then right below that choice, Format Selection.
| | 05:14 | When you get here you've got control
over some marker options here, and maybe the
| | 05:19 | size of this isn't big enough.
We'll make it bigger or smaller as you need,
| | 05:23 | and you see what's
happening off to the left there.
| | 05:25 | And maybe it will accompany this
also you might go to Marker Line Color.
| | 05:29 | Instead of green, make it
black if you wished, that idea.
| | 05:32 | And there all kinds of other variations
that you might want to pursue here as well.
| | 05:36 | So let's say now that's a little more prominent.
| | 05:38 | We can see the close prices.
| | 05:40 | Of course, you can track stock data
over longer periods of time than this, but
| | 05:44 | I think you might see that it's going to get
relatively crowded, if we cover a time period.
| | 05:47 | Twice as much, probably not so bad, but
you want to experiment with that if you
| | 05:51 | work with stock data.
| | 05:53 | Another kind of chart, sometimes
called XY chart, sometimes called Scatter.
| | 05:57 | I will click a choice here for some
data that might be ideal for this.
| | 06:01 | XY charts, or scatter charts, typically
are comparing two different sets of values.
| | 06:07 | Let's say we've tracked the people who
have run this race, 99% participants here.
| | 06:11 | In no particular order, the data. We show
people of different ages. We see their
| | 06:15 | times. The data doesn't have
to be in any particular order.
| | 06:19 | Let's take this data here, and
there is no associated data with it,
| | 06:23 | so we can simply click on one cell
and Insert in the Ribbon > Scatter.
| | 06:30 | I always make the first choice here.
| | 06:31 | There are some other variations.
| | 06:33 | Let's try this to see what we get.
| | 06:35 | Initially, this is likely be
disappointing, but a few quick fixes will help us
| | 06:39 | read this a little more readily.
| | 06:40 | First of all, you will notice across
the bottom of the screen, although it
| | 06:43 | doesn't say anything about
age, that's what it represents.
| | 06:48 | If you go through the data, you will
realize the youngest age is about 20.
| | 06:51 | So why don't we right-click on the
bottom axis down here somewhere and
| | 06:56 | Format axis and change, in other words
override, the automatic Minimum and turn that into 20.
| | 07:04 | Then let's go to Maximum, and
immediately we see what's happened on the chart.
| | 07:08 | The oldest person race is 68, 69.
| | 07:11 | Let's make the maximum here be 70 instead of 80.
| | 07:15 | That helps a bit too. And also with
the time, this part gets a little tricky.
| | 07:20 | If you do deal with times,
these are kind of difficult.
| | 07:23 | Sometimes what I would
suggest is maybe make a guess.
| | 07:26 | Now, you would have to know a little bit
here about the way Excel handles times.
| | 07:30 | What I'm getting at here, it looks like
the lowest time is around 40 seconds or
| | 07:34 | so, actually a bit above that.
| | 07:36 | So, how do we make this different here?
| | 07:38 | In other words, how do we change the left axis?
| | 07:40 | Well, first of all, click there, and we want
our Format Axis dialog box to react to that.
| | 07:45 | So on the Minimum here,
under Fixed, 1/24th of the day,
| | 07:50 | that's what an hour is,
| | 07:52 | so how about 40 minutes?
| | 07:53 | That's about two-thirds of that.
| | 07:54 | So you might want to do a
little bit of guessing here.
| | 07:56 | What if we put in .05?
| | 07:58 | That's one-120th of a day.
| | 08:00 | What's that's going to change to?
| | 08:01 | Maybe click down here on the word Fixed.
| | 08:03 | You see what it comes up to now?
| | 08:05 | How about .02. In other words, you've got to
play with that a little bit. That not so bad.
| | 08:09 | How about .03? So you kind of get the idea.
| | 08:12 | There we are looking fairly good.
| | 08:13 | It looks like the top is not so bad.
| | 08:15 | So that's a better representation.
| | 08:17 | Are we seeing what's going on here?
| | 08:19 | I think in general we can see, or can we?
| | 08:22 | Can we see, the older you are it
takes a little bit longer, more or less?
| | 08:27 | What you might want to do here with
this simply by right-clicking on one of
| | 08:30 | these points, you might want add a
trendline here, and that's a linear trend-
| | 08:34 | line, and that's perhaps the topic of
different a discussion, but at least it
| | 08:38 | gives us some sense of what's
happening here with these ages and times.
| | 08:42 | Of course, you can do this with a
variety of different kinds of comparisons, but
| | 08:46 | in general an XY, or Scatter,
chart is comparing two sets of data.
| | 08:51 | So area stock and XY charts are
specialized chart types for certain kinds of
| | 08:56 | data, and here and there you want to keep
an eye on them. At least be aware of
| | 08:59 | some different examples as to when
you might want to use these chart types.
| | Collapse this transcript |
| Using doughnut, bubble, and radar charts| 00:00 | Three other types of unusual charts
are Doughnut charts, Bubble charts, and
| | 00:04 | Radar charts, all of which have specialized uses.
| | 00:08 | A Doughnut chart, which we're about to
create on this sheet, is sometimes used as
| | 00:12 | an alternative to overcome
the limitations of a pie chart.
| | 00:16 | A lot of you may know that if you were
interested in showing this data as a pie
| | 00:20 | chart and you hadn't used pie charts
very much, you'll probably be surprised.
| | 00:24 | I'm going to go the Insert tab and
choose Pie, and either 2D or 3D pie.
| | 00:32 | How about a two-dimensional pie here?
| | 00:33 | And right away, you might be saying,
"Yes, but I chose Europe and Asia, and I'm
| | 00:37 | only seeing Domestic."
| | 00:39 | Well, the limitation, and the major one for a
pie chart, is that it only picks up one series--
| | 00:45 | that means either one column or one
row of data--even though we highlighted
| | 00:49 | more data here, and shrinking this or
expanding it has no impact whatsoever on the chart.
| | 00:55 | Would we want to see all this data on a
single chart so we could see the proportions?
| | 01:01 | You might have tried some other
variations here on the Insert tab, maybe a
| | 01:04 | Column chart that's stacked 100%.
| | 01:06 | How might that look? Is that going to work?
| | 01:09 | Well, that has some merit potentially too.
| | 01:11 | Variation on a pie does show us
percentage breakouts, although they're not so clear.
| | 01:17 | Let's try, simply by converting this
one into a so-called Doughnut chart.
| | 01:21 | On the Design tab, Change Chart Type,
and under Doughnut chart at the bottom of
| | 01:27 | our list here, this one right here.
| | 01:31 | And I think you can see, that's
going to be difficult to read.
| | 01:33 | We'll have to apply some data labels.
| | 01:35 | The first thought might be let's go to
Chart Styles on the Design tab, make it
| | 01:40 | be more three-dimensional looking,
and it still doesn't help a whole lot.
| | 01:44 | If we start to add labels here,
it's going to get really crowded.
| | 01:48 | Now possibly you could say, well, maybe we
could try this for only a six-month period.
| | 01:53 | So I'm going to shrink the data amount
here to be just the first six months,
| | 01:58 | January through June.
Perhaps that has more merit.
| | 02:01 | Even there, if we start putting
labels on here, it might be crowded.
| | 02:04 | Maybe three months.
| | 02:06 | So you might have to make some kind of
an executive decision that says okay,
| | 02:09 | we're willing to try this.
| | 02:10 | The other aspect of this you wouldn't
exactly guess is that we can change the
| | 02:14 | size of the doughnut hole by
right-clicking on the series here, go to Format
| | 02:19 | Data Series > Doughnut Hole Size.
| | 02:22 | Now it looks like it might be set at
the smallest value, so drag this around a
| | 02:25 | little bit, right and left, and it does that.
| | 02:28 | We're still not dealing with the issue of
how readable is this going to be if we
| | 02:32 | start to add data labels.
| | 02:34 | Now there are so many variations here.
| | 02:36 | I just want to show you the impact of
what adding a few of these might mean.
| | 02:40 | And so if we do go to the Layout tab
and choose Data Labels and then go right
| | 02:45 | into More Data Label Options,
first you can see what's happened here.
| | 02:49 | We've got values on every single one of these.
| | 02:52 | As I slide this to the right,
recognize the colors represent the different
| | 02:56 | months. Checking one of the
boxes here, Category Name.
| | 02:59 | Now we've only chose the inner
ring, so what have we added now?
| | 03:03 | The months, that doesn't buy us much
because we see them in the legend anyway, so
| | 03:07 | that's not a good choice at
all, but how about Series Name?
| | 03:10 | Now we're seeing that those are Domestic.
| | 03:12 | And that word takes up some
space, and it's there six times.
| | 03:15 | We might want to change
the wording in the chart.
| | 03:18 | I think you're getting the idea here.
| | 03:20 | These have some
limitations, these kinds of charts.
| | 03:23 | If once again, we reconsider the
timeframe here and show only three months,
| | 03:28 | obviously, we're getting less data, but maybe
that's going to be a little bit more readable.
| | 03:32 | But you'll have to decide here and
there how many different series you want
| | 03:37 | to cover within these.
| | 03:38 | They do allow us to show more series on
a pie chart, and here and there they do
| | 03:42 | have their uses, although I think it's limited.
| | 03:45 | Another kind of chart is a Bubble chart,
and a Bubble chart is very similar to
| | 03:49 | a Scatter chart, but it does give us the
advantage of showing a third dimension as well.
| | 03:54 | We don't mean 3D in that
sense, but a third measure.
| | 03:57 | So clicking on the data here where
we're showing different ages of people and
| | 04:02 | their salaries and their job rating, we can
show all three of those by way of a Bubble chart.
| | 04:08 | Choose Insert > Other Charts > Bubble.
| | 04:12 | I'm going to pick the second
one, Bubble with a 3D effect.
| | 04:16 | At first glance here, it doesn't look so
good, but let's make some changes to it.
| | 04:21 | First of all, the ages here go from about,
we can see across the bottom, 20 to 60.
| | 04:26 | So I'm going to simply right-click on
the bottom here and activate the Format
| | 04:31 | Axis dialog box, and under the Minimum
amount here, change that to be 20, and
| | 04:37 | then jump into the Maximum amount, make that be--
| | 04:40 | well, it's currently 60--
| | 04:41 | let's make it be 55.
| | 04:42 | I think the biggest Age here is around that.
| | 04:45 | And then we can see what happens.
| | 04:46 | That's going to help a bit.
| | 04:48 | The size of the bubbles is a bit large too.
| | 04:50 | We'll deal with that. Let's
first deal with the salaries.
| | 04:52 | It looks like the lowest salary is
above 20,000, but if we were to click on the
| | 04:57 | left vertical axis, the value axis there,
change the Minimum value to be 20,000
| | 05:04 | and the Maximum value to be about 70,000.
| | 05:09 | Now the size of the bubbles relates
to the Job Rating that we're seeing in
| | 05:13 | column C. So the bigger the
bubble size, the higher the rating.
| | 05:18 | But they're all sort of clustered and
here we can't see around them very easily.
| | 05:21 | That's a bit difficult to read,
| | 05:23 | so why don't we scale the bubble size to be 50?
| | 05:27 | And perhaps that's a bit better.
Maybe even 25, see how that's going to look.
| | 05:32 | That might look even better.
| | 05:33 | Maybe that's too small, so you might kick
that around a little bit. I'll make it be 40.
| | 05:37 | That was just to change the size of the bubbles.
| | 05:40 | So this bubble right here, it looks
like it might be the largest, and as
| | 05:43 | we point to it, that's a Job Rating of 9,
representing someone is making 64,000 here.
| | 05:49 | And so in our list over to the left
here, we could scroll up and down, for
| | 05:52 | example, and check out that if we wanted to.
| | 05:54 | So I think you can begin to see, it's
like a Scatter chart and yet the size of
| | 05:58 | the bubbles represents the
rating coming out of column C.
| | 06:03 | One of the big problems with Bubble
charts of course is almost always you have
| | 06:07 | to spend a certain amount of time
explaining how to read the chart.
| | 06:11 | So it's not to say that you
shouldn't use them, but they certainly have
| | 06:13 | some limitations there.
| | 06:15 | Another kind of chart, and
very unusual, is a Radar chart.
| | 06:18 | And I must say I've rarely seen these, and I'll
create one of the data that we're seeing here.
| | 06:23 | We're simply trying to show, based on this data--
| | 06:26 | let me zoom in a little bit on this--
tracking month after month of data, we
| | 06:31 | realize our orders occur differently on
different days of the week, and we even
| | 06:36 | have orders on Saturday as well
too, and our shipments do too.
| | 06:39 | Let's compare the two of these.
| | 06:41 | And certainly a variety of different
chart types might work here, but a Radar
| | 06:45 | chart might show us some spikes.
| | 06:47 | So let's simply click in the data here,
go to the Insert tab, choose Other
| | 06:52 | Charts, and a Radar chart.
| | 06:54 | I'm going to choose the
first one here. And we see this.
| | 06:57 | Let me zoom back a little bit or make
this a bit smaller, and we'll see what's
| | 07:01 | going on here, and close
this dialog box. There we go!
| | 07:05 | So based on the data and looking at
the chart, I think you can begin to
| | 07:08 | see what's happening.
| | 07:09 | There is one day where the shipments
exceeded the orders, one day of the week.
| | 07:13 | It looks as if on Wednesdays we do
more shipping than we actually get orders.
| | 07:17 | And you can see in the chart here how
the red line which represents shipments
| | 07:21 | sticks out a bit farther here.
| | 07:23 | And we can see the relative
comparison of the two on other days as well.
| | 07:28 | So it's a different way of viewing the data.
| | 07:31 | Don't try and view this as a
three-dimensional object as I have done at different times.
| | 07:35 | It looks like a cube. Ignore that part of it.
| | 07:37 | Think of it as a web perhaps.
| | 07:38 | It's called a Radar chart.
| | 07:40 | Not that frequently used. You might
see them from time to time, definitely
| | 07:44 | specialized use, as we saw earlier
with Bubble charts and Doughnut charts.
| | 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 | have, or you didn't think you needed,
and then you want to change the chart.
| | 00:08 | You can do this pretty easily with
just standard copy/paste techniques.
| | 00:12 | In this worksheet, we currently have no
chart, but let's create one based on the
| | 00:16 | data from January through December here,
Domestic, Europe, Asia, just the data
| | 00:21 | here. Let's not include the Totals to the
right. And a quick way to create a chart
| | 00:25 | is Alt+F1 right here on the worksheet.
| | 00:28 | So we got a fairly decent-looking chart,
| | 00:30 | no titles in there just yet.
| | 00:31 | Then it occurs to you that maybe
you would like to put in averages.
| | 00:35 | We don't have any average data there
just yet, so let's put in the word 'Average'
| | 00:38 | here in cell F4 and then, below this
for each month once that's in place, we'll
| | 00:43 | just highlight all of these and use
the function =average (. And we need only
| | 00:50 | highlight cells B5 through D5. And then
since we have got all of these cells
| | 00:55 | selected ahead of time, we can just
press Ctrl+Enter to get those formulas in
| | 00:59 | place, maybe adjust the title a little
bit here, right-align it, make it bold.
| | 01:05 | Let's add this data to the chart.
| | 01:07 | Now whether it was there or we just
created it makes no difference. We simply
| | 01:10 | want to take this data and do a copy paste.
| | 01:13 | So I'll right-click Copy on the data, and
we are going to paste it. And you might
| | 01:18 | think you could just paste it right in
the body of the chart, say in the plot
| | 01:21 | area, but if you try that,
there's no paste option.
| | 01:25 | Do this in the chart area, that area
along the perimeter of the chart where you
| | 01:30 | have got a lot of white space, say here.
| | 01:32 | So I am going to right-click on
the white space here and paste.
| | 01:37 | And we now see that the averages
are here, along with the legend update.
| | 01:41 | Now that might not be the best choice,
so in this situation what might work
| | 01:45 | better here is to take the average series,
| | 01:48 | now I just clicked on it once here,
and change the chart type of that.
| | 01:52 | So on the Design tab in the Ribbon up
above, choose Design, the leftmost button,
| | 01:58 | Change Chart Type and let's pick one of
our line option choices, perhaps the one
| | 02:02 | that has markers on right here.
| | 02:04 | You can double-click it
to make it slightly faster.
| | 02:07 | So we've not only added data to the chart,
but we have changed the chart type of the added data,
| | 02:12 | and you can do that wherever necessary.
| | 02:14 | You can copy data from different parts
of not only this worksheet, but from other
| | 02:17 | worksheets as well, to make
changes or adjustments to a chart.
| | 02:21 | Sometimes the reverse is true.
| | 02:23 | Now this might make our average seem
less important, but sometimes you will say
| | 02:26 | let's just take out the data.
| | 02:28 | So if I didn't want Asia here and maybe
I change my mind in the process, but I
| | 02:32 | am clicking the color for Asia, and all
the Asia columns are selected, I could
| | 02:37 | simply press the Delete key and
Asia disappears from the chart.
| | 02:41 | Now, that doesn't change the average,
so this would be misleading right now,
| | 02:45 | unless I change the formula.
| | 02:46 | But it is to point out that
you can delete as well as add.
| | 02:49 | So I am going to press Ctrl+Z,
meaning undo, to go back to where we were.
| | 02:53 | So you can copy and paste data
from any location into a chart.
| | Collapse this transcript |
| Creating charts from multiple data sources | 00:00 | You can base a chart on data from
nonadjacent ranges, either on the same
| | 00:04 | worksheet at the time you create a chart,
but you can even base a chart on data
| | 00:10 | that's on different
worksheets, or even different workbooks.
| | 00:14 | However, you must first create a chart
before pasting that data in from other locations.
| | 00:20 | Quick reminder here:
| | 00:21 | in this particular sheet right
here where there isn't a chart,
| | 00:24 | if we didn't want to show the data for
Domestic and Asia, we don't have to hide
| | 00:29 | column C or delete it--in other words we
don't want to show Europe--we can simply
| | 00:32 | highlight this data right here, let go
of the left mouse button and then hold
| | 00:37 | down the Ctrl key and highlight the
Asia data. And if I press Alt+F1, we are
| | 00:41 | going to see a chart here without Europe.
| | 00:44 | So that's easy and straightforward.
| | 00:46 | But here's a different situation.
| | 00:47 | I have got a sheet called California
with California data on it, and Arizona,
| | 00:52 | same layout--and that's not a
prerequisite--and Oregon. They just happen to
| | 00:56 | have the same layout.
| | 00:57 | I would like to create a chart that shows
the sales from all three of these together.
| | 01:02 | Let's just start by creating a chart on
one of these sheets, say California here.
| | 01:07 | And I am going to highlight just this
data right here to create a chart showing
| | 01:12 | sales data January through June.
| | 01:15 | Once again Alt+F1, a reasonably fast
way to get there. There is the chart.
| | 01:20 | The legend to the right says Sales.
We'll have to adjust that eventually.
| | 01:24 | Well, let's go to the Arizona sheet,
and we want to be highlighting the same
| | 01:28 | relative range, this data right here.
Highlight this data, right-click, and copy.
| | 01:35 | Go back to California sheet, and in
the chart area near the outer parameter,
| | 01:40 | right-click and paste.
| | 01:42 | Notice the legend, not the way
we want it to be. We will adjust that.
| | 01:46 | Now let's go to the Oregon sheet and do
this same kind of thing, highlight these
| | 01:52 | cells right here, right-click, and copy.
| | 01:55 | Over to California sheet where
the chart is and in the chart area,
| | 01:59 | right-click and paste. There we go.
| | 02:04 | So the data is all there, and
it reflects the data accurately.
| | 02:07 | We can bounce back and forth and check
that out for sure, but we do need to make
| | 02:11 | some adjustments to the legend.
| | 02:13 | So let's right-click, for example, on
one of the California bars, the blue one,
| | 02:17 | and go to Select Data,
| | 02:20 | and notice we see the word Sales there
three times, and that of course is what we
| | 02:23 | are seeing in the legend.
| | 02:24 | Let's take the first one here and edit
and see what this is actually referring to.
| | 02:29 | Its referring to the California data, but
what we want to see here instead of the
| | 02:34 | word Sales, you see how it says
California A4, its picking up the word Sales,
| | 02:39 | we want it to actually have
the word California in it.
| | 02:42 | So I'm editing here, backspacing, and
putting California within double quotes,
| | 02:48 | one at the end there and one in
front of the C, and then OK, and watch the
| | 02:52 | legend on the right.
| | 02:53 | We now see California there.
| | 02:55 | So we've got to do this two more times.
The second Sales here, we click Edit.
| | 02:59 | It's going to say Arizona. I am going
to click on that top panel, put in the
| | 03:03 | trailing double quote and also an
initial double quote in front of the A and
| | 03:07 | behind the equal. Click OK and that
cleans up that one and then Sales here, the
| | 03:12 | third one, Edit, same idea.
| | 03:14 | Just the word Oregon in double
quotes here. There we go. OK.
| | 03:20 | And in a similar fashion, we could have
copied and pasted data from other workbooks
| | 03:25 | as well, in this case just other
worksheets of the same workbook.
| | 03:29 | So a chart can be based on data from
different locations, not only from the same
| | 03:35 | worksheet, but also from different
worksheets, or even different workbooks.
| | Collapse this transcript |
| Adding new data using a table| 00:00 | With certain kinds of dynamic charts,
you want them to be updated monthly,
| | 00:04 | daily, maybe even hourly--
depends upon the nature of the data.
| | 00:08 | In the chart that we're looking at here,
this line chart, maybe we are going to
| | 00:12 | add data every month.
| | 00:14 | If you look at the information in column
A, we see that it does cover the period
| | 00:18 | up to December 2010, and maybe we're
going to put in the January 2011 data, then
| | 00:24 | February, and so on.
| | 00:25 | Now when you do click on the line chart, as
I'm doing here, click on the actual line,
| | 00:30 | you see the data being referred to.
| | 00:33 | One approach to this updating could be,
well let's just put in the new data here
| | 00:37 | and then readjust those
markers simply by dragging them down.
| | 00:41 | Nothing wrong with that.
| | 00:42 | Another approach could have been that
when the chart was created maybe you
| | 00:46 | highlighted cells like this, and it
would have left gaps over here to the right.
| | 00:51 | In fact, I could show the effect of this right
now simply by pressing Alt+F1. This will
| | 00:56 | create a new chart on top of this
Column chart this example here.
| | 00:59 | You can see what's happening there.
The reason this looks a little bit
| | 01:02 | strange, by the way, is because
of what Excel does with dates.
| | 01:05 | So ignore that aspect for the moment,
just to show what we have going on down here.
| | 01:09 | Let me get rid of that.
| | 01:10 | Okay, so let's show what would
happen here if we were to add, simply by
| | 01:14 | dragging downward, maybe with a
right drag, Fill Months. Nothing has happened
| | 01:19 | in the chart at all.
| | 01:20 | I'll put in the new value of
185 and nothing has happened.
| | 01:24 | So certainly one way to adjust this, as
I had suggested, was to click the line,
| | 01:29 | take these choices here. We can drag
the markers at the bottom here downward to
| | 01:34 | expand the data referred to in the chart.
| | 01:37 | So now we have picked up January
2011 with the values appropriately.
| | 01:42 | So you could do that every month.
Maybe that's not a whole of work.
| | 01:45 | But there could be times when you're
dealing with data that's updated more often,
| | 01:49 | or maybe you just want this
data to be more automatic.
| | 01:52 | What if we were to turn this data into a table?
| | 01:54 | Now, table is a new feature in Excel 2007.
| | 01:57 | It's very much like the old list
that had existed in prior versions.
| | 02:02 | If we define this data as a table, then
as we add information to the bottom of
| | 02:07 | it, either the numbers and/or the dates,
the chart will automatically expand.
| | 02:13 | So let's convert this into a
table by doing one of three things.
| | 02:16 | You can click anywhere within the data,
and then from the Insert tab, you could
| | 02:21 | insert a table there.
| | 02:23 | From the Home tab, you can start here
by formatting as a table, or you could
| | 02:28 | press Ctrl+T or Ctrl+L, a lot of different ways here.
| | 02:31 | Format as a Table, and right away it comes
up with the visual, which isn't that important.
| | 02:36 | I'll just pick this one.
| | 02:37 | We see what's going on there.
| | 02:39 | It says, in effect, you want to do it
for these cells? Yes, I do. Click OK.
| | 02:43 | So what's different now? We've got a chart.
| | 02:45 | I'm going to simply drag this downward.
| | 02:47 | You see what's happening in the
chart already and the value here.
| | 02:51 | Now in dragging that I made a mistake.
I repeated the dates, so what I meant to
| | 02:55 | do, and I'll do it now, is either drag
with the right mouse button downward to
| | 02:59 | increment it by a month.
| | 03:01 | Now we're going to see February,
and then I'll put in the amount.
| | 03:04 | I want to emphasize too, you will
be able to do this manually as well.
| | 03:07 | Next number of 195, let's say,
and let's see what's happening there.
| | 03:11 | So March data is now available.
Maybe I'll put in the sales number first.
| | 03:16 | So right here I'm going to type in
230, and so we don't have to manually
| | 03:20 | adjust it each time.
| | 03:21 | And then over here I'll put in the March
data, and maybe I'll just type it this time.
| | 03:26 | Now the actual content of this cell,
for example, you can see it in the formula bar,
| | 03:30 | 2/1/2011, so down here 3/1/11, Enter,
and you see what's happening there.
| | 03:38 | So you can enter either one.
| | 03:40 | And sometimes you just type them in.
4/1/11 and over here this value is 200.
| | 03:47 | So by converting the data into a table--
and there are some other advantages to
| | 03:51 | that too, some of which are just
formatting. But even if you haven't use that
| | 03:54 | feature very heavily, this is an ideal
way to set up certain charts so that
| | 03:59 | they really do become dynamic, and it makes
the chart grow and grow and grow and grow.
| | 04:04 | Now in the process here, if we do this
month after month into other years as
| | 04:08 | well, we might want to
consider other aspects of the design.
| | 04:11 | But the main point here is we've made
it dynamic simply by converting the
| | 04:15 | source data into a table.
| | Collapse this transcript |
|
|
12. Printing and Sharing ChartsPrinting charts | 00:00 | At different times using
charts you may want to print charts.
| | 00:04 | Now, sometimes a chart is on its own sheet.
| | 00:06 | You can certainly print that
single sheet with the chart alone.
| | 00:10 | Sometimes you'd like to print
the chart that's on a worksheet.
| | 00:13 | For example, the worksheet that we're seeing
here has five charts to the right of the data.
| | 00:18 | We might want to print all the data and
all five charts, or maybe the data and
| | 00:23 | three of the charts, or maybe
just a chart here, a chart there.
| | 00:27 | We can do all these things,
and every print feature that we use is very
| | 00:31 | reminiscent of printing that you
might have done previously, working with
| | 00:34 | just worksheet data.
| | 00:36 | Now quick reminder here,
| | 00:38 | there is no other sheet here in this
workbook right now that has a chart on
| | 00:42 | it, but I think a lot of you know
that when you do create a chart you can
| | 00:45 | certainly put it on the separate sheet.
Or if you happen to right-click on any
| | 00:50 | existing chart, for example, you
can move a chart to its own sheet.
| | 00:54 | So let's just set up a simple situation here.
| | 00:57 | I'll use the latter example.
| | 00:59 | I want is chart here to be on a separate sheet,
| | 01:02 | so I'll just move it to where? A new
sheet. Chart1, Chart2, click OK, and there
| | 01:09 | it is on a new sheet.
| | 01:11 | And when a chart is on its own sheet,
maybe you just put it there or you
| | 01:14 | moved it as I did here,
| | 01:15 | we can simply jump right into
Print Preview. A couple ways to do this.
| | 01:20 | If we simply go to Page Layout, we
could then click the arrow in the lower
| | 01:24 | right-hand corner of the Page
Setup group. This is one way.
| | 01:27 | Go to Print Preview this way, and we're
likely to see the chart, as I'm seeing it
| | 01:31 | here, pretty much the way it's
been appearing on the screen.
| | 01:34 | Now you can go to Page Setup, make a
few adjustments here and there, maybe with
| | 01:38 | regard to margins and header, footer--
| | 01:40 | the same kinds of things that you
might do with worksheet data. And in some
| | 01:44 | situations here maybe portrait versus
landscape, make that change as well.
| | 01:48 | Printing charts that are on a separate
sheet is usually not much work. We're all set.
| | 01:52 | We could just click Print
from here if we wanted to.
| | 01:55 | Now I'm going to press Escape or close
Print Preview here and go back to the
| | 01:59 | worksheet that contains charts. And of
course the chart here had been moved.
| | 02:03 | I might drag one of
these up there, put it there.
| | 02:06 | It's just a question of what
it is I would like to print.
| | 02:09 | And sometimes maybe I want to
print just one of these charts.
| | 02:13 | Maybe I want to print this one here, the
line chart here that says Regional Sales.
| | 02:18 | Once again we might want to go--
and it's a good idea let's say--always to go to
| | 02:22 | Print Preview first, but once again we
could, by way of Page Setup, go right into
| | 02:29 | Print Preview from there,
| | 02:30 | here we are, and take a look at
this and I think it's apparent here,
| | 02:33 | we would want this to be
landscape in this particular example.
| | 02:36 | So up to Page Setup, choose Landscape,
and we're probably pretty much set there.
| | 02:42 | Now what you might want to do in this
example here, and it didn't occur to me
| | 02:45 | perhaps until I saw the Print Preview,
is that even though I might have been
| | 02:49 | happy with the proportions of the chart,
if I somehow wanted to fill out the
| | 02:53 | page a little bit better here,
| | 02:56 | what I'm going to have to do here is
close the Print Preview and then manually
| | 02:59 | experiment a little bit by making
this chart taller. And then we could jump
| | 03:03 | back into Print Preview.
| | 03:04 | I'll use that same button there, Print Preview.
| | 03:07 | At least it's filling out the
paper better if that's of concern to you.
| | 03:10 | If that looks good, Print.
| | 03:13 | Now, increasingly with Microsoft
recognizing that many people put charts right on
| | 03:18 | the worksheet, there certainly are
going to be times when you want to print
| | 03:21 | charts as well as the data.
| | 03:24 | Although we could print all these charts,
suppose we just say arbitrarily that
| | 03:27 | the three of them are more useful,
maybe the First-Half Sales to are right,
| | 03:31 | the one below it, and the column chart below
the data itself, these three, not the pie chart.
| | 03:35 | What's the best approach here?
| | 03:37 | I think the best way to start here is to go
the lower right-hand corner of the status bar.
| | 03:42 | We've got three buttons down here.
| | 03:45 | The one to the right, the rightmost of
the three, Page Break Preview, click this.
| | 03:50 | Usually this causes a zoom back,
and we do see blue borderlines out there.
| | 03:55 | I'm going to close this dialog box.
| | 03:58 | If I were to head down the path of
printing right now, it would be printing all
| | 04:02 | this data here, and you can see
the page 2 there on two pages.
| | 04:05 | Now if I don't want to print this
example here of the pie chart, I'll simply
| | 04:09 | take the blue border off to the right,
put the mouse on it. We'll see that two-way
| | 04:13 | arrow. Drag this into maybe about there.
| | 04:16 | And momentarily there, you probably saw
there is a page break, and I'll move this
| | 04:20 | chart aside so you can see it a bit better.
| | 04:22 | That's a dotted line. Obviously we
don't want our page break to appear there.
| | 04:26 | Take the dotted line and move it to the right.
| | 04:28 | And now it's a little bit of back and
forth, a little bit of experimenting, maybe
| | 04:31 | we'll move this chart back here,
and it is best when you move a chart
| | 04:35 | probably to drag the border.
| | 04:36 | There we go, a little bit better.
| | 04:38 | I'm going to press a couple of Ctrl+Zs to undo to
maybe clean that up, drag that border again. There we go.
| | 04:44 | If you are resizing charts and you're
trying to establish some sort of order,
| | 04:48 | or tidiness, when you drag a border
edge or corner on these charts, hold down
| | 04:53 | the Alt key, and then it forces the
borders to line up perfectly with cell
| | 04:58 | boundaries. And sometimes that's helpful
just to establish a certain pattern there
| | 05:02 | of neatness. I'm going to do this bottom there
and on this green bordered chart, take the upper
| | 05:05 | left, hold down the Alt key. Sometimes
it takes a few attempts. There we go.
| | 05:10 | And be sure let go off the mouse
when you're doing these things too.
| | 05:13 | So I'm not suggesting this is exactly
critical, but sometimes we just have that
| | 05:16 | need to make things look a little
neater, a little tidier, possibly that.
| | 05:20 | And maybe on the bottom here one more
time and that's about it, and move that
| | 05:24 | blue line up possibly at the bottom,
and let's jump into Print Preview.
| | 05:28 | Now when you click in a worksheet, you
can jump into Print Preview quickly with
| | 05:31 | Ctrl+F2. It will take you there immediately.
| | 05:34 | That's looking pretty reasonable.
| | 05:36 | It's not always that it works this way
| | 05:37 | though. Sometimes you have to
consider resizing the charts and even now, I
| | 05:41 | might say, the chart to the right is
a little bit narrow, and so on.
| | 05:45 | So you've your own needs regarding how
these charts should look and whether they
| | 05:49 | really need to line up
with gridlines and so on.
| | 05:51 | And when we were ready, we would
simply print from here if we wished.
| | 05:55 | So close Print Preview.
| | 05:57 | So a couple of different approaches there.
| | 05:58 | No matter where that chart is,
you can print the chart only.
| | 06:01 | For example, we could click
here and we've got our preview.
| | 06:04 | Print the chart with the data. In other words, we
highlight the cells we've done, and in
| | 06:09 | the previous example, print a chart
that's on a separate sheet, using standard
| | 06:13 | printing techniques that you're probably
familiar with from your use of printing
| | 06:17 | when printing worksheets in Excel.
| | Collapse this transcript |
|
|
| Conclusion |
