Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One of the basic tools we use at different times to redesign the look of a worksheet is the ability to move or copy data to different locations. We're looking at the worksheet called Move-Copy-Insert. And in Columns A to I, we've got Sales and Profits for the first half here. And possibly for presentation reasons, or for printing reasons, we might want to redesign the look of this. For example, maybe we want to display this on the screen. We'd rather not talk about this information. We don't want to delete it.
Why don't we just move this data somewhere else? Now, there are multiple ways to move information. Certainly a common way is the two- step technique called Cut and Paste. We can get to cut in a number of ways. On the Home tab, you'll see a Scissors here. As you slide over, it recognized the keystroke shortcut, Ctrl+X. We could click the Scissors, and if we want this data down in Row 17, we could then click there, and notice the prompt at the bottom of the screen--"Select destination"--and press ENTER or choose Paste.
We could click the Paste button up above or we could simply press Enter, and we move the data that way. Now, generally there's a faster way of doing this. I'm going to press Ctrl+Z to undo what I had done and Escape here. When we select data, which we must do before cutting and pasting, why not just drag the data? Now, unless we're dragging this hundreds of rows, thousands of rows or columns downward or rightward, why not just drag the data? So using the mouse, we can point to any edge-- --it doesn't have to be the top, it can be any edge--drag it here, we could even put it into separate columns--although that probably wouldn't make a lot of sense here-- but drag it to wherever you want, maybe even overlapping.
It looks like it's overlapping, but this is simply going to move the data downward. So dragging data. The formulas within here still refer to the data up above, we're still seeing the same numbers. It's easy, it's fast. I think much of the time moving data really is just a simple drag. You select the data, hold down the left mouse button, drag across all the data, move it wherever you want to move it. We can do that easily. Once again, I'll press Ctrl+Z a few times to go back to where we were. There we are. Now, at other times you might want to copy data.
The data here has formulas with it as well. We might want to make a copy of this, maybe we want a different set of numbers, we want to experiment with these a little bit. So we want to keep the data here, but let's also copy it down below. Now, many times this too is a two-step process, and the process here begins with Copy or Ctrl+C. So we could copy that data and just as with Move, we could go to a destination area down here and press Enter. So we've copied the data, but let me press Ctrl+Z to undo.
Dragging probably is going to work better. Select the data that we want to copy, drag any edge--it doesn't make any difference which edge--and as you drag, hold down the Ctrl key. You'll see a tiny plus that accompanies the arrow there, and you don't have to hold down the Ctrl key immediately, but just as you get toward the destination, make sure you are holding down Ctrl; be sure to let go of the mouse first. So we've copied the data, and we see it up above as well, and we could copy it again and again if we wish. The formulas that are here, for example this one, refers to these cells, not to the cells up above.
So copying data many times is, after selecting it, simply dragging the data with the Ctrl key. Sometimes you heard the phrase Ctrl-Drag. There might also be times when we need to insert data. Now, if we're looking at this list where we see Sales, Expenses and Profits, why down here is it Sales, Profits and Expenses? That seems to be not right, they should be in sync. And so one way to rearrange this is to insert a new row above Profits and then move this data above it, something like that.
Let's simply make it a one-step operation. After selecting the data here, we're going to drag this upward using the Shift key. In other words, we want this data--the Expenses Change data--to be between Sales and Profits. So we've selected the data, we hold down the Shift key as we drag this upward, and we let go of the mouse first. There it is. So we've simply moved the data upward, moved and inserted at the same time. You can do this with entire rows. You can do it with entire columns.
On the same worksheet, off to the right, in columns M rightward, we've got a list of data. Maybe it just so happens that we want the Benefits column to the left of Hire Date. So we'll click Column R. We're going to drag the left edge. Now, we can drag either edge or even the top, it doesn't make any difference, just put the mouse pointer on the edge here, and as we drag leftward, we've got the Shift key held down. Drag it over to here, let go of the mouse, we're moving the Benefits column to the left of the Hire Date column.
And where necessary, you can move two columns, three columns, you can move these leftward or rightward; it doesn't make any difference. If we want to move the Building and Department columns to the right of Years, we'll drag this rightward here, with the Shift key held down, drag it right there, let go of the mouse; we move those two columns that way. So we can easily move cells or columns or rows while inserting the data. So anytime it comes to moving data or copying data or moving and inserting as a general approach, dragging the data tends to work faster than the commands we see on the clipboard.
But there's no question that we need this capability as we redesign our worksheets.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91297 Viewers
80 Video lessons · 138227 Viewers
59 Video lessons · 57050 Viewers
52 Video lessons · 70711 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.