Hello, In this continued series of Ninja level Excel shortcut keys from our Excel Training program, let me walk you through some of the Excel shortcut keys which relate to workbook navigation, sheet navigation and cells or cell selection. And in between I'll throw some of the very important shortcut keys which you would normally not find even the advance users of Excel using those. So lets go with certain base data.
Now you notice the first thing I have in my workbook is a couple of worksheets. Some are old. Some I have generated right now. If you want to toggle between the different sheets, you might want to use Ctrl + Pageup, pgup. And page down, Ctrl + Pgdn. So this is a Excel shortcut key which actually helps you toggle between the different sheets in especially, in case if you're working with multiple worksheets. Now let me take a test data before I talk about other Excel shortcut keys. Let me go to sample data 1, which you will also find in the Excel case study file.
The advantage with us is that you have both the Excel videos, Corporate trainer with you and the Excel case studies to practice on. So lets go ahead. First things first. If you work with data, the primary step is to choose the data before you can apply sorting, filter or pivot table, isn't it?
Now, given the data we have, I wish to apply a certain filter. Now to do that I must choose the data. Now if I place my cursor on any one cell and press Ctrl + A, notice it chooses the data, but it stops at the point where there is a blank row. So to circumvent this I will take you through another series of Excel shortcut key which will help you select the data. So I place my cursor on the orange cell. And I keep shifting control pressed. With my left hand and keeping shift control pressed. Keeping it pressed now let me put my right hand side finger on the right arrow key. What it does - it quickly selects the data on one direction that is right direction. Then, keeping shift control pressed I pull the down arrow key, down arrow key, down arrow key, down arrow key, and I'll continue till the last line of the data. Although it helps you in selecting the data, but notice since there are so many blanks, is actually making your life worse. So now let me again take you to a excel shortcut key which is going to help solve this problem. So put your cursor on the orange cell. We will not be using Ctrl + Shift tried and down. Instead, we'll be using Shift + Ctrl + End key.
Now what is Shift + Ctrl + End key doing? If I take you to a blank worksheet, and I write some letter, let's say a, b and letter c or maybe d. Notice this cell has been the last used cell on this worksheet. Now even if I delete that particular letter and then place my cursor on letter a, and then if I press Shift + Ctrl + End key, it takes my selection right from active cell to the last used cell. So I used this trick, why? Because orange cell was the active cell. And Shift + Ctrl + End key, help me take the selection to the last used cell. Now at times it may happen there could be other cells, which have been used previously, and there are no data so, if you intend to apply Shift + Ctrl + End key, it will take your selection to the last cell.
Now couple of things. This has allowed you to skip the blanks and choose the last cell. But wait, you do not want to choose the cells, in fact columns of the F G H I and J. So what we do, we want to reduce the selection. So one step at a time. Shift key pressed and then left, left, left, left, left, now only 3 columns have been chosen. Now what shift right, right or left does is, it allows you to select 1 column or row at a time in terms of direction you are taking. At the same time you would say look, I've chosen the data, but I want to go back to the header. Right, so if I press the Ctrl + Backspace key, notice the data is still selected. And yet your selection appears to be taking you to the header. Because I've seen people what they do, once they've selected the data, they use the scroll bar on the right to reach the header of the data.
This could be a painful task, especially if you're working with really large data. So just summarizing what have we learned. We talked about Ctrl + A, we talked about Shift + Ctrl right down, down. And better than that on occasions we also used Shift + Ctrl + End key. In terms of selection, reducing the selection, shift left, left, left, left. And Ctrl + Backspace Keys helps me go to the header. More shortcuts on the way as we go to the next video in our Excel Training program.
The course itself was designed in very good way having step by step & logical approach to explain the concepts & logic that will make things easy to remember in future. This definitely will help to improve my work as I work extensively with excel.
- Ashraf Halai
CPA, CA at Aviation & Aerospace
if you are a regular user who performs complicated tasks in Excel on daily basis, you are likely to take that back within a week. The author's delivery is very efficient. Highly recommended.
- Ivan Arnaudov,
Owner and CEO at Business Ventures International LLC
If you're not completely satisfied, let us know within 30 days for a full refund. We want you to be 100% satisfied.