Copying and pasting data seems like a simple task, right? But there is a lot of hidden features
when it comes to pasting data in Excel. Here I have some sample data on profit.
These are all input fields.
The last one here is calculated so that's the sum of all the months here.
Now if I just copy these press control+c. Go down here,
press Control+V, it paste all the formatting, it will paste all the formulas
if they have formulas in them.
So that's the default
Copy and paste.
But we have a lot more options if we go to paste special here. Under paste special
we can decide exactly what we want to paste. If we just wanted to paste the formulas not the formatting.
Paste values so the numbers the result and not the formula behind this number.
If we just wanted to paste the formats and not the content of the cell we pick formats.
Paste the comments that are in the cell or the data validation. The column widths
and we also have the option of pasting just the formulas and Number Formats which means
this doesn't paste any other type of formatting that you have in that cell.
So for example, if you have a yellow background color that wouldn't come if you select formulas and number
formats.
You can also paste the values.
So this means not the formulas just the results and the number formats.
So take some time and take a look at the options you have here because that's going to save you a lot
of time
if you already select the right option when you're pasting your data. Let's go and do a test here.
In this case I want to copy these and they have formulas behind but I don't want to paste the formula.
Copy this,
come here and paste it as values.
So I'm going to right mouse click and paste as values is automatically available here but we can also
go to paste special, click on values, and click on Ok.
So notice paste as values only brings the number without the formatting.
I can right mouse click, go back to paste special, if I also wanted the formatting I would select values
and number formats. That brings just the number formats.
If I also wanted to bring in the Cell Formatting I could have selected values and then formats because
this brings in everything. The number formats as well as the cell formats.
And notice that you can continue to go back to paste special and add on more things.
It still keeps it in memory.
Now the downside of pasting as values is that your numbers are not dynamic anymore. If something changes
here it's going to pull through here but it's not going to pull through here.
If you want it to be dynamic so if you want it to reference back to this cell you can use another option.
So after you copy go to the cell you want your results in, go to a paste special and this time select
paste link from down here.
What this does is it links directly to your results.
It doesn't take over the Cell Formatting. The Cell Formatting comes from the existing cells
these numbers are sitting on.
So if we want to bring over the cell formatting we can go to paste special and we can select formats as
well and click on ok. So these keeps the links and adds the formats.
Now you can do the same thing for the labels as well so I'm going to copy these, right mouse click here,
go to paste special, and paste links.
So these are now directly linked to these values.
Now if something changes here, if this number changes to 10.
It's going to pull through here and is going to pull through here. Where as in the previous example.
It's not going to pull through. Another really handy paste special option is to paste as transpose.
So if you wanted to have our data set the other way around we just have to select it, control+C, go down
here, right mouse click, go to paste special, put a check mark beside transpose, and click on Ok.
Now we get our data set the other way round
but this also got transposed properly so our values add up correctly.
Okay.
So what the transpose option does is it turns around
whatever was in the columns it puts it on rows and whatever was under rows it puts it on the columns.
Now here's another hidden feature for paste special.
So I have these January numbers here and let's say I received some additional numbers that I need to
add on to my January numbers.
So this number should get added on to "WenCaL" hundred should get added on to the second number.
I could write a formula somewhere so I could say equals this plus this and pull this down
then copy and paste as values and put this back here. Or I can use paste special.
First thing I need to do is to copy the values I want to add to these.
So highlight, press control+c
Now go to January, right mouse click, go to paste special.
Keep your eye on the original numbers.
Click on Add, click on ok.
.
These numbers got added to these values.
I didn't have to write a formula, I didn't have to take additional steps.
I did it directly in the cells. Taking some time to become familiar with the paste special options can
come in really handy and it can save you a lot of time.