How to automatically transfer data from one sheet to another in Excel

by May 15, 2020

Automatically transfer Excel data

Organizations that need to regularly transfer data between Excel workbooks can find it to be time-consuming and tedious. We are talking specifically about transferring data from one Excel file —or workbook— to another, meaning between separate Microsoft Excel files, or, workbooks, not tabs or sheets within the same file.

Automatically transfer data from one sheet to another in Excel and ensure accuracy and cohesion amongst your files. By automating processes you can stop wasting time on tedious work and free up time in your workday for more important tasks. Many organizations don’t just have raw data, they have carefully formatted their spreadsheets. It is important to be able to preserve the source formatting of a file when doing a data transfer.

How do you get two Excel workbooks to talk to each other and transfer data?

It is possible to do this in a few different ways.

  1. Manually
  2. Using Sheetgo
  3. Using Power Query

We, of course, don’t recommend using manual labor to transfer data and keep spreadsheets up to date. This is time consuming, tedious, and leaves you vulnerable to human error. Not to mention, in this day and age organizations use real-time data to make important decisions. Having up-to-date, accurate spreadsheets is essential.

Today we are covering how to automatically transfer data from one sheet to another in Excel while preserving the original formatting of the source. We will cover Microsoft Power Query and Sheetgo. Microsoft Power Query is sufficient for people who work with only Excel files, but this is rarely the case. Organizations who have a lot of moving parts work with various kinds of files. Sheetgo handles Excel, Google Sheets, and CSV files, and you can use it within Google Sheets or on the Sheetgo Web App.

What is Sheetgo?

Sheetgo allows users without technical skills to easily transfer data between Excel files and maintain the original format. This is a really great way for an organization to update various departments by distributing data from a master spreadsheet. Or, for individuals to contribute to a large workbook.

With Sheetgo you can schedule automatic updates. Sheetgo provides the flexibility to allow you to do a one-time transfer, or schedule hourly, daily, weekly, or monthly updates. It’s up to you.

To transfer data between Excel files you will need to have them uploaded to your Google Drive. Unless you have them stored in Dropbox, then you can immediately access your Excel files from within the Sheetgo web app.

Step 1: Install Sheetgo

Install Sheetgo here. Once inside the web app, click on Connect to start creating the connection between your two Excel files.

To start, choose your data source. Select the Excel spreadsheet and its specific tab you want to export to the other Excel file.

Choose it either directly from inside Dropbox, or follow the steps below to automatically add it to your Drive first.

Automatically add your Excel files to your Drive

Install Google’s backup and sync software. Create a folder on your desktop where you save all your Excel files, and select it to automatically sync to your Google Drive. 

Inside your Google Drive, go to Computers > Select your computer > Find your folder. Right-click on the files. to add them to your Drive.

Step 2: Select your data destination

The next step is to select the Excel file you want the data to be exported to.

Either let Sheetgo automatically create a new Excel file in Drive or Dropbox, or select one of your already existing files from either of those storage services.

Finally, click on Finish and save to create the connection between your Excel files.

Step 4: Automate your workflow

Sheetgo has now created your Excel connection workflow.

Click on Automate on the floating bar and choose the frequency with which you want Sheetgo to update the Excel – Excel connection.

Conclusion

This is how you automatically transfer data from one sheet to another in Excel.

Organizations with sensitive data may not want to share all parts of a master file with other departments or individuals within their organization, however. Or, they may only require specific pieces of information for analysis.

Read through the following blog post to see how to filter data from one spreadsheet to the other.

Share This