How to Copy Data from One Excel Sheet to Another

Moving data from one Excel sheet to another is one of the most common tasks you'll ever perform in a spreadsheet. This article walks you through several methods for doing just that, from the basic copy-and-paste to more advanced techniques that keep your data in sync automatically.

The Classic: Simple Copy and Paste

This is the method everyone knows, but it’s the foundation for everything else. It's perfect for a quick, one-time transfer of data where the original formatting or formulas aren't a major concern.

Let's say you have sales data on a sheet named "Q1 Sales" and you want to move it to a summary sheet named "Annual Report."

1. Open your Excel workbook and navigate to the "Q1 Sales" sheet.
2. Click and drag your mouse to select the range of cells you want to copy. You can also click the first cell of your data, hold down the Shift key, and click the last cell to select the entire range.
3. Press Ctrl + C on your keyboard (or Cmd + C on a Mac) to copy the data. You'll see a dashed, moving line (often called "marching ants") around your selection.
4. Click on the tab for the "Annual Report" sheet at the bottom of the screen.
5. Click the single cell where you want the top-left corner of your copied data to appear.
6. Press Ctrl + V (or Cmd + V on a Mac) to paste the data.

Just like that, your data is in the new sheet. But what happens when simply pasting everything - formulas, formats, and values - isn't what you need?

Going Deeper with Paste Special

Standard pasting brings everything over, which can sometimes mess up your destination sheet's formatting or create errors with formulas. This is where Paste Special becomes your best friend. After you’ve copied your data (Step 3 above), navigate to your new sheet, right-click on the destination cell, and choose "Paste Special."

This opens a dialog box with several powerful options:

• Values: This is arguably the most useful option. It pastes only the calculated results of your cells, leaving behind all formulas and formatting. This is perfect when you want a static snapshot of your data without bringing over potentially complex formulas that could break in the new location.
• Formulas: Pastes the formulas exactly as they were. Excel is smart enough to adjust relative cell references (e.g., a formula summing A1:A10 will adjust if you paste it in a new column).
• Formats: If you've spent time styling a report with specific fonts, colors, and borders, this option lets you copy only that formatting to apply it to another dataset.
• Column Widths: Ever paste data only to find all the columns are squished and you have to resize them manually? Use this option to copy the exact column widths from your source sheet, saving you a tedious cleanup job.
• Transpose: A fantastic tool for reshaping your data. This option flips your data, turning rows into columns and columns into rows. It’s incredibly useful for reorganizing data for different charting or analysis needs.

You can mix and match these, too. A common workflow is to first copy and paste using "Column Widths," then immediately paste again in the same spot using "Values."

Cloning an Entire Sheet with "Move or Copy"

Sometimes you don't just need the data, you need the entire sheet - layout, charts, formatting and all - duplicated. Instead of trying to select and copy everything manually, it's far easier to clone the sheet.

1. Right-click on the tab of the sheet you want to copy (e.g., "Q1 Sales").
2. From the menu, select Move or Copy...
3. A dialog box will appear. To make a copy within the same workbook, check the box at the bottom that says Create a copy. This is the most important step! If you forget it, Excel will move the sheet instead of copying it.
4. In the "Before sheet:" list, you can choose where you want the new, copied sheet to be placed.
5. Click OK.

You’ll immediately see a new sheet tab appear, named something like "Q1 Sales (2)". It’s an exact, independent replica of the original sheet. You can rename it and modify it without affecting the original.

You can also use this feature to copy a sheet to a completely different, already open Excel workbook. In the "Move or Copy" dialog box, just use the "To book:" dropdown to select the destination workbook.

Creating a Live Link: Referencing Data from Another Sheet

The methods above create a static copy. If the data on your original sheet changes, your copied data will be outdated. If you need a summary sheet that always reflects the most current data from another source sheet, you should link to it with a simple formula.

This creates a dynamic reference, pulling the value live from the source cell. If the source changes, the reference updates automatically.

Let’s say you want cell B2 on your "Annual Report" sheet to always show the total sales value from cell F50 on your "Q1 Sales" sheet.

1. Navigate to your "Annual Report" sheet.
2. Click on cell B2.
3. Type the equals sign: =
4. Now, without typing anything else, click on the "Q1 Sales" sheet tab at the bottom.
5. Click on the source cell, F50.
6. You'll see the formula bar now reads: 'Q1 Sales'!F50
7. Press Enter.

You'll be taken back to your "Annual Report" sheet, where cell B2 now displays the value from 'Q1 Sales'!F50. Now, any changes made to cell F50 on the Q1 sheet will instantly be reflected here.

This technique is the foundation of powerful Excel dashboards. You can pull key metrics from dozens of different data sheets into one clean summary report that is always up-to-date.

For Automation: Using Power Query

If you find yourself repeatedly copying and cleaning the same data from one sheet to another every week, it’s time to automate. Power Query (called "Get & Transform Data" in recent Excel versions) is an incredibly powerful tool for this.

It lets you create a connection to a data range in one sheet and load it into another. Best of all, it remembers any cleaning and transformation steps you apply and can be refreshed with a single click.

A Simple Power Query Example:

1. First, make sure your source data on the "Q1 Sales" sheet is formatted as an official Excel Table. To do this, click anywhere inside your data and press Ctrl + T. Click OK.
2. With a cell in your new Table selected, go to the Data tab on the Ribbon.
3. In the "Get & Transform Data" section, click From Table/Range.
4. The Power Query Editor window will open. Here you can perform all sorts of data cleaning (like removing columns or filtering rows), but for now, we'll keep it simple.
5. Click the Close & Load dropdown button in the top-left, and select Close & Load To...
6. In the "Import Data" dialog box, choose how you want to see the data (Table is fine) and where to put it (New worksheet or Existing worksheet).
7. Click OK.

Excel will create a new sheet containing a direct link to your source table. The main advantage? When you add new data to your "Q1 Sales" table, you just have to go to the Power Query output sheet, right-click the table, and hit Refresh. Power Query will automatically go back to the source, grab all the new data, apply any steps you defined, and update your table.

Which Method Should You Choose?

With several options available, here’s a quick guide to help you decide:

• For a quick, one-off data transfer where formatting isn't critical, use the classic Copy and Paste.
• When you need control over what you're pasting (e.g., just the final numbers or just the styles), use Paste Special.
• To create an exact duplicate of an entire worksheet, use the Move or Copy Sheet feature.
• To create a dashboard or summary that updates automatically when source data changes, use direct cell linking.
• For a repeatable, automated process of pulling and cleaning data from one location to another, use Power Query.

Final Thoughts

Mastering these different methods for moving data between Excel sheets allows you to work more efficiently, whether you're creating a simple static report or a complex, dynamic dashboard. Choosing the right technique for the task at hand saves time and prevents the frustration of broken formulas and messy formatting.

While Excel is a powerful tool, you can see how much manual work is still involved, especially when you have to first export data from other platforms like Shopify, Google Analytics, or Salesforce just to get it into a spreadsheet. The entire reporting process - from exporting CSVs to combining them in Excel - can take hours. With Graphed, we automate that entire headache. We built it to connect directly to your data sources, so you can build live, real-time dashboards just by describing what you want to see in plain English, skipping the spreadsheet wrangling entirely.