{"id":5518,"date":"2021-02-04T09:38:56","date_gmt":"2021-02-04T09:38:56","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=5518"},"modified":"2024-03-26T11:28:48","modified_gmt":"2024-03-26T11:28:48","slug":"copy-data-from-one-sheet-to-another","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/","title":{"rendered":"Copy Data From One Sheet to Another in Excel"},"content":{"rendered":"\n
Copy-Data-From-One-Sheet-to-Another<\/a>Download File<\/a><\/div>\n\n\n\n

You can move, copy or link cells between worksheets to organize your data the way you want. You can copy sheets in the same workbook, or between two workbooks.<\/p>\n\n\n\n

Copy a Worksheet Inside the Same Workbook<\/h2>\n\n\n\n

Each workbook has at least one worksheet. Every worksheet<\/a> is identified by a worksheet tab at the bottom-left corner. If you create more sheets, the tabs are placed to the right of the current sheet.<\/p>\n\n\n\n

To quickly copy the entire worksheet, right-click the sheet tab and select Move or Copy<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After you click the button, a new window will appear. Here, you can select whether a new sheet should be placed before or after the source sheet. I want it to be placed on the right side of the copied sheet, therefore I select (move to end)<\/strong>.<\/p>\n\n\n\n

At the bottom-left corner, there is a checkbox called Create a copy<\/strong>. You have to select it if you want to duplicate the source sheet, otherwise, Excel will just move the sheet.<\/p>\n\n\n\n

In this case (one sheet), nothing will happen because the sheet can’t be moved after itself.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After you click the OK<\/strong> button, a new sheet will appear. It’s a copy of the source sheet with the same name, and number 2 in parentheses because sheet names have to be unique inside a single workbook.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now, you have two identical sheets with different names.<\/p>\n\n\n\n

To change the name of the new one, right-click it and select Rename<\/strong>. You can also double-click it to make the field editable.<\/p>\n\n\n\n

Type a new name and press Enter<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Copy a Worksheet Between Different Workbooks<\/h2>\n\n\n\n

You are not restricted to copying a sheet inside a single workbook. You can copy sheets between multiple workbooks. The condition is that both workbooks have to be opened at the same time.<\/p>\n\n\n\n

To copy a sheet from one workbook to another, right-click the sheet tab and select Move or Copy<\/strong>, the same way as you did before.<\/p>\n\n\n\n

But this time, in the To book:<\/strong> drop-down list select the workbook where you want to copy the sheet. Don’t forget to check that you Create a copy<\/strong> checkbox, otherwise the sheet will be moved to the new workbook.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you choose (new book)<\/strong>, Excel will create and open a new workbook with the copied sheet.<\/p>\n\n\n\n

Copy Sheets by Dragging<\/h2>\n\n\n\n

There is another, quicker way to make a copy of a worksheet.<\/p>\n\n\n\n

While holding the Ctrl<\/strong> key, drag the sheet tab to the right.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This way of copying the sheet is quicker but gives you the same result.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Link Cells Between Sheets with a Formula<\/h2>\n\n\n\n

Let’s say, you have the Source<\/strong> sheet and the new empty Destination<\/strong> sheet. Now, you want to create a link from cell A1<\/strong> in the Source sheet to the Destination sheet.<\/p>\n\n\n\n

You can do it with the equality operator<\/a> (=<\/strong>).<\/p>\n\n\n\n

Enter the name of the source file and cell you want to link to and place it to cell A1<\/strong> in the destination file:<\/p>\n\n\n\n

=Source!A2<\/code><\/pre>\n\n\n\n
If the name of the sheet is too long, you can enter the equal sign in cell A1<\/strong> in the Destination<\/strong> sheet, click the tab of the Source<\/strong> sheet, and then A2<\/strong>. This operation will enter the formula.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
It’s time to see whether this link works. Change the cell value in the source sheet.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Now, you can see that the value changed in the Destination<\/strong> sheet.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Link Range Between Sheets<\/h2>\n\n\n\n
In the last step, we linked a single cell. But you can also do it to entire ranges<\/a>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This range is linked and can be modified only in the source sheet. You can’t delete or copy any value in the B1<\/strong> to D1<\/strong> range. If you try to modify one of these cells, it will result in an error:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Link Data Between Sheets with Paste Link<\/h2>\n\n\n\n
If you want to have a link to each value of the source sheet cell separately, and you also want to modify individual values in the destination sheet (this will break the link to this cell), you can use one of the pasting methods called Paste Link<\/strong>.<\/p>\n\n\n\n
First, select cells from the source sheet you want to have links to and copy them (Ctrl + C<\/strong>).<\/p>\n\n\n\n
Right-click cell A1<\/strong> in the destination sheet and select the Paste Link<\/strong> option.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
All these values are references to the source sheet.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
You can also do it between different workbooks.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Notice, that the link changed, and now there is information that the data is from a different workbook.<\/p>\n\n\n\n
If you close the source workbook, the link will be pointing to the absolute path of the sheet.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If you open the workbook with the source sheet, references will automatically change back.<\/p>\n\n\n\n
Link Data Only if The Condition is Met<\/h2>\n\n\n\n
So far, we copied all cells without any requirements. You can also require a condition to be met to display linked data.<\/p>\n\n\n\n
In this example, there is a bunch of numbers. Some of them are positive and some are negative.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We want to create a link to the value, only if the value is greater or equal to 0.<\/p>\n\n\n\n
For this task, use the following formula and enter it into a different worksheet.<\/p>\n\n\n\n
=IF(Numbers!A1>=0, Numbers!A1, “”)<\/p>\n\n\n\n
Autofill<\/a> this formula for a row and then for columns:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The positive values are copied as before, and the negative is replaced with space. You can, of course, change empty spaces to different values, for example, dashes.<\/p>\n","protected":false},"excerpt":{"rendered":"
You can move, copy or link cells between worksheets to organize your data the way you want. You can copy sheets in the…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCopy Data From One Sheet to Another in Excel<\/title>\n<meta name=\"description\" content=\"Copy data from one sheet to another in Excel easily. Learn how to duplicate sheets in the same workbook, move sheets between workbooks and more.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Copy Data From One Sheet to Another in Excel\" \/>\n<meta property=\"og:description\" content=\"Copy data from one sheet to another in Excel easily. Learn how to duplicate sheets in the same workbook, move sheets between workbooks and more.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-02-04T09:38:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-26T11:28:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Copy Data From One Sheet to Another in Excel\",\"datePublished\":\"2021-02-04T09:38:56+00:00\",\"dateModified\":\"2024-03-26T11:28:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/\"},\"wordCount\":911,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/\",\"name\":\"Copy Data From One Sheet to Another in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png\",\"datePublished\":\"2021-02-04T09:38:56+00:00\",\"dateModified\":\"2024-03-26T11:28:48+00:00\",\"description\":\"Copy data from one sheet to another in Excel easily. Learn how to duplicate sheets in the same workbook, move sheets between workbooks and more.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#primaryimage\",\"url\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png\",\"contentUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png\",\"width\":427,\"height\":372},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Copy Data From One Sheet to Another in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Copy Data From One Sheet to Another in Excel","description":"Copy data from one sheet to another in Excel easily. Learn how to duplicate sheets in the same workbook, move sheets between workbooks and more.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/","og_locale":"en_US","og_type":"article","og_title":"Copy Data From One Sheet to Another in Excel","og_description":"Copy data from one sheet to another in Excel easily. Learn how to duplicate sheets in the same workbook, move sheets between workbooks and more.","og_url":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/","article_published_time":"2021-02-04T09:38:56+00:00","article_modified_time":"2024-03-26T11:28:48+00:00","og_image":[{"url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Copy Data From One Sheet to Another in Excel","datePublished":"2021-02-04T09:38:56+00:00","dateModified":"2024-03-26T11:28:48+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/"},"wordCount":911,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/","url":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/","name":"Copy Data From One Sheet to Another in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png","datePublished":"2021-02-04T09:38:56+00:00","dateModified":"2024-03-26T11:28:48+00:00","description":"Copy data from one sheet to another in Excel easily. Learn how to duplicate sheets in the same workbook, move sheets between workbooks and more.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#primaryimage","url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png","contentUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/02\/move-or-copy.png","width":427,"height":372},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/copy-data-from-one-sheet-to-another\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Copy Data From One Sheet to Another in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5518"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=5518"}],"version-history":[{"count":6,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5518\/revisions"}],"predecessor-version":[{"id":15450,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/5518\/revisions\/15450"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=5518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=5518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=5518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}