Printer-friendly versionEmail

Learn how to use Paste Special to eliminate potential headaches when moving or deleting data.

In this example, we override our original concatenate function which allows us to move our data anywhere without changing the look.

Transcript

Welcome to Teaming for Technology’s tip number 91. We’re working in Excel today and I’d like to demonstrate the Paste Special functionality and show how it may eliminate a possible headache for you.

I’ve set up a scenario here for us where in column E we have first names and Column F we have last names. Now in column G we used the concatenate function to combine E and F to create a full name. So when I click in G5, I see the full name Joe Mauer, but when we look in the formula bar we actually see the function called concatenate where it’s combining E5, a space, and F5. And that’s fine if we want to view our data just how it’s displayed. But it causes us some problems if we were to go ahead and delete column E or F.

As an example, I’ll right-click and delete column F (Last Name) and you’ll see that we now get a reference error. That’s because Excel is trying to concatenate something that’s no longer there. It’s trying to combine E5, plus a space, plus a reference that we just deleted. We can fix this problem by using the Paste Special functionality.

Let me undo a few steps to get back to our original data. Here again in G5 we see the name Joe Mauer, but we also see the concatenate function in the formula bar. We can get rid of the concatenate function by using the Paste Special function. Let’s begin by selecting our data: G5 through G12. Copy the data to the clipboard and instead of pasting it normally, we’ll right-click and select Paste Special.

A window pops up for us and we see in the top left an option called “values.” By clicking that option, Excel will only paste the value of the cell instead of the true content which in this case was the concatenate function.
So select values and click OK. And now you’ll see that when I click on G5, we see Joe Mauer in the cell and up in the formula bar. Now when we delete column F (Last Names), nothing happens to our Full Names list.

Once again, let me undo a few steps and show you that again. We’ll highlight the cells we want to effect, copy them, right-click the same selection, and selected Paste Special. Click on Values and then click OK. No longer do we have the concatenate function in the cells.

Thanks for watching T4T’s tip number 91 which was a little bit about the Paste Special functionality. We’ll see you next time.