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.


Download Practice Workbook

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


5 Examples of Copy and Paste Values with Formula in Excel

First, let’s get introduced to our dataset that we’ll use to explore the methods. The dataset represents article numbers of some writers’ published articles on a content creating site named ‘ExcelDemy’.


1. Use 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: How to Copy and Paste in Excel and Keep Cell Size (7 Examples)


2. Use 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: How to Copy and Paste Multiple Cells in Excel (7 Quick Ways)


Similar Readings


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

You can copy and paste 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: How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)


4. Insert VALUE Function to Copy and Paste Values in Excel

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

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


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

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.

Read More: Excel VBA: Copy Range to Another Workbook


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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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