99.4% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Copy And Paste In Excel
Tue 23rd November 2010
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.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Thought Provoking Consulting
Excel VBA Introduction
Trainer was very enthuastic and covered a lot of ground. One way to improve may be to provide the files that were used on the day so we can go back through the code (may already be a thing but not as far as I'm aware).
Power BI Modelling, Visualisation and Publishing
Trainer was extremely enthusiastic and helpful, very enjoyable
I felt the trainer was very articulate and presented the course very well.