How to Copy a Cell in Excel Using Formula(7 Methods)

Copying a cell in Microsoft Excel is an important feature when we deal with different kinds of data. There are lots of ways to copy a cell value. Using the formula is a convenient way to copy a cell value in Excel. In this article, we will learn about how to copy a cell in excel using formulas in 7 different methods.


Download Practice Workbook

Download the practice workbook here.


7 Quick Methods to Copy a Cell in Excel Using Formula

First and foremost we will get familiar with the dataset that we will use in this article. To demonstrate this, we will work with the following data set of five companies, their founders, and founding years.

7 Quick Methods to Copy a Cell in Excel Using Formula


1. Excel Copy a Cell Value to Another in Same Worksheet Using Cell Reference

Using the cell reference formula is one of the easiest ways to copy a cell in excel. A cell reference is a reference to a cell or a group of cells on a worksheet that may be used in a formula to tell Microsoft Excel where to look for the values or data that the calculation needs to calculate.

In this example, we will work with the previously mentioned data set. We will copy the values of column ‘Founded’ in the column of the second table named ‘Copy Founded’.

Excel Copy a Cell Value to Another in Same Worksheet Using the formula Cell Reference

We will do this in the following steps:

➤ Firstly, we will select Cell H6  and input the formula:

=D6

➤ Next, press Enter. We will see the value of Cell D6 in Cell H6.

1. Excel Copy a Cell Value to Another in Same Worksheet Using the formula Cell Reference

➤ Then, you can input the corresponding formulas in your relative cells. It will copy all the values of the whole column ‘Founded’ in column ‘Copy Founded’.

1. Excel Copy a Cell Value to Another in Same Worksheet Using the formula Cell Reference

➤ On the other hand, you can do it with the Fill Handle tool also. Just drag the Fill Handle tool to the end of the column ‘Copy Founded’. We will get all the copied values of the column ‘Founded’ in the column ‘Copy Founded’.

Related Content: How to Copy the Same Value in Multiple Cells in Excel (4 Methods)


2. Copy a Cell Value from Another Worksheet in Excel

Sometimes we might need to copy a Cell from our current worksheet to another worksheet. We can copy a Cell value in excel from another worksheet easily. We will copy the values of the column ‘Founder’ from “Another Worksheet-1” to “Another Worksheet-2.” Let’s see the necessary steps to follow for this task.

➤ Firstly, we will go to “Another Worksheet-2”.

Copy a Cell Value Using Formula from Another Worksheet in Excel

➤ Next, select Cell C7. Input the following formula, and press Enter.

='Another Worksheet-1'!C6

➤ Then we can see the value of Cell C6 of “Another Worksheet-1”  in the Cell C7 of “Another Worksheet-2”.

='Another Worksheet-1'!C6

➤ After that, to copy the whole column input corresponding formulas to all the cells under the column ‘Founder’ in “Another Worksheet-2” like shown in the figure below. We will do this with the Fill Handle tool. Input formula in the first cell of column ‘Founder’ and then drag the fill handle to the end of that column.

='Another Worksheet-1'!C6

➤ Finally, the result is here.

Related Content: How to Copy a Worksheet in Excel (5 Smart Ways)


3. Use VLOOKUP to Copy Cell Value in Excel

VLOOKUP is an abbreviation for ‘Vertical Lookup.’ It’s a function that tells Excel to look for the specific value in a column in order to return a value from another column in the same row.

We can use this formula to copy a cell in excel. To understand this we will continue with our previous data set. This time we will copy the values of column “Company” to the other column named “Copy Company”.

='Another Worksheet-1'!C6

We will go through the following steps to use the VLOOKUP formula.

➤ Firstly, select Cell F5. Input the following formula and press Enter.

=VLOOKUP(B6,B6,1,0)

Use VLOOKUP Formula to Copy Cell Value in Excel

Here, we can see the copied value of Cell C6 in Cell F5.

➤ Next, input the corresponding formulas for all the cells under column ‘Company’. By doing this we can copy the whole column.

Use VLOOKUP Formula to Copy Cell Value in Excel

➤ Finally, we can also do this with the Fill Handle tool. After inputting the first Cell value just drag the Fill Handle to the end of the column “Copy Company”.


Similar Readings:


4. Insert HLOOKUP Formula to Copy Cell in Excel

HLOOKUP stands for Horizontal Lookup, and it may be used to obtain data from a table by searching a row for matching data and then printing the results from the relevant column. HLOOKUP looks for a value in a row, whereas VLOOKUP looks for the value in a column.

It should be noted that to demonstrate this method we will continue with our previous dataset. In this case, we have a missing value of the founder of Google. We will copy it from our original data set. After copying we will paste it to the dataset of copied values. We will explain this abiding by the following steps:

➤ At this time, select the missing Cell G8. Insert the following code and press enter.

=HLOOKUP(C8,C8,1,0)

Insert HLOOKUP Formula to Copy Cell in Excel

We can see the value of Cell C8 in Cell G8.


5. CONCATENATE Formula to Copy Cell to Another Cell

The Excel CONCATENATE formula concatenates up to 30 values together and returns the result as text. We can use this formula to copy values as well.

From the previous dataset, we have the first row missing in our copy dataset. We will use the CONCATENATE formula to copy the first row from the original dataset. Let’s see how can we do this.

Excel CONCATENATE Formula to Copy Cell to Another Cell

➤ Firstly, select Cell F5, the first value under the column “Copy Company”. Insert the following formula and press Enter.

=CONCATENATE(B6)

Excel CONCATENATE Formula to Copy Cell to Another Cell

Here, we will see the copied value of Cell B6 in Cell F6.

➤ Next, insert the corresponding code in Cells G7 & H7.  We can see the codes in the image given below. Now, press Enter and we will be able to copy the value of the other two cells.

Excel CONCATENATE Formula to Copy Cell to Another Cell

➤ Finally, you can do it with the Fill Handle tool also. Just drag the Fill Handle from Cell F6 to Cell H6.

Related Content: How to Copy Visible Cells Only in Excel (4 Fast Ways)


6. Apply IFERROR to Copy Cell Value

Excel IFERROR formula is used to catch and manage problems in formulas and calculations. IFERROR formula examines a formula and, if it evaluates to an error, returns another value you specify; otherwise, it returns the formula’s result. A formula, expression, value, or cell reference can all be used.

We will do our previous task again. This time we will copy the values using the IFERROR formula.

Apply IFERROR Formula to Copy Cell Value in Excel

➤ First and foremost, we will see why we will use this formula. Insert the following formula in Cell F6.

=VALUE(B6)

Apply IFERROR Formula to Copy Cell Value in Excel

This returns an error. It happens because the Value function only works for numeric values. We will solve this issue using the combination of CONCATENATE and IFERROR formulas.

➤ Next, select Cell F6. Insert the following formula and press Enter.

=IFERROR(VALUE(B6),CONCATENATE(B6))

Apply IFERROR Formula to Copy Cell Value in Excel

We can see the copied value of Cell B6 in Cell F6.

➤ Finally, Insert the corresponding code in Cells G6 & H6. Now press Enter. We will get the copied value of Cells C6 & D6 in Cells G6 & H6. On the other hand, we can do this step also by dragging the Fill Handle tool.

Formula Breakdown

  • VALUE(B6)

This will look for the value in Cell B6 to insert in the destination Cell.

  • CONCATENATE(B6)

This will join the value of Cell B6 in the destination Cell.


7. Copy Cell in Excel Using INDEX-MATCH

The INDEX formula can return an item from a specific position in a list. The MATCH formula can return the position of a value in a list. The INDEX – MATCH formula can be used together, as a flexible and powerful tool for extracting data from a table.
In this example, we will use our previous data as well as will complete the missing column of the company Google. Let’s see how many steps we have to go to do this.

Copy Cell in Excel Using INDEX-MATCH Formula

➤ Firstly, select Cell F8. Insert the following formula:

=INDEX(B8,MATCH(B8,B8,0))

➤ Now, press Enter. We will see the copied value of Cell B8 in Cell F8.

Copy Cell in Excel Using INDEX-MATCH Formula

➤ Next, Insert the corresponding code to Cells G8 & 87. By pressing Enter we will get the copied value of Cells C8 & D8. Moreover, we can do this step also using the Fill Handle tool. Just drag the Fill Handle to the right. We will get the same result.

Formula Breakdown

  • Match(B8,B8,0)

This will look for the value of Cell B8 and ‘0’ refers to the exact match of the string.

  • INDEX(B8,MATCH(B8,B8,0))

This will return the copied value of Cell B8.


Conclusion

Copying a cell in Excel using formula is not only easy and effective but also saves time. In this article, I have tried to make this process easier for you. So, download the practice workbook added to this article and try yourself. If you feel any confusion just leave a reply in the below box. Stay tuned with us for more interesting solutions to Excel Problems.

Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo