How To Copy Data From One Sheet To Another In Excel Using Formula

Microsoft Excel has provided a wide range of basic & unproblematic methods to re-create jail cell values from another worksheet to a new worksheet or even a new workbook. Hither, I've tried to sum up all the fruitful methods to copy & paste cell values in another worksheet under multiple criteria & with cell references.

Download Practice Workbook

You can download the practise workbook that we've used to fix this commodity.

5 Simple Means to Copy Cell Value from Another Sheet in Excel

1. Re-create & Paste with Multiple Options

In our dataset, nosotros take ii columns(Cavalcade D & East) with 10% & 20% increased salaries of 5 employees in Sheet one. Now we're going to copy the whole array or table below to another canvas(Canvass ii) in the same workbook.

Stride 1:

➤ Select the whole assortment or table (B4 : E9)

➤ Press CTRL+C to re-create the selected assortment.

Step 2:

➤ At present open Sheet 2 & on Cell B2 or B4 where you want to paste, select that prison cell.

➤ Correct-click your mouse & from the Paste options cull the 1st one named Paste(P) merely.

You'll get the whole data with formulas & formats every bit this Paste option volition exactly copy all the data including formulas & formats of the cells from whatsoever sheet.

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

If you get for the Paste Formulas pick, then only the formulas executed in the 1st sheet volition exist shown in Sheet 2 with resultant values but no prison cell format will exist copied.

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

Yous tin can paste past mentioning the reference of the cells too. Choose Paste Link option & the source name or link will be assigned to pasted values in another canvas.

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

The whole functions mentioned higher up can also exist 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.

2. Creating Prison cell Reference to Another Sheet

We can as well utilise cell & canvas references to copy data from the other sheet for adding. Hither, in Sail 1 we have a chart of electric current salaries only. We want to determine the salaries with a x% increase with the help of this data in another sheet (Sheet ii).

Steps:

➤ Open Sheet two & in Cell C5, type-

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

➤ Printing Enter & you'll get the increased salary for Sam.

➤ Now use the Fill Handle from Cell C5 to autofill other cells in this cavalcade.

So what'southward happening hither is by mentioning Sheet1! before C5, nosotros're actually referring to Cell C5 from Sheet i. And the residual of the calculation in the column will be executed with the sail & jail cell references by the Autofill option.

3. Creating Jail cell Reference to Some other Workbook

Now, we want to utilize the cell reference from another workbook (Book2) which is currently open and and then nosotros'll execute our calculation in Book1 with that cell reference to make up one's mind the salaries with a ten% increase for each employee.

Steps:

➤ In Prison cell C5 in Book1, type-

=[Book2.xlsx]Sheet1!C3+([Book2.xlsx]Sheet1!C3*10%)

➤ Press Enter, y'all'll get the resultant value for the 1st one.

➤ Use Make full Handle now to autofill the whole column similar earlier as stated in the previous method.

And then here nosotros're using the reference of some other workbook. And that's why we take to mention [Book2.xlsx]Sheet1! before typing C3 as this C3 Prison cell is present in Sheet 1 of Book2.

If you want to add reference to another workbook which is closed then you have to mention the source file path before typing the reference of another workbook. Here, in this case, if Book2 is not open and so the commands in the part bar volition be-

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

You tin can re-create the formula mentioned & apply to your worksheet but make certain that the location of your own extension file or the source path of your reference workbook is mentioned properly.

4. Defining the Name of the Source Information & And then Referring to Another Sheet or Workbook

At present we tin follow another fruitful method by using Name Manager to define the source data.

Step one:

➤ Go to the source information that y'all need to utilise in another worksheet.

➤ Press CTRL+F3 pinnacle open Proper noun Manager.

➤ Tap on New.. option to actuate New Name dialogue box.

Footstep ii:

➤ Give a name of your source data inside the Name box. Y'all cannot use Space while typing the name.

➤ At present click on the Refers to box & then select that whole array or table you want to refer to.

Press OK & the Proper name Manager volition testify the newly created source file with the proper name in the list.

Pace three:

➤ Now become to any worksheet in your aforementioned workbook & use that defined name in the part bar. You'll find an option to utilise the data by the name you lot fabricated through the Proper name Manager.

➤ Press Enter.

You'll get the referred data in your new worksheet at once. The whole data is here stored equally the name you've divers.

Now, in case y'all need to refer to that data from some other workbook, yous have to mention the file name of that workbook along with the defined proper name every bit well. And so, here the commands will exist in the role bar if we want to refer our data from another workbook which is open correct now-

=Book1.xlsx!Salary_Increase

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 likewise as the defined proper name for the data. And then, our commands in the function bar hither volition be-

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

5. Copying Cell Value with VBA Editor

We can re-create a range of data from a sheet to another sheet with VBScript coding too.

Steps:

➤ Printing 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 re-create the post-obit codes or macro and paste them to your own module.

          Sub CopytoAnotherSheet()    Worksheets("Sheet1").Activate    Range("B4:E9").Copy    Worksheets("Sheet3").Activate    Range("B4:E9").PasteSpecial End Sub

➤ Subsequently copying the codes, press F5 and return to your Excel worksheet by pressing Alt+F11 again.

And then, here in the picture beneath, you lot'll see the data has been copied from Canvass 1 to Canvass 3.

If you lot want to paste with multiple options like stated in the 1st method, then in the 4th line of the body of the VBA editor, press Space subsequently typing PasteSpecial & yous'll notice a number of Paste Options. You can select any of them past navigating with Down or Up Arrow keys & then pressing Tab to insert the specified option in the macro.

Concluding Words

These are all the well-nigh suitable & simplest techniques I've found and so far to copy prison cell values from some other worksheet or even some other workbook if that is open up or closed. If you think I've missed a method that I should have mentioned in the article and then please let me know through your valuable comments. Or you tin have a look at our other interesting & informative articles on this website.