Graphed LogoGraphed
Login

How to Copy Data from One Tab to Another in Excel

Cody Schneider•

Moving data from one tab (or sheet) to another in Microsoft Excel is a foundational skill, but there’s more than one way to do it. You can create a simple static copy, or you can build a dynamic link that updates automatically when the source data changes. This guide covers a range of methods, from the basics of copy and paste to powerful formulas like VLOOKUP and XLOOKUP that will help you work more efficiently.

Method 1: The Basics - Simple Copy and Paste

This is the most straightforward method and is perfect for when you need a one-time, static copy of your data. A static copy means that if the original data on the first tab changes, the copied data on the second tab will not update.

Step-by-Step Instructions

  1. Navigate to the source sheet containing the data you want to copy.

  2. Select the cells, row(s), or column(s) you need. You can click and drag, or use keyboard shortcuts like Ctrl + A to select all data in a contiguous range.

  3. Copy the selected data using the shortcut Ctrl + C (or Cmd + C on Mac). You'll see a dashed line moving around your selection.

  4. Click on the tab for the destination sheet where you want to place the data.

  5. Select the single cell where you want the top-left corner of your copied data to begin.

  6. Paste the data using the shortcut Ctrl + V (or Cmd + V on Mac).

Pro-Tip: Use Paste Special

Sometimes you don't want to copy everything - just the values, the formulas, or the formatting. This is where Paste Special comes in. After copying your data (Step 3), right-click on your destination cell and look for "Paste Special."

Here are a few of the most useful options:

  • Values (V): Pastes only the results of formulas, not the formulas themselves. This is great for freezing a report at a specific point in time.

  • Formulas (F): Pastes the formulas exactly as they were, maintaining their relative references.

  • Formatting (R): Copies only the cell styling, like colors, borders, and number formatting, without any of the data.

  • Transpose (T): A handy trick that pivots your data, turning rows into columns and columns into rows.

Method 2: Dynamically Link to a Single Cell

If you want the data on your second sheet to update automatically when the source sheet changes, you need to create a dynamic link. The simplest way to do this is with a direct cell reference formula.

This is perfect for dashboard reports where you want to pull summary totals from a separate data tab. For example, you might want to show the "Grand Total Sales" from your "SalesData" sheet onto your "Summary" sheet.

Step-by-Step Instructions

  1. Navigate to your destination sheet and select the cell where you want the linked data to appear.

  2. Type the equals sign (=) to begin your formula.

  3. Without pressing Enter, click on the tab for your source sheet.

  4. Click on the single cell containing the data you want to link.

  5. Press Enter.

Excel will automatically write the formula for you. It will look something like this:

Here’s the breakdown:

  • The = tells Excel you're starting a formula.

  • 'SalesData' is the name of the source sheet. Excel adds single quotes if the sheet name contains spaces or special characters.

  • The ! (exclamation mark) separates the sheet name from the cell reference.

  • E25 is the cell on the 'SalesData' sheet that is being referenced.

Now, any time the value in cell E25 on the 'SalesData' sheet is updated, the cell on your summary sheet will update instantly.

Method 3: Dynamically Link to a Range of Data

What if you want to mirror an entire table or range of data from another sheet? You can extend the single-cell reference method. This is useful for creating a view-only version of a dataset for a presentation or report.

While you could manually create a link in one cell and drag the fill handle across and down, a more robust way (in newer versions of Excel) is to use a dynamic array formula.

Step-by-Step Instructions

  1. Go to your destination sheet and select the top-left cell where your data range should start.

  2. Type the equals sign (=).

  3. Navigate to the source sheet and select the entire range of data you want to display.

  4. Press Enter.

Excel creates a formula referencing the entire range, like so:

The data from A1:F50 on the "RawData" sheet will now "spill" into your current sheet. A thin blue line will appear around the range, indicating it’s a dynamic array. Any changes made to the source range will be reflected here automatically.

Method 4: The Classic - Look Up Data with VLOOKUP

Often, you don't need to copy an entire table. Instead, you need to pull specific information from one tab to another based on a matching value. For example, you have a list of Order IDs on one sheet and you want to pull in the "Customer Name" associated with each order from a master data table on another sheet. This is the perfect job for VLOOKUP (Vertical Lookup).

VLOOKUP searches for a value in the first column of a table and returns a corresponding value from a column you specify within that same table.

Step-by-Step Instructions

Imagine you have two sheets:

  • "Orders": Contains a list of 'OrderID's in Column A. You want to fill in the 'CustomerName' in Column B.

  • "MasterData": Contains a master table where Column A has 'OrderID's and Column C has 'CustomerName's.

  1. On the "Orders" sheet, click inside cell B2 (the first cell where you want the customer name to appear).

  2. Type the VLOOKUP formula:

  1. Press Enter. The customer name corresponding to the OrderID in A2 should appear.

  2. Click on cell B2 again, and drag the small square (the fill handle) at the bottom-right corner of the cell down to apply the formula to the rest of the column.

Breaking Down the VLOOKUP Formula

Let's make sense of that formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • A2 (lookup_value): This is the value you are searching for. In our case, it's the specific OrderID on the "Orders" sheet.

  • MasterData!A:C (table_array): This is the range of data on the other tab where Excel should look. Important: The column containing your lookup value (OrderID) must be the first column in this range. Here, we selected columns A through C on the "MasterData" sheet.

  • 3 (col_index_num): This tells Excel which column to return the value from, counting from the left of your 'table_array'. Since 'CustomerName' is in column C (the 3rd column of our A:C range), we use the number 3.

  • FALSE (range_lookup): This argument requires an exact match. 99% of the time, you will want to use FALSE to prevent incorrect matches.

Method 5: The Modern Champion - Look Up Data with XLOOKUP

If you have Microsoft 365 or a newer version of Excel, XLOOKUP is a more flexible and powerful successor to VLOOKUP. It overcomes many of VLOOKUP's limitations.

Why XLOOKUP is Better:

  • It can look to the left. Unlike VLOOKUP, the lookup column doesn't have to be the first one in your table.

  • It's simpler. You specify a lookup column and a return column separately, so you don't have to count columns.

  • It defaults to an exact match. You don't have to remember to type FALSE.

Step-by-Step Instructions (XLOOKUP)

Let's use the same "Orders" and "MasterData" scenario. Let's say on the "MasterData" sheet, 'OrderID' is in column D and 'CustomerName' is in column B.

  1. On the "Orders" sheet, click inside cell B2.

  2. Type the XLOOKUP formula:

  1. Press Enter, then drag the formula down.

Breaking Down the XLOOKUP Formula

=XLOOKUP(lookup_value, lookup_array, return_array)

  • A2 (lookup_value): The OrderID we're looking for.

  • MasterData!D:D (lookup_array): The column where Excel should search for that OrderID.

  • MasterData!B:B (return_array): The column from which Excel should return the corresponding value (the customer's name).

It's that clean and simple. You define the lookup-in column and the return-from column, and XLOOKUP handles the rest.

Which Method Should You Use?

Choosing the right technique depends entirely on your goal.

  • For a one-time snapshot of data that shouldn't change, use Copy and Paste (preferably with Paste Special → Values).

  • To create a dashboard or summary report that pulls in key totals, use a direct single-cell link.

  • To create an exact mirror of a dataset that updates automatically, use a direct range formula.

  • To pull in specific data points from a larger table based on a common identifier (like an ID, email, or name), use XLOOKUP (or VLOOKUP if you're on an older version of Excel).

Final Thoughts

Mastering these different ways to move data between Excel tabs transforms you from a casual user into someone who can build organized, efficient, and dynamic reports. Whether you’re creating a simple summary or joining data from multiple lists, these techniques are essential for clean and accurate spreadsheet management.

While these Excel functions are powerful for organizing data once you have it, the most time-consuming part of reporting is often the manual work of just getting data from different platforms - like Shopify, Google Analytics, or Facebook Ads - into a spreadsheet in the first place. We built Graphed to completely eliminate that process. Instead of downloading and wrangling CSVs, you simply connect your data sources once, then ask for the charts and dashboards you need in plain English. Your dashboards update in real time, so you can spend your time acting on insights, not just pulling reports.