Excel Formula to Copy Cell value to Another Cell

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 to show you some easy & basic formulas to copy cell value to another cell. You can use the formulas to any version of Excel from Excel 2003.

First things first, let’s get to know about today’s example workbook.

Excel Sheet - Excel Formula to Copy Cell value to Another Cell

You will find numbers of sheets inside the workbook. Within every sheet we will see different formulas to copy cells. But the basic schema will be the same, a table of three columns, First Name, Last Name, Age.

In a practical scenario you will encounter different types of values and datasets. For making things simple, we choose this type of dataset to show you examples.

Practice Workbook

You are welcome to download the workbook from the link below.

1. Conventional Copy-Paste

1.1. Select – Copy – Paste

To copy a cell, you need to select a cell first. Then on the Clipboard section of the Home tab you will find Copy.

Copy - Excel Formula to Copy Cell value to Another Cell

Click Copy there. Here we’ve selected the A3 cell containing First Name and exercise Copy.

Now select the destination cell(where you want/need to insert your copied value). Then again on the Clipboard section you will find an option called Paste. You will find a number of paste options clicking on the list icon on Paste.

Paste - Excel Formula to Copy Cell value to Another Cell

For the time being select the first one, it’s called Paste. It will insert the copied item as the source.

Copy Paste - Excel Formula to Copy Cell value to Another Cell

Let’s explore another option.

  1. Select a cell
  2. Right Click on the mouse.

You will see something similar to the image below.

Right copy - Excel Formula to Copy Cell value to Another Cell

  1. Click Copy
  2. Now selecting the destination cell, again Right Click on the mouse.

Paste value - Excel Formula to Copy Cell value to Another Cell

Let’s explore another option here, the smaller square box selected option is called Paste Values. Click that.

Values only - Excel Formula to Copy Cell value to Another Cell

It gave the value only, not the format presentation as the source one. To keep it the same as the source one you can use Paste option.

Pastess - Excel Formula to Copy Cell value to Another Cell
Paste format - Excel Formula to Copy Cell value to Another Cell

I suggest you explore all the paste options to understand them, so that while using them in a real scenario you can easily choose your suitable one.

1.2. Copy – Paste Between Two Cells

You can copy paste a value inside two existing values. Let’s explore the example.

We have copied and pasted First Name and Age to two adjacent cells.

Adjacent Cell - Excel Formula to Copy Cell value to Another Cell

Now to copy and paste the any value between two adjacent values we need to

  1. 1.Select and copy what we want
  2. Bringing the cursor to the right most of the two adjacent cells, Right Click on the mouse.

Insert Copied Cells- Excel Formula to Copy Cell value to Another Cell

  1. Click on Insert Copied Cells.

A dialog box will open similar to the image below.

Dialog box - Excel Formula to Copy Cell value to Another Cell

Select Shift cells right and Click OK. 

Between cells - Excel Formula to Copy Cell value to Another Cell

Value will be copied between two cells.

1.3. Keyboard Shortcuts

You can copy and paste using keyboard shortcuts as well. To do the task,

Select the cell. Then Hit CTRL + C to copy the cell.

For pasting, go to the destination cell and Hit CTRL + V

Shortcut copy - Excel Formula to Copy Cell value to Another Cell

2. Formula to Copy Cell value to Another Cell

2.1. Using Cell Reference

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.

=Cell Reference 

Cell Reference - Excel Formula to Copy Cell value to Another Cell

Here in the example, we wanted to copy the name from A4, so we have written =A4. It gave us the name (John) that belonged to the cell A4.

We can do the same for the value from B4.

Cell reference 2 - Excel Formula to Copy Cell value to Another Cell

Feel free to do the same for the rest of the values to make your understanding clear.

Complete copy cell reference - Excel Formula to Copy Cell value to Another Cell

2.2. Using VALUE-CONCATENATE

You can copy a cell value using the CONCATENATE function as well. Though by reading the name you have already understood this function is used to concatenate or add strings together, still it will help you copying.

CONCATENATE(Value)

Generally, the CONCATENATE function takes numbers of string values to add them together. But since our intention is to copy a cell, then we will provide the cell within the function.  

CONCATENATE copy - Excel Formula to Copy Cell value to Another Cell

Here we have provided Cell Reference of the value John(A4) within the CONCATENATE function, and it gave the desired result.

Similarly, we can do it for the first row of the Last Name column of our example.

CONCATENATE copy 2 - Excel Formula to Copy Cell value to Another Cell

Now, if you use the CONCATENATE for the Age column in our example, you will find the answer.

CONCATENATE number - Excel Formula to Copy Cell value to Another Cell

It provided the value, but the CONCATENATE function’s output is always a string – character, in other words text. This copied 21 is no longer a number value.

To eradicate this type of mismatch, we can bring the VALUE function into the picture. Write the formula for copying the Age column with the VALUE function.

VALUE- Excel Formula to Copy Cell value to Another Cell

It provided the result and didn’t convert it into a string.

Let’s try to write the VALUE function for copying the first row of the Last Name column.

VALUE garbage - Excel Formula to Copy Cell value to Another Cell

It gave a garbage value.

One thing to note about the VALUE function is that it works only for numbers, and at a time you can provide only one value inside the VALUE function.

As we noticed, the CONCATENATE function is not suitable for numbers and the VALUE function doesn’t work for anything but numbers, so we can write the formula using the IFERROR function.

IFERROR(value,value_if_error)

IFERROR VALUE - Excel Formula to Copy Cell value to Another CellHere, we’ve written the formula such that, if any error occurs by using the VALUE function for the cell then do the task using the CONCATENATE for that cell.

Do the same for the rest of the values.

All example IFFERROR- Excel Formula to Copy Cell value to Another Cell

2.3. VLOOKUP to Copy Cell

You can copy the cell value using the VLOOKUP function. Let’s get to know the syntax for the copy formula using VLOOKUP.

VLOOKUP(value, value range,1,FALSE)

value: The cell value you want to copy

value_range: the range where the value belongs to

1: In generic VLOOKUP formulas in this placeholder we used to set the column of range to look for the value, since our value will be at the start of our range we are using 1

FALSE: For an exact match we write FALSE or 0. You can write 0 in place of FALSE.

Write the formula in the Excel for the example.

VLOOKUP - Excel Formula to Copy Cell value to Another Cell

Here our value is A4, as we want to copy the value ‘John’, in place of value_range, we’ve inserted just one cell A4, because our value is in the A4 cell only, no need to traverse around.

Write the same formula for the first row of the Last Name column, changing the Cell Reference values.

VLOOKUP 2 -Excel Formula to Copy Cell value to Another Cell

Do the same for the rest of the rest of the values (you can do for the cell you want to do).

All Example VLOOKUP-Excel Formula to Copy Cell value to Another Cell

2.4. HLOOKUP to Copy Cell

Similar to the VLOOKUP function, you can do the task using HLOOKUP as well.

The syntax will seem familiar to you:

HLOOKUP(value, value_range,1,FALSE)

value: The cell value you want to copy

value_range: the range where the value belongs to

1: In generic HLOOKUP formulas in this placeholder we used to set the column of range to look for the value, since our value will be at the start of our range we are using 1

FALSE: For an exact match we write FALSE or 0. You can write 0 in place of FALSE.

Write the formula in the Excel for the example.

HLOOKUP-Excel Formula to Copy Cell value to Another Cell

If you understand the previous section, you have already understood the concept here. As we wanted to copy the value ‘John’, inserted A4 in place of value and value_range (to eradicate needless traversing) and it gave the result.

Do the same for the rest of the values, that will give you a hands on experience before diving into practical scenarios.

HLOOKUP 2 -Excel Formula to Copy Cell value to Another Cell

2.5. INDEX-MATCH to Copy Cell Value

You can use the combination of INDEX-MATCH function to fetch the value from a particular cell.

If you are aware of INDEX function then you know that it takes at least two parameters, reference, row_number

INDEX(array/reference, row_number)

array/reference: A cell or range of cells to look at

row_number: The row number where the value locate

We will fetch the row_number using the MATCH function.

MATCH(lookup_value, lookup_array, match_type)

lookup_value: The value to search in the lookup_array.

lookup_array: A range of cells that are being searched.

match_type: This is an optional field. 0 for an exact match.

Now the syntax of our formula will be like:

INDEX(Cell Reference, MATCH(Cell Reference, Cell Reference/ Range, 0))

In place of array we aim to use a single cell, since we want to fetch the value from that cell, no need to go across all the cells.

Write the formula in Excel.

INDEX-MATCH-Excel Formula to Copy Cell value to Another Cell

It worked. We got the value of A4. We can check whether it’s okay with B4 or not

INDEX- MATCH 2 Excel Formula to Copy Cell value to Another Cell

Things are fine. For practice purposes you can do the same for the other values as well.

ALL - INDEX - MATCH - Excel Formula to Copy Cell value to Another Cell

2.6. VBA to Copy Cell

2.6.1. Copy a Single Cell

We can copy the cell using VBA code. VBA stands for Virtual Basic for Applications.

We need to set the operation using VBA code and will trigger when it’s necessary.  To copy a cell from another we can generate the VBA code as follows:

Sub Copy_SingleCell()

    Selection.Copy

    ActiveCell.Offset(0, 5).Range("A1").Select

    ActiveSheet.Paste

End Sub

Inside the Developer tab you will find the Visual Basic option.

VBA - Excel Formula to Copy Cell value to Another Cell

Click there. A VBA window will be opened to you.

Module VBA-Excel Formula to Copy Cell value to Another Cell

Inside the Project section at the left of your screen, Right Click on the mouse putting the cursor there, explore the Insert option and select Module.

Now write the code here.

Code - Excel Formula to Copy Cell value to Another Cell

This code will select the cell and paste it at a difference of 5 columns, because we have set the Offset value 0 and 5. 0 indicates no change of row, 5 indicates change of 5 columns. You can increase or decrease the value as you preferred.

Select cell - Excel Formula to Copy Cell value to Another Cell

Let we’ve selected the A3 of our table. Now, on the Visual Basic window you will find a run icon. Click that.

Run VBA - Excel Formula to Copy Cell value to Another Cell

It copied the cell and pasted at a difference of 5 cells.

Copy with VBA - Excel Formula to Copy Cell value to Another Cell

It copied the value with the original formation. To copy the value only (not format) you need to add

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

These should be added at the bottom of the code. So code will be like

Sub Copy_SingleCell()

 Selection.Copy

    ActiveCell.Offset(0, 5).Range("A1").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

 End Sub

These additional lines indicate that Paste will be values only. We are selecting the Paste Special option, then set Paste as PasteValues, and left everything False.

Writing the code, select the cell and run the code

Copy Value - Excel Formula to Copy Cell value to Another Cell

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

Sub Copy_Range()

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveSheet.Paste
End Sub

ger it. You will find something similar to the image below

Copy Range-Excel Formula to Copy Cell value to Another Cell

You need to select from where you want to start the range, then run the code. Our code was for copying the entire column.

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

Syntax for that will be

'Sheet name'!

Let’s see an example

Another sheet-Excel Formula to Copy Cell value to Another Cell

In the example, we are in the sheet named Bonus Sheet, and we want to copy a value from another sheet called Cell Reference. Our targeted value at the A4 cell of Cell Reference sheet. So we wrote

='Cell Reference'!A4

And it fetched that value. Let’s see another example
HLookup sheet-Excel Formula to Copy Cell value to Another Cell
We wanted to get the value belonging to the A8 cell of the Hlookup sheet. And the formula provided us that.

You can notice, when you name your sheet with multiple words you need to mention the name within ‘ ‘ but for a single word name ‘ ‘ is not needed.  

Conclusion

That’s all for the session. We’ve tried to show you a few formulas to copy cell value to another cell. Hope you will find this helpful. Feel free to comment if anything seems hard to understand. We have shown you VBA code for copying columns, can you write the code to copy rows? Let us know.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo