Here 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.
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
- text colour
- fill colour
- number format (e.g. percentages)
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 but I don’t want the fill colours I’ve used to highlight those who didn’t do well in the exam. Follow steps 1 to 4 above, then click on “Paste values only”. As you can see it’s pasted just the names.
Let’s say I wanted to copy and paste the names and the numbers. I repeat the sequence above. 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.
Follow steps 1 to 4 above, then click on “Paste format only”.
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”.
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 follow steps 1 to 4 and then click on “Paste formulas only”.
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 transpose”. As you can see the names and exam results go across the page instead of down it.
If you selected the horizontal range and repeated the process it would lay it out vertically again.
I find “Paste values only” the most useful option of the above. The other two options, “Paste data validation” and “Paste conditional formatting” I’ll cover in future posts.
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 more than one set of cells at the same time. Type in the first couple of dates and the first couple of times you want to copy down. Then repeat the same process as before.
Here it has created dates for a series of Mondays and times an hour apart.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store