Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Microsoft Excel has provided a wide range of basic & simple methods to copy cell values from another worksheet to a new worksheet or even a new workbook. Here, I’ve tried to sum up all the fruitful methods to find Excel formula to copy cell value from another sheet under multiple criteria & with cell references.


Download Practice Workbook

You can download the practice workbooks that we’ve used to prepare this article.


4 Simple Ways to Copy Cell Value from Another Sheet in Excel

In this section, 4 ways are available for Excel formula to copy cell value from another sheet. We will discuss the ways with proper illustration. So, let’s start.


1. Copy & Paste with Multiple Options

In our dataset, we have 2 columns (Columns D & E) with 10% & 20% increased salaries of 5 employees in Sheet 1.

Dataset for Excel Formula to Copy Cell Value from Another Sheet (1)

Now we’re going to copy the whole array or table below to another sheet(Sheet 2) in the same workbook.

Steps:

  •  First of all, select the whole array or table (B4 : E9)
  •  Now, press CTRL+C to copy the selected array.

Copy Data to apply Formula to Copy Cell Value from Another Sheet

  • Then, open Sheet 2 & on cell B4 where you want to paste, and select that cell.
  • After that, right-click your mouse & from the Paste options choose the 1st one named Paste(P) only. You’ll get the whole data with formulas & formats as this Paste option will exactly copy all the data including formulas & formats of the cells from any sheet.

Copy Cell value from Another Sheet and Paste it

If you select Paste Values(V) you’ll see only the text & number values have been copied but no formula or cell format will be copied with this option.

Copy Cell Value from Another Sheet and Paste Values

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

Copy Cell values with Formula from Another Sheet and Paste it

Now if you want to copy the cell format only then select the Paste Format option. It will not copy any values or formulas from the reference cells except only cell formats.

Copy Excel Cell Format from Another Sheet and Paste

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

Copy Excel Link from Another Sheet and Paste

With the Paste Transpose option, you can convert the rows & columns into columns & rows respectively. And here the resultant data along with the formulas & cell formats will be preserved too.

The whole functions mentioned above can also be executed even with more & customized options by selecting Paste Special from the Right-click of your mouse when you’ll go to paste the values.

Read More: Excel Formula to Copy Text from One Cell to Another Sheet


2. Creating Cell Reference from Another Sheet

We can also use cell & sheet references to copy data from the other sheet for calculation. Here, in Sheet 1 we have a chart of current salaries only. We want to determine the salaries with a 10% increase with the help of this data in another sheet (Sheet 2).

Steps:

  • At first, open Sheet 2 & in cell C5, type-

=Sheet1!C5+(Sheet1!C5*10%)

  • Then, press Enter & you’ll get the increased salary for Sam.
  • Now use the Fill Handle from cell C5 to Autofill other cells in this column.

Excel Formula to Copy Cell Value from Another Sheet

So what’s happening here is by mentioning Sheet1! before C5, we’re actually referring to Cell C5 from Sheet 1. And the rest of the calculation in the column will be executed with the sheet & cell references by the Autofill option.

Read More: Excel VBA to Copy Data from Another Workbook without Opening


3. Creating Cell Reference to Another Workbook

Now, we want to use the cell reference from another workbook (Book2) which is currently open, and then we’ll execute our calculation in Book1 with that cell reference to determine the salaries with a 10% increase for each employee.

Steps:

  • First, in cell C5 in Book2, type-

=[Book1.xlsx]Sheet1!C5+([Book1.xlsx]Sheet1!C5*10%)

  • Then, press ENTER, you’ll get the resultant value for the 1st one.
  • Now, drag the formula below to copy the whole column like before as stated in the previous method.

Excel Formula to Copy Cell Value from Another Sheet

So here we’re using the reference of another workbook. And that’s why we have to mention [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 then you have to mention the source file path before typing the reference of another workbook. Here, in this case, if Book1 is not open then the commands in the function bar will be-

='C:\Users\88019\Desktop\[Book1.xlsx]Sheet1'!C5+('C:\Users\88019\Desktop\[Book1.xlsx]Sheet1'!C5*10%)

Excel Formula to Copy Cell Value from Another Workbook

You can copy the formula mentioned & apply to your worksheet but make sure that the location of your own extension file or the source path of your reference workbook is mentioned properly.

Read More: Excel VBA: Copy Cell Value and Paste to Another Cell


Similar Readings


4. Using Named Range and Referring to Another Sheet or Workbook

Now we can follow another fruitful method by using Named Range to define the source data.

Steps:

  • First, go to the source data that you need to use in another worksheet.
  • Then, press CTRL+F3 top open Name Manager.
  • Now, click on New.. option to activate New Name dialogue box.

  • After that, give a name of your source data inside the Name box. You cannot use Space while typing the name.
  • Now, click on the Refers to box (i.e. Salary) & then select the whole array or table you want to refer to.

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

Named Range to Copy Cell Value from Another Sheet

  • Now go to any worksheet in your same workbook & 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 at once. The whole data is here stored as the name you’ve defined.

  • Now, 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. So, here the commands will be in the function bar if we want to refer our data from another workbook which is open right now-

=Book1.xlsx!Salary

  • And if the reference workbook is closed then we have to add the source path of that workbook or Excel file before mentioning the workbook name as well as the defined name for the data. So, our commands in the function bar here will be-

='C:\Users\88019\Desktop\Book1.xlsx'!Salary

Read More: How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)


Excel VBA to Copy Cell Value to Another Sheet

If you are fond of VBA, then we have a good news for you, we can copy a range of data from one sheet to another sheet with VBA code too.

Steps:

  • Firstly, press Alt+F11, VBA window will open.
  • From the Insert tab, select the Module command. A new module named Module 1 will appear where you’ll write your codes.
  • Now copy the following codes or macro and paste them to your own module.
Sub CopytoAnotherSheet()

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

End Sub
  • After copying the codes, press F5 and return to your Excel worksheet by pressing Alt+F11 again.

  • So, here in the picture below, you’ll see the data has been copied from Sheet 1 to Sheet 3.

Read More: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)


Conclusion

These are all the most suitable I’ve found so far to find Excel formula to copy cell value from another sheet or even another workbook if that is open or closed. If you think I’ve missed a method that I should have mentioned in the article then please let me know through your valuable comments. Or you can have a look at our other interesting & informative articles on this website.


Related Posts You May Also Like

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo