How to Cut, Copy and Paste Data in Microsoft Excel 2016
During this Microsoft Excel 2016 video tutorial, we will take a look at Cut, Copy and Paste.
Video Transcript
The first thing I need to point out is that Cut, Copy and Paste work to some extent in a similar way to the way that they work in other Windows applications.
But there are quite a few additional features or shall we say specific features of Excel that you need to be aware of.
What I’m going to go through first is some pretty straightforward examples of Cut, Copy and Paste. I’ll point out many of those features as we go.
You can Copy, Cut, Paste individual cells, whole rows, whole columns, any combination of cells, rows, and columns. And you can Copy, Cut and Paste the whole worksheets, including between workbooks.
Let’s just take a straightforward example. Let me just select the cells from M9 down to M14. There’s a number of ways of doing that. Of course, one way of doing that is to click on M9, keep the mouse button down, drag down to M14, and then I can just copy.
Now, on the Home Tab in the Clipboard Group, there’s a Cut and Copy button available to me. There’s no Paste at the moment because I haven’t got anything on the Clipboard.
But, if I just click the Copy button I’ve copied the selection there to the Clipboard. Note, the little marching ants round there. And if I select a similar range of cells here, of course, the Paste button is activated in the Clipboard Group and I can simply click on Paste and the contents of the selected cells are pasted.
Now, of course as usual because that column is too narrow you can’t see that that’s correctly pasted. So if I select column H I could, for example, go to the Format button and say AutoFit Column Width and it will AutoFit that column to the pasted content.
Now, one very important point to note here is that the marching ants are still marching. So that selection is still in force and the content is still on the Clipboard. So if I want to Paste it somewhere else I can.
Let me select another of cells but this time I’m going to do it wrong. So I’m going to have one more cell selected than I actually need. I’m going to click on Paste and basically what it does is to paste into the cells that it needs.
So it still works but it doesn’t use all of the cells. Let me select less cells than I need, do Paste again and in fact, if you just select one cell, the one where you want the Paste to begin, it will successfully paste into that range.
Now, you need to be a little bit careful about this because Excel will Paste over existing content if you ask it to. So if I selected the cell above H9, so I select H8, and click on Paste. It will Paste what’s in the Clipboard.
Overwriting what I pasted before. So you do need to be a little bit careful when you’re pasting. Now, you can of course, use the standard Keyboard Shortcuts for Cut, Copy and Paste.
If I hover over the Cut button up there, as you can see the Keyboard Shortcut is Control-X. The Keyboard Shortcut for Copy is Control-C and in fact, although Paste is currently disabled the Keyboard Shortcut there is Control-V.
If I do a Cut, of course, so if I click Cut for my selection of cells and click here and do a Paste, having done the Cut and Paste of course then the selection is no longer available to me in its original location. It’s been Cut.
Want More? Get Started With a Free Excel 2016 Course! Click Here
And something else you will have noticed is this tiny little icon down in the bottom right-hand corner and it’s called the Quick Analysis Tool.
Now, I’m not going to look at the Quick Analysis Tool at this point but later on in your use of Excel, you’ll find that having in this example pasted some content into the cells.
This tool gives you access to a number of things you may want to do to this pasted data. For instance, you might want to draw a particular chart or perhaps create a total for the contents of those cells.
Now, as I say I’m not going to look at that at the moment but I’m sure you’ll find that’s something that will be useful to you later on.
What I’ve done now is to change the formatting of some of the content of this sheet. So in column K, I’ve changed the Font, the Font Size, I’ve got it Italic and Underlined, and in Column O I’ve added some color to the background and changed the Font Color to White.
Now, when you’re doing Copy, Cut and Paste you are actually potentially cutting, copying and pasting a lot more than just the words or numbers in the cells.
You can also cut, copy and paste colors, formats, font sizes, all sorts of different things. And when you do a Copy you copy all of those things to the Clipboard.
When you do a Paste you select what you actually want to Paste.
Now let’s take an example. Let me go to these money values, these currency values over here, and I’m going to Copy them.
This time I’m not going to use the Ribbon. I’m not going to use Keyboard Shortcuts. I’m going to right click and use the Contextual Menu and one of the options there is Copy.
So marching ants as usual. I’ve copied everything to the Clipboard. I’m going to Paste it into column J but when I Paste it what I’m going to do first, I’m going to right-click and I’m going to look at these Paste Options.
Now, if you look at the top group there is Paste which will literally paste exactly what there is in column O. Note the colors, the values and so on.
But I could also say Paste Formulas. Now at the moment pasting formulas won’t mean very much to you but I’ll explain that a little bit later on in the course.
But if you have formulas here, so for instance if you had a formula whereby the contents of these cells were being calculated by adding up other cells it’s the formulas that you’d paste not the values.
I can say Formulas and the Number Formatting. Note that in that case not only am I putting in the numbers such as 127.5 but I am pasting the fact that it’s currency formatting.
I can say Keep the source formatting. I can say Paste with no borders. I can say Keep the source column widths as well. So not only paste what’s there but keep the column width.
So, as you can see I’m not actually going to go through all of these. As you can see there are many options for how you do this paste and what you keep and what you don’t keep.
Now given that you have really quite an array of options here it can be quite difficult to decide exactly which one to use. And there’s a long list there.
There are over a dozen buttons. But if you go down to the very bottom there is a Paste Special option. And Paste Special brings up the Paste Special Dialogue which many people find an easier way of doing exactly the sort of Paste that they want to do.
Now, in order to demonstrate this, I’m actually going to Paste into a different location. So let me cancel this and let me put the cursor there and then right click and go into the Paste Special Dialogue.
And with this dialogue, you have really a couple of main sections. You have a Paste section and an operations section. And then you also have a couple of options down here, Skip blanks and Transpose.
I’ll come back to Transpose in a moment. Note also that you have an option there to Paste a link. So if you’re using Links that’s the button to use.
When it comes to Paste you’ve got the simple option on the left. Do you want to Paste Formulas, Values, Formats, Comments, or Validation?
Most of the time, you’ll probably be pasting Formulas or Values or Formats but lets on this occasion say that what we want to Paste is everything, All, using the source Theme.
We’re basically going to Paste the numbers, the number formats and the source theme from our original selection. But in the lower section.
Want More? Get Started With a Free Excel 2016 Course! Click Here
I’m going to choose Transpose which will transpose columns to rows, rows to columns. Now I could as part of the Paste operation include an operation of add, subtract, multiply, or divide.
On this occasion let’s just keep it simple and we’ll do a Paste of everything but we’ll do a transpose. Let’s see what happens. And what you get is the contents of those cells formatted as actually, it’ll be accounting amounts.
You can tell it’s Accounting rather than Currency. Because the currency signs are to the left of the cell and they’re pasted across instead of pasted down.
So that’s an example of the use of the Paste Special Dialogue.
The next thing I’m going to do is to demonstrate to you the use of the Format Painter. And this is a quick and easy alternative to using Copy and Paste of Formats.
If I select one or more cells, let me select from O9 down to just say O11 and what I want to do is to apply the formatting of those three cells to a different three cells.
Having made that selection if I click on Format Painter and then click on, let me just click on the first one and what happens is the format is applied to the range of cells.
The set of cells corresponding to the size of the range. In this case three cells, a little, short column of cells if you like, that I had selected when I clicked the Format Painter button.
So, the Accounting Format has been applied to those three cells. Now you may look at that and think, “How did Monday become $42,072?”
Well, I did mention to you before that dates in Excel are held as the number of days. In fact since the 1st of January 1900. So it’s actually 42,072 days from the 1st of January 1900 to Monday, March 9, 2015.
So given that Excel has stored that date as a number if you reformat it as an accounting figure that’s how much money it corresponds to.
Now, when you use the Format Painter, as we just have, once you’ve used it once it’s no longer available. And if I click somewhere else it doesn’t have any impact.
If I select those three again and instead of clicking on Format Painter I double-click on Format Painter than in effect it’s sticky and I can use it once, note the little brush next to the cursor.
I can use it again. I can use it over and over again. And then when I finished using it I can just press the Escape key.
Want More? Get Started With a Free Excel 2016 Course! Click Here
Now, it’s very straightforward to Copy and Paste any kind of shape here. So if I wanted to Copy and Paste a set of cells there which corresponds to six down and two across, if I Copy that I’m going to use the keyboard this time, Control-C, click somewhere else suitable, Control-V.
I’ve copied and pasted all of that. And you can, as I mentioned right at the beginning, Copy and Paste whole rows and whole columns.
You need to be a little bit careful when you’re doing Copy and Paste in particularly rows and columns because as I say generally speaking things overwrite.
So if I selected row 9, right click on it and click Copy, and then say select row 13 and do a Paste. Let’s just do a straightforward Paste.
Then, I’m going to completely overwrite what’s in row 13. However if I were to restart that and Copy row 9, so do a copy, and then select row 14 but instead of doing a Paste I select the option here Insert Copied Cells what happens is it inserts the row into the sheet and moves what was row 14 down to become row 15.
And or course everything below that gets moved down as well. And exactly the same thing would happen with columns. Let’s select column H and copy it and then let me select column K, right click, Insert Copied Cells, and it puts a copy of row H as the new row K and moves everything else to the right. So that’s an area to be careful with.
That’s the end of this section. Please join me in the next one.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
