Okay guys, let's get going.

My name's Gerard. Welcome to this Felix live session.

Today we're going to be looking at Power Excel.

On your screen, you've got my email address, so if you want to say hi, please do any questions, feel free.

In the agenda you can see that we're going to be doing Excel shortcuts. I'm gonna spend nearly all of our time on that.

But I want to slip in some product as well. We'll get a lot of people ask us about some product.

I'm gonna be looking at two Excel files, so if you want to download them, if you go to the webinar chats, there are instructions for how to download them.

It's the resources that you guys need.

It says there are no resources for today's webinar. That's not quite true.

If you go to the resources tab at the bottom of Zoom in that you should then be able to click on the webinar resources link and then you should be able to then go to our website and download them.

If I just show you what it looks like.

So you'll end up coming through to this website here.

And there are the files at the bottom right hand corner.

Cool.

Okay guys, we need to get going.

So I'm going to start off in the Excel preparation document.

So if you guys want to download that or if you just want to follow along with me, please do so.

I go straight to it and here it is.

And I want to try and get off my mouse. Here's my mouse.

I have a wired mouse find. It just gives a little bit more responsiveness, but I want to get off my mouse.

So the first thing we need to do is try and change from this welcome tab.

And we want to go down to the preparation sheet.

So the shortcut to do that is control.

Hold the control button and then page down.

I can press it a couple of times to get to here and then control page up and control page down.

And I'm assuming that you guys are all working on Windows laptops.

Great.

Next up I want to start writing down some of the shortcuts, but I could use pen and paper. Here's my pencil. I could use pen and paper, but I'd much rather use Excel shortcuts to write down my Excel shortcuts shortcut squared.

So what I'm gonna do is open up a brand new Excel sheets using your shortcuts.

And the shortcut for that is control N, control N, hit control N, make sure you're in the Excel.

And a brand new file opens up brand spanking new file.

Let's write down what we've learned already.

So it was control page down and control.

Let me write it out in full just so we can see it.

And control page up.

So that was to change sheets, but already we get to this problem that you can't see.

Everything I've written, the column column isn't wide enough.

I'd love to autofit column width.

Now if you're a big fan of the mouse and you haven't used it before, then you can go up to this line in between the A and B and you can double click.

Yeah, that's pretty cool. But that's the mouse.

You want to try and use our keyboard if we can.

Because I want you guys working quicker in Excel so you're not spending all your time doing one bit of work.

We can do lots of work, really impress our bosses.

So what I need to do is I need to go up into the ribbon and I'll need to, without using my mouse, go to the home ribbon, then go to this word format and then go to autofit column width.

Okay, so that's what I need to do, but without using my mouse.

So I'm going to press the out button just once.

So I just tap it and I get these letters appear across the top.

They're called accelerator keys.

And you might have seen them pop up before and you might have wondered, what the hell are they? I dunno what they are. I'm going to press the H because the H is on the home ribbon and that's why want, because I want to get to this format just here on the home ribbon.

So I'll press H and another letter appears.

I've got an O over the word format. So I press O.

And then on top of autofit column width, there's an I.

So I type I and my column width automatically increases.

So I'll just share that again. There it is.

It automatically increases to the cell that I was in.

Let's write it down. So alt hoi that's your autofit column width.

I need to do it again. My column B isn't wide enough.

So let's do it. Alt hoi. Amazing, alt hoi is one of my top 10 favorite shortcuts. Particularly because if you say it really quickly, it makes it sound like a sailor alt hoi.

Oh guys, that's the level of the jokes of Pete's. They're gonna get worse I afraid.

Alright, now the next thing I noticed is that unfortunately I missed capitalizing the A of autofit.

I've made an error in my cell.

I'd like to jump into the formula bar.

I'd love to go to that A and then correct it.

So normally we'd grab our mouse, we'd click, we'd select, then delete.

We can use it with Excel shortcuts instead.

The shortcut, we need to do that to jump into the formula bar is F2.

So I press F2, it's now blinking at the end.

I can use my mouse. Use my mouse, excuse me.

Use my arrows to go back to the beginning.

Deletes water fits. Cool.

So F2 jump into the formula bar and al toy.

There we go. This excuse me for, okay, so we're doing good, we're doing pretty good, but I now realize I need to save my file.

I need to save it because I've got all this lovely interesting knowledge happening here.

I don't want to lose it. So normally we go file save as.

And you can do that using accelerator keys.

So I could do alts, the F appears above the files.

Press F over save as I get an A.

And then I like to choose my folder.

So I'm gonna go with the O for browse and it takes me to here.

Now that's what I use.

I use alt FAO, but the alternative is F12.

You can use the F12 button on your keyboard.

Unfortunately, I can't use the F12 button on my keyboard.

My F12 button does something else. Okay. And I've decided I don't want to use F12.

I want to always keep it as as that.

So I hit alt FAO and then save it.

You can use your mouse for this bit. It's alright. Cool.

So F12 or alt FAO.

That's nice.

Whoa, there are so many people coming into this session, this is amazing.

It should tick it up by the second. Okay, welcome, welcome.

Just come in, next up. Next up. What do I want to do next? Well, I've just saved as, okay, we've jumped into the formula bar, we've also fit the column width. We've done loads and loads of cool stuff here.

But I want to go back to the Excel preparation file.

How do I do that? Well, I could use my mouse, go down to the bottom and if I hover over the Excel icon, I can see that I've got four Excels open.

And if I go to the PowerPoint I can say I've got two PowerPoints open, et cetera.

But want to do that with my keyboard And I'm gonna hold the alt this time, hold the alt and press tab.

And what I now see is everything that I've got open, you can see I've got Excels here.

Zoom, hello me, I've got Excels, I've got a file explorer that loads open so I can very quickly just alt tab to the next item.

Cool. Oh let me just get rid of that.

Cool. So alt tab. Let me get back here.

Alt and tab, that is to change app or file.

Cool. I'm gonna have to hit alt tab again to get myself back to Excel preparation.

So alt tab, let's now start moving around a bit quicker.

I want to start jump, jump, jump, jump around.

Little bit quicker than normal.

So we could start with just the arrows, the arrow keys, they're my default. That's how I get around Excel.

But maybe I want to make some bigger jumps a little bit quicker.

So what I can do is I can press control, hold the control, and then hit arrows.

I can jump around a block of data.

So control arrow, that's pretty cool.

That one I think a lot of people have come across that one before.

So that's awesome. If you have, if you haven't even better.

So control arrow, jump around a block of data.

So control and then arrowy.

Jump to the edge of a block of data.

It's called a contiguous block of data.

Oops, jump to the edge of a block of data.

Okay, but what if I make slight mistake guys, watch my screen.

I'm going to press control right arrow correctly, but then I'm gonna accidentally do it another time.

So watch what happens. Control right arrow, I'm in cell XFD4, oh my gosh, this is gonna take me ages to get back with the arrow key.

I could hit the control left arrow gets me back.

But instead I'd like to press one button and it'll always get me back to column A.

Or if I frozen pans, it'll get me back to the first column of that.

That button to use is the home button.

And if you've never seen the home button before, because some people haven't, just that little button just there.

It's the home button just there.

You may have to press function home.

Boom, it takes me back to A4.

Let me write that one down again.

So home, home is jump to column A.

Great.

Now let's start doing some formatting.

We'll try and do this as quickly as we can.

Let's go back to cell C4.

I'd like to do some formatting of cell C4.

I'd like to make it red font.

I'd like to make my highlight or background color yellow.

And let's say we change the font size.

So how do I get these? I use my accelerator keys.

So I press alt and I can see this red font that's on the home ribbon.

So I'll press H. More letters appear.

I'm going to grab the FC.

And once I'm in here, I'm not gonna use my mouse, I'm gonna use my arrow keys to go down to red.

Let's do the same thing. Let's change the background color to yellow.

So I press alt H for home, then another H to get to highlights and then go down and make it yellow with the arrow keys.

Cool. Now I love those shortcuts and I tend not to write all those down because you can just see them. You get alt H. So again, it was, oh it was FC.

FC for font color.

What was it to highlight? H, H for highlight.

Oh my gosh, this is amazing.

When Microsoft were coming up with all these, they must have been thinking FC for font color, H for highlight. This makes it so easy if your first language is English, awkward.

So the fact we can get into the accelerator keys means we don't have to write down all of these.

If you forget what they are, just have a look at ALT and H and just work it out from the screen and we can then teach ourselves shortcuts.

Winning at X, winning at Excel.

I'd like to copy this cell.

So to copy it, control C, you might have come across that one before.

And then to paste it, I'll go to the cell above. Control V. Control V, control V, yep, that's paste. We got that would I'd much rather pace the formatting or do format painter.

This I use a hundred times every single day.

So let me go back to cell C4 and I'd like to paste the formatting Yellow, the red Onto the cell next door, which has the word name in it.

To do that I need paste special and there's a couple of shortcuts you can use to get there.

You can go up to the home ribbon, go to paste, and then at the bottom it's paste special and the shortcut for that is alt HVS.

I know a lot of people like that one. It's the modern one, alt HVS.

Alternatively, if you're old school like me, you can do control alt control alt V.

And that gets you this pace. Special dialogue box.

So alt HVS, in fact, lemme just write all this down before we forget it.

So it was alts, HVS or control Alt V.

And that's the go to pay special. Okay? But you must copy something beforehand.

So alt HVS or control alt fee. And you that gets you pay special, but you must copy beforehand.

Gotta copy something be Okay. So I'm just in the middle of doing that.

So lemme do it again. I copy C4. Go to word name Control out V, I've got my dialogue box.

What do I want to hit in here? Well, I wish someone had taught me this when I was in my first year of work.

I didn't work this out until I've been there over a year.

Someone showed me and my chin hit the floor.

I could not believe this. I was so angry in a, in a good way.

In a good way. But it gave me energy to work faster.

I could use my arrows to go down, but it's the underlined letter that someone showed me, the underlined letter of the word formats, that's T.

And if I just press T, it jumps me to there and I can start jumping all A for comments C, if you wanna go to formulas, excuse me, you can start jumping around doing all kinds of cool stuff here.

So n validation, I'm gonna go for T and it paste the formatting.

Lemme just show you how quickly we can do it.

I've still got C4 copied and I can just go control alt VT enter control alt VT enter control out.

VT enter, it's that quick.

So paste special formats, control alt VT.

That's definitely in my top 10 favorite shortcut.

Awesome, awesome. But I now realize I'd like to repeat the last thing I did repeat the last thing I did.

This is a huge time saver.

So instead of doing control alt VT enter, instead of doing five keystrokes, I can now just do one like this.

And the shortcut that you want to press is just F4.

It repeats the last thing you did. How quick is this? Saves so much time.

I could select loads of things and then do it all in one go.

F4 repeats the last thing you did.

Oh, Another top 10 focus you got then.

So F4 repeats.

Oh The last Action.

Oh You guys are getting all of the top 10 today.

Alright, I want to go back again.

So alt tab gets me back here.

I want to give you one more from pay special because I think it's so useful.

Again, I was taught this right at the start of my career and I use it so often.

I want you to imagine, lemme get rid of some of this this yellow, okay, we'll keep the red but we'll get rid of the yellow.

I want you to imagine the boss has come to me and the boss has said, Gerard, all these positive numbers, they needed to be negative.

Oh no. And the negatives, Gerard, they need to be positive.

My heart sinks. Ah no. Ah boss, don't make me do it.

Don't really do it. I've got a really big night out planned.

I'm gonna have to stay and do this. But there's a really quick way to change the signage of something.

If we go out to the right hand side here, if we just find an empty cell, I'll type them, I will type A minus one, I can copy that cell and then paste a multiplication of that cell.

So I'm going to copy the minus 1 and I'm going to select all of these cells that I want to multiply.

And I'm doing that by pressing the shift button.

And then arrows, I now want to use paste special.

So control alt V.

And in here there are two, two letters you want to press.

Not one. Everyone thinks there's one and everyone does the one and they think, oh it worked for me, it needs used two.

The first one that you want is M for multiply.

So that one makes sense, but the second one you want is gonna be V for values.

If you don't do the V for values, you'll copy all of your formatting from the minus one over to here and I'll lose all that lovely red that I had.

So MV press, okay, Ah, And they all change magic and I can now delete the minus 1.

Amazing. These are things that are gonna help you guys if you're on your desk, if you're on an internship, if you're preparing, you're just trying to get yourself a job.

These are all things that are going to help you.

Let's write that one down.

So to change the sign, I type up minus 1 and then I do control alt vmv, multiply value.

Cool. It doesn't like the minus 1, so I'm just going to go up into my formula bar and just put a little apostrophe at the beginning and that a little apostrophe means I can write whatever I want afterwards in Excel.

Just think of it as text.

Cool, we are doing so much, we've only got six minutes left.

So let's go back again. I want to go back to column A.

To go back to column A, I need to press one button.

It starts with an H home.

So press the home button and I want to go down to some workouts underneath so I could use the arrows, but I'd rather jump.

So I'm gonna press control and the down arrow, jump myself down to workout two. There we are. So in workout two, we've got some products, we've got the units of those products that have sold and I just need to find the total.

So I could write equals some open brackets, select the items above.

That's cool. I didn't use my mouse, I could, I could do it like that, you know equals SUM.

But I'd like to do it quicker.

And I want to use my favorite shortcut.

My favorite shortcut of all of them.

The auto sum shortcut, just amazing shortcut.

It's out equals If you are in a different region to me, then instead of using alt equals, it might be alt shift equals or sometimes called alt plus or alt plus.

So it depends where you guys are.

I've got a good question in the chat just about when will the recording be available? The recording is generally available at the end of the day.

So I'm in London, so generally at the end of the day, so that's typically about five o'clock.

Okay, so I can't promise that, but it's generally available then. Thanks for that question. So alt equals alt equals automatically adds them up.

Now alt equals is incredible because it also works to the side as well.

Let's say I've got lots of numbers in a row like this and I want the sum to appear in this orange cell.

I just hit alt equals winning at Excel.

Cool Alt course is actually my favorite shortcuts.

It's number one, so let's make sure we write it down. Otherwise we'll forget it. So alt equals auto sum.

Have a play around with it because you can do other stuff as well.

Okay, I want to go back to column A.

So if we remember what this one button I need to press, it's just the home button.

Got it. Let's go down to workout five.

So I press the Control button and the arrow down button down, down, down.

And here I am. Sorry, not workout five, workout six.

There we go.

So I'm in workout six and we have some salespeople.

We've got Jen, Christina, they've made some sales in January and then in February et cetera, I need to total them up underneath.

So to total them up exactly the same, it's alt Equals.

So that's worked. Awesome.

Now I could repeat myself, repeat myself, repeat myself or repeat myself. So I could do alt equals here and then I could try, excuse me, alt equals here.

I can then try doing alt equals here and it just becomes very repetitive.

So instead I do it once, it's worked.

But now I'd like to copy that to the right and the shortcut to copies to the right is you select the cells, shift arrow, r arrow, and then control R.

Control R. It makes you sound like a pirate. Control R.

I did say the jokes are gonna get worse.

So control R, that's my third favorite shortcut in the entire world.

Let's put it on the list. Control R to copy to the right.

Awesome. We are doing so much frankly we've only got two minutes left so I need to try and skip onto something else.

So I hope you found those shortcuts useful.

I want to just introduce one of my favorite functions, which is some products and I'll introduce another Shortcut as well.

If you have a look in the chats, we've got the link to the resources.

I'm just going to show you where that is on the website. Here it is. I want to open up this some product workout, empty in case some products work out empty.

Okay, we're just gonna do one little exercise here, so let me go find it.

Here it is. I need to change tab, I need to change to the other sheets.

And what was the shortcut? It was control page down. That's it. Control page down. Control page down.

I just want to have a look at workouts.

One here it says calculate total sales.

It says calculate it first using the intermediate step, total sales column.

So I'm gonna do this quickly, just watch, don't even do it, just watch this bit because you don't really need this.

Total sales is unit sold multiplied by yeah, price per unit.

Ah, units price multiply. That gets me in my sales.

Great, I wanna repeat that as I go down.

And so instead of using control R to go to the right, I'm going to select down and I won't use control R, I'll use Control D, control D to copy down.

That's my second favorite short card in the whole world.

So that's great. I can then total the them up. The items above. You guys know my favorite shortcuts alt equals alt equals add or alt shift equals some of you now that's worked and it's fine and there's nothing wrong with what I've done there, but some products allows you to do that without having this total sales column.

Some products I just type it in quickly, say S product.

Some product says what's your array one? So that's the first column. This one, oh, this one here.

And it, what it's gonna do is it's going to multiply it with array two, which is this second column just here.

So product means multiply. So it's gonna multiply the 15 and the 10 and it will then add some that to 39 times 11 and then sum that to 47 times nine, et cetera.

And so what it does is it comes up with exactly the same answer that we had as for the previous total.

So it's really good, really quick. Saves us loads of time.

I absolutely love the sum products function. Cool.

So I'm just gonna put control D onto my list.

And then some products, so control D, which was copied down and then equals some products, allows us to multiply, multiple columns and then some them.

So guys, I hope you found that useful. We are at time.

So this recording will be available very soon on our website.

Guys, if you found it useful, I'd love to see you at another Felix live. Again, all the best. Hope it's been great for you guys.

Have a good one. Bye-Bye.