How to Copy Data from One Cell to Another in Excel Automatically

In Excel, it’s a very common practice to copy data from one cell to another automatically. It may be from one worksheet to another sheet or even a workbook. It is needed for almost every user who uses Excel. Today we are going to discuss several methods to copy data in Excel automatically. But the copy process is not automatic in Excel. We will discuss this topic in detail too.

To discuss this topic we will use a data set that shows the sales amount on different days of last week.

Data Set to show how to Copy data from one cell to another automatically in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Methods to Copy Data from One Cell to Another in Excel Automatically

1. Use Cell Reference to Copy Data from One Cell to Another

We can copy data using cell references in the same sheet and different sheets. Here data will change as reference data changes automatically.

1.1 Copy Data to Same Sheet

We can copy data in the same sheet using cell reference. The process is described below:

πŸ“Œ Steps:

  • Select the cell where we want to copy data.
  • Press β€œ=” and type the Cell Name we want to Copy or select that cell. Here we will copy the C5.

Use Cell Reference to Copy Data from One Cell to Another Cell in the same sheet

  • Then press Enter and see that data is copied from Cell C5 to our destination Cell D5.


1.2 Copy Data to Different Sheets

Here we will copy data from the Data Set sheet to the Cell reference_2 sheet.

πŸ“Œ Steps:

  • Select a cell in the Cell reference_2 sheet to copy data.
  • Press β€œ=” and type the source Sheet name in between the quotation (β€˜ ’)
  • Then put an exclamatory(!) sign and type the Cell Name.

Use Cell Reference to Copy Data from One Cell to Another Sheet

  • Finally, press Enter we will get a copy of the cell of the mentioned sheet.


Copy Data to Different Sheets with Alternate way:

We can also do this using a different technique.

πŸ“Œ Steps:

  • Select a cell in the Cell reference_2 sheet to copy data.
  • Press β€œ=” on the cell.

  • Select the source sheet from the sheet name section.

  • Now select the cell we want to copy.

  • Finally, press Enter and we will our result on the destination sheet.


2. Apply Paste Special Command in Excel

2.1 From Mouse Right-Click Menu

We can copy data using Paste Special from Mouse Shortcut.

πŸ“Œ Steps:

  • Select the cell we want to copy.
  • Press Ctrl+C and copy the cell.

Apply Excel Paste Special Option to Copy Data to Another Cell Automatically

  • Go to the destination cell and press Ctrl+V.
  • From the Ctrl drop-down, we will get Paste Link (N).

Apply Excel Paste Special Option to Copy Data to Another Cell Automatically

  • Press the Paste Link (N).
  • The link of the source cell will be copied to our destination cell.


2.2 Applying Clipboard Paste Options

Now, if we change the reference cell then the destination cell will also change.

We can copy data using Paste Special from the Clipboard group of commands.

πŸ“Œ Steps:

  • Select the cell we want to copy.
  • From Ribbon go to Home.
  • Go to Copy Command.
  • Then select Copy from the drop-down.

Apply Excel Paste Special Option to Copy Data to Another Cell Automatically

  • Select a cell to paste the data.
  • Now go to the Paste command.
  • From the Paste drop-down select Paste Link (N).

Paste special options of Clipboard group of commands in paste special

  • After selecting Paste Link(N), the link cell link of the source cell will be stored here.

Result of Clipboard group of commands in paste special

In this method, if the source data change the destination data will change automatically.


3. Copy Data from One Workbook to Another

We can also copy a cell from one workbook to another workbook.

πŸ“Œ Steps:

  • Two workbooks are taken for copy data.
  • We will copy it to Copied Excel FileΒ workbook.

Two workbooks: copay data from one to another

  • Select a cell in the destination workbook.
  • Put β€œ=” sign.
  • Select the cell from the source workbook.

Select cell of source workbook to copy data from one to another.

  • Press Enter. We will see that data from the source workbook is copied to the destination workbook.

Copied data from one workbook to another.


Conclusion

Here we discussed how to copy data from a cell same sheet, a different sheet of the same workbook, and in a different workbook with examples. We showed the easiest methods to perform the task. If you have any queries, please let us know in the comment box. For more Excel-related articles, please visit ExcelDemy.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo