How to transfer data from one sheet to another in Excel

Written by
Laura Tennyson

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 the Sheetgo add-in for Excel.

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 the Sheetgo add-in, 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 1

How to link files and sync data in Excel

Follow the steps below to use the Sheetgo add-in to connect your Excel files and transfer data from one sheet to another automatically.

Step 1:  Install the Sheetgo add-in for excel

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.

Sheetgo add-in

Now you can install the Sheetgo add-in for Excel from the Microsoft Appsource. Once installed, you should be able to access the Sheetgo add-in within any Excel file by clicking on the Sheetgo logo located in the top right-hand corner of your screen, as shown below.

transfer data from one sheet to another in excel 2

Step 2: Select your Excel source file

Open up the Excel file containing the data you wish to transfer to another file. Open the Sheetgo add-in for Excel and log in. Now you can begin connecting your Excel sheets to transfer your data. 

Start by clicking Select data under the Export data option.

transfer data from one sheet to another in excel 3

Now choose the tab where your data is located. Once finished, click Continue.

transfer data from one sheet to another in excel 4

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 your destination file

Scroll down until you reach the Send data section, and select Excel file.

transfer data from one sheet to another in excel 5

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, click on the Existing file option.
  • 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.

In this example, I will choose to create a new file within my Onedrive cloud storage. I will name my file “Third quarter sales” and rename the tab where my data will be transferred to as “2021”. Once finished, click Finish and save. 

 

transfer data from one sheet to another in excel 6
Step 4: Your data is transferred

As you can see, Sheetgo has created the connection. Click on the Run button to update your connection and complete the data transfer.

transfer data from one sheet to another in excel 7

To find your newly created Excel file, simply search for the new file in the storage system you selected. In my case, when I open my OneDrive folder, I can clearly see my new file.

transfer data from one sheet to another in excel 8

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

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.

Head back to the Excel add-in and click Automate.

transfer data from one sheet to another in excel 9

You can edit the days and hours your connection updates itself. Click Save when you’re happy with your custom schedule.

transfer data from one sheet to another in excel 10

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 up your Excel add-in and locate your connection. 
  • Select the connection you want to edit and go to More options (⋮) > Edit connection.
  • Under the Source data section, 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.

transfer data from one sheet to another in excel 11

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.

In the Excel add-in you can only filter your data by condition. If you want to access the other two filter options, it’s best to carry out your connections in the Sheetgo web app. If you’d like to learn more, check out the Sheetgo help center to find out How to use Sheetgo filters to transfer specific data.

How to filter data from one sheet to another in Excel

  • Open your Excel add-in and select whether to export or import data and choose the source file and tab. Press Continue
  • Scroll to the Filter data section. Here, select the Filter by condition option.
transfer data from one sheet to another in excel 12

Here, you can choose how you want to filter your data. For example, I only want to transfer the data relating to the sales of Product 1 only. To do this, I apply the following: 

  • Condition type: Data matches one condition (OR)
  • Data matches > Condition > Column: Column C: Product
  • Criteria > Text is exactly 
  • Value > Product 1

Once finished with your filter, click Continue. Now, only the rows of data that contain “Product 1” in column C will be transferred from my Excel.

transfer data from one sheet to another in excel 13
  • Continue by selecting your destination file. 
  • Once the connection has been made, click Finish and save.

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 Excel add-in, and locate your connection
  • Head to More options (⋮) > Edit connection.
  • Scroll to the Destination section, and expand Settings. Slide over the button labelled Append data
  • Click Finish and save.
transfer data from one sheet to another in excel 14

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…