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.

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-

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

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–

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

Read More: How to Copy and Paste Multiple Cells in Excel (7 Quick Ways)

Similar Readings

• How to Autofilter and Copy Visible Rows with Excel VBA
• Copy Unique Values to Another Worksheet in Excel (5 Methods)
• How to Copy Merged and Filtered Cells in Excel (4 Methods)
• Run Time Error 1004: PasteSpecial Method of Range Class Failed
• Macro to Copy and Paste from One Worksheet to Another (15 Methods)

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.

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

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–

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

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–

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

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

• VBA Code to Compare Two Excel Sheets and Copy Differences
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
• Difference Between Paste and Paste Special in Excel
• Copy Alternate Rows in Excel (4 Ways)
• How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)
• Excel VBA: Copy Cell Value and Paste to Another Cell
• How to Copy Data from One Cell to Another in Excel Automatically