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

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-

• Then just press the Enter button.

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

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

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

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-

• Finally, just press the Enter button and all the values will be copied and pasted at a time.

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

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.

• 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.

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

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-

• Hit the Enter button for the output.

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

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

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–

• Press the Enter button.

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.

<< Go Back to Copy Cell Value | Formula List | Learn Excel