Join Dennis Taylor for an in-depth discussion in this video Using worksheet navigation tips, part of Managing Multiple Worksheets and Workbooks with Excel 2007.
An Excel workbook can have many sheets. It's limited only by memory. Here's a workbook called Sample that has quite a few worksheets in it. If I sent this to you and you'd never seen it before or maybe it's one of your files that you haven't seen in a long time, it's a good idea to get oriented with the workbook to figure out what's going on. Many of us will start clicking sheet names at the bottom. It's not a bad idea. But the arrows in the lower-left corner of an Excel workbook are referred to as navigation arrows. Right-click on those arrows and you'll get a vertical list of sheet names.
The top-down order of those names matches the left or right order that we see at the bottom of the screen. And usually when you do this, you're seeing a lot more sheet names this way than you might as you look first across the bottom of the screen. A workbook as I said has many or can have many many worksheets. This technique here will show you no more than 16 sheets. So if you have a workbook with more than that, the last entry will say more sheets and this isn't quite so efficient, but it's still handy at times. Another thing you can do to facilitate usage of worksheets is to put the mouse at that point where the sheet names end at the bottom of the screen here and the scroll bar begins.
If you put the mouse right at the point, you'll notice that the mouse pointer becomes a two-way horizontal arrow. You can click and drag and move this to the right and see more sheet names. And that scroll bar can be pretty small and still be efficient. So why not see more sheet names? That's handy and of course in many workbooks, you'll be seeing all of the sheet names. A lot of workbooks have only five or ten sheets, perhaps even fewer. Now another aspect of clicking on these navigation arrows over on the left-hand side is that not only do you see the sheet names, but you can use this as a go-to or jumping vehicle.
I want to go to that MixedNames sheet. Check that out a little bit. Oh yeah, I remember that one. Done looking at that for a while. I'll right-click on these. In fact, as I use this workbook or any workbook, why not use this feature often? It's a quick way to jump to different worksheets within a workbook. Now within a given worksheet another way to switch worksheets is from the keyboard. If I press Ctrl+Page Down, I'll move one sheet to the right and you can watch the sheet tabs at the bottom.
This is likely to be more useful when you have a workbook where the sheet names are pretty clear in your mind. For example, a workbook that has January, February, March in sequential order, and you're working with a February sheet. You know the next sheet to the right is March. You don't have to look at the bottom of the screen. So Ctrl+Page Down there is going to get you there quickly, one sheet to the right. Ctrl+Page Up, one sheet to the left. Fast and easy. Within a given worksheet, and I'm clicking on the staff name, this is the first sheet. If you'd like to move across the screen more rapidly and your hands are on the keyboard particularly, instead of scrolling, just press Alt+Page Down, one screen to the right, Alt+Page Down. Or one screen left as you might imagine, Alt+Page Up, Alt+Page Up.
This will take you to the first column rightward that is not fully there. So for example, here, if I'm looking at the screen like this here, the first column on the right that is not completely there as Column L. So I'll press Alt+Page Down and it becomes left column and so on. So it's fast and it's easy to move around worksheets. Now, other workbooks might have a different layout here. I'm going to open another workbook here called Hyperlink State File and a quick look at the bottom of the screen suggests and it is true, there is a tab here, a worksheet for each state.
Now moving back and forth to the different states at different times can get a little old and here if we right- click on the navigation arrows, we're not seeing as many sheets as we might want, ones at the end of the alphabet. So here's another approach. There's a sheet here called Links and potentially this could be a table of contents. I am going to right-click on Ohio here, and create what's called a hyperlink. This can be a jump to a Web site or can open another file, but let's keep it within this document and simply associate this with the Ohio sheet. There it is.
Click OK. This is now a hyperlink. What happens now? We jump to Ohio, there it is. If you need to do this to all the sheets it's going to take some time, but here there's the idea that you can do this on a piecemeal basis with this state, that state and if you have another workbook, or it makes sense and you're looking at one worksheet, you frequently need to jump to another one, set up a hyperlink. One more here. Click California. Ctrl+K, a keystroke shortcut. That's' another way to do it. the same general idea here. There is California. Okay, now I've got a hyperlink to there.
The more ambitious people might on each state sheet have a hyperlink that takes them back to the Links sheet, but it's another method of moving across different worksheets within the same workbook. So there's no shortage of ways and techniques to accelerate movement both across and within worksheets in Microsoft Excel.
- Navigating across worksheets and workbooks
- Displaying multiple worksheets or workbooks simultaneously
- Inserting, deleting, moving, and copying sheets
- Changing sheet names and tab colors
- Showing and hiding worksheets
- Creating linkage formulas among workbooks
- Locating and managing links