Connect Excel to Excel and other spreadsheet formats

by Jun 29, 2020

Does your entire business or department run on Excel files? You’re not alone. Spreadsheets are a powerful and versatile tool for storing, managing and processing data for anything from accounting to stock control. And now that we can store Excel files online, it’s easier than ever to collaborate with colleagues and clients, working in the same files together in real time. But did you know you can connect Excel files and move data between them automatically?

Spreadsheets can grow to a huge size: one Excel worksheet alone can store to to 2GB of data. When you start multiplying that for every colleague and  team, and every process in the company, it doesn’t take long to build up large silos of unconnected, isolated and even duplicated data. Thanks to the power of the cloud, it’s now possible to get control over your data by connecting spreadsheets. When you connect Excel workbooks you can:

 

  • Push and pull data between spreadsheets without copy-pasting
  • Save time on manual work
  • Gather data from different files automatically
  • Create an automated workflow to move your data at regular intervals
  • Generate automated reports and dashboards
  • Share specific data with others without granting access to your own spreadsheet

How to connect Excel files:

Connect Excel files

Step 1: Install Sheetgo

Install Sheetgo here.

Once inside the web application, click +Create workflow and then Connect.

Give your workflow a name. In this example, I’ve named the workflow “Connect Excel workbooks”.

Step 2: Select Excel as your data source

Firstly, you need to choose your data source. This is the file you want to export data from. Select Excel as your data source.

Note that Sheetgo pulls data from the source to the destination file, but the source file always remains intact. Data is not deleted from the original file.

Find the Excel file in your cloud storage solution.

In this case, I select the “Excel_data_source.xlsx” workbook.

If your source workbook contains multiple worksheets (tabs) make sure to select the correct worksheet.

Connect Excel data source

Step 3: Adjust the settings (optional)

Go to Settings. In this section, you can configure the connection.

For example:

  • Select how big the file header is
  • Change the date format
  • Transfer formatting (Google Sheets only)
  • Identify source (Sheetgo adds an extra column to the destination file so you can track where your data came from)
Connect Excel source data settings

Step 4: Select your destination file

Click Done editing to move to the next step. Now choose your destination file (where you want to send the Excel data to).

This can be an existing or new Excel file, but it can also be another spreadsheet format such as Google Sheets.

For this example, I will connect two Excel files to one another.

Choose an already existing workbook from inside your cloud storage, or let Sheetgo automatically create a new one for you.

Connect Excel data destination

If you choose to create a new file, give it a name. As you can see in the screenshot, I have called mine “Excel_data_destination”.

Give the file tab a name too (mine is “Sheetgo_data”). Sheetgo sends data from your source file to this tab in your destination file.

Finally, click Finish and save to create the workflow. 

Step 5: Automate the workflow

Your Excel file is now connected to another spreadsheet. You can update (refresh) the data in the destination sheet at any time by updating the connection. Now you can automate the workflow, to ensure that you’ve always got up-to-date information, without even having to open your spreadsheet.

Click Automate on the floating bar of your workflow, and choose how often you want the data to be updated. This can be every hour, day, week, or month and you can also select the time of day or day of the week.

Connect Excel automatic updates

Automated Excel connection

That’s is how you connect Excel workbooks and create an automated workflow to move and manage your data. 

Looking for more spreadsheet automation solutions? Learn how to log changes to dynamic data over time by appending Excel and creating a historical track of your data.

Share This