How to transfer data from one sheet to another in Excel

how-to-transfer-data-from-one-sheet-to-another-in-excel-connect-excel-files

Written by Laura Tennyson

Jan 11, 2021

Want to export data from one Excel file to another?

Microsoft Excel is an indispensable program for millions of businesses all around the world. Despite the huge variety of other software now available, spreadsheets are still the most powerful and versatile tool for storing, managing and processing data.

And now that you can store Excel files online (on OneDrive or other cloud storage platforms), it’s easier than ever to collaborate with colleagues and clients on the same files. Multiple people can now work together in the same Excel workbook in real-time without sending email attachments and new file versions back and forth.

Although they have countless benefits, spreadsheets can be time-consuming to work with, and data governance is difficult. One Excel file can store up to 2GB of data. When you start multiplying that for every colleague and department, it doesn’t take long to build up large silos of unconnected and potentially duplicated information.

Both of these problems can be solved by linking Excel files. When you connect one workbook to another, you can create an automated system to transfer data from one sheet to another in Excel.

This means you can move data automatically between different worksheets, and different workbooks.

Why connect Excel files?
  • Import data from another sheet in a different Excel file.
  • Export data to another sheet or workbook.
  • Replace copy-pasting with an automated system.
  • Prevent manual errors.
  • Free up time with automated Excel files.
  • Get up-to-date, accurate data from across the company.
  • Control access to private information.
  • Share data without sharing your spreadsheet.
  • Improve data oversight.
  • Extract specific information for analysis.
  • Replace large, slow, and complex Excel files with an efficient system of connected sheets.
transfer-data-from-one-sheet-to-another-in-excel

How do I transfer data from one sheet to another in Excel?

  1. Manually
  2. With coding: VBA and macros
  3. Sheetgo
Manual data transfer

The first option, copy-pasting data from file to file, is tedious and unreliable. Thanks to the cloud, there’s no need to do this anymore. By linking Excel files online, you can send data from spreadsheet to spreadsheet automatically.

Coding with VBA or macros

This is an option for those with programming skills. Using Visual Basic for Applications (VBA) you can write code to create a macro. Macros work like small programs inside your spreadsheets. They’re ideal for carrying out routine tasks like formatting. You can record a macro from inside an Excel file using standard spreadsheet functions. If you can code using VBA, you can also send data from one sheet to another.

The downside to this is that each time you want to change the system, you’ll need to adjust the code or the macros. If the person who designed the spreadsheet leaves the company, the rest of the team may be unable to use or adapt the spreadsheet.

Sheetgo

What is Sheetgo? Sheetgo is a no-code tool that enables anyone to connect spreadsheets and transfer data from one sheet to another in Excel. You can push and pull data between different worksheets in the same workbook or between different Excel workbooks. On top of that, you can import and export data from Excel to Google Sheets or CSV files and vice versa.

You don’t need any technical skills to use Sheetgo. Just create a connection and schedule automatic updates to transfer data from one sheet to another as frequently as you want.

How to sync data in Excel

Open Sheetgo by clicking on the blue button below.

You will see that you have the option to log in with your Microsoft, Google, or Dropbox account.

You can link Excel files stored on the same cloud storage folder or on different platforms. This means you can share data and connect to colleagues’ files stored on OneDrive, SharePoint, Google Drive, or Dropbox.

Step 1: Select your Excel source file

Once you have opened the Sheetgo web app and logged in, click the blue Connect button.

Give your Untitled workflow a name at the top of the screen so you can locate it and make changes later.

In this example, I’ll name my workflow Transfer data between Excel files.

A Sheetgo workflow can be as simple or as complex as you want. You can start with one simple connection that sends data from file A to file B.

It’s then easy to build on the system later, adding more connections to create an interconnected system that sends data back and forth between multiple files.

Under Select source data, click Excel file.

transfer-data-from-excel-to-excel-select-source-file

Click +Select file and locate the file from your cloud storage.

Are your files stored on your computer?

If you want to connect files that are stored locally (on your computer) you can set up an automated system to back up and sync files from your desktop to your online cloud storage service.

This enables you to create automated data flows using Sheetgo. It also keeps your files secure and allows you to access them from anywhere. Learn more.

Step 2: Select the source tab

Your source file appears inside Sheetgo.

If the workbook contains multiple tabs, you now need to select the correct tab (worksheet).

Under File tab, select the correct sheet using the dropdown menu.

connect-excel-to-excel-select-source-tab

Step 3: Select your destination file

When you have selected your source file and tab, click Continue.

Under Send data, select Excel file.

Choose which Excel file you want to export (or send) the data to.

  • To transfer the data to an existing Excel spreadsheet in your cloud storage, go to Existing file+Select file.
  • If you want to transfer the data to a new Excel workbook, select New file. Under File name, enter a name for the new workbook.

If you choose New file, Sheetgo will create a new workbook for you and save it to the main “root” folder in your selected cloud storage. To change which folder it is saved to, click change destination folder.

Note that when Sheetgo transfers data, your source file remains intact. In other words, data is copied from the source to the destination, but nothing is deleted.

Click Finish and save to create the connection between your Excel files. Sheetgo will now start transferring the data.

The data is transferred

Sheetgo creates the connection.

excel-to-excel-connection-complete-webapp

Click on Workflow to see how your files are connected.

connect-excel-to-excel-network-view

Go to Workflow or Files and double-click on the destination workbook to open it.

You will see it contains a new tab (worksheet) with the imported data.

The tab name starts with “Sheetgo” to indicate that this is a connected tab, reminding you not to edit this data. Every time you update the connection, the data in this tab will be refreshed with the data from the source file.

If you want to manipulate or start editing the imported data, we recommend you move it to another tab using formulas.

transfer-data-from-one-sheet-to-another-excel-destination-tab

To update (refresh) the data in the destination tab at any time, just open Sheetgo and click Run on the menu bar.

Step 4: Automate Excel to Excel data transfer

To ensure you’ve always got the latest data in your destination file without having to open Sheetgo, schedule automatic updates.

Click Automate on the menu bar and choose how often and at what times of the day or week you would like it to be updated.

connect-excel-to-excel-automate-settings

How can I transfer data from multiple worksheets into one?

In this example I’ve shown you how to set up a simple connection between two Excel files to transfer data from one sheet to another in Excel.

It’s also possible, however, to consolidate data from multiple Excel source files into one central master sheet for reports or dashboards.

To add more source files to the connection you have already created:

  • Open the workflow inside Sheetgo and click on Connections.
  • Select the connection you want to edit and go to ⋮ > Edit connection.
  • Click +Add another source file and select the source file and tab.

If you have a large number of source files, you can also consolidate from a folder. When you drop a new file into the folder, it will be included in the next update automatically.

For full instructions, check out our in-depth guide on how to merge Excel files.

connect-excel-to-excel-add-another-source-file

What if I only want to transfer specific data?

No problem. If you don’t want to import the entire contents of the source tab to the destination tab, it’s easy to apply a filter to your connection. Sheetgo allows you to filter Excel data by condition (such as column, or value) or by writing a query.

Note that to transfer filtered data from one Excel file to another using Sheetgo, your Excel files must be stored in Google Drive. 

How to filter data from one sheet to another in Excel

  • Open Sheetgo and go to +Create workflow > +Connect
  • Give your Untitled workflow a name at the top of the screen. Here I’ll name my example workflow Filter Excel to Excel.
  • Under Select source data, click on Excel file.
  • Click +Select file to locate the source file from Google Drive. This is the file containing the data you want to filter and export to another spreadsheet.
  • Select the file from Google Drive.

In the example below, you can see that I want to filter data from an Excel file called Apartment sales May.

  • Select the file tab. This is the specific tab (sheet) containing the data you want to export, or transfer, to another Excel file.

Here I’ll select the tab called California.

how-to-filter-excel-data-from-one-sheet-to-another
  • Click Continue.
  • Under Filter data, choose how you want to filter your data.

Here I’m going to select Filter by condition.

how-to-filter-excel-data-from-one-sheet-to-another-select-filter-type

My Excel source data (file: Apartment Sales May, tab: California) contains a list of property sales in California. I want to filter out only the properties with 2 bathrooms.

As you can see, this information is in Column F of my spreadsheet.

how-to-filter-excel-data-from-one-sheet-to-another-filter-by-column

To transfer only the properties with 2 bathrooms I select the following:

  • Condition type: Data matches one condition (OR)
  • Data matches > Condition > Column: Column F: baths
  • Criteria > Number is equal to 
  • Condition > Value > 2
transfer-filtered-data-from-one-excel-sheet-to-another

Send the filtered data to another Excel file

When you have selected the conditions for filtering your data, click Continue.

  • Under Select destination choose which file type you want to send the filtered data to.
  • Select Excel file.
  • Choose if you want Sheetgo to transfer the data to a new file, or transfer the data to an Excel file that’s already saved to your Google Drive.
  • If required, change which folder the new file is saved to by clicking Change destination folder.

Here I want to transfer the filtered Excel data to a new workbook.

Under File name, I call it Two-bathroom apartment sales May.

  • Click Finish and save to create the connection.

Sheetgo creates the connection and the destination file opens.

Here you can see my new XLSX file: Two-bathroom apartment sales May.

Filtered data has been imported into the tab Sheetgo_California.

The imported data only contains properties with two bathrooms, as indicated in Column F: Baths.

transfer-filtered-data-from-one-excel-file-to-another-connection-complete

Tip: Want to create a historical chart from your imported Excel data?

Each time you update the connection, Sheetgo will refresh the data in the destination tab. If you want to preserve previous data entries, enable the Append data setting.

To switch on Append, open the workflow, and go to Connections.

Select the connection you want to edit and go to ⋮ > Edit connection.

Click on Destination > Edit > Settings.

Get full instructions on how to append data in Excel here.

Can I use macros with Sheetgo?

Sheetgo is not compatible with .xlm or .xlsm files but it can be used with Google Sheets files containing macros.

Some macro actions in Excel can be replaced with Sheetgo. For more guidance on how to automate your spreadsheet work, talk to our experts.

Automate your Excel work today

Thanks for reading our guide on how to connect your Excel files and transfer data from one sheet to another! Check out our other posts below for more spreadsheet automation ideas.

If you found this guide useful, please share it with your fellow spreadsheet users via the social media buttons on the left.

You may also like…

Share This