Join Dennis Taylor for an in-depth discussion in this video Navigate between workbooks and worksheets efficiently, part of Excel: Tips and Tricks.
- (Male Narrator) We're looking at a workbook in our chapter four file that has many different worksheets in it. You see those at the bottom of the screen. In fact, there could be more then we're actually seeing. It's not a bad habit when you open a file that you've not seen in a long time, or maybe never. In a lower-left corner, you'll see a gray panel. And sometimes, you'll see two arrows there. Here, one of them is very gray, other one's green. If we right click, and we see a pop-up, as we slide over, that's right click in this panel on the lower- left corner of the screen, and we will see a vertical list of sheet names.
If there are more then 20, we'll see a scroll bar on the right-hand side. So, this is a workbook that looks like it has 17 or 18 different sheets in it. The top down order that we here see here does correspond with the left-right order of the sheet tabs that are visible down below. If we want to jump to a certain sheet, here's one called "find formulas". I'll just click it, and click okay, or double click it, either way, we jump to this sheet. And the displays change at the bottom of the screen. We can always go back to the lower left corner, there's an arrow on either side now that's usable, we can click the left arrow.
We're not switching sheets, but we're seeing different sheet names to the left. Or we click the arrow on the right, we see the display move that way. If we want to see that menu again, we'll simply right click. This is down in the lower right corner again. Right click. Maybe we'll go to the employee sheet again. It's the first one, double click it. We can also switch sheet tabs by pressing CTRL + Page Down, watch the bottom of the screen, we're moving to the next sheet, next sheet, next sheet, rightward. If it's a workbook that has sheet names like January, February, March, you know almost instinctively by doing this.
Pressing CTRL + page down, you're moving to the next month. CTRL + Page up leaves us one page leftward. So that can be handy at times, too. Also, at the bottom of the screen, you'll see off to the right, it might be very gray, three vertical dots. You can click and drag to the right, this exposes more sheet names as it shrinks the scroll bar. Now, the scroll bar can be pretty small, and yet, still be efficient. In the current worksheet, we wouldn't really use it very much, but a worksheet that has lots of columns like this one, and maybe we've zoomed in on this...
We can still use that scroll bar, even when it's pretty small. We are seeing more sheet names. They can have up to 31 characters, and at different times, we'll want to reconsider whether these are explanatory enough or not, we can double click and change the name if we wish. But, there's always that trade-off. We want the sheet name to be somewhat of an explanation, and yet, at the same time, not take up too much space. The longer these sheet names are, the more space they take up. Once again, we can go back to that lower left corner, right-click, and jump to a different sheet if we need to.
Simply by double clicking. What if we have different files open? I'm going to create a new workbook with CTRL + N, I might have other files open, as well, too. When you press CTRL + N, you automatically create a new workbook. It'll have a name like "Book 1", "Book 2", depending on what else might be open. How do we remember which workbooks are open? Sometimes you might have four or five open. You can always go to the "View" tab, and click "Switch Windows". This'll show us the names of all open workbooks. We can certainly click on the other one to jump back there.
If you have only two workbooks open, it's real handy to press CTRL + TAB to move back and fourth between them. If you have three, or four, or five, pressing CTRL + TAB simply moves you back and fourth between the current one and the one that you were just viewing. So there, you want to press CTRL + SHIFT + TAB to be able to get to the others. If you find yourself using that button often on the "View" tab, it can get a little annoying that you always have to go to this tab. So many of you know, that any button that's in the Ribbon menu system can also exist in the Quick Access toolbar above the ribbon.
Right-click, in this case the "Switch Windows" button, add it to Quick Access toolbar, there it is. It's going to stay there all the time. So that means if you're using the home tab, and you want to get to the other workbooks, and maybe you've got four or five open, you don't have to go to the view tab each time. This button is always visible. You can click the drop arrow there and choose the other book. In this case, there're only two, but if you have three, four, five open, that's going to be handy. And a lot easier to move back and fourth. So, a number of different techniques we've seen here, these are sometimes called Navigation Techniques, is we jump back and fourth between different worksheets within the same workbook, and in some cases, between different workbooks.
- Creating charts with keystroke shortcuts
- Expanding and collapsing ribbon and full-screen views
- Display shortcuts
- Efficiently navigating between workbooks and worksheets
- Selecting entire rows, columns, regions, and worksheets
- Data entry and editing shortcuts
- Rapidly creating formulas
- Operational and formatting shortcuts
- Data management techniques