My name's Phil Fox, and I'm a full-time trainer at, uh, financial Edge.

And I'll be, uh, dealing with this. I'll be taking this next 30 minutes, uh, session, uh, next 30 minutes webinar on Excel charts and, uh, graphs, um, or see lots more people arriving.

So it's great, great to see everybody.

You should have a link to, uh, some Excel, uh, web, uh, sorry, some Excel downloads. Uh, and they should be exactly the same as you'll have seen on the way into this, uh, session.

But if not, if you didn't see them, then just click on that link, click, click on that URL, and it'll take you to our, uh, Felix website, and it'll basically enable you to download the, uh, two, two, um, Excel sheets that we're gonna look at, uh, today.

Uh, one is the, um, the full version, uh, with the solution with all of those Excel charts and graphs.

And one is the empty version, uh, which we're actually going to use, uh, as examples of using Excel, um, uh, charts and, uh, graphs.

Um, and I can see another couple of people, uh, just joined, so I'm gonna do exactly the same again, I'm just gonna paste that link into the chat box.

And so, uh, let me, let me, I think I did that. So let me just try one more time. There we are. Yeah.

So you should see, uh, lots of, lots of, uh, entries there in the chat box. Basically, that's the URL, uh, where you can download these, uh, download the two, uh, two charts, uh, sorry, two Excel documents, uh, with the charts and graphs.

Okay. So without further ado, what we're gonna do is we're gonna dive into Excel.

So, uh, the purpose of this is just to show you some of the, uh, standard Excel charts and graphs, uh, that Excel, uh, will enable you to, uh, to, to use, um, but also, uh, to show you how you can tweak those, how you can adjust them, how you can get them more, uh, in line with what you actually, uh, want.

So, first thing I'm gonna do is I'm gonna open up the, um, there we are, there we are open up, open up the, um, the blank Excel, uh, version, and I can see we've just got a couple more people.

So again, uh, apologies for doing this numerous times.

Uh, the, if you've just joined, then you should see in the chat box, the link to the URL.

We can download this, uh, uh, this Excel document.

So on we go. Um, lots and lots of examples in here.

Of course, we've only got 30, 30 minutes, uh, so we're not gonna spend, you know, we're not gonna be able to do every single one of these, uh, charts, um, and graphs, uh, but we'll do some of them.

Um, and all also, uh, if you download this document, you can also download the full version, which has got a full version, um, of, or a solution, uh, file with all of those charts and tables actually, uh, there and, uh, completed.

So let's go straight into this. We're gonna go right to the top of the, uh, this very first workout.

Now, there's some good news and bad news, uh, with Excel charts and graphs, if you haven't used the Excel charts before.

Um, the good news is that Excel is incredibly helpful.

It's got some great, really powerful, Really good looking, uh, standard templates for charts and tables and graphs.

Um, and you can use those and they look great right from the off, they look really good.

The bad news is, um, they're a little bit fiddly, a little bit difficult to tweak, to adjust.

Uh, so if you get exactly what you want the first time, that's great. That's really good. Uh, Excel's really helping you out there.

Um, it'll be, it'll be really difficult to do these from scratch.

Um, but if they're not exactly what you want, sometimes a little bit fiddly, sometimes a little bit complex, uh, to work out how you actually adjust, uh, those, uh, charts and tables, sorry, charts and graphs.

So, uh, we're gonna do a bit of both. We're gonna put, uh, use some of the standard templates in Excel, but we're also going to look at how we tweak those, how we'd adjust them.

So, um, if we go to the workout tab on, uh, this Excel, uh, document, and we dive straight into that, uh, what we're gonna do is we're gonna go right to the top of, uh, this, and using our normal keyboard shortcuts, we hold down shift, we use the arrow key, we're gonna highlight these, uh, this, uh, little group of numbers up at the top.

So it's a really simple, um, example.

You know, we're not gonna get into mountains of data.

Very, very simple. Uh, so how do we create a a chart from this? Well, we go up to the inserts, and of course, you could use keyboard shortcuts for this, but if, if you, unless you use charts and take charts and graphs all the time, sometimes that's a little bit, uh, clunk, uh, a little bit slow to do.

So, uh, I think using the mouse is absolutely fine, uh, most of the time with, uh, charts and graphs, because fundamentally, you are moving things around.

You are expanding them, you're changing them, you are, um, and you're using the mouse a lot of the time.

So I highlight those, um, that the, the basic data, um, and I go up to insert and I go to charts.

Um, and there's lots and lots of examples.

Um, and Excel does a fairly good job of recommending some very standard, uh, very, um, most used tables.

And you can kind of see, I could just click down on the left hand side, and it gives me, um, a preview, not just a preview of generic information, but a preview of the information that I've actually got, um, on, uh, that I've actually highlighted.

So you've got a very good example, a very good idea of what it's gonna look like.

Now, the problem is, if I click on this one, for instance, this clustered column, it's assuming that I've got two columns of data.

Um, and actually what I've got on the left hand column is the, is the x axis.

And on the right, on the right hand column, I've got the y uh, axis.

Uh, so I don't want this, I don't want a clustered column.

So I need to go into a little bit of detail.

I need to go into all charts up here.

And now I get the whole range of potential charts on the left hand side.

Now I'm gonna go into columns, um, uh, now that's the clustered one before.

Um, that's the one where it's assuming that I've got two columns of data, I'm actually gonna go to the one on the left, which is, you can see is highlighted.

And again, if IH hover over that, it gives me a really good preview of what it's gonna look like with my data, and that looks pretty good to me.

So I'm gonna click on that. I'm gonna click okay.

And there we go. Straight away, I get actually something pretty good.

It's really nicely laid out.

You can see that it's going with the themes of, uh, my, uh, my, my, my spreadsheet here.

So it's got that blue color from, uh, financial Edge.

Um, and it's actually pretty well laid out.

It's actually really good.

Um, uh, it's just a standard, um, picture so I can grab the edges and move it, and I can resize it with these, um, uh, these little handles on the, uh, side.

So it's, it's really pretty good. It's really pretty good.

Um, now let's look at a few elements of this.

Um, just describe how, uh, charts and tables actually work.

Um, first thing is you'll see up on the top of the, um, ribbon.

I've got two new menus when I'm working on a chart. I've got chart design.

And the chart design is really the very broad elements, the look and feel of the chart, the type of the chart.

You can see over here, I could change the type of the chart, so I could go back to one of those clustered columns or a pie chart or something really fundamental to this chart.

Um, you'll also see that I've got all of these various styles, and again, if I just sort of hover over one like this, um, it sort of automatically sort of changes the style.

I mean, it must, uh, most of the time I tend to prefer the, the fairly simple ones.

The, the, the less fussy ones. I think they look a little bit more professional. Okay.

Um, but this one's pretty useful here. Add chart elements.

Now if I click on this, um, you'll see that it comes up with all of the elements of the chart, all of the little pieces of the chart.

Very useful menu for adding elements, for adding things like titles, access titles, and so on.

We're just gonna dive into one of these, and we're gonna do data labels.

So I hover down to data labels here, and it gives me lots of alternatives.

Now, if I actually hover over these, it might not not be that easy to see, but in the center of the little bars, you can hopefully see that I've got a 20, I've got a 15.

It's actually putting those data labels in the middle of the charts, in the middle of the bars.

Now, I could change the colors of those, make them a little bit more obvious, little bit clearer.

I'm gonna go with this one here. Outside ends.

If I go down to outside ends, you can see now it puts data labels just outside those little bars, just outside those, uh, those columns gonna click on that one.

And there they are again, a little bit of basic stuff.

Um, how do I actually adjust what the chart looks like? Well, if I click on any particular elements, I'm just gonna click on one of the blue bars.

You'll see that it highlights all of the bars.

You'll see that it highlights all of the bars.

And so I could do things like change the colors of all of them.

If I click just one of the bars again, then what it does is it just highlights that one.

So it gives you that ability to sort of drill down.

You can make sort of macro changes, changing changes to all of the bars, or you could just change the color of one.

Maybe you could change the color of this biggest one to a different color to really highlight that it's the highest level.

I can do the same thing with these data labels.

I click the data labels. You'll see the first time it highlights all of them, and now I can tweak them.

Now I can adjust them. So I'm gonna go back into home, um, where I would normally find things to do with the text, things to do with the look and feel of, uh, my, uh, my spreadsheet.

Uh, you can see that it's going with calibri, it's going with nine sites. So let's make it a little bit bigger. Let's make it say 11.

So they all get a little bit bigger. Let's make it bold.

So again, they become a little bit more obvious.

Um, now what if I wanted to just change this one here to make it red, to really highlight that it's the biggest number.

Well, I'll just show you again.

I'm click outside the chart and I'm gonna click back inside the chart.

The first time I click that number 20, highlights all of the data labels, click that 20 again, and now it's highlighting just that one, just the number 20.

So now I can go into, uh, my, um, uh, fonts, and I'm just gonna go and make it red.

Oh, that's the, that's the background. That's not what I wanted to do. Um, no fill.

Let's go to the next one at the actual font color.

I clicked on the wrong one. There we are.

And now I've got data labels with the number 20 highlighted and the others all a little bit bigger.

Just another couple of little elements to show you.

First thing is, um, I'm gonna click the charts title up here.

Now, if I click in this, it's just a text box.

I could type into this, I could put anything I want to just type into it, move it around with those handles, make it bigger, smaller, change the fonts and so on.

It's quite a neat little thing you can do. You can actually link this title into your spreadsheet.

So I just click on that chart title.

I then click okay, and sorry. Then click equals. And you'll see what happens is up in the formula bar, I get an equals.

And so I can now use my mouse to click satisfaction score here, and you can see that it links into that cell.

I hit return, and now that title is linked directly to one of the cells.

So if I change this to satisfaction scores, hit return.

And then the title of the chart changes as well.

Final thing I'm just gonna show you is there's often very many ways that you can tweak charts.

Um, if you click on the chart, um, as well as the chart design menu, we also end up with this format, um, menu here.

If I click into that, then you get lots of options for drilling down, looking at the real sort of detail of what's going on within the chart.

But there's other ways as well.

So for instance, if I click on the, um, chart title, I write, click on that chart title, and I click the very bottom menu item, which is format chart, title.

Then I get a little side box here, and this is context sensitive.

What that basically means is if I click something else, so I click for instance, the data series, then this changes to format data series, and I get lots and lots of options.

Um, in terms of, uh, the chart in this sidebar, um, just to give you a little, um, uh, uh, heads up on this. We will use this in a couple of examples.

In general, the sort of left one or two, this sort of paint pot and this shape, this pentagon shape, um, tend to be, uh, to do with the look and the feel of the thing that you are pointing at.

So the color of lines, the thickness of, um, uh, axes, those sort of things.

And the elements on the right hand side where it says series options, that tends to be a little bit more mechanical.

The sort of the, you know, things like, uh, the numbers on an axis, um, you know, uh, a little bit numerical, a little bit more mathematical.

Okay. But often a lots of very detailed options in here for adjusting your, uh, type your, your chart.

So let's go, uh, down.

Um, we're gonna do another, um, example just based on the same data.

And we're gonna do a pie chart now.

So I'm gonna click outside of the chart, um, and I'm gonna go back to my data up here.

And then I'm gonna go insert chart.

And this time I'm gonna go to the pie chart icon up here.

And I get lots and lots of different options.

I'm just gonna go again with fairly standard approach.

And you can see as I sort of hover over each of these little icons, I get an indication of what it's gonna look like.

I'm gonna go with the straightforward one here.

I think the, normally the more straightforward ones are the more professional, um, the more sort of grown up.

Um, so let's go, let's jump down here. Let's move it down a little bit. So here's my pie charts.

And again, you know, this is just a graphic item.

I could move it around, I can make it bigger.

And the, um, the actual size of the, uh, pie chart changes and so on.

Again, I've got lots and lots of, um, uh, flexibility to change what this looks like.

If I wanted to change the color of one of these, um, these sort of slices, one of these are, I'm gonna say quadrants, but quadrants not the right word.

If I've got more than four, um, basically I could just click on that a couple of times and I get just the yellow one.

I could go into these format data points and change the color and so on.

Um, what I'm gonna do is I'm just gonna click on the, uh, chart.

So the chart that, that the high chart itself is not highlighted, but I'm gonna go up to chart design, um, which is highlighted because I'm selected my chart, I'm gonna go add chart elements. And again, I'm gonna put some data labels.

So I'll put the data labels here.

Um, now again, it gives me, um, options.

So if I click center, they're right in the middle of those, uh, slices.

Um, inside end is just inside.

Um, this one's all often quite useful. Best fit. What best fit normally does is it puts it inside the slice, unless the slice is really narrow, in which case it makes a little call out, which just sits outside the main pie chart.

So that's often a very good one to choose if you've got a range of, you know, some big slices and some very narrow slices. So I'm gonna go with that one. Um, I wanna make them perhaps a little bit more bold, a little bit more interesting.

So I'm gonna click on those.

And you see, I just click on one and they all get highlighted.

Let's make 'em white, let's make them a little bit bigger.

So I'm gonna go up to home.

I'm gonna go up to, uh, this, I'm gonna go make them a little bit bigger, bigger font size. Let's make it 10 and a half. So they grow a little bit.

Let's make them bold, and let's change the color to, uh, white.

And there we are. They look pretty good. So they look really pretty good.

Now let's delve into the depths a little bit.

Let's look at it something a little bit more complex.

So I click those again and again, you'll see over on the right hand side, this formatting sidebar has changed.

Um, it's context driven, and it's basically saying format data labels.

If you can't see this, then what you do is you right click on one of these, uh, data labels, and you'll see it says at the bottom format data labels.

And that's how you get into that sidebar.

Okay, so we're just gonna dive into this.

I'm gonna go over to the right hand side, which says, uh, label options.

So I'm gonna jump into that. Um, and excuse me.

Um, and if I dive into the very right hand one, now, this is the sort of more interesting one. This is the real sort of nuts and bolts of what's going on here.

Um, so I get a bundle of alternative items here at the moment, it's showing me just the value.

If I put percentage as well, click on that one.

Then you'll see for all of those labels, it does two things. It first of all, shows you the actual value in the case of the yellow one, it's 15, and then it says that's 31% of the total, which is useful, but perhaps a little bit messy.

So we're gonna click on the value.

That one disappears, and it goes back to just now putting just the percentages in there, which is, I think, pretty, pretty cool.

Um, again, we can click into the chart title, hit equals go up to satisfaction scores, hit return, and we've got a really pretty nice pi pie chart right from the beginning with very little work.

Okay, let's dive down a little bit further.

So we've now got something with a couple of columns of data, a little bit more sophisticated this time.

So what we're gonna do is we're gonna highlight all of this data, including the headings, often worthwhile putting the headings in here, because that then gives the, um, uh, that then gives, oops, sorry, I moved away.

That then, um, gives excel, uh, and the charts, uh, a definition for each of those data, um, elements.

And, you know, you, it, it, it, it'll automatically try and create a key, uh, for your, for, for your, for your charts.

So, um, let me just get rid of that format shape, so we've got a little bit more space on the screen.

Okay, so got two of these, um, uh, columns of data.

I'm gonna go into insert.

Um, I'm gonna go into charts, and I could do it as a line, which is, again, it gives me a fairly good line, um, fairly good representation of what it looks like.

I'm gonna go to the next one here, which is a clustered column and a cluster of columns. Very, very common, uh, way of presenting data.

Um, so you've got actual results next to budgets, you know, or this year's results, next to last year's results by month or something like that.

Um, uh, and again, you can see that it pops up with a good, uh, nicely formatted, nice colors, um, uh, chart, click on.

Okay. And there we are. We got something pretty good.

And you can see that it's used, the titles at the bottom, it's got actual and target in there.

So here it goes. And you might sort of say, actually, that looks great. That looks really good. Let's just go with that.

Um, how, if I want, what if I want to just tweak that a little bit, adjust that a little bit.

So what I could do, one of the things about this is I think it's a little bit messy on the left hand side, uh, particularly if I make it perhaps a bit smaller, you can see that the, um, the axis is really a bit busy too. Perhaps too many, uh, too many, too many values, too many lines.

Um, so how do I adjust that? Well, I click, I, I click on that left hand side, and you can see it highlights the axis.

Now, if I right click on that, at the bottom it says format axis, and it's gonna open up those, um, it's gonna open up those, uh, that, that formatting sidebar, give me some, um, opportunity to tweak or adjust that, um, um, that axis.

And of course, it's context driven.

So the options now are totally different to the options when we're looking at data labels.

So on the right hand side, if you've, uh, if you are on the left hand side, these are things like how big the axis is, you know, whether it's a black line, a gray line, a green line, dotted line, whatever, those sort of things.

But here is the more sort of numerical elements or options relating to this option, uh, to, to, to, to this, um, uh, to, uh, to this chart.

So you can see one of the very useful things here is the minimum and the maximum.

Um, so I could, um, I, it auto normally does a fairly good job of, of sort of sticking the, uh, axis, making the axis, uh, big enough for the data you are presenting.

Um, but if we wanted to just show the sort of the, you know, the, the movement, um, I could perhaps make the minimum 5,000 that, and now it starts at 5,000, it goes all the way to 10,000.

And now I can see a little bit more clearly the range, uh, between, uh, these, um, all of these, all of these items.

Um, uh, I can go back if I want, if I click reset, then it goes back to starting at zero.

And there's an argument that, um, you know, it's, if you're gonna show a, a chart like this, then you know, some people would argue it should start at zero to show the full magnitude of the numbers.

Okay? Um, the other thing that I'm gonna do here is I wanna get rid of some of these items.

It's a little bit bus.

I probably don't need every 1000, um, to have a label.

So I'm gonna change this major units from every 1000 to every 2000 hit tab to accept that.

And now I've only got one every other 2000, and that's a little bit nicer to see.

And you'll see that it also changes those guidelines, those grid lines on the chart as well.

So it makes it a little bit more professional, a little bit less, uh, messy.

Now the other thing with these is that they're shown as, uh, 4,000 with one decimal place, or 5,000, or, sorry, sorry, 6,000 with one decimal place.

A little bit too busy.

Again, we don't need those, uh, 0.1 at the end.

So if I go down to the bottom, you can see I've got, uh, numbers down here.

Now, anyone that's dived into number formatting within Excel, um, for cells themselves will recognize this code.

Um, and basically it shows you how it presents a positive number, a negative number, and a zero.

Um, so I'm gonna sort of dive into this and I've got another one already set up without that 0.1 at the end, I'm gonna select that.

And now you can see that the axis just has, um, 2000 and 4,000 without the 0.1 at the end.

And you'll also see if you are watching that, it uses that extra space that it's just freed up on the axis, um, to, to, to expand the chart itself, gives you a little bit bigger chart.

Uh, so it's constantly, constantly, uh, trying to present a really good quality, uh, chart.

So, great, good stuff.

Now, last couple of things we're gonna do. We're gonna scroll down, uh, to, uh, workout four.

Now, workout four is a little bit more complex.

It's basically got share prices and share volumes.

So I'm gonna do the same thing here.

I'm gonna go to share price and share volume.

And oops, just let me move my notes down a little bit out of the way there.

So I free up a bit of screen real estate. There we are.

So I highlight, uh, this and I dive into insert, um, chart.

And I'm gonna start with just a line chart. Here it goes.

So here's our line chart, and it's kind of, okay, it's not bad.

Um, uh, problem with this is that the share price is really pretty low.

It's, uh, between, what's that nine and about 25 or 27.

Um, whereas the volume is everything between about 30 and about 400.

Um, it's putting it all on one axis.

Um, so what we're gonna do is we need to create a hybrid chart, a chart with, um, uh, some settings for the volume and some different settings for the share price.

So I'm gonna make this a little bit bigger so we can see this.

Um, and I'm now gonna go up into the chart design element.

I'm gonna go to change chart type, and it's initially coming up with a line.

I'm gonna go down to the bottom where it says combo.

And it's basically gonna, it defaults to having a column for the, uh, share price and a line for the volume.

I'm actually gonna swap that round.

So I've got the share price is a line and the volume is a column.

Um, so you can see it looks a little bit better already.

But here's the key thing.

What I'm gonna do is I'm gonna put the share price on the secondary axis.

Now what that does is it creates two y AEs, one on the left hand side, one on the right hand side, and you can see what it's done just in the preview.

I click okay. It's basically, uh, left the same numbers for the share volume on the left hand side up to 500.

But on the right hand side, it's basically given me a share price, which is now a maximum of 35, and therefore it's much more visible what the, uh, share price is actually doing.

And that's really pretty sophisticated.

Just a little aside on that, um, it's useful and it's quite good practice to put the share price on the right hand side.

Rationale for that is that we're probably more interested in the share price than we are in the volume, and particularly we're likely to be interested in where the share price currently is, what it, hi, what its history is, but where it is now.

So, uh, where it is now is pretty much at the end of this chart here, where you can see it's about sort of 22, uh, dollars or so, and therefore that's right next to the axis.

So it's good to have the, the, the labels right alongside the share price where it's finishing.

Okay, so we're gonna do one more.

Um, so we're gonna go to stock price on the next tab.

We have exactly the same sort of data, but a lot more data.

So I'm gonna go to the top here using my keyboard.

Um, hold down shift, couple of, uh, arrow clicks to the right and then hold down control and down arrow.

And I've selected all 250 lines of this chart.

I'm just gonna use the mouse just to get me back up to the top.

So we, um, uh, uh, back at the top of the data, I'm gonna do the same thing again, insert, uh, chart.

And I'm gonna go straight into all charts, straight into combo and do exactly the same as I did before.

So I'm gonna do price as a line, I'm gonna do volume as a clustered column.

I'm gonna put the price on the secondary axis on the right hand side, click.

Okay. And now we get a really pretty, um, attractive, really pretty good, um, chart with the volume and the share price over, um, that much longer period.

Okay, now just one thing I'm gonna add onto this.

Um, I'm gonna add on a trend line.

So I click on the share price, uh, line and it highlights, and of course I could change that.

I could adjust it, I could change the either colors and all that kind of stuff.

But I'm gonna go into the chart design elements up here, chart design menu, click add chart elements.

And then down at the bottom I've got trend line.

Um, now I've got lots of different options for a trend line.

And again, as I hover over these, um, you can see that it basically, um, shows me what it's gonna look like.

I'm gonna click with linear at the moment 'cause it's a fairly simple, um, line here.

Um, there's no sort of overall impact.

I'm just calling to click on linear and the I go. Okay.

Um, so I've got this trend line already. Now.

One last thing I'm just gonna add.

I'm gonna click on that trend line, that dotted line.

And of course I could change this. I could format it.

Gonna add one extra bit onto this, which is go into, again, the more complex options over on the right hand side.

Scroll down a little bit, and it just puts, uh, right at the bottom.

It says display equation on chart. I click that.

Now it's a little bit small, but you can see it here.

So if I just click on that equation, it's just a piece of text in a box.

So I can grab that, I can move that away a little bit.

And now I've got that, uh, equation for the line right next to the trend line.

Um, and I'd say that was a pretty sophisticated chart that we've managed to produce.

So exactly half an hour, it's exactly half past.

So I hope you found that useful.

As I said, Excel charts and graphs are incredibly comprehensive.

Uh, and the starting point is they will help you produce really fantastic charts, uh, right from the off.

Um, but it's useful to play with them a little bit to get used to some of the techniques, um, so you can get them from that standard layout to exactly what you want.

Um, a little bit fiddly, a little bit clunky sometimes.

Um, but, um, uh, hopefully you found that, uh, a useful ru rush, uh, uh, a useful sort of, uh, walkthrough how, uh, you can, uh, adjust your charts to be exactly what you want.

Okay. Thanks for joining.

Um, and, um, hopefully see you, uh, next time.

Quick and sweet. Thanks Mohammed.

Uh, that's nice of you to say. Okay, cheers.

Bye-bye everybody. Bye-bye.