Join Dennis Taylor for an in-depth discussion in this video Moving, copying, and grouping sheets, part of Excel 2016 Essential Training.
- In this file called 09 - 03 - RegionalSales, we're looking at the East sheet. Here's the Midwest sheet, South sheet, Pacific sheet. To be in sync with other data that we keep track of we need to move the South sheet and put in in front of the Midwest or phrase another way, we could move the Midwest sheet, put it after South. Moving a sheet simply means dragging the sheet tab and putting it before or after other sheets, leftward or rightward, wherever we want to put it. I'm dragging the South sheet leftward, putting it in front of Midwest and there it is.
Very easy to move a sheet. No commands needed. I might want to make a copy of one of those sheets. Maybe what I'd like to do is to create a sheet that has the same layout as all these, but I'm going to create some summary formulas there. How might I create a copy of one of these sheets? Almost the same way. Now, if you didn't know what I'm about to show you, you'd probably and certainly sensibly right click the sheet tab and choose Move or Copy. I doubt very many people use this for moving but for copy, not bad idea.
It's got an added advantage too. When you copy a sheet and you do have to check the box below here, not only might you copy it to a different location in this sheet, you could copy it to a new book, or if you have other workbooks open, you could copy it there. And you'll even have the option of copying it between whichever sheets you want in that other workbook because they will be listed. Now, I simply want to create a copy of this sheet in a new workbook. Click OK. Remember, we're currently viewing 09 - 03 -RegionalSales, we're about to see a new workbook.
Top of the screen says Book2, it's got a single sheet in it called Pacific. So we had right clicked on a sheet tab in the other file, chose copy and then new workbook and here we are. I'll press ctrl + tab to go back to the other workbook and here we are. If you'd like to make a copy of a sheet in the current workbook, we can simply drag a sheet tab left or right with the ctrl key held down. And there's a tiny plus there, I'll just drag it to the right and let go of the mouse.
Remember, the rationale for doing this might be, I'm about to create a summary sheet here, these two sheets are identical in all respects except for the name. So next order of business might be simply rename this. I'll double click and type Summary, write some formulas here or just take out this data and all the other formulas would work eventually correctly. I'd rename that as well. I'm not doing that here but it's easy to make a copy of a sheet in the same workbook. Simply drag the sheet tab with the ctrl key held down, you should let go of the mouse first.
We can copy a sheet. It also makes sense at different times to change different sheets if they have the same layout. Now they don't have to have exactly the same layout. In this example that we're about to show you, they do. But let's imagine on the East sheet, South sheet, Midwest and Pacific sheet we want to change the layout here by putting in a new row between rows 6 and 7. So how do we select multiple sheets? If there's a whole group of sheets we want to select, we'll click either the first or the last, makes no difference, then hold down the shift key and go to the other end.
So I clicked Pacific first and now with the shift key held down I'll click East. Notice the top of the screen will see the word Group. The word Group means at least two sheets are grouped and the display of the sheets looks different. You can see how these four sheets look different than the other ones that are not part of the group. So while these are grouped, any action we take here is going to be happening on all sheets. So if I right click row 7 and Insert, I might be looking at the Pacific sheet and seeing that but if I click over on Midwest, the same thing has happened.
On South, also East, it's happened on all of these. Now it's up to you to remember that they are grouped and you probably would, so we can ungroup at some point, but I remember not that long ago forgetting for the moment that these were grouped and I did something like change this number. I was thinking I was viewing the East sheet, so I changed that to 2000, and I pressed enter and then I thought, "Oops, wait a minute here, are we still grouped?" Yes, we are, you see Group at the top of the screen, so if I go to South, you see what's happening.
2,000, Midwest, it's there. It's also on Pacific, I better undo this. Ctrl z, there we are back to normal so to speak, we're back to the correct indicator of, I really, truly wanted to change East only. I can ungroup two ways. You can click one of the sheets that's not part of the group or right click the current tab and Ungroup Sheets. But it's a handy feature. If there were some rationale that said you needed to make a change to two of these sheets and they are not adjacent, you can use the ctrl key.
So if I want to make a change to the South sheet and the Pacific sheet, I've already clicked South, with the ctrl key I'll click Pacific. We see the word Group at the top of the screen, but the only two sheets that are grouped are Pacific and South. So if I make a change now, I'll do the same kind of thing again, I'll right click on row 7 and Insert, that has happened on the Pacific sheet, it's happened on the South sheet. It didn't happen on Midwest or East. Now, of course, what I just did wasn't really that useful, so I'll press ctrl z to undo it.
It also along the way ungrouped. So it's easy to use, it's easy to make changes this way and although usually the worksheets are exactly alike, they don't have to be. There could be times when you say I've got a bunch of different worksheets even with different layouts, upper left corner maybe I want to put the date or something like that, so group the sheets. You can do it that way as well. So you've got control over sheets and when you've got that layout situation where you've got multiple sheet tabs with similar layouts or almost identical layouts, it's easy to make changes to all of them at the same time.
- 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