Guys, welcome, welcome.

My name's Gerard Kelly. Let's get this session under the way.

Welcome to this session, brought to you by Financial Edge Training.

Cool guys in the chat if you've got access to the chat and I know some of you don't, so apologies if you don't have access to that.

The materials are now appearing, so we've got the Excel charts and graphs empty and at the end we'll give you the Excel charts and graphs full.

Cool. What we're gonna be doing in today's session, we're gonna be looking at database analysis, Excel charts and graphs.

We're gonna be covering some of these items.

We're gonna be creating a chart, adding chart elements, formatting it, working with the second axis, adding a trend line.

These all sound very disparate, but what we'll be doing is we're gonna build quite a few graphs, probably six, maybe eight, and we'll try and do lots of these in one graph and show you how they all come together.

Cool. Let's get going. So I'm gonna open up that empty file. If you don't have access to it, then of course we will email that to you.

So don't worry if you don't have access via the chats then we'll get this emailed to you.

Let's get straight into the empty file. And I'm now looking at the workout tab. There's quite a few tabs we'll be looking at, but we're just starting in the workout tab.

Looking at workout one, we're gonna start by building some basic graphs and then we'll gradually build ones that are just harder and harder and harder and harder.

Great. So let's have a read through.

We've got some satisfaction scores from one to five and we're asked to create two charts to show the breakdown.

We'll do a column chart and we want to show a pie chart.

So to create our, well create both of them.

The first thing we have to do is we have to select the data.

So that's our very first thing.

So I've now selected B six to C 10.

I'm then going to use, let's just use our mouse, let's be a bit naughty.

I'm gonna go to the insert ribbon and we've got all of these different graphs that we can insert here.

And I'm gonna go for my columns and I'm gonna go for my two D column clustered column.

So if I click on that and my basic graph has now appeared fantastic.

I'd like to add some data labels though it can be a bit tricky sometimes to work out this. Number three here, we've got seven people on that one or is it eight? We'd like to add something called data labeled.

So what I'm gonna do is I'm going to make sure that I've got something selected in the graph. So I've selected my chart here and a new ribbon appears at the top called chart design.

If I click on that and then go to the left hand side and it says add chart element in the add chart element, I can see data labels.

If I click on that, we've then got the option of adding them inside the end.

Very difficult to see. They're actually inside the blue, inside the end, inside the base, can't see them.

So I'm gonna click on outside end and they appear outside of the top.

Cool. So make sure I click on that.

They appear next up the charts, title i'd lo, I could just click into that chart title and I could just start typing.

And you notice it appears in the formula bar, but another way that we could do this is I click into the chart title again and I press equals.

And again, in the formula bar that equals has a pi, I can now link to a cell in Excel.

I'm gonna link to B five.

So satisfaction score five percenter that now's up, that now appears in the chart and I could update that if I wanted to change satisfaction score so it was capitalized S for score and it automatically updates in the chart.

So just makes that little bit easier for us to update our titles.

Great stuff. I hope that's okay if you guys so far, if you do have any questions, of course get into the q and a, I'll get you guys a uh, and I'll get answering your questions.

Par, pardon me. Okay, let's have a go at the pie charts.

I'm gonna select the data again.

I'm then gonna go to the inserts ribbon, but instead of going for the bar charts up the top here, I'm gonna go for my pie charts.

So click on that and that's just the very first one there.

Enter, just going to pull that down a little bit so I can see it a bit better.

Again, I want to add some data labels so I just make sure I've got something selected in the charts.

Go up to the newly created charts, design ribbon, add charts, elements and it's data labels that we want.

Now there's another one here that we could go for and it's best fit and this one suggests that the best fit is actually to include it inside.

If I put it outside, it sometimes makes the pie chart get smaller so we could have it best fit just I can't really see some of them a bit dark.

So I'm going to change the font color by going to home font color.

And I'm gonna go for just a little light gray, I can now see that a little bit better.

Last thing then is that chart title.

Click into that, press sequels and then click on satisfaction score.

Again, I can't really see that so I'm just gonna change that font color.

Give that something a little bit darker. There we go.

The legend at the bottom's.

Also a little bit dark, uh, a little bit light.

So home font color, automatic, that looks nicer.

Cool. Workout one's done.

And we've got our first two charts already done.

I hope that's okay. As we go through, we're just gonna add on slightly, slightly harder ones, slightly newer things that we can do.

We're gonna see share price, okay, we're gonna do share price line over time, share price and of volume charts.

Gonna do two of them. Uh, we'll add trend lines, add labels, can have secondary access. It's gonna be cool, it's gonna be really cool in our next workout. If I scroll down to two, we've now got actual versus target numbers and it can be useful to see both of them either next to each other or one on top of the other.

And we're asked to do both here.

We're asked to do a column chart side by side and a column chart with them overlapping.

Let's select our data and I've just just done that with like control shift down arrow.

Just make sure we select all that or we can use your mouse.

I'm gonna go up with my mouse again.

So the insert ribbon and I'm gonna go for my column or bar charts and I want to go for a clustered column.

That's the very first one there, the clustered column.

Now that's pretty much it.

It's actually worked really nicely.

I can see the actual versus target easily.

I could click into the title if I wanted to.

However, if I look along my left hand axis in my Y axis, the numbers, they're quite bunched up.

It only gets better if I make my graph quite a bit taller.

So instead of doing that, what I could do is change my axis so that there's less numbers on show or fewer numbers on show.

So I'm gonna click on my left axis and then right click.

And I want to click on formats axis.

And this sidebar pops up.

Now mine has already defaulted to this little options on the right hand side here, my access options.

If yours hasn't, then just click into that.

And at the moment the major units is a thousand.

The difference between the numbers on the left hand side is a thousand.

I don't think it's enough. I think the numbers are a bit bunched up.

Let's change it to two thousands. That looks a bit better.

Awesome. Cool.

So we've done our first chart already.

I didn't add a title but I'll skip over that.

So lemme get rid of that, that down there.

But our second one asks us to do the column charts with the two numbers overlapping.

So let's start the same way.

So I select my numbers, I then go to insert ribbon, I go to my column bar charts and I'm gonna take the very first one there, which is the clustered column.

Let's ignore that left hand axis this time because we've already learned that.

What I'd now like to do is get my blues and purples overlaps.

Now I know what you're thinking.

You're thinking blue and purple.

It's gonna be a bit difficult to see the difference.

So we'll change them in a minute.

Change the, so can I get you to click on either the blues or the purples, it doesn't matter which one you click on.

Then right click.

And once you've right clicked, we then want to format data series in my sidebar, my series options have popped up. If yours have it, if you've gone to one of the others, just make sure you click on that.

And where it says series overlap at the moment, it's negative 27, that means they kept apart.

Let's change that to 100% and that will bring one on top of the other.

Okay, so that's cool.

We've got them overlapping, I just can't see some of them.

Some of them, the blue's higher than the purple and one's overlapping the other.

It's not really working for me at the moment.

So we need to change the colors.

Okay, so what I'm gonna do is again, I'm gonna click away and I'm gonna click, just try and click on the blues rather than the purples. Okay. And we'll just change this one. To start with, I want to, in my format data series, on the right side, I'm gonna click on the painting.

And where it says fill, let's change that one.

Let's set, let's give that one where it says border.

Excuse me, I'm gonna go with solid line, I want it to be dashed.

Wanna have a nice dashed line.

And then with my solid fill, let's go with no fill and let's make the width of it much stronger.

Cool. Now I'm gonna click on my purples again.

I'm gonna click on that paint in again.

I want to change to a different color.

I'm gonna go for a nice light gray and all of a sudden I can see them a bit better.

My dash isn't quite perfect, is it? Let me change that dash, that's a better dash.

My actual versus my target. That little bit better. Cool.

So we've got my actual versus my target and we've done cut.

Excuse me, we've done workout two as well.

Cool. I hope you guys are doing well. There's nothing in the chat at the moment so I'm assuming everyone's good.

Let's move straight on.

We're getting harder with each workout.

Let's go to workout three.

And in workout three we're going back to pie charts.

So the first we've got some regional sales, so North America, it's got sales at 28,000 emea, 19,000, et cetera.

We're asked to do a pie chart by sales value.

Now that's relatively straightforward and it's actually very similar to workout one.

So we'll do a quick copy of that, select the data, Go to the in search ribbon, and I go to my pie charts and it's the very first pie there.

Now I just want to add some data labels so I can see the sales values.

So I make sure I've got, I'm clicked somewhere in my chart and I go to the chart design in the ribbon, add chart elements, data labels, and that's our best fit again.

And we'll change the color so that we can see it. Let's go for a lighter gray. There we go.

So we've got our very first one there.

Of course I could have put a chart title in there. I'm skipping over these just to make sure we do things.

Uh, we get as much done as we can in our 20 minutes, uh, 30 minutes.

But the next one asks us to do the same thing but just to change the set sales values to sales percentages.

So let's do the same thing again. Let's select our data insert ribbon pie charts two D pie.

Here it is again, great, but we're just gonna change the data labels this time.

Okay, so I'm going to add my data labels.

Make sure you clicked somewhere in the chart.

Chart design, add chart elements, data labels.

Again, I'm gonna go for best fit.

So one of the numbers is kind of popping out a little bit, But now I want to change those data labels.

So I need to click on just one of the data labels. It doesn't matter which one, make sure you've selected it.

And then when you right click, we need to go to format data labels at the bottom and it, my sidebar then pops up.

And this, this is where you can change the sales values, those numbers into percentages.

My label options, the label contains and I want to turn on percent.

I want to turn on category name, show leader lines.

I want to turn off value.

So those lines, leader lines, that's what links to the, to the uh, to the slices category name in this case the region.

And then my percentages.

Only thing I need to change then is the color.

Make sure I can see it a bit better. There we go.

Awesome, awesome. Workout three done already.

Everyone's quiet in the chat. So that's fantastic.

That's cool. Says to me that everyone's understanding it.

We're gonna skip workout four.

And so I now want to go to the next tab.

So the next tab says we'd like to have a stock price, history and trend.

So let's have a quick look.

We've got loads and loads and loads of dates more than I care to show you. So let's go back up the top. And we've got share prices for those dates and we've got volume as well.

The volume of stock that's been traded.

I need to select all of it. So I'm gonna go down to the bottom and I'm gonna start selecting all of that.

I'm gonna, and I'm going to press control shift up arrow.

I've selected all of that data once I've got all that selected got, I then want to create um, another chart.

But this time we're gonna have two things going on in our chart.

We're gonna have a nice little wavy share price showing us how the share price has been going over time.

And we're gonna have volume bar chart showing us the volume over time, volume of transactions over time.

So a nice little line.

So a nice little line for our share price and some volumes as well.

Uh, now little comment in the chats.

If you have any helpful, fun keyboard shortcuts for some functions, those would be appreciated.

Yeah, absolutely. So let's see if we can do this using shortcuts.

I'm gonna press alt, excuse me, I want create the chance. That's what I'm gonna do. So I'm gonna press al and then I go to the insert ribbon, that's n and then I need to decide which one I'm gonna go for.

I'm gonna start with two lines and I'll change one of the lines to a bar chart so I can see that my line is an N one.

So I click on N one and I just want that very first one.

So press enter. So use that alt key to get into your accelerator keys at the top.

Okay, so now I've got my basic chart. Awesome.

The blue line at the bottom, that's my share price.

But the purple line, that's my volume.

I don't want that as a line. Want that as a as a bar.

So I'm gonna click on the purple line and then I'm going to right click, excuse me.

And I'm going to change series charts type.

And what pops up is this change chart type.

And I'm in the combo at the very bottom.

It says at the moment that I've got a share price as a line and volume, that's the one that I want to change.

So I want to have my volume changed to a clustered column.

There we go. Now I need one of them to be on the secondary axis. Let's go for the share price this time.

Okay, in the next one we'll put share price on the other side. It really doesn't matter at all.

And then I'll press okay, and I've got my basic chart.

Let me just make it a bit bigger as you guys can see it.

Let, so I'm share price waving over time looks awesome.

I'd like to just add a trend line so we can see what the kinda like moving average is.

So I'm going to click on that share price line, right click.

And when we right click, we're then able to see add trend line as one of our options.

That trend line has now appeared.

We've also got this sidebar popup.

Now we've gone for a linear trend line.

If you have another preference, there are some others down the right hand side.

But the thing I'd like to add in here is the equation on the chart.

There's always someone who, who likes to look at the equation for that line that has now appeared, can't quite see it. So I'm gonna pick it up, just drag it and I'm gonna change the font color so I can see a bit more. Let's go for a nice vibrant blue. There we go.

Make it 10.

Fantastic. So now I've got my share price and volume chart together.

Hmm, looks awesome. I'd like to do another one of these.

I'd like to go onto the next tab. Basically do the same thing again, but I want to do a bit more with this volume chart. There's a few gaps in it the moment.

So let's go to the next one.

Uh, to change tab For those people who like a fun keyboard shortcuts, I'm gonna press control page down, control page down, takes me down through my tabs.

So work out six, it says using the data below, produce a graph showing both a share price, line graph and a volume bar chart in one.

Same thing, same as before. So let's select all of our data.

I'm gonna go down to the bottom, select across, start selecting up and I'm gonna create my graph again by pressing alt.

Then I need to hit N for insert.

Then I want N one my line.

And I'm gonna grab that two D line as my starting point.

Cool. So same as before. Lemme make this a bit bigger.

Okay, I want to change the chart type the purple line, that volume, let's change that again.

So left click on that and then right click go to change series chart type.

Again, I'm at the bottom, I'm in combo.

I'm gonna change my volume to be a clustered.

Uh, pardon me, not clustered this time, not clustered this time.

We're going to change that to be eight stacks column.

Now last time we had the share price on the secondary axis. Let's go for the volume on the secondary access this time.

So I've still got my share price on the left volume on the right, it's just the opposite way around press.

Okay, we've got our basic, our basic chart here.

The thing that I'd like to get rid of is all these gaps between the bar charts.

And the reason for those gaps is that we had share prices on the 30th and the 31st December, then we had none over the weekend.

And then you had third, fourth, fifth, sixth, seventh, February.

But then none over the weekend.

So we need to do a little bit of work to do this.

Okay, we're gonna have to add a second, another secondary horizontal axis, you then hide it and get rid of it.

But once that's there you can do more with the bars.

So I need to make sure I've clicked somewhere in my chart and I go to chart design in the ribbon, add chart elements axis, and I need a secondary horizontal.

And you can see it appears at the top.

Now I need to make some changes to this axis. I need to change it to text and then I basically need to hide it it away.

But once we've got that, we can start doing some cooler stuff with the bars.

So I now need to right click on that secondary axis at the top.

And once we've right clicked, we then go to format axis.

And again, I'm in access options on the right hand side sidebar here where it says axis type, we need to have a text axis.

So I click on that. It doesn't, it doesn't make it even any prettier, but it does help us where we're going.

I now want to basically hide this secondary axis.

So on the right hand side, I want my tick marks to be none.

So open it up, major type, none my labels label position, I need to be none.

Let's do that. And then one more.

The actual line itself, I need to make that disappear as well.

So I'm gonna click on my fill painter.

My paint can paint tin, excuse me.

And the line wants to be no line.

So that axis is effectively now hiding.

It's still there but we've just hidden it away.

I can now do some cooler stuff with my bar chart.

So let's click on the bars and you may have to do right click format data series.

Mine's already popped up on the right hand side here and I can do gap width, which at the moment is 150% on to change that to zero.

And now I've got a much better volume chance, much prettier.

No weekends, no millions, millions of lines.

It's got a nice block happening there. Awesome.

Cool. I hope that looks okay to you guys.

Okay, again, I could click in here, I could then press equals click into that.

Got myself a nice heading. I've got about two minutes left, so just enough time to get into the last workout that I wanted to cover.

So let's go onto the next tab, which is a rebased share price.

Now we need to kind understand what's going on here 'cause there's actually a little bit of, of kind of understanding on maths required.

We've got two share prices.

We've got Fords and General Electric.

I'd like to compare them, but unfortunately General Electric share prices on a completely different scale to Fords.

So it's very difficult. What we're gonna do is we're gonna rebase the stock prices both to a hundred.

And if they both start at a hundred, we can then see how they move and make, make it much more comparative.

So to do that, I'm gonna take that 100 and I'm gonna lock that with F four.

And I'm then gonna multiply that by boards 8.82 divided by holds 8.82.

But the second one I need to lock.

And if we just see what happens there, as I copy it down, I see the price gradually goes up on that kind of hundred starting points.

Cool. I'm going to do exactly the same thing for General Electric.

So I'm gonna take hundred as my starting points, multiply it by general ele, excuse me, I need to lock that 100, multiply it by general electric stock price divided by their stock price locked.

So as I copy it down, I'll always be taking that new share price compared to the current.

And as I it down, I can see it starts the fluctuate. Oh, and it even goes below the starting number.

Yeah, it does. So I'm gonna go right down to the bottom and I'm going to select up and I'm now going to copy them down.

I'm gonna copy them down. My shortcut I'm gonna use is Control D.

So control D, copy them down. Fantastic.

Now I just need to create my graph.

And this bit is actually relatively straightforward. There's not many clicks. So I'm going to select the whole of my charts, including all of the titles, including the original share prices, and then select all the way down.

And I'm actually gonna start at the bottom.

Make it bit easier for me. Okay, I'm now up at the top.

I now want to insert a line graph.

So I go to insert, go to my lines, just that basic line graph. To start with, I want to get rid of two of these lines and I can actually see the two I want.

The two I want both starts at a hundred.

So this is relatively easy.

I could just click on that first line at the top there.

Okay, delete it, click that bottom line and delete it.

Alternatively, I could have clicked on any of the lines, right click select data and then I could have removed the ones I didn't want.

Cool. And now we can see how they've done.

And I can see Ford comparatively has done astoundingly well at share price. Really shot up to a comparative, what's that? Up the top there? About 2 85, 2 82.

General Electrics, unfortunately hovering around a 50 mark.

Fantastic. And guys, that's all I wanted to cover with you in graphs and charts, in database analysis, helping us to summarize large amounts of data really easily.

Guys, I hope you found that useful and I hope you come to join us on another session soon.

As mentioned, the materials they are in the uh, the chat and we will email them out to you. For those of you who can't see the chat, Galen absolutely fantastic to see you.

See, see you chatting on here. Have a good one buddy. I'll see you later. Bye-Bye.