Join Dennis Taylor for an in-depth discussion in this video Moving and inserting rows and columns of data with a simple drag, part of Cleaning Up Your Excel 2013 Data .
In this worksheet called Move Data, you've decided the Social Security column really belongs between columns B and C. Now there are a couple of ways to achieve this effect, but it's not really necessary to insert a new column ahead of time. Rather than making this a multi-step operation, all we need to really do is select column D and then simply drag an edge of it with the Shift key. You can drag top, left, right edge, it makes no difference. As you point the mouse to an edge of the data, the mouse pointer becomes a four-way arrow.
As you hold down the left mouse button, the four-way arrow disappears, but now we're ready to drag. Hold down the Shift key as you do, and eventually you will see an I-beam indicator. So we can put it between A and B or between B and C, this is where we want it, we always let go of the mouse when we're using keys, and we're dragging here, so let go of the mouse first, we simply move the data to the left. At a later time maybe you decide that columns B and C really belong between columns E and F, between Building and Phone.
This time we'll drag the right edge of the data. Point to the edge, drag it rightward as we hold down the Shift key, we'll see that I-beam indicator again, we can drag it to the right, right to here, let go that left mouse button, and we've moved the data that way. You can also do this by way of the right mouse button using a menu. Different scenario now, we want to move Social Security between columns A and B. Click column E, the Social Security column, and we'll use the right mouse button to drag this data here, hold down the right mouse button as we point to an edge of the data, and then drag the data leftward.
No keys are being held down. We put this on top of Department, let go of the right mouse button, and from this menu, Shift Right and Move. In other words, shift the Department data to the right, as we move the Social Security data into this position, like that. So either way is effective. I think Shift Drag is slightly faster, but either method will work. We can do this with portions of columns, too. It doesn't have to be an entire column. And in the next worksheet to the right, called Washington, if you were looking at this data I think you'd come to realize that the order of Sales, Expenses, Profits that we see here isn't really being replicated in a sensible way in rows eight, nine, and ten.
In other words, these last two rows here should be switched. If we know there's no data to the right, we could do the entire row, or in some cases we'll just move the cells themselves. So this time why don't we just select the cells in question. We'll highlight these cells right here, about to drag these upward with the Shift key. We can drag the top edge, holding down the Shift key, drag upward to simply move the data up this way. Here, too, bypassing that step of inserting an empty row first. So these techniques are very fast and efficient any time you need to move and insert data, whether it's entire columns, entire rows, or just portions of columns or rows.
- Moving or inserting rows and columns of data with a simple drag
- Transposing row-column layouts into column-row layouts
- Replacing data at the character level
- Dealing with special characters and wildcards during search and replace
- Converting dates with text functions
- Converting text data to values/numbers
- Checking and correcting spelling mistakes
- Splitting data into multiple columns via the Text to Columns feature
- Combining data from different columns via concatenation or Flash Fill