Transcription
If you find yourself in front of a computer looking at spreadsheets, often these 20 must-know Excel shortcuts I will hopefully save you hours of time. Let's go!
And thank you to HubSpot for sponsoring this video. More on them later.
Alright, so here's the Excel file that we'll be working with, which you can download for free in the video description below. So let's get started with number 20. As you can see over here in the table, we have all of this data down below, which when you start to scroll down, you can actually no longer see the header. So we're not really quite sure what this is referring to, and same thing with all of these other ones. And so what we can do here instead is actually just do a shortcut which is going to freeze that top row. Now, to do this, you're gonna press Alt + W + F + R. And now when we start to scroll down, you'll notice that the top line is frozen, which makes things a lot easier for us. If you ever want to remove that, you can always go over here under "Freeze Panes" in the View option, just click on it, and for from here, just hit "Unfreeze Panes," and that's going to remove that for us.
Next up at number 19, we have the Control + Arrow keys. So as you can see over here, to navigate across this data set because it's very long, you can actually press the shortcut Control and then just use the arrows. So Control + Down Arrow to get all the way to the bottom, Control + Up Arrow, and Right and Left to go to the sides. Similarly, you can do Control + Shift + Down Arrow and Control + Shift + Right. That basically allows you to select certain areas. If you only want to select column B and C, just stop pressing the Control and just go Shift + Right Arrow, and you can see that allows you to do that.
Let's go back up to the top of the data set to look at number 18. So Control + Up Arrow all the way to the top. And over here, you can see that we've got talk about sales. And let's say we want to continue this on. Now, to do so, you can just press Control + R. That's basically going to copy what you had towards the left. And similarly, you can go to Control + D. That's going to drag everything that you had above down one.
Going back down towards the bottom, so Control + Down Arrow. Let's suppose over here that we want to add some new rows. And so instead of actually going ahead and typing everything in again, what you can do is hit the Alt + Down Arrow. That's basically going to show you the drop-down or down arrow of all of the things that you already had above. And so it's somewhat faster for you to just paste it in there.
At number 16, we have selecting a row or a column. Now, to do so, all you need to do is hover over a specific area and go to Shift + Space. And similarly for the column, it's just going to be Control + Space, like so. Now, that on its own just isn't very useful, so that's when the next trick comes in and combined, it can be quite handy. So again, we'll do the Shift + Space. And now if you want to add the row, you just need to go to Control + Shift + Plus. Again, Control + Shift + Plus. And to remove some, you have to go to Control + Minus and Control + Minus. Similarly for columns, you can go Control + Space and then Control + Shift + Plus and Control + Minus.
Now, what if we just want to move a specific row up a bit? Now, to do so, we'll go to Shift + Space again. And instead of having to add new rows, etc., we can just press the Shift key. And when you hover over the area, you'll see that the cursor changes to these arrows. And from there, you just want to click and drag it. Let's say we drag it up over here, and you can see how that's moved it all up. Similarly, we can take it back down. So Shift and then just drag it all the way down over here. Say to undo that, we're just going to press Control + Z and Control + Z again.
Now, let's go all the way back up. So Control + Up Arrow. And you can see that beverage brand here. Let's say that we think it's a bit too long, and so we want to space it out a bit, maybe put it into a different line. Now, to do so, we're just gonna get inside of it over here. Now, what we're gonna press is the Alt + Enter and hit Enter again. And now you can see how it's spaced it out across two lines, but it stayed within that same cell.
Coming in at number 12, we've got adding comments. So let's suppose that the interns worked on this file, but we don't really trust them, so we just want to make sure that this number does make sense. Now, one way to let him know is to go hover over the cell you're not sure about and just hit the Shift + F2. Now you can see that this pop-up is going to show up with your name, and then over here, let's say, "Can you check this?" Awesome. Now you can see that it's left this red sign, which when you hover over it, it's gonna show you the actual comment.
As we've seen here, this data set is not so small. So suppose we want to reach row 100. We can hover over there, which might be a bit tedious, but the shortcut there is just clicking the Control + G. That's going to show you the "Go To" pop-up. So within this pop-up, the reference that we want is we want to go to row 100, right? So it's we're just going to put a 100. That's going to take us to that specific cell. So we'll hit OK there, and you'll notice that it's taken us exactly there, right here.
And speaking of shortcuts, a great way to save time is with templates like the ones HubSpot, the sponsor of this video, is kindly providing us for free. Using the link in the description below, you can get multiple free Excel graph templates. Within the download, you'll find an Excel file with instructions on using the template alongside all of the chart types you might need to visualize your data. On top of that, you can easily modify the data and the charts will automatically change. These templates have either one column of data or multiple, depending on your needs. I personally find this useful when deciding which chart showcases my data best, as I can see multiple graphs at the same time and see which one looks better. So if you want to check these out, go to the link in the description below where you can download these free templates from HubSpot to level up your Excel game.
Alright, back to the video. Great, that's the first part done, and now let's get into the top 10. So we'll just go to the next tab by going to Control + Page Down. From here, you can see that we've got the salesperson alongside some missing data here for the first name and the last name. Now, to fill this in, we can go one by one, so hit Max there, and then Charles, and so on. Or the shortcut here is just to go to Control + E, and then you can see that it's going to fill all of these in in a heartbeat. Same thing goes with the last name. See Verstappen. And again, Control + E.
If we look over to the side where it says "Total Sales" here, let's say that we want to find out the total of that. Now, to do so, let's just go up towards the bottom here, and all we need to do is set the Alt + Equals. That's going to sum up all of the totals, and just hit Enter, just like that, we have the full number.
Next up at number eight, suppose we want to copy the whole sale amount and paste it somewhere else. So we'll go to Control + Up here, and all we're going to do is Control + Space. That's going to copy that whole column for us. Then we'll go Control + C. Let's say we want to paste it over here under column O, say Control + V. But the problem is that it all goes to zero, and that's because it was actually a formula. So beforehand, it was actually calculating the total sales, which was the price times the unit sold. But now, because it's not referencing that anymore, it no longer works. So instead, what we're gonna do is copy it again. So go to Control + Space and then Control + C. And this time, instead of just Control + V to paste, we're gonna press the Control + Alt + V. And you can see that this is going to show this "Paste Special" dialog box. And within it, you can just paste the formulas, the formats, etc. In our case, we want to paste the values. That's what's going to give us the actual numbers. So we just click on that and hit OK. And now you can see that it's based at the values, and if we look inside of them, they no longer have the formula either.
At number seven, we've got formatting shortcuts. So suppose over here that we want to change this from a dollar sign to a pound, so a British pound. To do so, we'll go to Control + 1. And this is going to open the "Format Cells" dialog box. And within it, we want to go under "Currency," and we want to change this to a pound. So this one is the one that works for us. And let's say we also want to add one decimal place. And basically, that would do it for us. But as you can see, within "Format Cells," you can also change a lot of other things like the font, the fill, etc. And so we go for an orange fill color as well to identify the pound signs. Hit OK there. Awesome. Now you can see that it's all reformatted. And now if you want to replicate this, this um, this step, what you can do is actually press the F4 key, which is another shortcut, which is basically going to replicate the previous step. So it's going to keep that same um, the number that was there originally, but it's just going to change the formatting.
Alright, now moving into the top five. And over here, let's just go back to this side of the data set. And within the state of all of these here, you'll notice that Texas has a typo where it's got two X's. Now, to get rid of that, instead of going one by one, which could be quite tedious, especially if you have a long data set, you just gotta go to Control + H, which is the "Replace" feature. Define and replace. So we wanna find the "Texas" with two X's, so "Texas" there with the two X's, and we want to replace that with a regular "Texas" as it should be spelled correctly. Then from here, we're just going to go to "Replace All," and it's gonna say, "Okay, we made six replacements." Hit OK there, and we're gonna close out of that.
Now that we've fixed that, let's say that we just want to filter by Texas. So instead of going ahead and having to apply a full filter, all we need to do is just right-click on Texas, go under "Filter" there, and we want to filter by the selected cells value. So just click on that, and from here, you can see that we have all of the Texas State's ones that's been filtered like so. Now, if you want to go to the filter the usual way, you would just have to go to the Home tab from here under "Sort & Filter" and go to "Filter" there. Let's just remove the filter for now.
Another great way to filter is using slicers. Now, to do so, first we're going to convert this into a table. So go to Control + T, and then it's going to select the data set. Make sure it's all correct there, and hit OK. Now, from here, what we're gonna do is under "Table Design," we're gonna go to "Insert Slicer." So just click on that. Then we want a slicer specifically for the states. This is going to allow us to select different states. So hit OK there. And so if we want to select Texas again, all we need to do is click on Texas, and that's going to filter it for us. Same thing goes with any other state. Similarly, if you want to select more than one, you've got to go to the stick sign here. And from there, let's say I want to select Florida and California as well.
In number two, we have a common issue that people encounter, which let's say, first, let's delete the state here. And let's suppose that we want to find out the top three units sold. So we'll just type that here, "Top three units sold." And so for the top one, it's simply going to be the MAX function, which is going to take the highest one, right? So we're just going to go MAX, hit the Tab key, and we're just going to select all of these here. So go to Control + Shift + Down Arrow to the very bottom and hit Enter there. But for the second and third, we can no longer use the MAX function. So instead, there's what's known as the LARGE function. Hit the Tab key there, and the array is that same area. So Control + Shift + Down, comma, and the key here is the actual ranking. So we want the second highest, so it's going to be the two. Close those brackets and hit Enter. And for the third, we're just gonna press the F4 key, which is gonna copy that same action, and then within it, we just got to change the number from a 3 from a 2 to a 3, sorry. And just like that, we have the top three.
And finally, as the number one, let's suppose that it was our intern that sent us this file, and we don't really trust him, so we just want to make sure that the formulas all make sense. Now, to do so, what we can do is actually hit the Control + this accent key of sorts. It's basically going to allow us to see all of the formulas and where they're going. Now, if you want to do a bit more than just that and see where exactly they're going to, what we can do is hit the Alt + T + U + T, which is going to allow us to trace the precedence. So basically, it allows us to see where the formula is actually coming from. Same thing if you hover over any other formula and go to Alt + T + U + T, that's going to allow us to do that. Same thing if you ever want to remove these arrows, you just need to go to the Formulas tab up over here and then go to "Remove Arrows."
For more on Excel, check out this video over here to learn some awesome Excel tricks. For this link over here to take our Excel course. Hit the like and that subscribe, and I'll catch you in the next one.