Hello again and welcome back to our course on Excel 2016. In this section we’re going
to take a look at Cut, Copy and Paste. Now 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. So what I’m going to go
through first is some pretty straightforward examples of Cut, Copy and Paste and 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 in fact you
can Copy, Cut and Paste whole worksheets, including between workbooks.
So 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 inforce 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 wrongly. 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.
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 boarders.
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’s 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 let’s
on this occasion say that what we want to Paste is everything, All, using the source
Theme. So we’re basically going to Paste the numbers, the number formats and the source
theme from our original selection. But in the lower section 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 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. 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.