Google Sheets – Moving, copying & pasting data

In this post, let’s look at how we can move, copy and paste our data on the sheet, starting with the basics and then introducing some lesser-known methods that will save you time and effort.

• Moving cells (drag & drop, cut & paste)
• Copying and pasting cells
• Paste special (values, format, all except borders, formulas, transpose)
• Quick sequential numbers

---

Here I’ve got some exam results and I’ve decided to layout the page a little differently.

Moving cells

There are two ways to do this.

First, click on the cell or on the range of cells. Then move the cursor near the top of the highlighted area and it will change to a little hand. This is the move symbol.

Click and drag to the area on the sheet you want and release the mouse button over the cell you want. The top left of your selection will be in this cell. For example, I’ve moved the bottom 3 rows to D2.

The other way is to select your data, then right-click and from the menu select “Cut”. Then right-click on the cell you want and select “Paste”.

You could also do this, with the keyboard shortcuts for cut and paste (Ctrl+X then Ctrl+V). Personally, I usually use the drag and drop method, but sometimes, for example, if you have a large sheet, it’s easier to cut the data, move to the other part of the sheet and paste it, rather than dragging it and having to scroll across or down to find the area you want.

---

Copying and pasting cells

Instead of moving your data, you can make a copy of it. This is particularly useful if you’ve already formatted the cells the way you want them, and copying those cells will also copy the cell formatting, saving you time later on.

Similar to above, select your data, press Ctrl+C (copy) and then Ctrl+V (paste) in the cell you want to copy to. You could also do this via the menu. Right-click on the original data, select “Copy” from the menu. Then right-click on the destination cell and select “Paste”.

---

Paste special

(values, format, all except borders, formulas, transpose)

When you cut or copy or paste as above, you are copying everything in those cells. This includes:

• the values (data)
• the formatting
  • borders
  • text colour
  • fill colour
  • number format (e.g. percentages)
  • alignment

Sometimes you want to copy only certain parts of the cells, for example, you want just the numbers (values) but not the formatting. This is where paste special comes in. The process is the same each time, except that in the last step you choose the paste option you want.

1. Select the cells
2. press Ctrl+C (Cmd+C on Mac) to copy
3. Right-click on the destination cell
4. Go to “Paste special” in the menu
5. Click on the paste option you want

Paste values only

I want to copy the list of students and marks but I don’t want the fill colours I’ve used to highlight those who didn’t do well in the exam. Copy the list.

Follow steps 1 to 4 above, then click on “Paste values only”.

As you can see it’s pasted just the names and the numbers, but you can also see that I’ve now also lost the percentage formatting that I had in the original cells. This of course can be quickly remedied by selecting the numbers and clicking on the percentage icon in the toolbar.

Paste format only

You may want to do the opposite, you have some useful formatting in some cells and you want to copy it to another area but without the values in the cells. This is particularly useful if you have formatted the cells in different ways, with colours, borders, alignment, etc and don’t want to have to set the formatting up again in the other area.

So, as an example, let’s say I want to copy the table format but not the contents, as I want to fill it with different information.

Copy the information.

Then go to Paste special > Paste format only.

As you can see it’s paste just the formatting (borders, cell fill colours). It has actually copied the text formatting too, like the alignment and the percentages, but as we have no text you can’t see that.

Usually, I find it easier to copy the table and paste normally, then select the data I don’t want and press delete key, but it depends on what you want to achieve.

Paste all except borders

I don’t use this one very often, but one use is when you want to copy the cells but you’re going to paste them into an area where the borders are set up in a different way. Here I’ll copy the exam results into a table with the borders slightly differently formatted.

Follow steps 1 to 4, then click on “Paste all except borders”.

As you can see it’s added the data and most of the formatting, except the original borders.

Paste formula only

When you have formulas in the cells you want to copy, you may want to copy those across but without the formatting. For example, I want to copy the average, min, max formulas in the exam results table to another table with exam results, but I don’t want the blue background.

To do this copy the cells you want and then click on Paste special > Paste formulas only.

This adds the formulas to the new cells, without any formatting.

Paste transpose

The final one we’re going to look at is “Paste transpose”. This takes data which is stored vertically and changes it so it is horizontal or vice-versa.

Let’s say I now want the names of the students to go across the page rather than down it, and their exam results to be in the row below. I could copy and paste each of the cells across, which not only would take time, but would be a soul-destroying task.

Follow steps 1 to 4 above, making sure the area you choose to paste in, will fit the newly-orientated cells.

Then click on “Paste transposed”. As you can see the names and exam results go across the page instead of down it.

Note, it also copies the formatting from the original cells including the cell justification.

If you selected the horizontal range and repeated the process it would lay it out vertically again.

---

Quick sequential numbers

We often need to enter a sequence of numbers, dates, or times to a sheet. Many a time have I seen someone typing in 1, 2, 3, etc manually, when there is a quicker way to do it.

Continuing with the exam results example, let’s say I want to number each student from 1 to 10. In the column next to their name I could type in the numbers manually but a quicker way is this:

1) Type “1” in the cell next to “Fred”, and then “2” in the cell underneath.

2) Select both 1 and 2, and then click and drag down to fill the cells below.

As can see Sheets assumes that you want to continue the sequence selected in step 1.

The numbers can be any sequence, for example, let’s create a sequence of numbers going up by 5 every time. Type in 5 and 10 in the cell below. Then click and drag, to fill the cells.

You can also do this for dates and times, and also more than one set of cells at the same time. Type in the first couple of dates you want to copy down. Then repeat the same process as before.

Here, it has created dates for a series of Mondays.

The same applies to times.

Here we’ve seen how you can quickly paste data in different parts of your sheet and to quickly create new data.

---