Skip to content
005: My Top 10 Excel Keyboard Shortcuts episode artwork

Oct 5, 2015 16 min

005: My Top 10 Excel Keyboard Shortcuts

from Learn Microsoft Excel with MyExcelOnline

Excel Keyboard shortcuts are used by almost every one of the 750 million Microsoft Excel users worldwide. They range from the CTRL combinations shortcuts to the Function keys shortcuts as well as many other Excel keyboard shortcuts. In this episode I go through my top 10 favorite Excel keyboard shortcuts that will save you heaps of time when working with Excel data. What is your favorite Excel keyboard shortcut? Let me know by entering it in the comments section of this episode over at: 👉 Show Notes: https://www.myexcelonline.com/podcast/005-my-top-10-excel-keyboard-shortcuts-excel-podcast/ 👉 Academy Online Excel Course: https://www.myexcelonline.com/courses

Episode details come from the podcast's RSS feed.

Embed this episode

Ready to play

005: My Top 10 Excel Keyboard Shortcuts

0:00 16:26
of matches

Transcript Auto-generated, may contain errors

This is a MyExcel Online Podcast, episode number five. Welcome to the MyExcel Online Podcast, the need-to-know Excel insights, knowledge, and tips brought to you by the experts that know them best. Are you ready to explore your full potential and get better at Excel? It's time to stand out from the crowd with your host, John Mikoludis. Hey guys, it's John McAleves here from the Microsoft Online podcast and I have a special show for you today. In today's episode, I'm going to go through my top 10 favorite keyboard shortcuts. With these tutorials in the podcast show notes, if you go to Microsoft Online.com slash podcast and you click on episode number five, you're going to have the links to every shortcut that I go through today. And all you're going to do is click on that link and it'll go and open up a new page and it'll show you the tutorial of how

that shortcut is done. So it's in an animated GIF format and you can sit back and watch the tutorial. So if you cannot grasp it while you're listening to this podcast, then when you go back to your web browser, make sure you click on the show notes and you're going to have every tutorial there for every shortcut that I go through. Another thing that I want to say is that each keyboard is different depending on which part of the world you're from. So you may have, for example, the equals key on my keyboard is on the top of the zero.

So it's on the right on top. For me to access it, I've got to press the shift and the zero. So that's where the equal sign is. But in most, that's because I'm in Europe, but in most cases, the equal sign is right next to the enter sign. And there's a lot of little nuances like that. So if I say you're going to press control asterisk, then, you know, you may need to press control shift asterisk, okay, depending on your keyboard. So each keyboard is designed differently depending on the region that you're in. Now, these are my favorite shortcuts. There are...

heaps of shortcuts out there but these are the ones that i like but i want to know your shortcuts so if you go on to marxonline.com slash podcast click on episode number five and go all the way to the bottom in the comments section i want you to type in your favorite keyboard shortcut and i'm going to post that in an upcoming show with all the listeners top 10 shortcuts okay so make sure you put in your favorite shortcut in there another thing i want to say is when we write a keyboard shortcut for example control one we usually write it as control plus one What that means is control and one. So in actual terms is you've got to hold down the control key and whilst it's being held, press the number one. You don't have to press the plus sign. So a lot of people get confused that you've got to press control, plus and one. No, the plus means and. So what it means is you've got to hold down the control key and then press the next button. Okay, let's get straight into it. Now, at number 10, we have control F1. Now, what it does is it hides and unhides the ribbon menu. So you press control F1, the ribbon disappears. You press it again.

it comes on there again. Now, this is great if you're working in a laptop and you have small space and you want to take advantage of that space. It's always best to press Control F1. And again, Control F1 to bring back the ribbon menu. Shortcut number nine. Now, this shortcut, I never used it, to be honest. And in MrExcel's new book, MrExcel XL, the 40 greatest Excel tips of all time, that was in there. And I was just amazed that I never actually used it with all my years of using Excel. And that's the beauty about Excel. All these shortcuts come up that you're never using. Like, wow, this is awesome. Okay, so it is control backspace. So say you click in an active cell, saying A1, and you have lots of data that goes all the way down to say A1000.

So your active cell is in A1 and then you scroll down with your mouse all the way down to row number, let's say, 1,020. Now, if you want to go back to A1, what would you do? Now, the slow way is to scroll all the way back up, all the way back up. But if you press control and backspace, it goes back to the active cell. So that is an amazing tip and one that I'm going to use all the time going forward. Shortcut number eight, control, minus or plus sign. What that does is it inserts or deletes a row or a column.

so what i usually do is with my mouse key i select say row 15 so i select the whole row and then i press ctrl plus plus plus to add extra rows or minus minus minus to delete extra rows the same thing for columns shortcut number seven ctrl d or ctrl r d is for down and r is for right so it copies the cell above if you press ctrl d and you press ctrl r it copies the cell to your left so say we are in cell e 13 and we have a formula in there and want to copy it down just go one cell down to E14 and press Ctrl D. What it does, it copies the cell above. Now, say we are in cell E16 and we have a formula there and then we'll go on the right hand side to F16 and we want to copy that formula. All you have to do is press Ctrl R and that copies the contents from E16.

shortcut number six, F4. Now, what F4 does, it has two purposes. The first one, it repeats the last command. So if you're formatting a cell, say you're putting in there a bold or you're putting in there a color, and then you go to another cell and you press F4, and then you go to another cell, you press F4, and then you keep going to another cell and press F4, it copies the last command. So the other use for the F4 is it puts in an absolute reference in a cell.

So if you have a formula, so you have a VLOOKUP formula, and then the second argument is your table array. So you highlight that table. If you press F4, it puts in the dollar signs next to the cell references. So what that means is when you drag the formula down, the table array stays fixed. Now, if you press the F4 again, then it becomes a mixed reference. And you press it one more time, it changes. And then you press it a fourth time, and it goes back to normal. So that's always good to use when you're working with formulas. Can you analyze this spreadsheet using Excel pivot tables? Sure.

if you can show me how to. Introducing the Extreme Pivot Table online course, which teaches you how to analyze business data and highlight key metrics so you can make insightful business decisions, create interactive analytical dashboards that will reach top management, and set you apart from your peers, which will make you more important and increase your chances of a promotion or pay rise. Developed over 15 years through extensive professional use and including real business case studies is the ultimate pivot table course. With over 200 short downloadable video tutorials accessible online anywhere 24-7. 12-month personal support if you ever get stuck and a 30-day money-back guarantee. Visit myexcelonline.com today and give your professional development the boost it deserves.

So you have a range of data, and then within that data, you have some blank cells. You want to highlight everything at once. What you'll do is click anywhere within that data and press Control, asterisk, and then that will highlight all your data at once. That's an awesome tip. Shortcut number four, control and the arrow keys. And also control, shift and arrow keys. If you have your range of data and just click on the top left-hand side of your data, if you press control and the right arrow key, it goes all the way to the end of your data. You press control down key, it goes all the way down to the end of your data. And if you have some gaps in there, then it'll stop and you're going to press control down again, again until you go to the end. And if you press control, shift and arrows, what that does is it highlights your selection.

and then you can press ctrl shift right and then ctrl shift down to highlight all your data Shortcut number three, F9, which checks your math. Now, this is a great shortcut and one I use all the time. And I'll show you three quick examples. So we have our data in between A1 and A5. So in A1, we have number one. In A2, we have the number two. In A3, we have number three. In A4, we have number four. And in A5, we have number five. Okay. And in A6, we have a sum. So we're putting a sum formula. So sum's A1 to A5. Now, in the formula bar,

If you have a look, it says equals some brackets A1 to A5, close brackets. Now, if you highlight with your mouse A1 to A5 and press F9, you'll see that it gives you the squiggly brackets and it has number one, number two, three, four, five. So it shows you the values within that range. If you press enter and then go have a look at your formula bar, then those numbers are locked in there. You press Ctrl Z to go out of it. Now, once again, we can highlight A1 to A5 within our formula bar. Press F9 to see the values that it returns us. And then we press Ctrl Z to get out of it or just escape. The second way where you will use this, say, for example, you have a formula, which is equals 15 plus brackets 20.

times 15 close brackets and say you want to get the value for 20 times 15 within the brackets what you're going to do is highlight that and press f9 and it'll give you the answer 300 so you can go within formulas and highlight certain parts and it gives you the answer you can escape to get out of it or ctrl z to get out of it And the third way where you can use this is in a logic function. So I've got a function here, which is equals A1 to A5 is bigger than 4. Now, we said before that A1 has number 1, A2 has number 2, and then all the way to A5, which has number 5. It increments all the way there. So my formula is equals A1 to A5 is bigger than 4. Now, if I highlight in the formula, but A1 to A5 is bigger than 4 and press F9, it'll give me false, false, false, false, and true. So A1 is number 1, and that's not bigger than 4, that's false.

The same thing for A2 is false, A3 is false, A4 is false, but A5 is true. So A5 is number five, which means it's bigger than four. So you can see within your formula bar, it gives you your trues and falses. So if you're ever doing some auditing and you want to drill down into your formula and see where you made a mistake, then F9 is an amazing, amazing shortcut to use. Shortcut number two, Control-Enter. Say you want to put a formula in 30 cells going all the way down. What you have to do is highlight your cells and then put in any formula that you like. And then instead of pressing Enter, you press Control-Enter. And what that does is it fills in that formula all the way to your selection. So that's a great shortcut to use if you want to fill in your formula into a selected range. And finally, number one.

Drum roll, control one. What that does, it displays the format dialog box. So if you have shapes, charts, smart art, picture, or a cell, if you press control one, it brings up the format dialog box. And within there, you can format whatever you like in a cell. You can format with your numbers or alignment font, border field. If you have a chart, click on your chart, you press F1, it brings up the format chart area. You can feel, you can border color, your style, you've got the shadows, you've got everything there. Control one, I use that all the time and it's my...

favorite shortcut and a lot of people's favorite shortcut as well. Okay, I hope you liked those shortcuts. Once again, I have tutorials for each of these keyboard shortcuts. If you go to myxonline.com slash podcast and click on episode number five, you're going to get all the show notes in there. You click the links and it shows you how to do these shortcuts. And finally, I want...

your shortcuts. I want to post them on one of these podcast episodes. So go into the comments area of episode number five and type it in there, put in your name, and I'm going to publish the 10 most popular shortcuts. And I'm going to mention your name as well there. So go ahead. I want to learn something new and I want other people to learn something new because I know there's a lot of keyboard shortcuts, a lot of gems. And if you can just type it in the comment box and give a description where...

and how you will use it, then I'm going to put that on one of these upcoming podcast episodes. All right, guys, thank you very much. And I'll see you in the next episode. We hope you enjoyed this episode of the My Excel Online podcast. Be sure to subscribe and share this with your colleagues so they can reach their full Excel potential. You can also visit myexcelonline.com slash podcast for show notes, as well as John's online Excel courses and free tutorials to help you stand out from the crowd. Until next time, keep excelling.

Frequently asked questions

How long is this episode of Learn Microsoft Excel with MyExcelOnline?

This episode is 16 minutes long.

When was this Learn Microsoft Excel with MyExcelOnline episode published?

This episode was published on October 5, 2015.

Is there a transcript available for this episode?

Yes, a full transcript is available for this episode. You can read the complete transcript on the episode page.

Can I download this Learn Microsoft Excel with MyExcelOnline episode?

Yes. Use the download control on the episode player to save the publisher-provided media file.