With every version of Excel that get's released, the way you do charts seems to change.
So back in the day there was a really simple four-step wizard where you chose your chart
type, you said where your data was, you set your options and you said where you wanted
to place your chart.
Now these days there's a lot more options available, but the way you do charts has actually
been simplified.
The most commonly used chart in the workplace is the column chart.
What I have on the screen here is a table showing the world's richest people, and from
this data I'm going to produce a column chart.
In subsequent videos, I'll show you some of the other chart types that you have available.
The first thing you need to do before you create any sort of chart is to select the
data.
Now what you'll find is that probably a lot of your time will be spent getting your data
ready, before you produce the chart.
And once the data is in place, you can then produce the job fairly easily.
A lot of people set the data up so that all they've got to do is select the table and
then produce the chart.
In our case here, we don't actually want this second column showing the company.
All we're interested in is the people and their figures.
So what I'm going to do is this.
I'm going to select the first column, which is the name.
I'm then going to hold 'CONTROL' and select these four years worth of data.
So as you can see, I've excluded the company in the second column.
Now from here there's two ways I can go.
I can either choose the 'Insert' tab and about halfway across the ribbon you'll find a Charts
section showing all the various types of chart available.
Now the most common, like I said, is the column chart here.
When I click this I get lots of options.
But there's also things like the Line Charts here, the Pie Charts here, there's various
Tree Maps and other types of charts here and lots of other types of chart for you to explore.
Like I say, I'm going to start with the column chart here.
Even though it's one type of chart, there's lots of different variations and options available.
So for example, do you want to go 2-dimensional or 3 dimensional and the first row and the
second row here show those options.
Do you want to go vertical or do you want to go horizontal?
Now, what it's actually done here is it's showing the various combinations of column
charts and bar charts all in the same gallery.
Sticking with a first row for a second, it's asking, do you want to go 'clustered' or 'stacked'?
Now 'clustered' means you show the bars side-by-side and 'stacked' means one of the top of the
other.
Okay, there's all sorts of options like that.
To keep things simple.
I'm going to choose a simple clustered column.
When you create a chart from the 'Insert' tab, it places the chart as an object on the
same sheet as where your data is.
So as you can see, here's the chart and here's the data right next to it, and if I want to
I can then move this around carefully and place it side by side.
With your selected data like this, you can just simply press F11 on the keyboard.
This option creates a brand new sheet for you and the chart is full size on the sheet.
So as you can see down the bottom, here's Chart4 and here's my original data.
The two things are separate at this point.
Whichever method you use, you have the same options available.
So if you look at the top here, you'll find this new section there called 'Chart Tools'
with two tabs called 'Design' and 'Format', and the 'Design' tab has all the tools you
need to manipulate your chart.
The 'Format' is an extra tab that contains other options for things like WordArt or shapes
or lines.
So if for example, you wanted to put a flashy title at the top, you could use a WordArt
style for that.
If you want us to draw an arrow pointing to something important, to highlight something,
you can either draw an arrow or maybe you want to circle it or highlight in some way.
And all the tools on the 'Format' tab are there for that reason.
So I'm going to concentrate mainly on the 'Design' tab.
You'll also notice in the top right corner here, three buttons.
As you can see, because I've got quite a few people in my data and four years worth of
data for each one, the chart is quite cluttered.
So, the filter is a simple way to reduce the amount of data on your chart.
So when you click on this, it gives you, first of all, your four different years that you
have available.
And secondly, all the people that are on the chart.
So if for example, I just wanted to compare all these people for two years instead of
four, I could untick these two here and now it's just showing 2017 and 2018.
I'll click apply and you can see that the chart itself has been decluttered.
So it's really concentrating on what you want to see at a given time.
If I re-show all the years, maybe there's just a handful of people within the chart
that I want to compare one against the other.
So maybe if I just clear the list and choose maybe Bill Gates, Jeff Bezos, Mark Zuckerberg.
So I'm just taking three people here.
Click Apply, and again it simplifies the chart and it just shows you the data for those three
people.
So the filter is an extremely handy thing that they've added in.
The functionality was always there, but it was a bit harder to achieve than what you
see here.
Next up we have the paint brush with represents what's called Chart Styles.
Now what you see here in this gallery corresponds exactly to what you see up on the ribbon at
the top of the screen.
As you hover over each of these thumbnails, it applies different styling and different
options to your chart.
It's looking visually different, and all you've got to do is just one click and it will change
your chart style.
At the top of the screen.
If you want to get the full gallery displayed, always look for this little button here called
'More' - it's like a little down arrow with a line above it and when you click it, it
will expand to show the whole gallery.
Just hover over any of these to see a preview before you commit.
The third button at the bottom here, on the chart itself, is the plus sign and it's referring
to what's called Chart Elements.
So when you think about a chart, there's lots of bits that go towards making the chart.
There's things like the axes, the axis titles or labels, the chart title, data labels or
data tables, error bars, gridlines, legend, trendlines.
That's for a column chart.
What you'll find is for each different type of chart, there's different options available.
So if you think of a pie chart, for example, pie charts don't have axes.
In fact pie charts only have three elements here, rather than the nine that you seen on
this list.
Now, apart from the plus sign, you can also go to the 'Add Chart Element' at the top,
which gives you the same options.
And actually, I prefer using the option at the top because when you hover over things
like this, you can see the sub options a lot easier.
So for example, if I went to 'Axis' over here on the plus sign - to see the sub options,
you've got to click the little arrow there and then only gives you the text options as
opposed to the visual representation that you see at the top.
So I just compare this, what we see here with what we see when we go to 'Add Chart Element'.
Under 'Axes' and it actually shows you the two options with an icon next door, which
is currently saying that these two are both selected - Primary Vertical and Horizontal
axes are showing.
If we go to the 'Axis Titles', you'll see that neither the Primary Horizontal on the
Primary Vertical have a label.
If you look down the bottom of the chart, you'll see there's people along the bottom,
so we don't really need a label for that.
But the numbers at the side here, the vertical axis, you probably do want to add a label
for that because what are these numbers representing?
What it's actually representing is their net worth in billions of US dollars.
Under Primary Vertical we could click on that to display it, go to the axis title on the
side, then delete what's there and put in 'Net Worth' and then 'Billions USD'.
OK, next up we have Chart Title.
Now the chart title is already on the chart.
It's just got generic label.
You can either position it above the chart or overlay it on the chart.
So that's just a personal preference.
Whichever one you choose,
you then just go into there, into the box that says "Chart Title', edit the text to
say whatever you want it to say.
The text in any of these labels can be edited using normal means.
So if I select all the text in the title, go back to the Home tab and just maybe increase
the font size a bit, change the font color or those kinds of things.
It's all editable.
OK, next up I'm going to go back to the 'Design' tab of Chart Tools.
Go to 'Add Chart Element' again.
And this time we're going to look at Data Labels.
Now, Data Labels and Data Table - they're kind of doing the same job in two different
ways.
So I'm going to go into Data Labels and hover over each of these options and just watch
what's happening on the chart itself.
So the first option adds the values halfway up the column, then we have the 'Inside End',
the 'Inside of the base' of the column, 'Outside End', and then what's called 'Data Callout'
which is quite interesting.
So the idea with any of these is that if you've got stuff that's quite close in value, having
the values actually written above the column or inside the column states it exactly.
So if you're trying to compare, you got exact values to work with.
Now the alternative to Data Labels (which you see here) is to go to the Chart Elements
and use Data Table.
So you can either have it with or without the legend keys.
Whichever one you choose, it adds a little table underneath the chart showing the values.
So in effect it's doing exactly the same job as the Data Labels.
Data Labels were actually on the chart itself.
Data tables sits underneath.
Now, what you'll find with any sort of report or presentation you do is that there's two
types of people.
You have managers who are simply interested in the bottom line.
Keep things simple.
Did we do better this month than last month, or in this case - this year versus last year?
And then there's the people that work with the data and quite often they need to see
the details of the data.
So for them having the actual values on the chart itself is a useful addition.
Now, other things you can add to your chart.
Things like the 'Legend' - where do you want to put it?
For mine at the moment, it's sitting right down the bottom, but I could position on the
left, at the top or on the right, or switch it off if you don't think it's needed.
Let me give you a quick tour of this 'Design' ribbon at the top of the screen.
Quick Layout' offers various preset configurations for your chart.
As you hover over each one, you may find that one of these preset layouts is pretty close
to what you actually want.
So rather than you having to customize everything yourself, you can just probably find one that's
reasonably close and then just customise the last couple of things.
So obviously we've got the Chart Styles over here.
This is more to do with the layout of the options.
So things like... do you want your data labels?
do you want gridlines on?
where to put your legends and all these Quick Layouts' give you those various combinations.
The next option is 'Change Colors'.
Now you have two main choices.
You can either choose multi-colored or you can choose monochromatic, which is variations
of the same color.
You can take any of these series here and customize them to any color you like.
So do that, you've got to make sure that you have what you want in focus.
So if I click, say, this dark green column here, you'll notice that all three of the
dark green columns got selected.
So now I could right click and go straight to 'Fill' here and choose a different color.
If you just want to do something special with one particular column and not the entire series,
if I click on, say, the yellow one, just like before it's selected all three yellows, but
if I click on that first yellow one again, it isolates just that first column.
So now if I do something special to this one, maybe I'll choose to 'Fill' again, but maybe
this time I'll apply a texture and let's just maybe do the bubbles there.
So as you can see, it's only affected the one column.
Any object on a chart, if you right click, it will give you various options to format
and you can also choose some of the options from the context menu.
Okay, moving to the other side of the gallery here, we have what's called 'Switch Row and
Column'.
Let me just switch off the Data Table just to simplify this layout a bit.
If you look at the bottom axis, you've got the people's names.
If you look at the legend, it's showing the years.
If you want to switch this around and plot it by year rather than by person, that's exactly
what 'Switch Row and Column' does.
So now now, if you look, it's got the years on the axis and the people in the legend.
If you want to go back, just simply 'Switch Row and Column' again and it toggles back.
We have a chart where the data expands constantly.
At the moment you've got years 2015 through to 2018.
The next year in 2019, if we got a new set of data, we'll need to add it in.
Now, if we look at the original data here, this table has actually been 'formatted as
a table' using this 'Format As table' option.
And when you do this, you choose from one of the options in the gallery, one of the
things it does, it adds a little symbol in the bottom right corner to indicate the boundary,
the current boundaries, of your table.
And if I was to add somebody else to the list here, you'll notice that the table has expanded
automatically downloads.
So the formatting of the table has continued, the bottom border has moved down and the symbol
that I just mentioned indicating the boundary of your table, has also moved down.
If we look at the chart over here (or on the Chart4 sheet) you'll notice that Somebody
Else has been added to the chart automatically.
One of the benefits of using the 'Format as Table' feature is that any data has been added
to your table gets included automatically in things like charts.
Also things like pivot tables and other features in Excel as well.
So maybe just delete this again quickly.
Now that symbol has gone back to the original position.
If you don't use the Format As Table' feature and what you have here is just manually formatted
or unformatted data,
then what you need to do is expand the range through your chart options.
So if that was the case, back on the chart, what you need to do is to choose the Design
tab under Chart Tools, then choose 'Select Data' which takes you back to the original
data that you selected for your chart.
And let's say that we had 2019 available in column G. What we can then do is to include
the new data.
That then gets added in automatically.
Now obviously for this demonstration it's blank, but if there was data there that would
then be included on the chart.
If you don't already, I recommend using that 'Format As Table' feature.
Okay, next up we have the 'Change Chart Type'.
This gives you access to every single chart that's available.
There's the column chart with all these various options.
We then have Line Charts with all its various options, Pie Charts, Bar Charts, Area Charts,
and so on.
So if you create a chart and you realize probably this isn't the best type of chart for my data
to represent what I'm trying to show, maybe switch to a different type of chart and go
with that.
Finally, the 'Move Chart' option here, we're currently working on Chart4 which is where
we produced the brand new sheet using F11 and it put the chart full size on that sheet.
But if you want to put this chart next to the original data, I might just go back to
the original data and get rid of that first chart we created, just so there's space for
this one.
Then back on Chart4 I'm going to click 'Move Chart' and the two options you have are ...
To place the chart on a brand new sheet (and that's actually where it is right now) ...
Or to place the chart as an object in one of the other sheets.
As you can see here, it shows all the other sheets available and you can choose which
one you want to place it in.
Let's choose the 'Rich List $', click OK, and here's the chart next to the original
data.
You'll notice that the Chart4 sheet has now been deleted because the chart itself has
been moved as an object into this sheet.
When you have your chance as an object, just be really careful when you want to move it,
because as I mentioned before, there's lots of elements that go toward making a chart
and if you just go in the middle somewhere and start dragging, it's highly likely that
you'll dislodge something.
So you might dislodge a column or a gridline or a label.
So the safest option is always look for your four-headed arrow.
That's what you need to move something and I tend to go straight to the border, the frame
of the chart itself.
Then still with the four headed arrow, click and drag.
That way you're moving the whole object as one unit and you're not going to accidentally
dislodge something from within the chart.
Resizing.
You can either use the side or the top and bottom handles, but the best ones to use are
the corner ones because you do both at once.
If you ever want to move the chart back to his own sheet, you simply go back to 'Move
Chart' at the top here and choose 'New Sheet' and give the new sheet a name.
It's the reverse situation to what we did before.
Don't be scared of charts, maybe come up with some data and just produce one of the charts
yourself and play around with some of the options.
It's pretty easy to set up and pretty easy to customize.
That's it for this one.