Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel 2010 Power Shortcuts, Excel expert Dennis Taylor shares tips and shortcuts to vastly increase efficiency and get the full power out of Excel 2010. There are tips for working with the Ribbon and Quick Access toolbar, navigating workbooks and selecting cells, rapid data entry and editing, working with formulas, formatting data, working with charts, sorting data, and much more. Exercise files accompany the course.
Within any given worksheet, there are some tips and techniques for moving around the worksheet perhaps faster than simply scrolling or using arrow keys. One tip that I think is viable in any worksheet and almost anytime, particularly when it's relatively new to you, is to get some idea of how big is this worksheet. Get in the habit of pressing Ctrl+End. Not the letter N but the End key. Now, I just pressed Ctrl+End, and the active cell has moved to into what we might call the lower right-hand corner of the active part of the worksheet, and there might or might not be any data in there.
Doesn't mean anything is wrong if it's empty. What does this mean? I just pressed Ctrl+End. I can say flat out and with total assurance that there is no information in this worksheet anywhere below this row. This is row 742. There's no information anywhere to the right of this column. You can be sure, if we scroll leftward, is there data in this row? Probably, not necessarily, but probably. Is there data in this column? Probably. We could scroll up. Looks like there isn't. So what happens here? Press Ctrl+End again.
The main idea though is you've already determined the scope of the worksheet. Maybe you don't have to worry too much more about it. But Excel does track your usage of a worksheet. If at some point, there were more names in the list or maybe, just one day you happened to write a formula down here or put in some data or whatever, used if for a second or two, got rid of it. Okay, fine. You forget about it. You go back to your work. You do this. You do that. What happens the next tine you press Ctrl+End? Looks like the active cell is now on row 746. There's no data out there.
Well, who cares really? As long as we know there's no data below or to the right, that's the main purpose of this. Now, if over time, if this list begins to shrink some more or if you do a lot of copying and pasting and put data down here and move it back and forth and so on. If you're a little bit annoyed that Ctrl +End is no longer active, what you will have to do is take the unnecessary rows and just highlight the cells. Do a right-click, Delete, delete the unnecessary rows, do the same thing with columns.
But even then nothing will change until you save the workbook and then later, when you press the Ctrl+ End, it will be accurate. The main idea is you want to know the scope of a given worksheet. That quickly gives you that scope by letting you know that there's no data beyond the certain cell, neither below and nor to the right of it. You could say the opposite of Ctrl+End is Ctrl+Home. And nearly always this puts the active cell in cell A1. The only exception to that is if we have frozen titles. But it's handy because many, many times it's in a worksheet, no matter what the size, I need to return to the upper left-hand corner of starting point.
Now if we have a particular worksheet that has many, many columns, moving across the worksheet is probably something we either do with the scroll bar. Maybe we use arrow keys. You can also zip across the screen with Alt+Page Down. Now right now on the screen here, column H is the first column that's not completely visible. So as I press Alt+Page Down, now column H becomes the leftmost column. If I do this again, you can see after the right column T is partially but not completely visible.
I'll press Alt+Page Down. We don't really care about those details that much but Alt+Page Up, Alt+Page Down moves the screen rightward or leftward one full screen. And the wider the worksheet, the more valuable this particular feature happens to be. Now, within a given column or a row, sometimes you want to move up and down a column somewhat quickly. If this is more or less contiguous data, how do we get to the bottom of the column kind of quickly? From the keyboard, it's Ctrl+Down Arrow or if we're at the bottom here and we want to zip to the top, it's Ctrl+Up Arrow.
It works in all four directions. If you're using a mouse and we ought to get to the bottom of this column, we have to click right here. Double-click the bottom edge of the cell. Now, stay away from the corner. Don't do that. Double-click the bottom edge. This moves the active cell downward until there is a break in the data. Same thing true on top here and here too, the same thing in all four directions. If we try this on a column where there are a lot of empty cells, for example in column I, this is not going to work so well here. We have the active cell there. Double-click. What happens? Jumps to there. Double-click, so sort of leaps across these.
Not so efficient. We wouldn't really be using it. In general, Ctrl+Down Arrow and double -clicking the edge work the same way. Occasionally, it looks a little bit odd. For example, right-click, double- click the right edge. It takes us there. We use Ctrl+Right Arrow, takes us there, slightly different. But those are just quick tips for moving in around on a particular worksheet. And occasionally you do remember an address, or say in this worksheet that you have been working with a lot maybe you made some changes around row 500 yesterday. You haven't been sorting.
How do we get there quickly? Simply press F5, type in an address, for example A500, press Enter. It will take us there immediately. So that's handy as well. The F5 key traditionally has meant "go to" not only in Excel, but also in Word. So some quick ways to move around a given worksheet, the current worksheet.
Find answers to the most frequently asked questions about Excel 2010 Power Shortcuts.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.