Let's take a look at some chart basics for a quick start.
Here I have information on the different apps and their sales value for 2019.
I want to show this with a chart.
The fastest way to do this is to click anywhere inside the data set and use the shortcut key Alt+F1.
This inserts a column chart.
The reason it inserts a column chart and not another chart type is because when you install Excel first
the default chart is a column chart.
You can change that and I'm going to show you in a second how you can do that.
So that was Alt+F1.
It inserts a chart in the current sheet. If you want to insert a chart as a separate sheet,
in a separate chart sheet, you have to press F11 instead. That created a separate sheet.
It's not a normal worksheet.
It's a chart sheet.
I personally prefer to insert my charts in the same worksheet where my dataset is.
And then once I'm done with the chart, I move it to another worksheet where I create my report. But I
generally don't work with chart sheets.
I don't want to have one entire chart taking up the entire sheet.
If you want to remove that tab, if you don't want it just right mouse click
and select delete and that's gone.
Now let me show you how you can get to this from the ribbon.
So again just click anywhere inside the data set, go to insert and right here you see the different chart
types.
The first one up here that's the column chart.
All I have to do is click on this dropdown and then select the type of column chart I want. And immediately
I see the preview down here so when my mouse rolls over this I can see the different charts. So if I come
down to the 2D bar.
My preview shows the 2D bar.
I can also go to recommended charts here.
I get some recommendations from Excel showing me the different chart types that might fit this dataset
If I like one of them
I just have to click on them.
It is going to insert it in my worksheet.
Let's go back to recommended charts.
So that was under insert, recommended charts.
I can also click on all charts right here and view all the different chart types that Excel has to offer.
Now whenever I select one of these, it kind of shows me a preview of how my end chart is going to look
based on the data set that I have. Now remember I said that the default chart type was a column chart.
Column is right here. On the first clustered column option, right mouse click.
I see set as default chart and there's a tick mark in front of it.
If my default chart is let's say a line chart because that's the type of chart I use often in my
reports.
All I have to do is to right mouse click here and select set as default chart.
So now it gets the tick mark right here.
This means that the next time I use the shortcut key Alt+F1, it's going to insert a line chart.
Right now I'm going to do cancel. And now use the shortcut key alt+F1 and I get a line chart as
my default.
Let's go back to our column chart and take a look at some additional chart options that we have.
I'm just going to delete the line chart here. To bring up the chart options, we just have to first
activate the chart.
So when I click on it we get two tabs up here.
One is called "Chart Design" and the other is called "Format".
A lot of these options are also available when you right mouse click on the different elements in
the chart.
If I come down here and right mouse click. I see select data, major grid lines, minor grid lines and some
of them are available right here.
So we see select data is here and we can take a look at charts elements and we have gridlines, primary
major vertical, minor vertical and so on. Now these two ways are not the only ways you can get to chart
options.
You also have these options right here.
You can go to grid lines and you get the same options that we saw up here. Under color, you can change
the style or the color scheme of your charts. Under filter, you can choose to filter out some elements
if you don't want to see them in your chart.
I'm just going to set this back to select all and apply. Now in addition to this, you can double click
on any element here and you get more options on the side here.
Let me just drag this and put it close to our chart and take a look at some of the options we have
here.
Up here we have some series options we can control the fill and the border of the series and we have
some more properties available here that we can adjust. Now the different elements have different types of
options available.
If I click on the axis instead, I get this time axis options and tick marks, labels, numbers.
These weren't available when I was in the series view.
In this view I didn't have that many options. So these options are very specific to the type of chart element that
we're dealing with.
Now you can click on them here and you see the options here. Or, you can click on this dropdown and see
the different elements that make up the charts.
So plot area that's this area inside the chart.
The chart area
that's the entire chart object. My series, that was series sales 2019.
That's this series right here.
Now let's take a look at some basic formatting for the series.
The main series options are right here.
I could decide to reduce the gap width. So let's say these bars are too thin and they're too far apart.
I can reduce the width between them by reducing this number, let's say to 100 percent.
The bars get thicker and the distance between the bars becomes smaller.
I could change the color of the series by changing the fill options here.
Let's select solid fill and change the color to another color.
Now you also have the option to change the color by going to format and selecting the different fill
option from here as well.
It's just different ways of getting to the option that you need.
If you want to remove any element of the chart you just have to click on it.
Let's say I want to remove the grid lines, I just activate them and then I press the delete key.
If I wanted to add data labels to these, I just have to click on the entire series so just click once
to select the whole series, right mouse click and add data labels.
This looks a bit crowded so I'm just going to expand my chart here and delete the y axis.
Just activate it and delete it here to give these numbers a bit of breathing space.
Now what about formatting of the numbers here.
Let's say I want to add the thousand separator here. By default, the formatting of data labels is derived
from the formatting of the data in the cells.
So to change that, all I have to do is change the formatting of these.
I'm going to use the shortcut key.
Control+1, go to number, use a thousand separator and 0 decimal places.
If I'm still not so happy with the labels looking like this that they're too much stuck together, I can
position them differently.
To do that I first have to activate the labels so just click on one of them to select the whole thing.
Make sure you don't double click because the moment you double click, you're only selecting that one
label.
I'm just going to click away, click once to select everything, go to label options here and this time
I'm going to select "inside end". Now I also want to turn this around.
Click on the size and properties options here. Under alignment, change the text direction to
270 degrees.
I'm just going to change the color of my series so that I can read the numbers better.
I'll just make these lighter.
You can also make the labels lighter.
Just make sure you don't make it white.
If you have columns that are too small because then you can't read the whole number.
So just go here.
In this case with a lighter grey. Ok so it takes some time to find where the options are.
What I suggest you do after this lecture is to take some time, follow what I just did. But then also
go through in a bit more detail what these different options do.
For example, what options are available for the axis.
Click on axis options and check them out.
What options are available for data labels.
Click on them.
Check these out. For the series as well.
The title of your chart is automatically derived from the series.
If you just have one series. You can type it in manually as well.
You just have to highlight it first and then just start typing.
So just sales, let's say.
What you can also do though is to connect it to a cell.
First activate the chart title then immediately go to the formula bar and type in an equal
and then use a cell reference. In charts you can not type a formula as the title. I can't go and say
equals this cell and this cell.
If I press enter, I'm gonna get an error.
It just accepts one cell reference.
So if you do want to connect different cells together, you have to do that in a cell using any type of
formula that you like.
So let's say here.
I would type in the formula equals productivity division.
I'm going to add a colon, space, quotation mark then "&" and combine it with this and press enter.
Now this is going to update automatically.
So once I get 2020 data, the chart title is going to be updated automatically.
Now let's say I wanted to quickly change the chart type and decide I don't want a column chart.
I want to have a bar chart.
All I have to do is right mouse click on the chart and select change chart type.
You can also do that by going to chart design and selecting change chart type from here.
Now let's go and take a look at a bar chart.
Let's say I want this one. I just click on it and then click on OK.
And I have my bar chart.
But this time I don't want to have the data labels like this so I'm going to activate the data labels,
go to label options and click on "outside end". And I'm going to turn them around to the way they originally
were which was horizontal.
Again, if I want to make these bars thicker.
Basically if I want to reduce the gap width between them, all I have to do is reduce the gap width further.
Notice that I'm always clicking on the element first that I want to format.
Otherwise, I'm not going to have the correct options. Type in 40, press enter and my bars are thicker.
To move a chart to another sheet.
You can either use Control+X to cut the chart, go to your new sheet and press Control+V. Another way to
move your chart is to right mouse click on the chart area and select move chart. You have the option
of creating a new chart sheet for it or you can move it as an object inside another sheet.
Let's say I want to move it to sheet 1, I select sheet 1 from the dropdown, click on ok and it
moves it right there.
That's the basics of working with Excel charts. In the next lecture,
let's take a look at creating a chart that allows us to compare two different series with one another.