Mukesh Dipto
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.
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’.
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.
➤ 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’.
➤ 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”.
➤ 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”.
➤ 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.
➤ 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”.
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)
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.
➤ 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:
- How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
- Paste Options in Excel with Shortcuts: A Complete Guide
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)
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.
➤ Firstly, select Cell F5, the first value under the column “Copy Company”. Insert the following formula and press Enter.
=CONCATENATE(B6)
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.
➤ 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.
➤ First and foremost, we will see why we will use this formula. Insert the following formula in Cell F6.
=VALUE(B6)
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))
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.
➤ 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.
➤ 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.
