When you need data from another Excel file, copy and paste is usually fastest, but import tools let you pull in large datasets and keep them updated automatically

The simplest way to move data between Excel files is to open both files side by side, select what you need in the source file, and paste it into the destination file. This works well for small amounts of data — a few rows or columns you need once. For larger datasets, or data you need to refresh regularly, Excel's built-in import features save time and reduce mistakes.

The method you choose depends on how much data you're moving, whether it comes from another Excel file or a different format (like a CSV or text file), and whether you need the connection to stay live so updates in the source file show up automatically in the destination file.

Key Takeaways

  • Copy and paste works for small datasets but doesn't create a link between files, so changes in the source won't show up in your destination file.
  • The Get & Transform feature (called Power Query in Excel for Windows) lets you import data from another Excel file and refresh it whenever the source changes.
  • Linking cells with formulas like =INDIRECT() or external references creates a live connection, but breaks if you move or rename the source file.
  • CSV and text files can be imported using the Text Import Wizard, which lets you control how columns are split and formatted.
  • For very large files or data you use repeatedly, consolidating into a single workbook reduces file size and makes backups simpler.

Copy and paste for data you only need once

Open the source file (the one with the data you want) and the destination file (where you want it to go) in separate windows. In the source file, click the first cell of the data you need, then hold Shift and click the last cell to select the entire range. Right-click and choose Copy, or press Ctrl+C on Windows or Command+C on Mac.

Switch to the destination file and click the cell where you want the data to start. Right-click and choose Paste, or press Ctrl+V on Windows or Command+V on Mac. The data appears in your destination file exactly as it was in the source. This method is fast and requires no setup, but the two files stay separate — if someone updates the source file later, your destination file won't change.

Use Get & Transform to import and refresh data automatically

If you need data from another Excel file and want it to update when the source changes, use Get & Transform (in Excel for Windows) or Power Query (the same feature, different name). Open the destination file where you want the data to land. On the Data tab, click Get Data, then select From File, then From Excel.

Navigate to the source file and click Open. A preview window shows you the sheets and tables in that file. Select the sheet or table you need and click Load. Excel imports the data and creates a connection to the source file. Now, whenever you open the destination file, you can right-click the imported data and choose Refresh to pull in any changes from the source file.

This method works best when both files stay in the same location on your computer or network. If you move or rename the source file, the connection breaks and you'll need to update it manually. To fix a broken connection, right-click the imported data, choose Edit Query, and point it to the new file location.

Link cells with formulas for live connections between files

You can create a formula that pulls data from another Excel file without importing it. Open the destination file and click the cell where you want the data. Type an equals sign, then switch to the source file and click the cell you want to reference. Press Enter. Excel creates a formula like =[SourceFile.xlsx]Sheet1!A1 that pulls the value from that cell in the source file.

This creates a live link — if the value in the source file changes, it updates in your destination file automatically. The downside is that the formula breaks if you move, rename, or delete the source file. Before moving a source file, save the destination file so Excel can update the file path in the formula. If a link breaks, right-click the destination file, choose Edit Links, and point it to the new location.

For multiple cells, use the INDIRECT function to build a formula that references a range. This is more advanced and requires you to type the file path and cell range as text, but it's more flexible if you reorganize your files later.

Import CSV and text files using the Text Import Wizard

If your source data is in a CSV (comma-separated values) or text file rather than an Excel file, open the destination Excel file. On the Data tab, click Get Data, then select From File, then From Text/CSV. Navigate to the file and click Import.

The Text Import Wizard opens and shows you a preview of how the data will be split into columns. By default, Excel detects whether your file uses commas, tabs, or other characters to separate columns. If the preview looks wrong, change the delimiter at the top of the wizard. You can also set the data type for each column (text, number, date) to prevent Excel from misinterpreting your data. Click Load when the preview looks correct.

Consolidate multiple files into one workbook to simplify backups

If you regularly work with data from several Excel files, consider consolidating them into a single workbook with multiple sheets. This reduces the number of files you need to back up, makes it easier to find related data, and eliminates broken links if you move files around.

Open a new blank Excel file. For each source file, open it, select all the data you need (Ctrl+A selects the entire sheet), copy it, then switch to your new file and paste it into a new sheet. Right-click the sheet tab at the bottom and choose Rename to give it a descriptive name like "Sales Data" or "Inventory." Once all your data is in one file, you only need to back up that single file instead of tracking multiple sources.

Avoid common mistakes when importing data

The most common problem is forgetting to save the destination file after importing. If you import data using Get & Transform and then close Excel without saving, the import settings are lost and you'll have to set them up again next time. Save your file when ready after importing.

Another mistake is moving or renaming the source file without updating the destination file first. If you use linked formulas or Get & Transform connections, update the links before you move anything. Right-click the imported data or linked cells, choose Edit Links or Edit Query, and confirm the file path is correct. If you've already moved the file, you can update the path from this same menu.

Finally, watch out for formatting changes when you import. Excel sometimes converts dates to numbers or text to dates unexpectedly. After importing, scan the first few rows to make sure numbers look like numbers, dates look like dates, and text hasn't been cut off. If something looks wrong, undo the import, and use the Text Import Wizard or Get & Transform settings to specify the correct data type for that column.

Frequently Asked Questions

What's the difference between copying data and using Get & Transform?

Copy and paste moves the data once and breaks the connection to the source file. Get & Transform creates a link that stays active, so you can refresh the data later if the source file changes. Use copy and paste for one-time imports; use Get & Transform if you need to update the data regularly.

Can I import data from an Excel file on someone else's computer?

Yes, if the file is shared on a network drive or cloud storage like OneDrive or SharePoint. Use Get & Transform and navigate to the shared location. The link will work as long as the file stays in that location and you have permission to access it. If the file moves or is deleted, the link breaks.

What happens if I import data and then the source file gets deleted?

The data stays in your destination file, but you can't refresh it anymore. If you try to refresh, Excel shows an error. The data itself doesn't disappear — it's already copied into your file. You just lose the ability to pull in new changes from the source.

Can I import data from a Google Sheet into Excel?

Not directly through Get & Transform. read the Google Sheet as an Excel file or CSV first, then import that file into Excel. Alternatively, you can copy and paste from Google Sheets into Excel, though formatting sometimes changes during the transfer.

How do I know if my import is using the right data type for each column?

After importing, click a cell in each column and look at the formula bar to see what Excel thinks it contains. If a number column shows text, or a date column shows a number, the data type is wrong. Use the Text Import Wizard or Get & Transform settings to specify the correct type before importing.