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 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 a regular copy and paste from the very beginning. 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 without necessarily copying 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 that I just copied. I'll press Control + ALT + V. You can think of this as Control + ALT + V, as an alternative to 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. If you take a look at the cell that says 'something, ' it still says 'something, ' but what was pasted is 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, students practice doing this with comments. So, I'm going to select the cell that contains a comment.
I'm going to press Control + C, then I'll head over to a cell that says 'Add comment.' Again, I'll need to bring up Paste Special, and the option I will 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 I want to choose from the Paste Special 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 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 select only 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 press the letter I. All I have to do now is click OK, 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 available to you if you don't necessarily care about the original values and just want the result.
I'll press Escape. So, an exercise 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 the gross pay for these four employees and 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. Everyone's salary has increased by $50.
Our last Paste Special option on the sheet involves the ability to take a horizontal list of years, in this case, and change it to a vertical list. So, I'll select all the years. I'll press Control + C. Then, I'll go over to the gray cell and bring up Paste Special by pressing Control + ALT + V. The option you'll choose to change the orientation of the cells you just selected is 'Transpose.'
So, all I need to do is type the letter E or, with my mouse, click the checkbox for 'Transpose.' When I click OK, what used to be horizontal is now vertical. This works both ways.
At this point, I could choose to copy what is vertical, then move over one or two cells to the right and bring up Paste Special. I'll choose 'Transpose' again. I'll type the letter E and press ENTER. What used to be vertical is now horizontal.
So, that's 'Transpose.' Let's look at an exercise you would do in class. I want to select this entire table and transpose it, starting at the gray cell, B94.
An easy way to select the entire table is to press Control + A, then Control + C. Then, I'll navigate down to the gray cell and bring up Paste Special again. I'll choose E for 'Transpose.' I'll press ENTER, and now I have my transposed table.
In the table, what used to be headers located in a row are now item labels in a column, and what used to be item labels in a column are now headers in a row. Everything is in the right place, just with a different orientation. So, those are four quick Paste Special options you can use while working with your data in Excel.