Enhance your Excel skills by mastering Paste Special, a feature that enables efficient formatting, formula copying, and quick mathematical operations. Learn how to adjust cell content and orientation seamlessly using targeted Paste Special options like Formats, Formulas, and Transpose.
Key Insights
- Utilize Paste Special formats (Ctrl + Alt + V, then T) to copy only the cell formatting, including background colors and borders, without altering existing text.
- Apply Paste Special formulas (Ctrl + Alt + V, then F) to replicate calculations efficiently across cells while maintaining original cell formatting.
- Perform quick mathematical operations, such as dividing cell values by 1,000 or adding $50 to salaries, directly using the Paste Special operation feature, eliminating the need for additional columns or manual formula entry.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Paste special. In this section, we will use paste special to apply formats as well as perform calculations on selected cells. 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 going to press control C. 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 paste 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'll press escape. Now, I'm going to show you how you can copy something from a cell and not necessarily everything. I'm going to press control C. 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 that I just copied. I'll press control ALT V. You can think of this as 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 underlying 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 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.
I'm going to press control C, then I'm going to head over to a cell that says add comment. Again, I'll need to bring up a 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 letter 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 copied the comment from the original comment cell. I'll press escape.
Another paste special option is formulas. 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 three 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 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 to undo, and this time I'll press control C to copy. I'll select the cells that I want to paste the formula into and bring up paste special. Again, I'll press control ALT V. The option that I want to choose from the paste dialog box is 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. 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 1,000. 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 okay, and I just divided all those amounts by 1,000.
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, I'll press control C to copy.
Then, I'll select all of the gross pay for these four employees, and I'll bring up paste special. I want to keep the formatting that's there, so I'll choose V for values, and then the letter for addition is going to be D, and then I'll press ENTER. Everybody's salary has increased by $50.
Our last pay 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 C. Then, I'm going to go over to the gray cell, and then I'm going to bring up pay special, control ALT V. The option you'll choose to change the orientation of the cells you just selected is going to be transpose.
So, all I need to do is type the letter E, or 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 up pay 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 cell B94.
So, an easy way to select the entire table is simply press control A, then I'll press control C, then I'll navigate down to the gray cell and bring up pay 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 were located in a row are now item labels located in a column, and then what used to be the item labels located in a column are now headers located in a row. Everything is in the right place, just a different orientation. So, those are four quick pay special options that you can use as you're working with your data in Excel.