Investment Banking Advanced Excel Shortcuts

[Music] Okay guys, let's get going. My name is 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 going to 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 going to be looking at two Excel files. So, if you want to download them, if you go to the webinar chat, 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. 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 um 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. 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 pencil and paper, but I'd much rather use Excel shortcuts to write down my Excel shortcuts. Shortcut squared. So, what I'm going to do is open up a brand new Excel sheet using a shortcut. And the shortcut for that is controll N. Crl N. Hit CtrlN. 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 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 auto fit 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. We 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 auto fit column width. Okay, so that's what I need to do but without using my mouse. So, I'm going to press the alt 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 don't know what they are. I'm going to press the H because the H is on the home ribbon and that's what I 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 auto fit column width there's an I. So I type I and my column width automatically increases. So I'll just show 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 the auto fit column width. I need to do it again. My column B isn't wide enough. So, let's do Alt Hoi. Amazing. Alt Hoi is one of my top 10 favorite shortcuts, particularly because if you say it really quickly, makes it sound like a sailor. Altoy, guys. That's the level of the jokes of Pete. They're going to get worse, I'm afraid. All right. Now, the next thing I notice is that unfortunately I missed capitalizing the A of auto fit. 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. Delete. Auto fit. Cool. So, F2, jump into the formula bar and Altoy. There we go. 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 alt the F appears above the file. So, press F over save as. I get an A. And then I like to choose my folder. So, I'm going to 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 all right. Cool. So, F12 or Alt F AO. That's save as. 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 if you just come in. Next up. Next up. What do I want to do next? Well, I've just saved as. Okay, we jumped into the formula bar. We've auto 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 PowerPoint, I see I've got two PowerPoints open, etc. But I want to do that with my keyboard. And I'm going to 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. Hey, this is 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 going to 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 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 arrow key 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 going to accidentally do it another time. So, watch what happens. Control right arrow. I'm in cell XFD4. Oh my gosh, this is going to 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 PES, 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. That's the home button just there. You may have to press function home or Fn home. So I'll press 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 going to use my mouse. I'm going to 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 highlight, 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 go alt H. It again, it was Oh, it's FC. FC for font color. What was it to highlight? 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 winning at Excel. I'd like to copy this cell. So to copy it, Ctrl C. You might have come across that one before. And then to paste it, I'll go to the cell above. Ctrl + V. Crl + V. Crl + V. Yep, that's paste. We got that. But I'd much rather paste the formatting or do format painter. This I use a hund times every single day. So let me go back to cell C4. I'm going to copy it 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. 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 ctrl altv. Ctrl Altv and that gets you this paste special dialogue box. So alt hvs. In fact, let me just write all this down before we forget it. So it was alt hvs or ctrl altv and that's to go to past special. Okay, but you must copy something beforehand. So alt hvs or control altv and you that gets you past special but you must copy beforehand. Got to copy something beforehand. Okay. So I'm just in the middle of doing that. So let me do it again. I copy C4. Go to the word name. Ctrl AltB. I've got my dialog 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 go 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 format. That's T. And if I just press T, it jumps me to there. And I can start jumping for all A for comments C. If I want to go to formulas, uh, excuse me, you can start jumping around doing all kinds of cool stuff here. So, N validation. I'm going to go for T. And it pastes the formatting. Let me just show you how quickly we can do it. I've still got C4 copied and I can just go control altv entertrl altv entertrl alvt enter. It's that quick. So past special formats altv troll altv. That's definitely in my top 10 favorite shortcuts. 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 timesaver. So instead of doing ctrl altv 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. Another top 10 favorite shortcut there. So F4 repeats the last action. You guys are getting all of the top 10 today. All right, 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, let me get rid of some of this uh this yellow. Okay, we'll keep the red, but we'll get rid of the yellow. Won't you 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. Oh no. Oh boss. Don't make me do it. Don't make me do it. I've got a really big night out planned. I'm going to 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 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 one 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 controll altv. 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." You need to use two. The first one you want is M for multiply. So that one makes sense. But the second one you want is going to 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 escape and they all change. Magic and I can now delete the minus one. Amazing. These are things that going to help you guys if you're on your desk, if you're in an internship, if you're preparing, 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 a minus one. And then I do controll altv mv multiply value. Cool. Uh it doesn't like the minus one. 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. And Excel will 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 get 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 going to 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 sum 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 s u m open bracket, 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, most amazing shortcut. It's alt 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 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 equals is actually my favorite shortcut. 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. You can do other stuff as well. Okay. I want to go back to column A. So if we remember this one button I need to press. It's just a 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. Uh, sorry, not workout five, workout six. There we go. So, I'm in workout six and we have some sales people. We've got Jen, Christina. They've made some sales in January and then in February, etc. 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, repeat myself. So I could do alt equals here. Then I could try alt equals here. I 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 copy to the right is you select the cells. Shift arrow arrow arrow. And then Ctrl R. Control R. It makes you sound like a pirate. Ctrl R. I did say the jokes were going to get worse. So controll R. That's my third favorite shortcut in the entire world. Let's put it on the list. CtrlR to copy to the right. Awesome. We are doing so much. We've only got two minutes left, so I need to try and skip on to something else. So, I hope you found those shortcuts useful. I want to just introduce one of my favorite functions, which is sum product. And I'll introduce another shortcut as well. If you have a look in the chat, 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 products workout empty. Okay. Some products workout empty. Okay. We're just going to 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 controll page down. That's it. Control page down. Control page down. I just want to have a look at workout one. Here it says calculate total sales. It says calculate it first using the intermediate step total sales column. So I'm going to do this quickly. Just watch. Don't even do it. Just watch this bit. You don't really need this bit. Total sales is unit sold multiplied by Yeah. price per unit. Ah, units price multiply. That gets me my sales. Great. I want to repeat that as I go down. And so instead of using CtrlR to go to the right, I'm going to select down. And I won't use CtrlR. I'll use controll D controll D to copy down. That's my second favorite shortcut in the whole world. So that's great. I can then total the them up the items above. You guys know my favorite shortcut. Alt equals alt equals add or alt shift equals for some of you. Now that's worked and it's fine. There's nothing wrong with what I've done there, but some product allows you to do that without having this total sales column. Some products, I just type it in quickly. Say Sum product open parenthesis. 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 going to 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 going to multiply the 15 and the 10 and it will then add sum that to 39 * 11 and then sum that to 47 * 9 etc. 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 product function. Cool. So, I'm just going to puttrl D onto my list and then some products. So, Ctrl D, which was copied down, and then equals some products, allows us to multiply multi multiply multiple columns and then sum 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. [Music]