Join Dennis Taylor for an in-depth discussion in this video Moving, copying, and inserting data, part of Excel 2016 Essential Training.
- We're looking at the worksheet called Move Copy Insert in our 05 - Layout file. There certainly are times when you say, "I need a copy of this data." For example, in this worksheet, this is a projection for the year 2016, and we see some numbers here. We might want to experiment with these and have another copy of this same layout, with formulas, down below. Using the slider bar in the lower right-hand corner, I'm going to zoom back a little bit, we've got other data off to the right that we'll get to, I'd like to copy this data down to row 17.
Now, a familiar technique for a lot of people is to copy this data and then paste it, and there's certainly nothing wrong with that, and there are any number of different ways to do this. We've got buttons here on the Home tab. Some people are familiar with control + c to copy, control + v to paste. You can certainly do that, but you can also drag data, and often that's going to be the fastest. I simply want to copy this data down in row 17. Now, if I wanted to move it, and based on the layout we're seeing here, that's probably not likely, but I could, after selecting the data, simply drag any edge, drag it down here.
I've moved the data. I didn't really want to do that though, so I'll press control + z. But that's very easy to do, and often, it's going to be the fastest way, unless you're moving the data, for example, you know, hundreds of rows or columns downward or rightward. If we'd like to make a copy of this, we do almost the same thing, but we hold down the control key. You don't have to hold it down immediately, but as you're holding it down eventually, you'll see a tiny, little + that accompanies the arrow as you drag this. Drag it down here, be sure to let go of the mouse first. We've made a copy of it.
And the formulas up here, as I zoom back a little bit again, the formulas up here, for example, that deal with rows 4 and 5 are automatically copied down here, so that they're dealing with data in rows 19 and 20, so we don't worry about our formulas as we do this. We've made a copy of it, and maybe we're experimenting with some different numbers here in our projections. That could be one of the reasons for copying the data. All of our formulas, as I've suggested, also get copied as well. Another way to do this, not necessarily better, I'm going to take this data here, highlight it, and press "delete." Highlighting the data again, you can also drag this data with the right mouse button, and once again, you could drag any edge.
I'll drag the bottom edge, with the right mouse button. The right mouse button always means shortcut menu. Drag it down to here, I'll let go of the right mouse button, and there's a menu, and I'll simply select Copy Here. And that's done, it's easily handled. When you're doing this, too, there's no rule or law that says the copy has to be in the same set of columns, although often it would make sense, as we saw here, and when we're moving data, remember too, I can move this by selecting it, drag any edge, move it over there, move it over here.
All the formulas get readjusted as we do this. So, many, many times, dragging the data is likely to be the fastest and easiest way to either copy or move data. Now, there are times, too, when you want to do a move and insert. Some of you sharp-eyed observers there might have noticed already, there's something a little bit strange in this worksheet. In row 4, we've got Sales and then Expenses. Formulas in row 6 to calculate Profits. Down in row 9, we're tracking the percent of Sales Change, but then, oddly enough, we've got Profits and Expenses, whereas up above, it's Expenses above Profits.
Wouldn't it make sense to take this data here, and have it appear between Sales and Profits? Between rows what are now 9 and 10. Now, we can do this. In Excel, you can insert, in other words, move and insert at the same time. Now, if you don't know the shortcut, what you can do here is right click row 10, add a new row, move the data up, then get rid of the empty row and so on, might take a little bit of time. Now, what we're about to do, we could do for an entire row, however, there's data off to the right.
We don't want to disturb that, so we'll simply highlight these cells right here, and if we'd like to move them upward, all we've got to do is to drag the top edge here, with the shift key held down, drag it up to there, let go of the mouse first, we've moved the data. All of our formulas get readjusted properly, where necessary. Everything is working just fine. And we can do this with column data as well, either entire columns or just cells. We decide, for whatever reason, it's going to make sense to put the Department to the left of the Building, so we click the column for Department, the entire column this time.
We will be dragging the left edge with the shift key held down, drag it leftward to here, let go of the mouse, and we've done that. At different times we do this with multiple columns. For whatever reason, we'd like to take these two columns here, maybe shift them to the right between Benefits and Compensation. I'm going to drag any edge, maybe the top edge, with the shift key held down, drag it over to the right here, put it between Benefits and Compensation. Over to here, let go of the right mouse button first, but you can see how easy it is to do this.
So, rather than going through the longer two-step, three-step process of inserting a new column, moving the data, then getting rid of the empty column, why not simply drag the data with the shift key to move and insert the data? So it's easy to manipulate your data. And the more you get comfortable with these, the more you have the sense that no matter how the worksheet looks, we can easily redesign the look of our worksheet layout by moving data, copying data, or moving and inserting data.
- 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