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 up to 2GB of data. When you start multiplying that for every colleague, team, and 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, however, 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 data management tasks
- Gather data from different files automatically
- Generate automated reports and dashboards
- Share specific data with others without granting access to your own spreadsheet
How to connect Excel files:
Step 1: Install Sheetgo
Once inside the web application, click +Create workflow and then Connect.
Give your Untitled workflow a name at the top of the screen so you can identify it later.
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.
Note that Sheetgo pulls data from the source file to the destination file, but the source remains intact. Data is not deleted from the original file.
Click +Select file, find the Excel file in your cloud storage and click Done.
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.
Under Source data > File tab, make sure to select the correct tab if your file contains more than one tab(worksheet).
In this example, I select a workbook called “Excel_data_source” and “Sheet1”.
Step 3: Adjust the settings (optional)
Go to Settings. In this section, you can configure the connection to
- Select the size of the header
- 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 — recommended when you consolidate multiple files into one)
After adjusting the settings, click Done editing.
Step 4: Select your destination file
Under Send data to, choose your destination file. This is the file you want to send the Excel data to.
This can be an existing file or a new file that Sheetgo will create for you automatically.
In both cases, Sheetgo will create a new tab(worksheet) in the destination spreadsheet containing the imported data.
Send data to an existing file
- Under Destination file select the file type. This can be another Excel file, a Google Sheets file or a CSV file.
- Click +Select File then locate the file in any of your cloud storage solutions. Click Done.
- Under New File Tab enter a name for the new tab. This will also be the name of the connection. If you leave it blank, Sheetgo will automatically name the tab Sheetgo_SourceTabName.
Send data to a new file
- Under Destination file select which type of file you want to send the data to (Excel, Google Sheets or CSV).
- Click on Change destination folder to choose which cloud storage platform you would like Sheetgo to save the file in.
- Enter a name for the new spreadsheet in the File name box.
- Enter a name for the new tab in the Tab name box.
In this example, I want to send data to a new Excel file stored in Google Drive. I name the file “Excel_data_destination” and the tab “Sheetgo_data”.
Click Finish and Save.
Your workflow has been created. Click on Workflow to see get a birds-eye view of how your files are connected, Connections to see your list of connected tabs and Files to see the connected spreadsheets.
Check out your destination file. You will see it now contains a connected tab with the imported data from your source file.
Step 5: Automate the workflow
Now that you’ve created the workflow you can update (refresh) the data in the destination sheet at any time by clicking Run on the floating workflow menu bar.
To ensure that you’ve always got up-to-date information without having to open a spreadsheet, schedule automatic updates.
Click Automate on the floating menu bar, 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 a specific day of the week.
Automate your daily spreadsheet work
That’s how you connect Excel workbooks to move and manage your data automatically. You can build on the workflow by adding more connections to other files.
Looking for more Excel automation solutions? Learn how to combine multiple Excel workbooks into one or how to append Excel data to create a historical track of changes over time.