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?

Over a million companies use Microsoft Excel, making it one of the most widely-used pieces of software in 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 (in OneDrive or other cloud storage platforms), it’s easier than ever to collaborate with colleagues and clients.

When you use Excel for the web in your browser, you can work with others in the same Excel workbook in real-time. This means there’s no need to send email attachments and new file versions back and forth.

Although spreadsheets have countless benefits, they can be time-consuming to work with, especially when you want to combine data from multiple files.

On top of that, 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.

These problems can be solved by linking your Excel files with Sheetgo.

It’s a no-code automation tool for spreadsheets that enables you to transfer data between different sheets automatically. Read on or skip to the step-by-step instructions.

Automated data transfer in Excel

In the past, if you wanted to move data between different Excel files, you’d have to copy-past data manually or write some code using VBA.

Even if you (or one of your colleagues) has coding skills, programming your own system in spreadsheets can cause problems along the line.

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

A more flexible, scalable, and efficient alternative is to connect your Excel files. Once they’re connected, you can push and pull data between different worksheets in multiple workbooks.

Why connect Excel files? The benefits of automation

When you connect Excel files with Sheetgo you can move data from one sheet to another, or from one workbook to another automatically.

You can:

  • Import Excel data from another sheet
  • Export data to another sheet or workbook
  • Replace copy-pasting with an automated system
  • Prevent manual errors
  • Save time
  • Get up-to-date and accurate data
  • Control access to private information
  • Improve data oversight
  • Replace large Excel files with an efficient system of connected spreadsheets
transfer-data-from-one-sheet-to-another-in-excel

How to link files and sync data in Excel

Follow the steps below to connect your Excel files and transfer data from one sheet to another automatically.

Step 1: Open Sheetgo

Log in to 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.

It’s possible to 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 2: 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 3: 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 4: 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.

When 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.

Your data is transferred

As you can see, Sheetgo now 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 5: 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.

Many macro actions in Excel can be replaced with Sheetgo: test it out to see how it works.

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! 

As well as automating your data flows in Excel, Sheetgo enables you to import and export data from Excel to Google Sheets or from CSV to Excel and vice versa. Check out our other posts below for more spreadsheet automation ideas.

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

You may also like…