Excel Formula to Copy Cell Value from Another Sheet (4 Examples)

Method 1 – Using the Copy and Paste tool to Copy a Cell Value Including Excel Formula From Another Sheet

In our dataset, we have 2 columns (Columns D & E) with 10% & 20% increased salaries of 5 employees in Sheet 1. We’re going to copy the whole array or table below to another sheet (Sheet 2) in the same workbook.

Steps:

• Select the whole array or table (B4:E9).
• Press Ctrl + C to copy the selected array.

• Open Sheet 2 and select its cell B4 (or wherever you want to paste).
• Right-click and go to the Paste options.
• Choose the first option named Paste(P) only.

If you select Paste Values(V), you’ll see only the text and number values have been copied.

If you go for the Paste Formulas option, only the formulas executed in the 1st sheet will be shown in Sheet 2 with resultant values but no cell format will be copied.

If you want to copy the cell format only, then select the Paste Format option.

You can paste by passing the Reference of the Cells, too. Choose the Paste Link option, and the source name or link will be assigned to pasted values in another sheet.

With the Paste Transpose option, you can convert the rows and columns into columns and rows, respectively.

You can customize the paste options and even perform operations by selecting Paste Special from the context menu.

Method 2 – Creating a Cell Reference to Copy Cell Values from Another Sheet Containing Excel Formula

In Sheet 1, we have a chart of current salaries only. We want to determine the salaries with a 10% increase in another sheet (Sheet 2).

Steps:

• Open Sheet 2 and insert the following in cell C5:

• Press Enter and you’ll get the increased salary for Sam.
• Use the Fill Handle from cell C5 to Autofill other cells in this column.

Method 3 – Creating a Cell Reference to Another Excel Workbook for Copying Cell Values with a Formula

We’ll calculate the salary increases and put them into another workbook.

Steps:

• In cell C5 in Book2, insert the following:

• Press Enter and drag the formula below to copy the whole column.

We use [Book1.xlsx]Sheet1! before typing C5 as this C5 cell is present in Sheet 1 of Book1.

If you want to add a reference to another workbook that is closed, you have to mention the source file path before the reference. In this case, if Book1 is not open, the formula would be something like the following:

You’ll need to modify the formula with your folder path.

Method 4 – Using an Excel Named Range and Referring the Formula to Another Sheet to Copy Cell Value

Steps:

• Go to the source data that you need to use in another worksheet.
• Press Ctrl + F3 to open the Name Manager.
• Click on New to get a New Name dialogue box.

• Name your source data inside the Name box. You can’t use a Space while typing the name, so use dashes or underscores if you want multiple words.
• Click on the Refers to box and then select the whole array or table you want to refer to.

• Press OK and the Name Manager will show the newly created source file with the name in the list.

• Go to any worksheet in the same workbook and use that defined name in the function bar. You’ll find an option to use the data by the name you made through the Name Manager. Press Enter.

• You’ll get the referred data in your new worksheet.

• In case you need to refer to that data from another workbook, you have to mention the file name of that workbook along with the defined name as well:

• If the reference workbook is closed, add the source path of that workbook or Excel file:

Excel VBA to Copy Cell Value to Another Sheet

Steps:

• Press Alt + F11, and the VBA window will open.
• From the Insert tab, select the Module command. A new module named Module 1 will appear.
• Copy and paste the following code into the module:

Sub CopytoAnotherSheet()

Worksheets("Sheet1").Range("B4:E9").Copy Worksheets("Sheet3").Range("B4:E9")

End Sub

• Press F5 and return to your Excel worksheet by pressing Alt + F11 again.

• You’ll see the data has been copied from Sheet 1 to Sheet 3.

Download the Practice Workbooks

<< Go Back to Copy Cell Value | Formula List | Learn Excel