Have you ever copied a cell in Excel but when you pasted it into another cell, the value was completely different? How do copying and pasting work in Excel? Did you copy the formula or the value? There are so many different “Paste” options in Excel:

In this article, we will go through a few of them. If you would like more information on any particular one of them, leave a comment below and we will add it onto this article.

Copy & Paste Formula

Have you ever copied a cell in Excel but when you pasted it into another cell, the value was completely different? This is because by default, when we copy and paste (CTRL C and CTRL V) in Excel, we are copying what is in the formula bar. And this is not necessarily the value. Of course with relative cell referencing, the referenced cell in the formula may change but the formula will not:

We now copy B2. But when we paste into B3, we will not see “Bitcoin” in B3. Instead we will see “Tellor”. This is because instead of copying “Bitcoin”, we actually copied the formula “=A1” and because of relative cell referencing, Excel is returning the value in the left adjacent cell:

This is the copying & pasting by default in Excel and hopefully explains why when you do copying and pasting in Excel, you end up seeing a different value. So let’s explore what we need to do if we want to actually copy and paste the value.

Copy & Paste Value

There could be numerous reasons why we want to copy the value inside the cell instead of copying the formula. For example, after using VLOOKUP you now have the desired returned value. And you may want to copy the value returned, not the VLOOKUP formula. Or when you use the RAND() function, the function is triggered every time we make any changes in Excel hence we get a random number generated every time. How can we use RAND() to generate a list of random numbers once and then not have it changed from then on?

First we copy (CTRL C or right-click and select Copy) the cell or the array of cells. And then we need to right-click and select Paste Values:

We can now see in the formula bar at the top, we no longer see the formula “=RAND()” but we see the actual numbers:

Another reason to copy and paste values in Excel is that after using a function, copying and pasting values will remove the formula. This means we can then make changes to (or delete) the data in the referenced cells and it will not impact the returned values we got from the formula.

Copy & Paste Format

As the name suggests, in this case we are copying and pasting the format of a cell(s). It doesn’t matter if the cell(s) contains a formula or a string. It is the format that is being copied across. So what will be copied and pasted? It is basically everything here:

It includes the font, color of the cell, cell format (General, Number, Currency, Short Date…), alignment, etc. This is the data we have before we copy and paste format:

We then copy range A1:A6 and then paste format in C1:C6 by right-clicking and selecting the second last Paste Options:

And this is the result:

Notice that the values in C1:C6 do not change. The only thing copied and pasted across is the format. This includes the bold font, the alignment, color of the cell and so on.

Format Painter

There is another way to transfer format across from one (or a range of) cell to another:

1. Select the cell(s) which has the format you would like to copy
   
2. Click on Format Painter icon
   
   
   
3. Click on where you want the cells to have the format applied to:
   
   

Notice that with Format Painter or with Paste Formats, it doesn’t affect the column width. If you would like to copy and paste the column width, you could copy and then right-click, select Paste Special:

Select Column widths and click Ok.

Copy & Paste Transpose

Simply put, Paste Transpose allows us to transfer a set of values from column to row or from row to column. This is particularly useful when we have a large set of data. We use this most often when we need to set up a table.

1. We copy the cells we would like to transpose
   
2. We then right-click and select the third last Paste Options:
   
   
   
3. And this the result:
   
   

A couple of things to keep in mind of when using Paste Transpose:

• We cannot copy and paste transpose a whole column or row
  
• Copying and pasting Transpose will not work with a formula that uses relative cell referencing. It will work with strings/numbers or formulas with absolute cell referencing

This is what happens with relative cell referencing. In B2:B5, we relatively reference the cell to the left. E.g. we have “=A2” in B2, “=A3” in B3 and so on. And when we paste Transpose, we have #REF error:

We’ve covered the basics in copying and pasting in Excel. We’ve explained why sometimes you get a value you don’t expect from copying and pasting. We’ve also explored some of the most popular paste options in Paste Special. If there’s anything else you would like us to go through in this article, please leave a comment below!