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.

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.

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.

EXCEL COPYING WITH
THE FILL HANDLE

COPYING
WITH THE 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 CustomLists.
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

EXCEL PASTE
SPECIAL

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 PasteSpecial. 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 PasteSpecial
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.

EXCEL THE INSERT
COMMAND

INSERTING
ROWS, COLUMNS AND CELLS

When working in Excel, there are
times that you may need to insert an entire new row, an entire new column or
only a single cell. Although the term Inserting gives the
impression that you are actually adding another row, column or cell, you are in
fact NOT. You may remember that in the first lesson we mentioned the fact that
Excel has 256 columns, 65,536 rows and 1,677,216 cells per worksheet. These
numbers are fixed, so technically we cannot add more. What we can do, however,
is move a particular row down, column across, cell in. This can seem confusing,
so it is probably best that we do a practical example so you can see how it
works.

In cell A1 type any
data. In cell A2 also type any data.

Select cell A2 right
click, and select Insert.

You will notice that the Insert
dialogue box will be displayed giving you four options.

Shift Cells Right

Shift Cells Down

Entire Row

Entire Column

For this exercise, lets select Shift
Cells Right, then click OK. You will notice that the contents of cell
A2 has now been moved to cell B2.

Lets now select cell B2,
right click, select Insert and select Shift Cells Down. The
contents of cell B2 should now appear in cell B3.

Now select any cell in row 1, right
click, select Insert then Entire row, click OK. You should
have an entire new empty row for row 1.

Lets now insert an entire column.
Select any cell in column B, right click and select Insert, then Entire
Column and click OK. You should now have an entire new empty column
for column B. When we insert rows, columns or cells in this manner (that
is with a single cell selected) Excel will always display the Insert
dialogue box, allowing us to make our choice.

The same Insert dialogue box
is available by selecting Insert on the Worksheet menu bar and
then your required option (cells, rows, columns)

We can, however, bypass this
dialogue box and insert either an entire row or column by default. To insert an
entire row by default, select any row number (the row number on the left in the
shaded area) so that the entire row is highlighted. Then right click and select Insert.
You will notice that Excel makes an educated guess that you are after an entire
row as it is an entire row that you have highlighted. The exact same thing would
happen if we had an entire column highlighted, ie; an entire column would be
inserted.

If we want to insert more than one
cell, row or column at a time, we simply select the appropriate number of rows,
cells or columns before using Insert either via the menu option or the
right click Shortcut menu. For example, if you select four entire rows, then
right clicked and selected Insert, you would be inserting four new empty
rows.