| 00:00:00 |
00:00:01 |
>> Mike: Hey guys, it's Mike here. |
| 00:00:01 |
00:00:03 |
And this will be the fifth and final video |
| 00:00:03 |
00:00:06 |
in my Excel MOS Crash Course. |
| 00:00:06 |
00:00:08 |
In this video we're gonna go over |
| 00:00:08 |
00:00:10 |
how to create charts and objects |
| 00:00:10 |
00:00:11 |
in your Excel MOS Exam |
| 00:00:11 |
00:00:15 |
which is gonna be about
15 to 20% of your exam. |
| 00:00:15 |
00:00:16 |
And the first part is I'm gonna show you |
| 00:00:16 |
00:00:20 |
how to create charts and
alter current chart elements |
| 00:00:20 |
00:00:23 |
and how to format graphic
elements within a chart |
| 00:00:23 |
00:00:27 |
and as well how to insert and
format objects like pictures. |
| 00:00:27 |
00:00:28 |
So when it comes to creating charts, |
| 00:00:28 |
00:00:29 |
the nice thing is we won't actually |
| 00:00:29 |
00:00:31 |
have to create a chart from scratch. |
| 00:00:31 |
00:00:33 |
Usually on your Excel MOS Exam |
| 00:00:33 |
00:00:36 |
you'll just have to alter a
few features of the chart. |
| 00:00:36 |
00:00:37 |
For example for this first one, |
| 00:00:37 |
00:00:40 |
we're gonna switch the rows |
| 00:00:40 |
00:00:42 |
or switch row and column data. |
| 00:00:42 |
00:00:44 |
And the tricky part about this |
| 00:00:44 |
00:00:46 |
it might be worded in different ways, |
| 00:00:46 |
00:00:48 |
and you might have something like, |
| 00:00:49 |
00:00:52 |
you won't have them come out
and say switch row and column, |
| 00:00:52 |
00:00:56 |
but it might say like
in this example here, |
| 00:00:56 |
00:01:00 |
make sure that the shoe type |
| 00:01:00 |
00:01:01 |
is along the x-axis |
| 00:01:01 |
00:01:02 |
which is not right now, |
| 00:01:02 |
00:01:03 |
it's sort of the shoe type |
| 00:01:03 |
00:01:06 |
goes along the y-axis |
| 00:01:06 |
00:01:07 |
it's the series here. |
| 00:01:07 |
00:01:10 |
So if we click on the whole chart, |
| 00:01:10 |
00:01:14 |
and then in the Chart Tools Design tab, |
| 00:01:14 |
00:01:16 |
click the Switch Row and Column, |
| 00:01:16 |
00:01:18 |
now the shoe types become, |
| 00:01:20 |
00:01:22 |
the data runs along the x-axis, |
| 00:01:22 |
00:01:24 |
and the months are down here |
| 00:01:24 |
00:01:26 |
as series or vice versa. |
| 00:01:26 |
00:01:28 |
So if you had to switch those |
| 00:01:28 |
00:01:29 |
or you might look for a word |
| 00:01:29 |
00:01:32 |
that says swap the data |
| 00:01:32 |
00:01:34 |
or it might say you want |
| 00:01:34 |
00:01:37 |
instead of having the
months along the x-axis, |
| 00:01:37 |
00:01:39 |
we want the shoe type,
something like that. |
| 00:01:39 |
00:01:41 |
So the next skill you have to
know for your Excel MOS Exam |
| 00:01:41 |
00:01:46 |
when it comes to creating
charts is how to add a series. |
| 00:01:46 |
00:01:48 |
So you see that there's two series here. |
| 00:01:48 |
00:01:49 |
We're gonna click on the chart, |
| 00:01:49 |
00:01:52 |
we see that October
November are the two series |
| 00:01:52 |
00:01:54 |
and we wanna add another one. |
| 00:01:54 |
00:01:57 |
So if it's just simply add a new series, |
| 00:01:57 |
00:02:00 |
we can drag this blue corner over here |
| 00:02:00 |
00:02:03 |
and include the December value, |
| 00:02:03 |
00:02:05 |
but you might also have to
take this a step further. |
| 00:02:05 |
00:02:08 |
In the Chart Tools tab you might have to, |
| 00:02:08 |
00:02:10 |
I'm gonna select the data, |
| 00:02:10 |
00:02:12 |
and then let's say they wanted you |
| 00:02:12 |
00:02:15 |
to also edit the title of the new series |
| 00:02:15 |
00:02:16 |
or add the new title. |
| 00:02:16 |
00:02:18 |
So you could do that here |
| 00:02:18 |
00:02:20 |
in this Select Data Source dialog box |
| 00:02:20 |
00:02:24 |
just by clicking on this
series, pressing Edit. |
| 00:02:24 |
00:02:28 |
And then instead of using the
value from my data field here, |
| 00:02:28 |
00:02:30 |
I'm gonna type in, maybe
they want you to type in |
| 00:02:30 |
00:02:33 |
the whole word for the months |
| 00:02:33 |
00:02:35 |
or just this one in the series name. |
| 00:02:35 |
00:02:36 |
So you might have to change |
| 00:02:36 |
00:02:37 |
the series name as well |
| 00:02:37 |
00:02:38 |
that you just added. |
| 00:02:38 |
00:02:40 |
So December, we press OK, |
| 00:02:40 |
00:02:41 |
and you see that the new series name |
| 00:02:41 |
00:02:43 |
is the full December. |
| 00:02:44 |
00:02:45 |
So when it comes to creating charts, |
| 00:02:45 |
00:02:47 |
you also might be asked
something challenging |
| 00:02:47 |
00:02:50 |
like let's make another chart |
| 00:02:50 |
00:02:52 |
but only include November data. |
| 00:02:52 |
00:02:55 |
Now the tricky part about
this is you might think, |
| 00:02:55 |
00:02:56 |
okay, well then we want November data |
| 00:02:56 |
00:02:58 |
so I'll just highlight this column, |
| 00:02:58 |
00:03:01 |
but they want the November
data and the shoe type. |
| 00:03:01 |
00:03:02 |
So just like this chart |
| 00:03:02 |
00:03:04 |
but they also have to mention |
| 00:03:04 |
00:03:06 |
if it's the November data, |
| 00:03:06 |
00:03:09 |
it's November data
pertaining to the shoe type. |
| 00:03:09 |
00:03:11 |
So we also have to highlight that row, |
| 00:03:11 |
00:03:13 |
or sorry, column as well. |
| 00:03:13 |
00:03:15 |
And then you're probably asked |
| 00:03:15 |
00:03:16 |
to choose a specific type of chart, |
| 00:03:16 |
00:03:18 |
let's just keep it with the column chart |
| 00:03:18 |
00:03:20 |
like we made in the last one. |
| 00:03:21 |
00:03:23 |
Okay so that would be the November data. |
| 00:03:23 |
00:03:27 |
Don't forget that this is all
based on the shoe type as well |
| 00:03:27 |
00:03:30 |
and it's not just the November data. |
| 00:03:30 |
00:03:32 |
Even though you're asked to
only include the November data, |
| 00:03:32 |
00:03:35 |
it's November data
corresponding to the shoe type. |
| 00:03:35 |
00:03:37 |
So a second skill that you have to know |
| 00:03:37 |
00:03:38 |
for your Excel MOS Exam |
| 00:03:38 |
00:03:40 |
is how to format graphic elements |
| 00:03:40 |
00:03:43 |
and that whether you're
dealing with charts or objects. |
| 00:03:43 |
00:03:44 |
And when it comes to the charts, |
| 00:03:44 |
00:03:47 |
you're gonna have to know
how to use Chart Elements. |
| 00:03:47 |
00:03:48 |
So when you click on this chart, |
| 00:03:48 |
00:03:50 |
there's a little plus
sign here, Chart Elements, |
| 00:03:50 |
00:03:54 |
and you might have to add
an axis title or a legend. |
| 00:03:54 |
00:03:56 |
And with these you have
additional options, |
| 00:03:56 |
00:03:58 |
like for the legend example, |
| 00:03:58 |
00:04:01 |
you can put it at the
top, right, or bottom. |
| 00:04:01 |
00:04:02 |
Right now it's on the right, |
| 00:04:02 |
00:04:03 |
but if you're asked to
put it on the bottom, |
| 00:04:03 |
00:04:06 |
switch it up, put it on
the bottom like this. |
| 00:04:06 |
00:04:11 |
You also might be asked
told to add a chart title |
| 00:04:11 |
00:04:12 |
and then rename it. |
| 00:04:12 |
00:04:14 |
Let's say this one is shoe type, |
| 00:04:14 |
00:04:15 |
this is how I would do that. |
| 00:04:18 |
00:04:19 |
We press Enter or no, |
| 00:04:19 |
00:04:20 |
probably not press Enter, |
| 00:04:20 |
00:04:22 |
just click away from the chart. |
| 00:04:22 |
00:04:26 |
So that's how you would change
some of the chart elements. |
| 00:04:26 |
00:04:28 |
Another way to change chart elements |
| 00:04:28 |
00:04:30 |
would be to change the chart style. |
| 00:04:30 |
00:04:33 |
So you might be asked to do a
style with a dark background |
| 00:04:33 |
00:04:37 |
or given specifically
style, turn it to Style 8, |
| 00:04:37 |
00:04:38 |
which we can do here. |
| 00:04:38 |
00:04:42 |
You might also be asked
to change the layout type. |
| 00:04:43 |
00:04:45 |
So you'll be given the number Layout 5 |
| 00:04:45 |
00:04:47 |
and change that to Layout 5 here. |
| 00:04:47 |
00:04:49 |
And those are some ways |
| 00:04:49 |
00:04:52 |
that you can alter the
look of your charts. |
| 00:04:52 |
00:04:54 |
So one last skill when
it comes to formatting |
| 00:04:54 |
00:04:56 |
graphic elements of a chart |
| 00:04:56 |
00:04:59 |
would be to move the chart
to its own chart sheet. |
| 00:04:59 |
00:05:02 |
So the way to do that is
click on the chart area, |
| 00:05:02 |
00:05:05 |
and then in this Chart Tools Design tab, |
| 00:05:05 |
00:05:07 |
there's a Move Chart button. |
| 00:05:07 |
00:05:08 |
And we'll just click on that. |
| 00:05:08 |
00:05:10 |
And if it says new sheet, |
| 00:05:10 |
00:05:12 |
don't confuse that with object, okay? |
| 00:05:12 |
00:05:15 |
So right now this chart is
an object on this worksheet. |
| 00:05:15 |
00:05:18 |
It doesn't take up the whole sheet, |
| 00:05:18 |
00:05:22 |
but creating a new sheet from a graph |
| 00:05:22 |
00:05:24 |
will make you make the
chart the whole sheet, |
| 00:05:24 |
00:05:27 |
so make sure you know
what they're asking you. |
| 00:05:28 |
00:05:30 |
Do they want you to insert the chart |
| 00:05:30 |
00:05:31 |
on the worksheet as an object |
| 00:05:31 |
00:05:35 |
or do they want its own
separate chart sheet with it? |
| 00:05:35 |
00:05:36 |
So new sheet. |
| 00:05:36 |
00:05:38 |
And they'll probably give you a name |
| 00:05:38 |
00:05:40 |
like shoe type or something like that. |
| 00:05:40 |
00:05:41 |
Then when we press OK, |
| 00:05:41 |
00:05:43 |
you can see something at the bottom, |
| 00:05:43 |
00:05:44 |
a new worksheet is gonna pop up |
| 00:05:44 |
00:05:47 |
and it's gonna have
this whole graph on it, |
| 00:05:47 |
00:05:49 |
and this is actually called a chart sheet. |
| 00:05:49 |
00:05:52 |
So that is the last skill |
| 00:05:52 |
00:05:54 |
for when it comes to
formatting graphic elements. |
| 00:05:54 |
00:05:57 |
So beyond charts, you might
actually have to add in |
| 00:05:57 |
00:05:59 |
some pictures on your Excel MOS Exam, |
| 00:05:59 |
00:06:02 |
so I'm gonna show you how
to insert a picture first. |
| 00:06:02 |
00:06:05 |
So it'll probably be saved
in your Documents folder. |
| 00:06:06 |
00:06:08 |
So to insert pictures from there, |
| 00:06:08 |
00:06:11 |
we go to the Insert tab, click Pictures. |
| 00:06:11 |
00:06:13 |
And my folders are gonna
be a little bit different. |
| 00:06:13 |
00:06:15 |
So I'm gonna click Pictures |
| 00:06:15 |
00:06:16 |
and then just go to my Picture tab. |
| 00:06:16 |
00:06:18 |
But yours is gonna be in the Document tab. |
| 00:06:18 |
00:06:20 |
So yours would be in Documents. |
| 00:06:20 |
00:06:24 |
I'm just gonna take a picture
from my Pictures folder. |
| 00:06:26 |
00:06:29 |
Search for a goofy picture of me. |
| 00:06:32 |
00:06:35 |
And sure, why not? |
| 00:06:36 |
00:06:38 |
Okay something like that. |
| 00:06:38 |
00:06:42 |
This is something my students
came up with using PowerPoint. |
| 00:06:42 |
00:06:45 |
So anyways yeah, so that's
how we'd insert the picture. |
| 00:06:45 |
00:06:46 |
Make sure that you insert the picture |
| 00:06:46 |
00:06:47 |
on the right worksheet. |
| 00:06:47 |
00:06:50 |
I know that seems simple but it's really, |
| 00:06:50 |
00:06:51 |
it's an easy mistake to make. |
| 00:06:51 |
00:06:55 |
So if it says on the Shoe Sales worksheet, |
| 00:06:55 |
00:06:57 |
make sure you're not in
the Shoe Type worksheet. |
| 00:06:57 |
00:06:59 |
Alright so make sure you're
inserting the picture, |
| 00:06:59 |
00:07:00 |
you don't wanna lose marks |
| 00:07:00 |
00:07:02 |
for putting it on the wrong worksheet. |
| 00:07:02 |
00:07:05 |
And then you might add
some picture effects here |
| 00:07:05 |
00:07:07 |
like Artistic Effects. |
| 00:07:07 |
00:07:10 |
You might have to choose Pencil Grayscale |
| 00:07:10 |
00:07:11 |
or something like that. |
| 00:07:11 |
00:07:15 |
Or you may have to choose
something like a Glow effect |
| 00:07:15 |
00:07:17 |
and then you might have to
choose a more custom glow effect, |
| 00:07:17 |
00:07:21 |
so maybe it was something like
we didn't have a color here, |
| 00:07:22 |
00:07:27 |
let's say green, olive
green, lighter 80%, sure. |
| 00:07:28 |
00:07:33 |
And then but you also have to
not only do that kind of glow, |
| 00:07:35 |
00:07:37 |
but it has to be a certain point, |
| 00:07:37 |
00:07:38 |
so we'll do more colors. |
| 00:07:41 |
00:07:44 |
Start Picture Effects, Glow, |
| 00:07:44 |
00:07:46 |
and we'll do Glow Options, |
| 00:07:46 |
00:07:48 |
'cause it might not be as easy |
| 00:07:48 |
00:07:51 |
as just picking a glow option. |
| 00:07:51 |
00:07:52 |
You might have to go further, |
| 00:07:52 |
00:07:54 |
you might have to increase
the size of the glow, |
| 00:07:54 |
00:07:56 |
make it really big. |
| 00:07:56 |
00:07:57 |
And let me choose a color |
| 00:07:57 |
00:07:59 |
that stands out a little bit more. |
| 00:07:59 |
00:08:01 |
Okay something like that or do a preset. |
| 00:08:03 |
00:08:05 |
Yeah those are the preset colors. |
| 00:08:05 |
00:08:06 |
Or if the color you needed to put in |
| 00:08:06 |
00:08:08 |
wasn't in the Preset colors, |
| 00:08:08 |
00:08:10 |
you could come here, change it, |
| 00:08:10 |
00:08:13 |
and then increase the size as well. |
| 00:08:13 |
00:08:14 |
And one last thing, |
| 00:08:14 |
00:08:16 |
you'll probably have
to do for your pictures |
| 00:08:16 |
00:08:18 |
is to add some alt text. |
| 00:08:18 |
00:08:21 |
So to do that you would click
on the picture or object, |
| 00:08:21 |
00:08:25 |
and then click Edit Alt Text here. |
| 00:08:27 |
00:08:29 |
And then you just type in whatever you, |
| 00:08:31 |
00:08:32 |
whatever the question asked you to. |
| 00:08:32 |
00:08:34 |
Let's say this is Cartoon Mike. |
| 00:08:35 |
00:08:39 |
That is sorta describing the picture. |
| 00:08:40 |
00:08:42 |
And then I exit. |
| 00:08:42 |
00:08:44 |
And that's how you add
an alt text to an object. |
| 00:08:44 |
00:08:46 |
So remember to check and go over |
| 00:08:46 |
00:08:49 |
all of the videos in this video series |
| 00:08:49 |
00:08:51 |
if you really wanna master
your Excel MOS Exam. |
| 00:08:51 |
00:08:53 |
And good luck and I'll
see you in the next one. |