Copy And Paste In Excel

Tue 23rd November 2010

Copy and paste are useful functions that have been with us for years. In Excel, however, problems can arise if, for example, as well as the cell contents the formatting is also copied. The Paste Special function allows us to decide what we want.

Since the development of electronic typing the good old copy and paste function has saved all of us a lot of keystrokes over the years. Everyone should be familiar with the keystrokes Ctrl + C to copy and Ctrl + V to paste (Ctrl + P was already taken up by the print shortcut and so V was chosen, probably because of its position on the keyboard next to the C). But whereas the basics of copy and paste will allow you to post that long quote in a lively online forum or, if you are an unscrupulous writer, pass that web copy off as your own, copy and paste have different functions within the world of Excel.

A standard copy and paste of cells from an Excel worksheet will copy everything to the pasteboard. This includes cell contents and any associated formatting. Problems may arise at the pasting end of the operation, when formatting of the original document differs from that of the target sheet. For example if the font size of the source text is different from that in the target worksheet then the difference in font size will be apparent in the pasted text.

To get around this you can take total control of what is and is not copied by using the Paste Special function. To demonstrate this let us assume that we want to copy a selection of cells without any formatting. This is where Paste Special comes into its own.

Select the cells you want to copy and press Ctrl + C. Select the cell in the target sheet where you want the copied cells to be pasted. Go to Edit/Paste Special. In the dialog box check the Formulas radio button and click OK

As with many aspects of Office applications, experimentation is a great teacher so you may like to try different options from the dialog box to see what effect they have on what is copied and pasted.

There are other neat pasting tricks up Excel's sleeve, including this one which allows the user to paste the same value into a vast range of cells without fiddling about with the mouse.

For example, type a value into the cell A1 and copy it to the pasteboard. Next look to the left of the formula bar where you will see a small box called the Name Box (scroll over to see this confirmed). This will currently show the selected cell, A1. Delete this and type in where you want your pasted cells to appear. Let's go the whole hog on this and type in C1:C55000 for pre 2007 versions of Excel and C1:C99999 for 2007 and later. Press Enter and the selected range will be highlighted ready to take the pasted value. Press Ctrl + V to paste and the entire column will now show the value you selected with only a few key strokes and without the use of the mouse.

Incidentally while we are talking about mouse-free editing, you can easily edit single cells without leaving the keyboard. To do this select the cell you want to edit using the arrow keys. Press F2 and this will allow you to edit the contents of the cell. When you have finished press Enter and the edit will be applied. Alternatively press Esc to cancel.

The simple task of copying and pasting takes on a whole new guise within the world of Excel. It is certainly worth getting to grips with the different ways this operation can be performed as pasting correctly formatted data could be vitally important. And pasting incorrectly formatted data could spell disaster.

Author is a freelance copywriter. For more information on microsoft excel courses london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1291-copy-and-paste-in-excel.html