Join Dennis Taylor for an in-depth discussion in this video Copying, moving, and/or inserting data on different sheets, part of Excel 2013: Managing Multiple Worksheets and Workbooks.
- View Offline
If you need to copy information from one worksheet, to another worksheet in the same workbook, or move information from one worksheet to another worksheet in the same workbook, it's probably going to be advantageous most of the time, to add a new window and see the actual sending and receiving areas on your screen. On the view tab, let's click New Window. Remember that action by itself only seems to, course a little blip on the screen. Then, back to the View tab, choose Arrange All. And, if we're only talking about worksheets within the same workbook, we must make sure the box, Windows of Active Workbook is checked.
You can arrange them anyway you want, but tiled is a safe bet. Click Okay. We now see two windows. Now possibly, we've got a different zoom factor. Not necessarily. And if you're trying this right now yourself, possibly you'll see the same zoom factor. But we're probably seeing the same sheet twice. And there could be situations with larger worksheets, where maybe that's appropriate. But let's say what we want to see on the right side of our screen, and it may involve moving the display a little bit along the bottom here. Let's go to a sheet called Sheet one, which is currently empty.
And maybe you're contemplating the idea, of putting together the data on one sheet, and so what you'd like to do for starters, is simply copy this data here, from one worksheet to another. Now, the dual display is not necessary, but it's better in the sense that if you're seeing the sending and receiving areas together, it's likely to be simpler. And we can use standard copy paste techniques here, we could press Ctrl+C. Having highlighted the data. Also we could have right clicked and pressed copy.
Go to the other worksheet, click in the upper left corner of the receiving area. Press Ctrl+V, or right click and paste. Pretty straightforward. You can also drag data. You want to be a little bit careful as you do this though. I'm going press Ctrl+ Z ,simply to undo that last paste. Certainly wasn't wrong but, let's talk about the other technique. We've got our data highlighted here, I'm going to start to drag to the right. Now, if we want to move this, we could keep going. But let's say we want to copy it. Hold down Ctrl as well.
A tiny little plus accompanies the arrow. Now if you do this slowly, it looks as if you're staying on the same worksheet. The columns start to change and so on. But if you do it a little bit faster, you'll end up on the other worksheet like this. And a Ctrl key still held down, you let go of the mouse. And there we are. Now, let me repeat that a little bit faster. Nothing wrong with doing it slowly. But on the other hand if you're copying data, or moving data from one worksheet to another, if you make it relatively fast, it doesn't have to be super fast or anything.
I'll do this again, and place this under the previous data. Let's say I simply want to copy this data, from one worksheet to another. I'm ready to drag it so it's highlighted. I'll point to an edge. And as I start to drag, I'll do it relatively fast. And I will be holding down the Ctrl key this time. And drag it across the screen there, over there. And let go of the mouse button before the Ctrl key. So that's relatively fast. And keep in mind, too, although we don't really want to do a move right now, if we wanted to move this data, as much of it as we want, we can do the same thing by dragging, without holding down the Ctrl key.
So I'll drag this to the right relatively fast and do that. And watch the left side of the screen, essentially lose its data. Now I really don't want to do that, so I'll press Ctrl+Z. But recognize, that sometimes it will be appropriate to copy data that way. If you only do this occasionally, I think perhaps standard techniques are likely to be better. In other words, just do a simple Copy and Paste. Or in the case of a move, a Cut and Paste. But dragging data across different worksheets, will work just fine. Now, there could be times, too, when you want to Copy, and Insert.
Now, I'm going to make up a situation here. Maybe let's imagine that this data here, isn't there, and for whatever reason, maybe wholesale, somehow got replaced or lost. I'm going to delete that row, now, this may seem a little contrived, but yet there could be times when this is appropriate, and you could be doing this with either entire rows or just cells. Suppose we want to copy this data, and insert it between rows three and four on the right? We're about to copy this so as we start to drag this, I'm going to drag it downward so we can see it a little bit better.
I'm holding down Ctrl, to make a copy, but also if I want to do an insert, I'll be holding down the Shift key. And as I drag this rightward, and you see how the icon's changing, and up here, drag it up there, put it between rows three and four, and let go of the mouse first. And there we are. And I certainly, in this case, copied more data than I needed, just get rid of that. But I think you can see how that works too. So if we want to copy, and insert data, from one worksheet to another. We hold down the Ctrl key and the Shift key, as we drag the data across the boarders.
Now there, again, we could do this manually too otherwise. But I think that's likely to be, fairly fast as you, move and or copy, and or insert data, across different worksheets.
- 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