Sync Excel to Google Sheets VBA

Microsoft Excel has been the world’s leading business application for more than 3 decades. Indeed for many people, the name Excel is synonymous with the word spreadsheet. According to Microsoft, an estimated 1 in 5 adults on the planet use Excel and there’s no sign of that changing any time soon. When it comes to flexibility and accessibility, nothing beats a spreadsheet for data management and analysis.

With hundreds of advanced functions, Excel is still the tool of choice for many businesses, but Google Sheets is a good alternative. Because it’s cloud-based, Google Sheets and the other Google Workspace apps (formerly known as G Suite) are simple to use, accessible from anywhere, and great for collaboration. You access Google Sheets via your web browser, so you don’t need to install any software — all you need is an internet connection.

Although it’s a bit more basic than Excel, Google Sheets is free and it offers small businesses a great alternative to purchasing a Microsoft Office license.

Anyone can access Google Sheets files via their browser, multiple users can work in the same sheet simultaneously and changes are saved automatically. On top of that, you can see what’s happening with your data in real-time.

Both tools have their pros and cons. This means that it’s now common for companies to use both spreadsheet formats. As a result, many people will need a way to convert Excel to Google Sheets, and vice versa. An easy way to do this is using Sheetgo’s add-on for Excel and Google Sheets.

Migrating from Excel to Google Sheets or working with both?

Although your team might be permanently migrating to Google Workspace, you may still receive data or reports from external sources. Even if your company is fully onboard with Google Sheets, there’s a good chance you will have to maintain a relationship with Excel.

Some companies restrict their employees to Excel and insist that staff only share files as attachments.

At other times it comes down to personal preference: analysts sometimes prefer to use Excel for heavy data processing before transferring that information to Google Sheets for collaboration and reporting.

Sometimes you might need to use Google Sheets as an input source for other software, such as Google Data Studio.

Which is better? Excel vs Google Sheets

There’s no easy answer here, as it depends on your needs and what you’re used to!

In general, Excel is a more powerful option for heavy users processing large amounts of data. When it comes to online collaboration and ease of use, Google Sheets leads the way. Check out our full side-by-side comparison of Excel and Google Sheets. 

Here’s a quick summary of how the two programs compare:

How to transfer Excel to Google Sheets

There are two ways to do this.

The first method is OK if you want to convert Excel to Google Sheets on a one-off, ad-hoc basis.

The second option is to set up an automated system that syncs Excel to Sheets on a regular basis.

1. Upload manually

1. Upload the Excel file to your Google Drive.
2. Double-click on the file and it will open.
3. At the top of the screen, click File > Save as Google Sheets.

This will convert the file. The new Google Sheets version of the file is now saved in your Drive, alongside the original Excel workbook.

You can also manually import data from Excel into a Google Sheets file from inside the spreadsheet.

1. Open a Google Sheets file.
2. Go to File > Import.
3. Choose your Excel file and click Select.
4. Choose from the options: Create new spreadsheet, Insert new sheet(s), or Replace spreadsheet.
5. Click Import data.

Problems converting Excel to Sheets

The manual upload or import method is straightforward but you might run into a couple of issues.

Firstly, Google Sheets may notify you that some Excel features are not compatible.

Here’s the type of message you might see:

There are other potential drawbacks to this approach too.

• Importing a large number of spreadsheets from XLS to Google Sheets manually is time-consuming.
• Macros are not converted to Google Sheets.
• References between linked Excel files will be lost, leading to #REF errors in your spreadsheet.
• If you want to update your Google Sheets with fresh data from Excel, you will have to carry out the process from scratch each time.
• Some Excel formats are not directly compatible with Google Sheets: in this case, you’ll have to copy and paste the data to reconstruct the entire Excel file in Google Sheets.

Why convert Excel to Google Sheets automatically?

An alternative to uploading each file manually is to set up an automated system that imports Excel data into Google Sheets while leaving the Excel files intact.

This has multiple advantages as you can:

• Upload Excel to Google Sheets automatically
• Work simultaneously with both Excel and Sheets
• Maintain complex Excel files with macros while transferring all (or part) of that data into Sheets
• Convert Excel to Google Sheets without manual work or adjustments
• Sync data between Excel and Google Sheets, ensuring that changes to your XLS files are saved in Google Sheets
• Facilitate data-sharing in a multi-cloud environment, importing data from colleagues’ Excel files automatically
• Generate automated reports and dashboards in Google Sheets using Excel data

How to sync Excel to Google Sheets

Sheetgo is a no-code automation tool that allows you to move data between spreadsheets automatically without macros, ImportRange, or Google Apps Script.

In a couple of clicks, you can set up a workflow that automatically pulls data from Excel files directly into Google Sheets. Here’s how to do it:

Step 1: Install Sheetgo

Install Sheetgo via the blue button below.

You’ll see that you can sign in with your Microsoft, Google, or Dropbox account. This means that you can connect Excel files to Google Sheets, no matter which cloud storage folder they are stored in.

Sheetgo

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 2: Connect your Excel file

Once your Excel files are online, the next step is to create a connection between your Excel file and Google Sheets.

1. Inside Sheetgo, click the blue Connect button.
2. Give the Untitled workflow a name at the top of the screen.
3. Under Select source data, click Excel file.

4. Click +Select file then locate the workbook you want to convert to Google Sheets.

The selected file appears in Sheetgo.

5. Under File tab make sure to select the correct tab (worksheet) containing the data you want to send to Google Sheets.

Step 3: Send data to Google Sheets

After you have selected the source file and tab, you need to select which Google Sheets file you want to upload the Excel data to.

1. Click Continue.
2. Under Send data, select Google Sheets.

Choose your destination file

•  If you want Sheetgo to automatically create a new Google Sheets file containing the Excel data, select New file. Sheetgo will save the file to your main Google Drive folder. To save it in a specific folder, click Change destination folder.
• If you want to transfer the Excel data to a Google Sheets file that already exists, select Existing file. This will create a new tab(sheet) in the Google Sheets spreadsheet containing the Excel data.

Here I’ll send it to a new file in my Google Drive. Under File name, enter a name for the new spreadsheet.

Save the connection

Click Finish and save to create the Excel to Google Sheets connection.

The destination spreadsheet opens inside Sheetgo and the imported data appears in a new tab.

Note that the padlock icon and green bar indicate that this is a connected Sheetgo tab. Every time you update the connection, the data in this tab will be refreshed with the latest data from the Excel file. To edit the data, transfer it to another tab using formulas.

Step 4: Update the connection

Now that you have created a Sheetgo connection between Excel and Google Sheets, you can sync data between the two spreadsheets at any time.

Inside Sheetgo, open the workflow and click Run on the menu bar. This will refresh the data in the Google Sheets file.

Step 5: Automate the workflow

To save time and ensure that your Google Sheets files, dashboards, and reports are always updated, you can automate the entire system.

Click Automate on the menu bar and choose how often you want updates: from once an hour to once a month.

Adjust the time of day and the day of the week, so the connection only syncs when you need it to.

Your automated system is up and running

You have now set up automatic uploads from Excel to Google Sheets using Sheetgo.

Whenever you (or your colleague) edit the Excel source file(s), changes will be reflected in the Google Sheets destination file.

If you want to save previous data entries and create a historical log, you can choose a staggered update schedule and append data at specific intervals.

You can extend the workflow by adding more connections to build a bigger system. You can also send data in the other direction, from Google Sheets to Excel. For more help, check out our post on how to automatically convert Google Sheets to Excel.

Want to import data from multiple Excel files into Google Sheets?

If you want to combine data from multiple Excel tabs (worksheets) into one Google Sheets tab, click +Add another source file and repeat the previous steps.

This will allow you to import and merge data from several Excel tabs (in the same workbook or different workbooks) into one spreadsheet.

If you have a large number of source files, try consolidating from a folder. This allows you to import data from the same source tab in multiple Excel files into one spreadsheet quickly and easily. Whenever you drop a new file into the folder, it will be added to the connection automatically.

To consolidate data from a folder of Excel files in Sheetgo, create a new workflow and click Connect.

Under Select source data, click An entire folder.

Note that the source tab(worksheet) must have the same format (column structure) in every file, in order to consolidate the data correctly.

For more detailed instructions, read our guide on how to consolidate data from multiple Excel files into one.

Can I filter data from Excel to Google Sheets?

Yes! By default, Sheetgo transfers the entire source tab to the destination tab but you can apply filters to extract specific data. This enables you can send a selected part of your Excel data to Google Sheets, based on the conditions you specify.

Note that your source and destination file must both be stored in Google Drive in order to use Sheetgo filters. If you need help syncing your Excel files to Drive, check out these tips.

How to filter a new connection

• Open Sheetgo and click Connect to create a new workflow.
• Under Source data select your Excel file from Google Drive.
• Select the file tab containing the data your want to export to Google Sheets.
• Click the Continue button.

In the example below, I want to export filtered data from a file containing the results of a customer survey.

I only want to export responses from people who replied in January, so I select the source tab January.

Filter by condition, query, or cell color

Under Filter data, choose how you want to extract information from the source sheet.

• Filter by condition: select data from the Excel file by column, number, date, text, or value.
• Filter by query: select data by writing an SQL query. Learn how to use a query filter.
• Filter by color: select data by the color of the cells.

Here I only want to export survey responses from women, so I use the following filter settings:

1. Filter by condition
2. Data matches one condition (OR)
3. Data matches > Condition > Column B:Gender
4. Criteria: Text contains > Value: female

I want to send this filtered Excel data to a new Google Sheets file.

In the file name box, I’ll call it Female respondents.

You can also rename the new file tab if you want to. Here I’m happy to use the name that Sheetgo generates automatically, based on the name of the source tab. In this case, my tab will be named Sheetgo_January.

Click Finish and save to create the filtered Excel to Google Sheets connection.

The new Google Sheets file opens inside Sheetgo.

As you can see, I now have a new spreadsheet called Female respondents. 

Survey responses have been filtered from my Excel file (Customer Survey USA > January) into the new tab Sheetgo_January.

Sync the files at any time

The Excel source file is now connected to the Google Sheets destination file.

Just like with a standard Sheetgo connection, you can update the workflow at any time by clicking Run. Or schedule regular updates by clicking Automate.

This will sync changes in the Excel sheet to the data that’s being filtered to your Google Sheets file.

How to filter an existing connection

• Open Sheetgo and select the workflow from the list on the left.
• Click on Connections.
• On the right of the screen, go to the menu ⋮ > Edit Connection.
• Under Source data, click Done editing.
• Click on the Filter data icon.

• Apply a filter then click Finish and save.
• Run the workflow to update the connection. The new filter will be applied.

Automated data transfer in spreadsheets

That’s how to convert Excel to Google Sheets automatically!

Once you’ve connected an Excel file to Google Sheets, it’s easy to add more connections or build a more advanced workflow between your spreadsheets. Want to move data in the other direction, from Google Sheets to Excel? Or maybe you want to combine data from multiple Excel workbooks into one central file. In case, the Sheetgo app and add-on provide you with all of these tools and more!

Did you like this post?

Check out our other posts below for more spreadsheet-based automation ideas and share this post via the social media links on the left.

Can you sync Microsoft Excel and Google Sheets?

Does Excel VBA work on Google Sheets?

Can I import Excel macro to Google Sheets?

Can you link a SharePoint Excel to Google Sheets?