Using Cut, Copy, Paste, and Paste Special

Using Cut, Copy, & Paste in Excel Worksheets

Using the clipboard in Excel is very much like using it in any other application – you select something that exists at point A, copy it to share it or cut it to move it, and then go to point B, where you want that “something” to now appear, and paste.

Simple, right? But given then nature of your worksheet content – or all the various types of data your worksheet can contain, from text to formulas and functions to dates, times, and numbers in any number of complex formats – Excel gives you several options for pasting whatever you’ve copied.

Let’s go with the simplest process first. First, I’ll copy the source content – in this case, the heading in cell B2. Notice Excel displays a dashed border around the cell I’ve copied.

Now I’m ready to paste it – I’ll switch to the target location, and click the Paste button on the Home tab or press Ctrl + V.

If I do that, here on another worksheet, notice that the formatting comes with it. There’s a blue background in the cell, and the type is 18 points and bold. That’s great if I wanted the words and the formatting, right?

But what if I wanted to paste the words but without all that formatting, so that I can make it match the rest of the target worksheet – not bringing the formatting from the source worksheet with it?

Then I need to use the options that appear if you click the bottom half of the Paste button on the Home tab or the options displayed in the pop-up menu when I right-click the target area and choose Paste.

As you can see, I can choose from up to 14 different paste variations from the Paste button… or 6 variations from the pop-up menu if I right-click in pursuit of the Paste command.

Looking at the Paste button’s options, the first option is to just paste – content, formatting, everything comes with it. That’s the equivalent of the plain old Paste that I did to share the heading from one sheet to another.

From the large pool of Paste options, the main one to remember here is Paste Values. It’s available from both the Paste button’s menu and from the right-click pop-up menu. Note what happens when I copy that heading from cell B2 in the first sheet and paste it into the second sheet, but this time choose the Paste Values button. I get the text, but no formatting comes with it.

I can also choose, as you see as I mouse over the options, to paste just the formula (if the source content were a formula or function), or the formula with its current formatting.

I can also keep the source formatting of text or any other content, I can paste it without borders around it, I can paste and retain the source column width, or I can transpose the content when it’s pasted, meaning if it was in a column, now it’s in a row, or vice versa.

To demonstrate that last one, note that I pasted a series of vertical cells, but chose to Transpose them – and now they’re running horizontally.

Obviously, there are a lot of Paste options, and you may never use more than 2 or 3 of them – but now you know where the options are, and by reviewing the screen tips, you can choose the one you need.

There is one more option that I want to demonstrate, though. The last option is Paste Special, and this option opens up a dialog box that allows you to choose from all the options I just went over, and then some – plus the ability to paste the content with a link from the source to the target.

What do I mean by that?

If we take this worksheet that keeps track of hours worked each day, entered by workers in a particular department, this is a great example of a reason to link source content to the target for an ongoing relationship between the two.

If you just paste the source worksheet’s cells into another sheet that I use for calculating what each person gets paid – a calculation which would be confidential and doesn’t belong in the sheet that everyone uses to enter their hours – I’d have to keep going back and getting the new hours added by the staff. That would be a lot of copying and pasting.

Well, you don’t have to do that if you use Paste Special and click the Paste Link button to connect the source worksheet’s cells to your confidential worksheet.

As you can see here, if I paste this block of cells containing the timesheet entries set up for the department to use communally for entering their hours, and I then use Paste Special with the Paste Link button to paste the block onto my worksheet that calculates people’s pay, when someone adds hours to the source, the target sheet, the linked worksheet, is updated automatically.

Pretty cool!

So – Excel gives you lots of options for pasting your content from point A to point B, bringing the contents and/or the formatting along with it, and it lets you create a connection between your source and target content. These features all exist to support accuracy and consistency within your data – and that’s very helpful!