Updated June 15, 2023
Part 26 – Excel Format and Navigation
Download Excel Format and Navigation Template
Transcript For The Video – Excel Format and Navigation
In this set of video tutorials on Excel Format and Navigation, we are going to learn some of the amazing shortcuts in Excel, which will not only help you on a day-to-day task but significantly reduce time and help you in terms of working efficiently. Now we will divide our shortcuts into six different categories formatting shortcuts. We will only concentrate on the top shortcuts which are really used on a day to day activities. We will only discuss five here for the navigation shortcuts from moving one place to another place in the worksheet. We will learn the ten most important shortcuts here. How to go about selecting a row or a column, or you know, selecting a cell; these are the seven shortcuts that we will learn. How we can copy and paste data? on how we can cut and kind of use that data? We will learn all these in working with data, and I will teach you eight shortcuts. We will also understand the antiquates of the workbook basics, how to save your workbook, essentially, how can you open your workbook. How can you include a new workbook? and likewise, so there are eleven shortcuts and last but not the least, we will also discuss some of the formula shortcuts which are very relevant to save your time. So let’s move on to the first set of shortcuts which is the formatting shortcuts. Now let us learn the top five formatting shortcuts. The first shortcut is bolding the selection, so let us say if I am typing the text. Let us say we type a,b,c. Now if you want to bold the selection, what you need to do is, you need to press CTRL and ‘B’; so that it gets bolded so that if you want to unbold it, you can press CTRL and ‘B’ again so that it gets unbolded so bold, unbold, bold, unbold CTRL+B. Likewise, CTRL is used to italicize your selection, so it can be done by CTRL and ‘I’. If you press CTRL+I again, it will become a normal selection, so if you want to italicize CTRL and I and otherwise CTRL and I to go back to where you came from. If you want to underline the selection CTRL and ’ U ‘ so, you will find the text gets underlined here CTRL and ‘U’. Likewise, let’s us say if you want to format this cell further and look at even advance feature. You can press CTRL and “1” The moment you press CTRL and 1, you will find that this dialogue box opens up, and this dialogue box provides you with different kinds of options in terms of formatting. If you want to change the alignment, if you want to the font, borders, fill option, you want to protect the cell, you know these are the general things which you can do when you open the dialogue box, so let me open the dialogue box again by using the shortcut CTRL and ‘1’ O.K. So remember these CTRL keys this the main thing and last but not the least let us say if you have some number say 0.23; you want to change this quickly into percentage. One approach is that you can directly come here and click on percentage, and the other shortcut, which is very significantly used in some cases, is CTRL, SHIFT, and percentage (%), so it gets converted into percentage automatically. So let me go back again, CTRL + SHIFT + %, so what happens is your decimal gets converted into percentage format. So this is how you can learn the top five shortcuts. Now let us learn some navigation shortcuts; essentially, navigation shortcuts mean that how you can maneuver between the worksheet Excel from left side to right side and essentially between the worksheets from one worksheet to another worksheet. So there will be ten commands or ten shortcuts you will learn in this. The first one CTRL and page down. What this means is you need to move to the next worksheet in the workbook, so say, e.g. if I am right now in the navigation worksheet, and I want to move from navigation worksheet to workbook basic worksheet. What I normally do is I use my mouse and go to the workbook basic and click, and this is how I change my worksheet. So let me go back to the navigation worksheet and see how this formula works CTRL and page down to move to the next worksheet, and look at the other one, CTRL and PAGEUP, to move to the previous worksheet. So if I want to move from navigation to formatting, CTRL and PAGEUP, and if I want to move to the next worksheet, CTRL and PAGE DOWN. So let us do once, CTRL and PAGE DOWN. So I am now taken to the workbook basic worksheet. Let us say I want to go back to the navigation worksheet. I will use CTRL and PAGEUP, so in this way, I come back to my original worksheet. Let me go to the formatting worksheet, so which command we will use? CTRL and PAGEUP. So I am now into the previous worksheet now. Let me go back to the navigation worksheet; I will use CTRL and PAGE DOWN, so in this way, you can use these two surf command which is very, very easy to use. The third one, which you probably are already be using, is the arrow key. So instead of using your mouse to move from one place to other places, you can actually use the arrow key, top arrow, down arrow, right arrow, and the left arrow. So you can use these arrow keys. So this is another keystroke which you must use. So now another thing which is important, let us say, is CTRL. An arrow key if you want to move to the edge location; edge location means let us say if I am here, I want to move to cell number 4; how can I do that? CTRL and left arrow. So I will move directly to the edge location? Let us say from number 4; I want to move back here. I can do that by using one-by-one arrow keys, or I can jump directly by pressing CTRL and right arrow key here. So let me do that CTRL and right arrow. I directly get transferred to the edge location of the worksheet. Let me press the CTRL and up-arrow. What will happen is I will move to the top here, CTRL and up arrow as I need to move to the edge located at the top. What happens if I press CTRL and down-arrow? I will move to the edge location again towards the bottom CTRL and down arrow. So this is how you know you can use your keystroke function with CTRL and arrow. Now if you want to move to the beginning of the row, let us say you are here and you want to move to the beginning of the row. You can use just click at HOME, so you go back to your beginning of the row. Let us say if I am here. I want to go back to home; I can directly come by pressing the HOME button. OK. What happens if I press CTRL and HOME? I will move to the beginning of the worksheet, so the navigation shortcut is written A1, so it is where I move up to. Let us say I try this function again if let us say this is where my cell is, and if I press CTRL, HOME, I go back to my navigation shortcut. So this is really useful as many of the time, you have lot of data in your worksheet, and you will be working in, let’s us say, 800th or 900th line number, and you want to go to the top of the worksheet on one side, you may be tempted to use your scroll bar. On other side, if you are really aware of these shortcuts, you can just press CTRL and HOME so that you can move up the ladder, and you save lot of time here. So this is CTRL and HOME. What about CTRL and END? This END function is on the right-hand side of the HOME CTRL and END. So what happens here it takes us the used portion of the worksheet, moves to the end of the used portion of the worksheet. So let us say if I am on the navigation shortcut, if I press, let us say, CTRL and END, it takes me to the end of the used portion of the worksheet, OK. Now this would be very simple because you already would be using it. Let us say if I am here if I type some number. I can press ENTER to move down a cell in the range, so this is how I can use this shortcut. What happens if I press SHIFT and ENTER? I move up the chain SHIFT and ENTER, so if I want to move up SHIFT and ENTER and what happens if I use SHIFT and TAB? I move to the left to the text or to the cell, so let us say SHIFT and TAB I move to the left of the text, so from B15 I move to the A15, so if I wanna move from this cell to this cell here I can use SHIFT + TAB to maneuver into the left-hand side. So this is how you can use the navigation shortcuts. Now let us look at some of the amazing workbook basic shortcuts. So they have listed here CTRL and O (oh) to open a workbook. So let me press CTRL and O. What happens when I do that CTRL and O? So the moment I do that, you can see it open a dialogue box and allows me to open an Excel sheet. So this is how I can open a dialogue box and open an Excel sheet using a shortcut Let us say if I want to create a new workbook, just press CTRL and N. So the moment I do that, you can see that this is new workbook comes; this is book 3, so a new workbook can be opened using CTRL and N. If I want to save my workbook CTRL and S, this is one of the commands which you will be using very frequently. You must have a habit of saving your document very frequently because, unfortunately, if Excel crashes, you will have your last saved version for your perusal. So CTRL and S, so I am doing that though it may not be visible here screen. Still, CTRL and S will really help you save your workbook. Print your workbook CTRL and P, so let me do that CTRL and P; the moment I do that, you can see you just need to press OK., so this is CTRL and P for printing. If you want to close this workbook, CTRL and W. However, I am not going to do this here because I am just explaining to you the concept. I am still working on the other set of functions. CTRL and W are what you need to do when you want to close a workbook.If you want to insert a new worksheet, these are worksheets, so if you want to insert you can do it from here SHIFT and F11, is again the shortcut as you can see at the bottom; SHIFT and F1 that’s what I will be pressing so a new sheet opens up. So this is what you can also use to save a lot of time. Another command which is really useful is hiding a selected row. Let us say I want to hide a cell or this row. If I want to hide this row, I can do this by choosing this. I can, in a traditional way, right-click and choose hide. So the cell between 1 and 3 gets hidden; if I want to unhide, I can choose these cells, right-click and unhide. So this is the regular way in which you can hide and unhide your cells, but the formula which is provided here is CTRL, and 9, and CTRL+SHIFT+9. So let me see how it works. Let me select this row CTRL and 9 so this is the shortcut for hiding these cells, and if you want to unhide the cells, you can select these two rows and press CTRL+SHIFT+9 OK. So this is how you can use these hide and unhide options. Let us do the same thing for the columns. So if I want to hide the column here, let us say so; what is the function? I can, in a traditional way, right-click on this and hide the column, and if I want to unhide this, I can select these two columns in tandem and right-click and unhide. So this is how you can do that in a traditional way. Let us apply shortcut; shortcut is CTRL and 0(zero), so 9 and 0 are placed next to each other in the keyboard. So it is very easy to identify CTRL and 0. So this column gets hidden, and CTRL+SHIFT+0 to unhide your column. So this is a very amazing function which helps you hide and unhide your columns and rows. Another useful function which you will find is very useful is, which you must use many times when you are working on important reports, run a spelling check on a worksheet or a selected text what you need to do is just click on F7. The moment you do that, your Microsoft Excel has an inbuilt feature which goes through a spell check inside your worksheet. So it prompts you in case you find there are issues associated with spelling mistakes, so F7 again is a very useful function, and try to use it as regularly as possible.
