The ultimate crash course in Excel – part 1

Now the basics are out of the way, let’s start using some simple functionality: copy, paste and paste special.

Copy

Much of editing in Excel is similar to editing in other Microsoft Office applications. So if you know how to copy and paste text into Word, you know how to do so in Excel. This article will give you a set of simple tips and tricks to copy/paste more effectively into Excel.

‘Copying’ in Microsoft Excel duplicates the contents of all the cells that are selected. These are then held temporarily on the ‘clipboard’, ready to be ‘pasted’ somewhere else. Usually, the content of the clipboard isn’t displayed but held invisibly in the computer’s memory ready for pasting.

Being able to copy is very important when dealing with large amounts of data as you’ll often need the same information in multiple locations. Cells that are selected ready for copying appear grey with a bold, dark border.

As with other Microsoft Office applications, there are many ways of performing ‘copy’.  To keep things simple, we recommend the two most common – one using the keyboard, and another using the mouse:

• Ctrl+C (Hold the Ctrl key down and press C)
• Right click on the selected cells to bring up the menu and select Copy

A quick video to show you how:

Cut

If you want to move the content to a different cell on your spreadsheet, then you need to use Cut instead of Copy. Here’s how you do that:

Paste

Once you have copied or cut data from a cell, you’ll want to paste it somewhere.

‘Pasting’ transfers the contents of a cell or cells (that were previously copied and held in the clipboard) to the selected cell.

Where more than one cell was copied, the cell selected for the paste will be where the top left hand corner of the selection is – the rest of the selection will have the same relative placement as in the original data.

As with copying, to keep things simple, we recommend using just one or two of the many ways of pasting – one using the keyboard, and another using the mouse:

• Ctrl+V
• Right click on the selected cells to bring up the menu and selecting Paste

You can paste copied cells again and again, at different points in your spreadsheet, without re-copying them. Pressing the Escape key (Esc) however will cause the copied contents to be forgotten.

What’s being pasted

Ctrl+V, or simply ‘Paste’ from a menu, reproduces the contents and formatting of the copied cell. If whole rows or columns were selected, their height/width would be copied too.

If the cell has a comment attached, that comment will also be copied.

• The content of the cell is whatever appears in the Formula bar when the cell is selected. This content can be a formula (in which case it is preceded by an = sign) or data.
• The result of the cell is what numbers or text appear in the cell in the worksheet. The result can change when a cell is copy/pasted elsewhere, if the content is a formula. Data cells will always stay fixed when copied – their content and result are identical.
• The formatting of the cell includes:
  • The appearance of the cell (fill colour, border, font type and size, orientation of text within it, etc.) and
  • The ‘type’ of cell – whether the cell contains numbers, text, or a date (more in our Cell Formatting and Number Formatting units)

Partial pasting and paste special

Sometimes it’s useful to paste only some attributes of a copied cell. Excel offers a few options.

Three favourites are:

Paste Values – By default Excel copies over a formula, but sometimes you just want the result. Paste Values does that for you.

The shortcut for Paste Values is Ctrl-Alt-V-V (then hit enter).

Paste Values and Number Formats – is the same as Paste Values but it brings over the format of the original cell. This is particularly useful when copying over dates or a currency but you don’t want to copy the formula from the original cell.

The shortcut for Paste Values and Number Formats is Ctrl-Alt-V-U (then hit enter).

Transpose allows you to flip rows and columns around in seconds. Turn a row of numbers vertical or vice-versa by simply copying and then using Paste Special – Transpose.

The shortcut for Transpose is Ctrl-Alt-V-E (then hit enter).

Pasting values:

Transposing:

If you haven’t done so yet, open up Excel and start practicing your copying, cutting and pasting.

Knew all that already?

In the next post, part 2 of the series, we get straight into the good stuff with Conditional Formatting. See you then!

These tips were provided by filtered.

AAT students and professional members can access a wide range of Excel resources and training.