Paste data using special paste options

- [Instructor] When working in our workbooks, we often need to copy information from one area of the workbook to another or even from one workbook to another. After watching this video, you'll know how to, turn on your clipboard so you can keep track of what you've copied and easily paste it into the document. We will then look at several paste special options available to you in Excel 2019. Let's jump right into our paste special workbook and take a look at our first table. It looks like the headings in our first table are formatted very nicely with green and a white text. And looking at our second table, our total prior year sales, these column headings have no formatting applied to them. So, we'll copy the formatting from our first table and apply it to the total prior year sales table. But first, let's turn on our clipboard. I'll click the more arrow in the clipboard group and this opens my clipboard window. You can have up to 20 items on your clipboard and it will keep a nice history of what you have copied, not just in this workbook but in your other workbooks and your other Office programs. So, let's begin by selecting the formatting that we want to apply to our other table. I don't have to select the entire range, just one cell that has that formatting. And I'll use Ctrl + C on my clipboard to copy that. And notice how region has now been added over to the clipboard. I'll come over to my second table and highlight the cells that I want to apply that formatting to and go up to paste in my clipboard group and these are all of the paste options that we have available to us. And as you mouse over each of these, it will tell you the name of that particular paste special option. We can also go down to paste special, which opens up our paste special window, where we can pick exactly what we want to apply. In this case, it will be the format. So, I will select that and when I'm ready, click OK. But while we're right here, let's take a look at some of the other paste special options we have available. The first one is just paste all. Paste the values, paste any formulas, paste the formatting. We can narrow it down just as we did, to apply the formatting to only paste formulas, just paste the values, comments or data validation. We can select to use the source theme so if we copied information from a workbook that has a different theme applied, when we paste it into our current workbook, we can keep that source formatting. We can paste everything in except for the borders around the information. This next one is really big, it will keep the column widths force. How many times have you copied and pasted something into another workbook and the columns resized and you had to spend a couple of minutes going through and fixing the width of every column? So, this is a nice one to know. We can paste formulas and number formats and we can paste values and number formats. So, those are all of your options in paste. With formats selected ,let's click OK. And that quickly, that formatting was applied to the selected cells. Now, let's take a look at how we can paste the value from a formula. In cell H5, we have a sum if formula that's looking at the table data from the table to the left. of being in the east region, is being pulled together, added together and we have a sum of 546,396.58. I'd like to paste just the value down below. Come down to the cell, right-click and go up to my paste special options. Paste special and let's find just the value. if you feel like you have the time to spend, mousing over each of these, by all means, do that. Sometimes it's quicker just to hop into the paste special window and select just the values and click OK. So, whatever works best for you. So, now in cell H10, we have just the value, not the formula. We also can take a value such as we have for 2017 for the east region and paste it into a cell that already has a value in it and add the two together. Let me show you how I can take the amount for 2017, east, right-click and copy, come down to H14, right-click, go to paste special. Down to paste special to open our window and we can use one of the operations to add, subtract, Let's click add and OK. And now the value in cell H5 has been added to the value in H14. Now, one other thing that I'd like to show you is how to take data and transpose it. For instance, in our travel expenses table, the months are the column headings and the cities are the row headings. I want to transpose the data so my cities are the column headings and my months are the row headings. I'll begin by selecting the data, right-clicking and copying. I'll come over to column G. Right-click and here's my transpose option. And there you have it. The data has been transposed, it's been flipped. So, my row headings are now the month So, my row headings are now the month and my cities are now the column headings. and my cities are now the column headings. These are some great tools that you can use These are some great tools that you can use to copy and paste information into your workbooks. to copy and paste information into your workbooks.