Other Excel Add-In, Templates and Training
Lesson 2A
CUT/COPY/PASTE
INSERT/DELETE
In this lesson we
will look at:
Copying or Cutting and Pasting
Copying,
Cutting and pasting is something you will no doubt be doing an awful
lot of when working in Excel, especially when you start to use formulas to
perform calculations. The reasons for this will become more apparent when we
cover the basics of Excel formulas in later lessons. For now, we will just look
at Copying, Cutting and pasting typed-in data.
Before we move on
to any examples, it is important that we know the difference between Cutting
and Copying. When we Copy data from one cell to another, we are
doing exactly what the name implies, Copying it. In other words we are
making a duplicate of the cells content and placing it somewhere else. When we
Cut data from a cell or range of cells, we are actually MOVING it
from its current location to a new location. Pasting, by definition
is placing either the Cut or Copied data in its new destination.
Copying
There are many
ways that we can Copy or Cut data in Excel, the way that you do it
is purely optional and should be the way you are most comfortable with. We will
have a look at the most common ways. Type your name into cell A1 we
will use this for all the examples of how to Copy and Paste. Make
sure you are selected in cell A1. Then you can either:
-
Right click on the cell and from the Shortcut
menu select Copy.
-
Go to Edit>Copy
-
Push Ctrl + C on your keyboard
-
Select the Copy icon from the Standard
Toolbar. This is the one immediately to the right of the scissors symbol.
(Note: Remember to hover your mouse over the icons on your toolbars
to see what they do).
You will notice
that cell A1 now has a moving dashed line around it. This is called a
Marquee. This indicates that you have copied the content of that cell (A1 in
this case) to what is known as the Clipboard. The clipboard is where
Excel stores all data that you Copy or Cut from your worksheet
until such time as you either Exit Excel, or clear the Clipboard.
Note:
Depending on what version of Excel you are using, sometimes you can see your
Clipboard as it will pop up onto your screen as a Toolbar, or if you are
using a newer version of Excel, the Clipboard Task Pane may locate itself
on the right hand side of your screen. Both of these are used for multiple
cutting, copying or pasting actions, and we don't need them right now. If
you see either the Clipboard toolbar, or the Clipboard Task Pane, close them
down by clicking on the X at the top right hand side.
Pasting
Now select any
other cell and either:
-
Right click on the cell and from the Shortcut
menu select Paste
-
Push Enter on your keyboard
-
Go to Edit>Paste
-
Push Ctrl + V on your keyboard
-
Select the Paste icon from the Standard
Toolbar. This is the symbol immediately to the right of the Copy symbol.
There is a slight
difference with the second option, ie; Push Enter on your keyboard. This
is that by pressing Enter you will CLEAR the Clipboard after it has
Pasted in the data. You will notice with the other four methods that the
contents of cell A1 stays on the clipboard. This means that we could
select other cells and continue pasting as much as we like using any of the
above methods other than using Enter on our keyboard.
There is another
method that can be used to Copy, this is called Drag and Drop, and
is possibly the fastest method if you only intend to Paste the data into
one destination as opposed to various.
For this method,
again select in cell A1, hold down your Ctrl key, and move your
mouse pointer to the outer perimeter of the cell until the mouse pointer changes
to a white arrow. It should also have a small plus sign to the top right of it.
Whilst still holding down the Ctrl key, hold down your left mouse button
and drag in the desired direction to Copy. Once you have your copied data
in the cell you want to Paste to, simply release the mouse button.
If you also hold
down your Alt key whilst doing this, you can change sheets by dragging
the copied data over the sheet name tab that you wish to Paste it to.
If you Copy
a range of cells eg; A1:B10 and then select a single cell as the destination
range, Excel will use the active cell as the top left of the destination range.
For example if you Copy A1:B10 and select cell C1 as the starting cell to which
to paste, the range C1:D10 will become filled with the copied data. If you
select cells C1:C2 and tried to Paste, Excel would tell you that the
Copy area and the Paste area are not the same size and shape. Select
an area of the same size and shape, or select a single cell only. Most often it
is best to select a single cell only and let Excel determine the Paste
area.
Paste Special
By default, when
you Copy and Paste the content of any cell(s), Excel will Paste all cell
formatting. We can, however, use what is called Paste Special to nominate
the attributes of the copied data we wish to Paste.
To do this,
again Copy the cell(s) in any of the above methods (except Drag and
Drop). Now select your destination cell and go to Edit>Paste Special or
right click and select Paste Special from the Shortcut menu. This will
display the Paste Special dialogue box. In this dialog box under
the heading Paste, there are different options that can be applied, the
default is All, which is exactly the same as using any of the Paste
methods described above. The other options are:
Formulas
This option would
apply only if the cell we copied contained a formula. What this means is instead
of the formatting and other attributes of the cell being Pasted, only the
formula itself will be Pasted. To put this into some sort of context, imagine
the cell containing any formula to be copied where the background colour of the
cell is bright yellow, using Formulas would not Paste the background
colour of the cell, just the formula.
Values
Again, imagine a
cell being copied that contained a formula where the result of that formula was
the number 20 (or any other number). Choosing the Values option, would
mean that we would only be Pasting the result of the formula into the
destination cell and not the formula itself.
Formats
Using this option
means you will not be Pasting the contents of the cell, but only the
formatting. Again, imagine a cell with a bright yellow background
containing the number 100 (or any other data). On selecting this option, the
destination cell would end up having a bright yellow background, but not the
number 100.
Comments
This option
applies to Cell Comments which will be covered in a later lesson.
Validation
This option
applies to Cell Validation which will be covered in the Level 2 course.
All except borders
Means all the
cell contents and formatting excluding borders would be Pasted. We will
be looking at borders later.
Column Widths
Means no content
or formatting will be Pasted, except for the width of the column that the
data was copied from.
Formulas and Number Formats
Using this option
will Paste only formulas and all number formatting options. Number
formatting will be covered later.
Values and Number Formats
This will
Paste only values and all number formatting options.
The next part of
the dialog box has a heading Operation. In order to demonstrate the
options under this heading, type the number 2 into cell A1 and the
number 10 in cell A2. Copy cell A1, then select cell A2
and again right click and select Paste Special.
Under the heading
Operation there are five options to choose from:
None
This is exactly
as the name implies and means None of the options under the heading
operation will be applied. Even if this option and all the other options under
Operation are not checked, which is the default, None would still apply.
Add
Select the option
Add and click OK. You will notice that Excel adds the copied
number (in this case 2) to the value of the destination cell (which in this case
is 10) to end up with a total of 12. In other words it adds a copied number to
the destination cell.
Finally, just to
stress my point that we are not technically adding more rows, columns or cells,
go to cell IV1, by either typing the cell reference in the Name box
to the immediate left of the Formula Bar, or push F5 and type
IV1 in the Reference: box. Type any data into cell IV1 and now
push Ctrl + Home to take yourself back to cell A1. Select any
entire column, right click and choose Insert. You will see that
Excel will display a warning letting you know that it cannot shift non blank
cells off the Worksheet. This is because we have data in cell IV1.
So, as you can see, Excel isn’t really adding an extra column, it is simply
moving the last one off the Worksheet before inserting a new one. Press
OK to cancel out of the warning box.
Subtract
Again, to see
this repeat the coping of cell A1 and again select cell A2 and
right click and select Paste Special. This time, select
Subtract and cell A2 will be have the number 2 subtracted from its
value.
Multiply
This works in
exactly the same way as Add and Subtract, except obviously it
Multiplies the destination cell by the value of the copied data.
Divide
This works in
exactly the same way as Add, Subtract and Multiply, except
obviously it Divides the destination cell by the value of the copied
data.
The other two
options work in the following way:
Type any number
into cell A1, any number in cell A2, leave cell A3 blank and again
type any numbers in cells A4 and A5. Now in cell B1 to
B5, type any numbers so that all cells are filled. Now select cell A1
holding down your left mouse button, drag down until cells A1 to A5
are highlighted. Right click anywhere within A1:A5 and select
Copy, select cells B1 to B5 in the same way, right click and select Paste
Special. Select the bottom option Skip Blanks and click OK.
What you will notice, is that Excel did not Paste the empty cell of A3
over the top of the value in B3. In other words, as the name implies it
skipped the blanks.
While you still
have five values in cells B1:B5, highlight these cells, right click and
select Copy, then select cell D1 choose Paste Special and
select the option Transpose. Click OK. You will notice that Excel
will have Pasted your rows of values into columns. In other words, instead of
cells D1 to D5 having the values Pasted into them, you should have D1
to H1.
Paste Link
The very last
option is the Paste Link button located at the bottom right of the
Paste Special dialogue box. To see this work, type any value in cell
A1, Copy this cell then select any blank cell, right click, choose
Paste Special and click Paste Link. You should notice that your
destination cell will be showing the value, the same as your copied cell. If you
look in your Formula Bar (located under your toolbars) you will see Excel
has placed what is known as an Absolute Cell Reference Formula, ie;
=$A$1. It is the dollar symbols that have made the reference absolute. We
will be discussing Absolute and Relative references in detail in a
later lesson.
The last method
of Copying and Pasting data is to select the cell you wish to
Copy, right click on any cell border and holding down the right mouse button
drag to any destination cell, then release the mouse button. Excel will display
another Shortcut menu, giving you various options. Some of these options are the
same as in the Paste Special dialogue box so we will only look at the
last four options of this Shortcut menu, the Shift Down and Copy and the
Shift Right and Copy.
Again, type any
value in cell A1, then type any value in cell D1. Select cell
A1 and right click on any cell border with the right white arrow showing.
Holding down your right mouse button, drag A1 to cell D1, then
release the mouse button and select Shift Down and Copy. You will notice
that Excel shifts down the value of cell D1 and places it into cell D2
before it Pastes in the data from cell A1. Delete the contents cell D2.
Again, select
cell A1, right click on any cell border, select cell D1 again and
release the right mouse button. This time select Shift Right and Copy.
This has now done the same as the Shift Down and Copy option,
except as the name implies, it has shifted the data in cell D1 to the
right before pasting in the contents of cell A1.
Cutting
Cutting
from a cell, or range of cells is done in a very similar way to the methods
described above for Copying. The differences being, when you Cut
data you remove the original data and when you Paste it, you will ALWAYS
be pasting in all attributes of the cell you Cut from. This means that
the Paste Special option is not available when you Cut a cell or
range of cells.
Again, as with
most options in Excel, there are a multitude of ways to Cut a cell.
The most common are:
-
Right click and
select Cut from the Shortcut menu
-
The Cut
icon on the Standard toolbar (the pair of scissors)
-
Go to
Edit>Cut
-
Press Ctrl +
X on your keyboard
-
Use the Drag
and Drop operation the same way as you would do to Copy, except you
DO NOT hold down your Ctrl key.
Fill Handle
Excel has yet
another way to Copy (not Cut) data in cells and this is via the Fill
Handle. You may have noticed that when you select a cell or range of cells
the bottom right of the outlined cell or range has a small black square. This is
called the Fill Handle. The Fill Handle can be used for filling a
range with increments of any choice we choose, in fact it can do much more than
just this, but we will only look at using it to Copy and to increment.
To see what we mean try this:
-
Type the number 1 in cell A1
-
Select cell A1 and place your mouse
pointer over the bottom right corner until the mouse pointer changes to a
small black cross.
-
Hold your left mouse button down and drag down to
cell A10
-
Your range should now contain the number 1
in all cells.
This is using the
Fill Handle to Copy. Now try this:
-
Type the number 1 in cell B1
-
Select cell B1 and place your mouse
pointer over the bottom right corner until the mouse pointer changes to a
small black cross
-
Now double click the Fill Handle with your
left mouse button
This should fill
the range B2:B10 with the number 1. What Excel does is look in the
range immediately to the left and if there is data in that range it copies down
to the last cell containing data. If the range to the left is blank, it
looks to the right, if there is data in that range it copies down to the last
cell of that range. If both ranges on the left and right are blank, nothing
happens when you double click the Fill Handle.
Let's say we have
the number 5 in cell A1 and we wish to fill down to cell A100
in increments of 5.
To do this,
follow these simple steps.
-
Type the number 5 in cell A1 and
the number 10 in cell A2.
-
Select both cells A1 and A2.
-
Place your mouse pointer over the black square,
bottom right corner of A2 until the mouse pointer changes to a small
black cross.
-
Holding down the left mouse button drag down to
cell A100.
Excel will fill
the range A3:A100 with increments of 5 up to 500. What
Excel does in this instance is look at the value of cell A1 and A2
(the selected cells) sees that there is a difference of 5 (10-5) and
increments by that number.
As you can
imagine this can be very handy even for a simple increment like this.
There is one more
feature of the Fill Handle we will look at and that is what's known as
Custom Lists. By default Excel has two types of Custom Lists,
these are Days of the Week and Months of the Year. Try this simple
exercise.
-
Type the text January in cell A1.
-
Select cell A1 and place your mouse
pointer over the bottom right corner of A1 until the mouse pointer
changes to a small black cross.
-
Holding down your left mouse button, drag down to
cell A12.
This time Excel
will have filled the 12 Months of the year for us. If we had typed a day of the
week Excel would fill the range with the 7 days of the week, looping and
starting again after it gets to the seventh. While these are the only two
defaults, Excel would still recognize Jan or Mon or any other day
or month abbreviation. Not only this but we could type any text followed
by a number such as Quart1 and use the Fill Handle to increment by
1 so you would see Quart2, Quart3 or type Quart1 in A1,
Quart3 in A2 and use the Fill Handle and Excel will increment
by 2
Goto Next Lesson
Back to Excel Add-in, Templates and Training Main Page
Special :
Get the Excel VBA Training for free when you purchase our Technical Indicators now!
|
