Working with multiple worksheets simultaneously

- [Instructor] It's not uncommon to have a workbook with different worksheets in it. And it's also not uncommon to, from time to time, want to copy data from one worksheet to another. Or sometimes to write a formula in one worksheet that gets data out of another worksheet. I've got a workbook opened on my screen. It's got four separate sheets in it. They happen to be identical in terms of layout. Now that's not a prerequisite for being able to see the different sheets at the same time. But if they do have identical layouts, sometimes we might want to make changes to all of them at the same time.

It's not terribly important here that I do this but I've decided that I want an empty row between what is currently Row seven and Row eight. And rather than doing that four times I want to do it all at once. So, New York is currently the active sheet. I'm going to hold down the Shift key and click the California sheet. And all four sheets are selected. If I only wanted to do this with two sheets for example, Texas and New York now that would be unlikely. But I would have clicked the New York sheet and then with the Control key I would have clicked the Texas sheet.

But any time you select more than one sheet you'll see at the top of the screen following the name of the workbook the word group in brackets. And that means that at least two sheets are selected. And that also means that any change you make is going to happen on all those sheets. So, if I right click right now on Row eight and Insert that's happened on all four sheets. I'll click the Florida sheet. See it's happened, here's the Texas sheet. Here's the California sheet.

They're still grouped, we see that word at the top of the screen. And also, you become familiar with this look of the sheet tabs where we don't see any solid colors. Looking like they're sort of in a cloud there. I could make other changes too. And you got to remember that the word group is up there and that they are grouped. If I lose my train of thought here and I adjust this number for California well, if they're grouped, I've made that change on the other three sheets, as well. So at some point you'll want to ungroup. And you can do that in one of two ways. You can click on a sheet that's not part of the group.

For example, I could click on the total sheet. Or I could right click the current sheet and ungroup the sheets. Now, I do have another workbook open, it's empty. Whether it is empty or not is not the issue. But I might want to see a part of that workbook as well as a part of this workbook. So, the View tab I the ribbon is where we typically get to these features. And the command we go to is Arrange All. And the description reads, stack your open window so you can see all of them at once. Notice it doesn't use the word worksheet, or workbook there.

But let's choose this Arrange All. There is a box that says windows of active workbook. But if we're trying to see different workbooks at the same time, we do not want that checked. Generally, the tiled option tends to be the best one. We'll leave it there, for now. Click Okay. Remember, I've got two workbooks open. I'm trying to arrange the windows so that both can be seen at the same time. So, on the right side of the screen there's that other workbook called Book one. The left side of the screen the workbook that I've been using.

California is the active sheet over there. Now Texas is. Now Florida is, and so on. I want to copy some data from the Florida sheet maybe, whatever it happens to be. I'll highlight this data just that portion, for example. Maybe I'll press Control C. Click over in the other workbook. Maybe in Cell A two, I'll click right there and Paste, Control V. But I could have dragged the data, it's not necessarily any better. The data's highlighted here. I'll drag this with the Control key held down to Copy.

And as I drag rightward here I'll speed up the process a little bit drag it to the right there. There we go. And let go of the mouse at some point. In the process, a little bit of scrolling took place on the left side of the screen. That wasn't necessarily bad. But you can certainly see the advantage here of being able to see a portion of two different workbooks at the same time. So let's say I'm finished with this task here. I don't need to see Book one. I don't want to close it just yet. So I click on the other side of the screen and now I'll maximize that workbook.

There we are. But now I'm thinking, I want to see a portion of each of these worksheets here at the same time. So I'll choose Arrange All. And now click the box windows of active workbook and you would think that maybe that means we're about to see two windows from the current workbook. But what happens? Nothing. What we need to do here is choose New Window. And if we're going to be seeing three new windows in other words, for three other states in the condition of the current one we need to click this three times.

And look what happens when you click New Window. The View tab gets unselected and we're back on the Home tab. So we've got to go back to the View tab. Choose New Window. Back to the View tab. Choose New Window. I've chosen three new windows. Now, View tab, Arrange All. Windows of active workbook, Tiled. Click Okay. And I think you can see a big problem here. First of all, look at the tabs. We're seeing California in all four of these quadrants here zooming a little bit differently. But what we really want to see in each of these quadrants here is a different state.

So in the upper right corner we'll just click anywhere in there then click Texas. I'll click in the lower left quadrant down here and click Florida. And then in the lower right click over there choose New York. And then probably one by one start zooming back. But I think you'll see here we still have a problem. If I zoom way back to see the numbers that's getting awfully tiny. And what if we had more significant rows that we wanted to see here? We can collapse the ribbon menu system pretty easily. I'm in the lower right hand quadrant right now.

I'll press Control F one. That'll collapse and not show the icons. Click to the left of that in the other worksheet over here. Control F one. Click up above, Control F one. And then to the right Control F one. So that's going to make it a lot simpler. And now we can see the data that we might want to see at the same time. So, at different times depending upon the nature of the data and maybe these sheets are a little bit different from one another. Or, very different, possibly. One by one, as needed, we could click within any of these.

I'm going to click in the upper left window. And pick its right edge here, the mouse on the right edge you'll see a two way arrow. Maybe I'll make that one smaller and the one to the right larger. For whatever reason. Or make it taller, so you could resize these manually if you wish. It's also worth at least exploring once or twice how the view here might look if we Arrange All and choose Horizontal. Or Vertical. If we choose Horizontal I think you'll agree not a great choice.

In fact, what happened to our menu here? So we'll have to click in one of these and make that window be a little bit taller to bring back the menu. Let's go back to View. Arrange All. Let's try Vertical. Click Okay. And now you see what's happening, that's not too bad. You can imagine, if it were only three windows that wouldn't be so bad. But look what happens to our menu here. I'll go to the View tab. One other choice here and you probably won't choose this View, Window, Arrange All, Cascade.

Well, we only see one of them, really. The others are in the background. So I think most of the time Tiled is going to be your best choice. View, Arrange All, Tiled, Okay. If you have only three windows, or if you have five windows, or seven windows, in other words, if you have an odd number of windows here one of them is going to be bigger than the others. So maybe I only want to be viewing three of the states here. I'll just take one of these windows and close it with the X in the upper right hand corner. Come back to the View tab in the ribbon menu system choose Arrange All.

Tiled. And because, right now, I'm looking at the window that's showing Texas and I will now have an odd number of windows, it'll get a larger portion of the screen. And there it is on the left side. But again, you could manually resize these, if necessary. At some point you want to go back to a standard view click in two of the windows, one by one, and close it. Click in the other one up there, close that. Go back to one of the windows here just maximize it. And if we want to bring back that menu it's Control F one.

Remember, Control F one is a toggle. It simply hides, or brings back the ribbon icons. So, different techniques here for displaying different worksheets of the same window. And earlier we saw how to display different portions of different workbooks at the same time.