Join Dennis Taylor for an in-depth discussion in this video Displaying multiple worksheets and workbooks, part of Excel 2016 Essential Training.
- If you have more than one workbook open, sometimes it's advantageous to see a portion of each of those workbooks. And at other times, when you're focusing only on a single workbook, it might be to your advantage to be able to see different worksheets of that same workbook. It can get a little bit confusing at times if you do have multiple workbooks open and you're trying to accomplish both of those objectives at the same time. We need to have three files open right now, 0901 Home Products Line, 0901 Regional Sales, and 0901 Employee table.
Now maybe the files are interrelated, or maybe we're going to be copying information from one file to another. How do we get back and forth between files? On the View tab, if you click the Switch Windows button, you'll see which files are open and which one we're currently seeing, of course, its heading is at the top of the page. And we can easily get here and switch. If you're working on the Home tab, or the Formulas tab, or the Data tab, if you're doing that frequently it could get a little annoying that you always have to go back to the View tab.
So a reminder that any button in this ribbon menu system is also a candidate for the quick access toolbar. So to make this a little handier, and always have this button available, let's right click on the Switch Windows button and add it to the quick access toolbar. And there we see it up there, the icon of different sheets. So that means if we're on the Insert tab, Formulas tab, or wherever else we might be doing, we could go to this button and switch files. It could also be faster by way of the keyboard.
Now Control Tab could be the button you want. If you use this repeatedly you might seem to think that it's favoring one over the other. At other times, click Control Shift Tab. So for a moment here I'm just clicking Control Tab, I'm going from Home Product to Employee, to Region to Employee, to Home Product to Employee. It always goes back to Employee. Now, if I use Control Shift Tab, it'll switch to the others. Now hopefully you don't have 10 to 12 files open at the same time.
You could, but Control Tab can be particularly handy when you have only two files. And when you're switching back and forth frequently, Control Tab is really handy. Experiment with that a little bit you'll get the hang of it, Control Tab, or Control Shift Tab. But there's always this button if you put it on the quick access toolbar, that's really handy, as well too. Now suppose we need to see a portion of all three of these. Go to the View tab, choose Arrange All. And in this dialog box make sure if we're talking about different workbooks, do not check the box for Windows of Active Workbook.
Now there are four choices up here, Tiled is often the best. Let's click Okay. If you have an odd number of files, and here we're talking three, one of them will have the larger portion of the screen. If I want the worksheet to the lower right to be dominant I'll click there, and from there I'll choose View, and Arrange All, and Okay. And that workbook, the Home Product Line workbook will be on the left side of the screen, line share.
But even here you've got some options, too. If we point to the boundary here, I'm sliding the mouse rightward here, to that split between the different workbooks, I can click and drag this way, and then on the other side do this. Now this can be a little tedious, it could take some time, but there could be some situations where you need to see portions of each of these. And in each of these independently of the other, you can use that Zoom slider bar and zoom back to possibly see more data. I'll do it in the bottom one too. And if it's important to see more and more data, in these different workbooks here, we can also quickly by double clicking the current tab in each one.
So for example, in the upper right window here I'll double click View, that collapses the menu to show only the tab. We can easily get to that. Another way to do that too is in the upper right corner of each window you'll see the icon here with the arrow Ribbon Display Options. You can do it that way too. So for the moment we're on the setting Show Tabs. Auto-Hide the Ribbon Completely does this. Now that's kind of confusing because it favors just the one workbook. So back up to the top of the screen, click, and let's go back to that button and choose Show Tabs.
And once again we'll have to revisit View to get this back in shape. So Arrange All, Tiled, Okay. There could be cases where, not necessarily the files are similar, but there could be cases where you might want to arrange these horizontally. Go to any of the View tabs here and choose Arrange All, and choose Horizontal. And that looks like it's not very effective here particularly because of the menus, so in this middle workbook I'll double click View to collapse that, that helps a little bit.
And the other ones are all set. Probably not the best choice, most of the time, but Vertical is hardly any better. Going back to one of the View menus here, View, Arrange All, Vertical, click Okay. Perhaps better than Horizontal, but we see what's happened here. Again, Tiled seems to be the best. Look at the menu, a bit tricky to get to now. There's View, and Window, and Arrange All. Back to Tiled. So sometimes it's just a question of your looking at data, you're not doing much more than that, you're comparing the information in different worksheets.
There could be other times when copying and pasting you could do from here. This is not a requirement. If I wanted to somehow copy this information here onto another worksheet, I don't necessarily have to see it, but I could copy it and then jump to the other worksheet and paste it. But here we see the two on the same screen, maybe that's a little clearer, at times. Now if we want to go back to a normal view here, we can simply maximize one of these. I'm going to go to the Employee table in the upper right corner, and maximize.
It's now fullscreen. I'll bring back the menu by double clicking View. It's back. I can get to the other ones by way of Control Tab. There's that one, I could maximize that one too. So we can bounce back and forth in different ways. And bring back the entire ribbon icons too just by double clicking View here. And again, consider those options. Now one of these workbooks has a few sheets in it, Employees Table, Furniture Sales, Project, Budget, and so on. So we might want to see different worksheets of the same workbook.
Now if you go to Arrange All, and in this dialog box choose Windows of Active Workbook, that seems to be what we want to do. Let's click Okay. Now that didn't really do anything. What we need to do on the View tab is choose the button to the left of Arrange All, New Window. Open a second window so you can work in different places. But that seemingly does very little, a little blip on the screen there. And the View tab has disappeared. Let's go back to View, and add another window.
And maybe another, in other words we have added, or we're on the path to adding, three new windows. Let's now go back to View, and Arrange All. We have this box checked, Windows of Active Workbook, click Okay. Kind of crowded there, probably because of the menus more than anything else. And for the moment it looks like we're seeing the same worksheet over and over and over again. So one by one I'm going to go to each window and double click the View tab. Upper left, right, bottom left, and bottom right.
Now in the upper left window I'm going to choose the Employees Table. I'm looking at that data. Upper right I'll choose Furniture Sales. Now just below that I'll go down here I might have to use the arrows to see different sheet names, I'm going to choose Mixed References. And over to the left here, another one. Now you really need to have your wits about you as you're doing this because at different times it's a really crowded screen. And the scenario I'm using here might be a little different than what you're thinking of. Can we do this on workbooks where the sheets are very similar? Well, of course.
But I might be in a situation here where I'm trying to copy data, I need to have these visible. I think you can sense this is going to be a little easier if we have only three, or only two sheets, but it's working here. And as long as we see this information, fine. Notice also something a little strange, maybe a heading in each of these that says 0901 Employee Table. Can't quite see it all but each of these has a slightly different name next to it. This says Table One. Up above it says Table Two.
Far upper left it says Table Four. Down below this it says Table Three. And here too, like in our previous example, if we want to get back to fullscreen we can do that, but it's a little bit different here. We actually want to close these. And this looks as if we're closing a file, but we're not, we're actually closing a window. So let's say we don't need this display anymore. We've either observed the data we want to see or we've done the copying and pasting and so on, let's close these windows one by one, three of them, go to the upper right corner of any one, click the X.
It's closed. Another one, click the X in the upper right corner. It seems like we're closing a file, we're not. Now if we do this on the last of the four windows we'd be on that path, but we're not, we'll just maximize this. So it can get a little confusing at times. There will be times though that you really want to see different worksheets of the same workbook, we just saw how to do that. And at other times you want to see portions of different open workbooks.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros