Formula to Copy and Paste Values in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

There are a lot of ways to copy and paste values in Excel. But if you think of a way to copy and paste values using formula. The answer is yes, Excel offers this feature. You will learn these methods from this article to copy and paste values using formula in Excel with sharp steps and clear illustrations.


First, let’s get introduced to our dataset that we’ll use to explore the methods.


1. Using Cell Reference to Copy and Paste Values in Excel

In our very first method, we’ll use the cell reference of a value to copy and paste. For that, I have added a new column named ‘Output’. It’s quite easy, let’s see the steps.

Steps:

  • Activate Cell D5.
  • Write the following formula in it-
=C5
  • Then just press the Enter button.

And soon after, you will see that the value of Cell C5 is copied.

Use Cell Reference to Copy and Paste Values in Excel

  • Now, to copy and paste the other values, just drag down the Fill Handle icon over the cells D6:D9.

Use Cell Reference to Copy and Paste Values in Excel

Then you will get all the output like the image below.

Read More: Formula to Copy and Paste Values in Excel Automatically


2. Applying Array Formula to Copy and Paste Values in Excel

Like the previous method, we can copy and paste a range of values by using an array formula. It will save time and there is no need to use the Fill Handle tool.

Steps:

  • In Cell D5 write the following formula-
=C5:C9
  • Finally, just press the Enter button and all the values will be copied and pasted at a time.

Use Array Formula to Copy and Paste Values in Excel

The following image shows the output after using the array formula.

Use Array Formula to Copy and Paste Values in Excel

Read More: Formula to Copy Cell Value and Format in Excel


3. Inserting Excel Array Formula to Copy and Paste Values from Another Sheet

You can copy and paste cell values easily from another sheet in Excel by using the array formula.

Steps:

  • Activate Cell C5.
  • Later, type-
=
  • Then click on the sheet name where the values are located that you want to copy.

Soon after, Excel will take you to that sheet.

Use Array Formula to Copy and Paste Values from Another Sheet in Excel

  • At this moment, select the range of values and it will show a dancing rectangle around the selected range.
  • Finally, just hit the Enter button.

Use Array Formula to Copy and Paste Values from Another Sheet in Excel

Consequently, Excel will take you to your main sheet and you will spot that we have successfully copied and pasted the values.

Use Array Formula to Copy and Paste Values from Another Sheet in Excel

Read More: Excel Formula to Copy Cell Value to Another Cell


4. Inserting Excel VALUE Function to Copy and Paste Values

If you like to work with functions, you will be glad to hear that it is also possible to copy and paste values using a function! So in this case, we’ll use the VALUE function.

Steps:

  • Activate Cell D5 and type the following formula-
=VALUE(C5)
  • Hit the Enter button for the output.

Insert VALUE Function to Copy and Paste Values in Excel

Lastly, just drag down the Fill Handle icon along the cells D6:D9 to copy the formula for the next cells.

Insert VALUE Function to Copy and Paste Values in Excel

Then you will get all the outputs like the image below.

Insert VALUE Function to Copy and Paste Values in Excel


5. Using VLOOKUP Formula to Copy and Paste a Value from a Range

Now we’ll use another function- the VLOOKUP function to copy and paste values in Excel.

Steps:

  • Write the following formula in Cell D5
=VLOOKUP(B5,B5:C9,2)
  • Press the Enter button.

Use VLOOKUP Formula to Copy a Value from a Range and Paste It

To finish, drag down the Fill Handle icon over the cells D6:D9.

Soon after, you will see the output like the image below.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Conclusion

I hope the procedures described above will be good enough to use a formula to copy and paste values in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo