Paste Special

Paste Special Formats

In this first topic, we're going to take a look at how you can do a regular paste and how you can use paste special to paste the formats of a cell.

I'm going to select this text. Everything inside of this cell and I'm in a press control, see? Then I'm going to head over to the cell where the word something is located, and I'm just going to do a regular pace just to show you from the very beginning a regular copy and paste. You'll notice that everything was pasted, everything from the cell, including the format and the text. I press escape.

Now I'm going to show you how you can copy something from a cell and not necessarily everything. I mean, the press controls see that little animation lets me know that the contents of that cell have been saved to the clipboard. I'm going to go over to the word something and this time I'm going to do something different rather than simply press control V.

I want to use paste special to paste a part of the cell that I just copy. I'll press control alt V. You can think of this as control it control alternative V as if you're doing an alternative of control V. Now what do I want to paste into the cell I've just selected?

I want to paste the formats as I look at the options I see. Format is an option and the underlined letter for formats is the letter t. That's very important because that's a keyboard shortcut. I can simply press the letter T and it will select that option.

Now all I have to do is press enter and if you take a look at the cell that says something, it still says something. But what was pasted was the format, which in this case is the peach background and the border surrounding the cell.

I'll press escape, I'll move over it, move away from the cell and you'll see the border there. In our class, we have students practice doing this with comments. So I'm going to select the cell that has a comment in it. When a press controls C, then I'm going to head over to a cell that says at comment again, I'll need to bring up paste special.

And the option I'm going to choose is comments and notes, I can select it with my mouse or I can simply type the letters C and press enter. Now there's a little red triangle on that cell. If I hover my mouse over that cell, I'll see that I copy the comment from the original comment cell. I'll press escape.

Formulas

Another paste special option is formula's. There are situations where you may just want to paste the formula and nothing else from the cell, so we have a calculation here that gives us 3 percent growth every year. We want to extend that for the following years 2019, 2020, 21, 22 and so on.

I'll just use the autofill handle now that works and actually gives us the result results we want. What you may not like about that is that the formatting has changed. Maybe we want to keep the original colors so I'll press control, z undo and this time I'll press control.

See the copy. I'll select the cells that I want to paste the formula into and bring up paste special again, I'll press control Alte V. The option that I want to choose from the page special dialog boxes formulas so I can simply press the letter F and then press enter. And that is the difference.

In this case, when we pasted, we kept the original formatting and only pasted the formulas. I'll press escape.

Operations

Another paste special option is called operation. This is probably not used as much. But it is an alternative to performing calculations using copy and paste. My goal in this exercise is to divide all these values by 1000. Now, I could do this by going over to a separate column, but I can actually use paste special to do this. All I have to do is copy the original value. Then I need to select the cells that I want to divide by the value I just copied.

I'll use control alt V again to bring up the paste special dialog box, and I have a couple of options. Let's say I want to keep the background color for the original cells so I'll choose V for values. So what that does is it only selects the value from the cell and not the formats. Now, the mathematical operation I want to perform here is division. So I'll choose divide. I could also type the letter I.

All I have to do now is click OK, and I just divided all those amounts by 1000. I didn't have to type any formulas. I didn't have to use an additional column.

So that's an unorthodox option that is available to you if you don't necessarily care about the original values and you just want the result. I'll press escape.

So an exercise that we could do that's related to this is we could choose to add $50 to the gross pay of all the employees in the table below. So all press controls see the copy. Then I'll select all of the gross pay for these four employees and I'll bring a paste special.

I want to keep the formatting that's there so I'll choose V for values. And then the letter for additions is going to be D. And then I'll press enter. Everybody's salary has increased by $50.

Transpose

Our last paste special option on the sheet involves the ability to take a horizontal list of years. In this case and change them to a vertical list. So I'm going to select all the years I'll press control. See? Then I'm going to go over to the gray cell and then I'm going to bring up paste special control, alt V.

The option you'll choose to change the orientation of the cells you just selected is going to be transposed. So all I need to do is type the letter E force with my mouse, click the checkbox for transpose when I click. Okay.

What used to be horizontal is now vertical. Now this works both ways. At this point, I could choose to copy what is vertical. And then move over one cell to the right. Maybe two cells and then bring a paste special. I will choose Transpose again this time I'll type the letter E and then when I press enter what used to be vertical is now horizontal. So that is transpose.

Let's take a look at an exercise you would do in class. I want to be able to select this entire table and transpose it. Starting at the cell in gray that SLB 94. So an easy way to select the entire table is simply press control a then I'll price controls C.

Then I'll navigate down to the gray cell and bring up paste special yet again. I'll choose E for transpose. I'll press, enter. And now I have my transpose table. You'll see that in the table.

What used to be the headers that are that we're located in a row are now item labels located in a column. And then what used to be the item labels located in the column are now headers located in a row. Everything is in the right place. Just a different orientation.

So those are four quick paste special options that you can use as you're working with your data in Excel.

More in Excel

Want to learn more tips and tricks in Excel to improve your workflows? Join us at our Excel courses in NYC or live online. To train the whole team, check out our Excel corporate training