Copying might be a monotonous phase while using Excel. Using formulas can bring some life to this copy task. The agenda for today’s tutorial is how to use excel formula to copy the cell value to another cell in 5 suitable ways. You can use the formulas in any version of Excel.
Let us take a sample dataset for discussion. In this dataset, there are 5 person’s First Names, Last Names and Ages.
Now using Excel formulas, we will copy cell value from this dataset to another cell.
1. Copy Cell Value to Another Cell Using Cell Reference in Excel
We will see copying cell elements using Cell Reference. All you need to do is, go to the cell you want to insert the copy value. And write the Cell Reference of the cell you want to copy following an Equal (=) sign. Let’s check the process below.
- First, select cell F5 and type this formula to extract the value of cell B5.
=B5
- Hit Enter.
- Following, apply the same process in cell G5 with this formula.
=C5
- Similarly, copy the value of cell D5 to cell H5 with this formula.
=D5
- Lastly, select the cell range F5:H5 and use the Autofill tool to copy the rest of the values from the dataset all at once.
2. Combining VALUE-CONCATENATE Functions to Copy Cell Value to Another
You can copy a cell value by combining the CONCATENATE function and the VALUE function as well. For this, go through the steps below.
- First, insert this formula in cell F5.
=IFERROR(VALUE(B5),CONCATENATE(B5))
- Press Enter.
- Now, apply a similar procedure in cell G5.
=IFERROR(VALUE(C5),CONCATENATE(C5))
- Similarly, use this formula in cell H5.
=IFERROR(VALUE(D5),CONCATENATE(D5))
- Finally, go through the same procedure for cell range F6:H10 and you will get the following output.
3. Copying Cell Value with Excel VLOOKUP Function
You can also copy the cell value using the VLOOKUP function. Let’s see how it works.
- First, insert this formula to extract the cell value of B5 to cell F5. Also, hit Enter.
=VLOOKUP(B5,B5,1,FALSE)
- Then, write the same formula for the first row of the Last Name column, changing the Cell Reference values.
=VLOOKUP(C5,C5,1,FALSE)
- Similarly, apply this formula in cell H5.
=VLOOKUP(D5,D5,1,FALSE)
- Finally, do the same for the rest of the cells to get this final output.
4. Copy Cell Value with HLOOKUP Function
Similar to the VLOOKUP function, you can do the task using the HLOOKUP function as well.
- First, type this formula in cell F5.
=HLOOKUP(B5,B5,1,FALSE)
- Next, hit Enter.
- Then, apply the same formula for the rest of the cells changing the cell reference.
- Finally, you will successfully copy cell values to another cell.
5. Excel Formula with INDEX-MATCH Functions to Copy Cell Value
You can use the combination of the INDEX-MATCH functions to fetch the value from a particular cell. Simply follow the steps below.
- First, insert this formula in cell F5 to copy the value of cell B5.
=INDEX(B5,MATCH(B5,B5,0))
- After that, press Enter.
- Following, apply the same in cell G5.
=INDEX(C5,MATCH(C5,C5,0))
- Lastly, type a similar formula in cell H5 changing cell reference to D5.
=INDEX(D5,MATCH(D5,D5,0))
- Finally, select cell range F5:H5 and use the AutoFill tool to get this final output.
How to Copy Cell Value to Another Cell in Excel: Conventional Methods
Microsoft Excel also helps to copy cell values to another one with its conventional methods. These methods are applicable to any version of Excel.
1. Select Copy & Paste Options
This first method will guide you by using the copy and paste options in the excel ribbon.
- First, select cell B4.
- Next, on the Clipboard section of the Home tab, click on Copy.
- Now, select the destination cell F4.
- Then, again on the Clipboard section, you will find an option called Paste.
- Here, click on the Paste icon from the list of options.
- That’s it, you will finally get the copied value.
- Apart from this, you can get the Copy command by right-clicking on the source cell.
- Following, right-click on the destination cell and then you will find the Paste command.
- You can try any of the copy-and-paste options.
2. Copy & Paste Between Two Cells
You can copy-paste a value inside two existing values. Let’s explore the example.
- First, we copied and pasted the First Name and Age to two adjacent cells.
- Then, select and copy the cell having the title Last Name.
- Afterward, put the cursor to the right of most of the two adjacent cells and then right-click on the mouse.
- Here, click on Insert Copied Cells.
- Next, the Insert dialogue box will open.
- In this box, select Shift cells right and click OK.
- Finally, the value will be copied between two cells.
3. Apply Keyboard Shortcuts
You can copy and paste using keyboard shortcuts as well. To do the task, simply go through this process.
- First, select the cell range B5:D5.
- Then, hit Ctrl + C on your keyboard to copy the cell.
- Afterward, simply go to the destination cell and hit Ctrl + V to get the copied values.
How to Copy Value to Another Cell with Excel VBA
We can copy the cell using the VBA code. VBA stands for Visual Basic for Applications. It is a programming language for Excel. Let’s check the methods to apply VBA code for both a single cell and a range of cells.
1. Copy a Single Cell
Let us copy a single cell first with a VBA code. To do this, follow the instruction below.
- In the beginning, select cell B4 as we want to copy it.
- Then, inside the Developer tab, select the Visual Basic option under the Code group.
- Next, under the Insert option, select Module.
- Now, write the code here.
Sub Copy_SingleCell()
Selection.Copy
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveSheet.Paste
End Sub
- Afterward, click on the Run Sub icon or press F5 on your keyboard.
- Finally, it copied the cell and pasted at a difference of 4 cells.
Sub Copy_SingleCell()
Selection.Copy
ActiveCell.Offset(0, 4).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
2. Copy a Range of Cells
Similar to the copy of a single cell you can copy a range of cells as well using VBA. If you want to copy a range of cells then the code will be as follows:
Sub Copy_Range()
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveSheet.Paste
End Sub
Finally, you will find something similar to the image below.
Additional Tips
If you want to copy a cell from another sheet all you need to do is insert the sheet name before the cell reference. For example, we wanted to get the value belonging to cell B4 of the INDEX-MATCH sheet. therefore, the formula provides this solution.
''
)Â but for a single word name, this punctuation mark is not needed.Download Practice Workbook
You are welcome to download the workbook from the link below.
Conclusion
That’s all for the session. We tried to show you the process of how to use excel formula to copy the cell value to another cell in 5 suitable ways. Hope you will find this helpful. Feel free to comment if anything seems hard to understand.
I’ve been looking forever for a formula to copy the content of one cell into another one , but with no luck , until I found this page . Thank you , amazing content and so easy to understand it . You are doing an amazing job !!
Hi Alexa,
Thanks for your feedback.
Best regards
i don’t want to do it for one cell and then copy formula manually to another
imagine u have dataset 1000×1000 (u dont want to do it manually waste of nerves and time)
doesn’t work for chosen value in another cell like this with substitute value based on N5
for example
=INDEX(C:C=N5;MATCH(C:C=N5;C:C=N5;0)) – C…column date format populated
excel has stupid logic, most of functions doesn’t allow to operate like this
Hello, AHMED!
Thank you for your query. Regarding your query, you can use an array formula to accomplish this result.
Thus, all the cell values will be copied to all the desired cells in an instant.
I hope this solves your problem.
Regards,
Tanjim Reza