Join Dennis Taylor for an in-depth discussion in this video Navigation and display tips, part of Excel 2016: Managing Multiple Worksheets and Workbooks.
- [Instructor] We're looking at a workbook called Sample. It's got lots of worksheets in it. Now, you can look at the bottom of the screen when you open the file and see the names. Sometimes you'll see a few, and sometimes a lot more, but a good idea is to click in the lower left corner. There's a panel down there that has two arrows. From time to time, and this is one of those times, one of those arrows might be gray, but no matter. Right-click in that panel, and you will get a vertical list in this dialog box. If the workbook has more than 20 sheets, the scroll bar to the right will be active.
You can scroll up and down and see the sheet tabs that way. The top-down order of this does correspond with the left to right order. We're only seeing a few sheet names at the bottom, but you can see those same sheet names in the list here. Now, if this is a workbook that you haven't seen in a while but you sort of remember it, you might be saying something like, well, I want to go to this formula sheet. I remember it here. You can click that and click OK or simply double-click the formula sheet, and now we're looking at the formula sheet. And the sheet tab display at the bottom of the screen has changed. In the lower left corner, now we see two arrows.
If you want to change the display of which sheet names are now showing, we can click the left arrow. Watch the display, or we can click the right arrow. We're not changing sheet locations. We're simply showing different sheet names by using these left and right arrows. If we want to go back and see the first few sheet names, we hold down Control and click that left arrow on the lower left corner, and now we're seeing the first few sheet names. If you'd like to see the last few sheet names, hold down the Control key and click the right arrow in that lower left corner.
Those are the last few. Let's go back to the first group again. Or remember, we could right-click down on the lower left corner. There's that list again. There's the staff list. Let's go back there, click okay. We're now seeing that first sheet again. Another technique you can use for switching sheets, perhaps not as fast as clicking on sheets or using that right-click technique, press Control + Page Down. Watch the sheet tabs at the bottom. I'm moving one sheet to the right as I press Control + Page Down. And sometimes, that's going to be handy.
If you've got a workbook where the sheet names are January, February, March, et cetera, when you're on the February sheet, you don't have to stop and think that March is the next one or what is the next one. Simply press Control + Page Down. So that's going to be handy at times. Or, sure enough, Control + Page Up to move leftward, and if you somehow wanted to get to the first sheet quickly, hold down Control and Page Up together, and you'll get there pretty fast, or the very last sheet, Control + Page Down. We're there in a second or two. So that can be handy at times, too. Now, you'll notice something else at the bottom of the screen.
Off to the right, you'll see three vertical dots. If you happen to double-click that, it might change the screen display. I say might, because it depends upon how this workbook was set up in the past. But if you double-click the three vertical dots, you're likely to see more sheets, and furthermore, you can drag those three vertical dots rightward and show even more sheets. Now, it does shrink the scroll bar, but usually, that's not a big problem. The scroll bar is still functional, and the little box within it will change, depending upon the worksheet.
The second worksheet in this workbook is called MultiFieldData. It's got many, many columns. Click there and watch that scroll bar on the lower right-hand corner. The box between the arrows is larger because there are more columns. So if you can work with a smaller scroll bar, and perhaps you can, why not show more sheet names? Keep in mind, sheet names can be up to 31 characters, but if they were all really long, we just wouldn't see as many tabs across the bottom of the screen. So, different thoughts about that. Now, occasionally, you'll see three dots on the right side.
These are horizontal dots. This changes the display in almost an unpredictable kind of way. It actually selects different sheets as you move rightward, or, in this case, we see the three dots, horizontal dots, off to the left. Start clicking there too. So, that's a little bit unpredictable. It tends to move the sheets one at a time, but at different points, it might change the display even more than that. Now, within a given workbook, and I'm going to go back to the very first one, Control with the left arrow down here in the lower left corner. Staff sheet, right there.
When you're working within a worksheet, it's a good idea, every so often, just to have a sense of how much data is in a worksheet, not in terms of bits and bytes, but, you know, how many rows, how many columns. When you press the Control + End key, that moves the active sell to the lower right-hand corner of the active part of the worksheet. And what that means is wherever that cell is, there's no data anywhere below this in this worksheet anywhere, and there's no data in any column to the right. So looking in this worksheet here called staff, you'd know for sure, if you pressed Control + End, and the active cell, as it does here, jumps to cell AE699.
No data below row 699. No data to the right of column AE. Now, Excel does remember where you put data, so if you one day just typed in a number or something here and then later erased it, Excel remembers that, so the next time after that, when you press Control + End, it's going to remember that. Nothing too big to worry about, but occasionally, what you'll do is get rid of the empty rows and only when you save the workbook does it readjust that location. But something you want to do from time to time. I'm going to go to the next worksheet and press Control + End.
I know there's nothing below row 1000. There's nothing to the right of column AJ. And how do we get to the upper-left corner of our worksheet? Control + Home. Now, most of the time, that means jump to cell A1. The exception to that is if you've got frozen titles. If you froze row 1 or column A or a couple columns and rows together, possibly, it might take you to here or cell B1 or ever to A2, something like that. Anyway, it does take us to the upper left-hand corner, no matter what, and that's handy too. Control + Home.
When you're working within data, and I'll go back to this first worksheet here. If you're working on, if you made a change here, an adjustment. You want to zip back up top. If you're hand's on the mouse, double-click the top edge of a cell. Not just any cell, but wherever the active cell is. Double-click the top edge. Watch what happens. Up here. Same idea, moving in all four directions, actually. I just made an adjustment there. I want to zip down to the bottom, I'll double-click the bottom edge of the active cell. You can do that in all four directions. If your hands are on the keyboard, it's Control + Up arrow or Down arrow, and of course this, too, works in all four directions.
If I'm going to go to the bottom of this list, Control + Down arrow. If you try the keystroke shortcut or double-clicking the edge in column I, this isn't going to work so smoothly, and you really wouldn't be using it over here, but just to show what would happen, I'm in cell I3. I'll double-click the bottom edge. The active cell moves down here, because the next cell's empty. Double-click here, it'll move just to there, and so on. So you wouldn't be using it in a column like this. And by the way, Control + Down arrow does almost the same thing here, but it is slightly different in the way it works. But for the most part, you don't worry about that too much.
So moving up and down a worksheet is going to be handy. There would be that rare occasion where you want to move to the right here until we don't see any data, so I'm going to press Control + Right arrow. Moves that to the right there. That went from cell A4 over to cell L4, and so on. So a lot of different techniques for moving up and down a worksheet. One other option here, too, I don't think you'd use it too often, but occasionally handy. This second sheet has many, many columns in it. Maybe we're zooming in. We want to see this data. Now, we want to zip to the right and see more information off to the right.
We can see part of column N, but not all of it, so I'm going to press Alt + Page Down, and the first column on the right that's not completely visible will then become the leftmost column. So as I press Alt + Page Down, column N is now the leftmost column. Do it again. We're expecting to see column W as the leftmost column, and the next one around is going to be column AH, and so on. So that moves us across the screen to the right. Alt + Page Down, and sure enough, Alt + Page Up moves us leftward. That could be handy at times too.
And by the way, if you hold down Alt for more than a half-second or so, it activates those letters up in the menu system. That inhibits the command. Just press Escape. You want to press Alt + Page Down almost simultaneously to make that work smoothly. Now, at different times when you're working with data, too, you might want to see more data. In other words, we might want to see data off to the right as well as the data here, so to zoom in and out, quickly and easily, other times you want to zoom out, too. Hold down the Control key. Mouse can be pointing anywhere.
Start moving the mouse wheel, and you'll quickly figure out which direction is going to work best for you, but we can zoom in on the data to see it large and clear. Maybe somebody's looking over our shoulder trying to see this a bit more clearly. We can certainly do that. Hold down Control, use the mouse wheel in different ways. Other times, perhaps, you're scrolling back to see what kind of data's on this sheet, that sort of thing, and there is a little oddity in the display here from time to time as you're zooming in and out. Certain columns might show pound signs. You're not doing anything wrong there. That's just what happens at different times.
I doubt if you'd want to work with the information this way, but you'll just have to readjust the column widths there for those situations or zoom back up again. In the lower right-hand corner, that slider bar just below the sheet tabs in the lower right-hand corner lets us do this, but you got to position the mouse either on the slider bar, or click the minus on the left side or the plus on the right side. So not quite as easily used as that Control with the mouse wheel. So, Control + mouse wheel to zoom in, zoom out, easy. So.
Different techniques here for working with different worksheets within the same workbook. For want of a better term, we've titled this navigation techniques as we navigate within these different sheets and across different sheets in the same workbook. Probably not a term you use as you work with Excel, but it describes what we've done in this particular movie.
- Working with different worksheets together
- Copying and moving worksheets within a workbook
- Linking formulas across multiple workbooks
- Changing tab colors and renaming sheets
- Copying, moving, and inserting data on different sheets
- Working with multiple workbooks
- Navigating workbooks
- Getting data from other sheets with 3D formulas