How to Sync Microsoft Excel Spreadsheet With Google Sheets File

Though one can use more than one office application suites that are online and are offered either for free or as a SaaS service, some people still prefer the good old office desktop applications. One such popular spreadsheet application is Microsoft Excel. If you're working in an environment that requires sharing of your Excel spreadsheet with other people, one of the best ways to do this is to sync it with a connected Google Sheets file. There are several ways to do it. Some of the methods are relatively easy, while others involve a substantial amount of code fiddling. I'll try to avoid the second scenario to enable you to set up the syncing process in an easy manner. Here I must tell you that the methods shown below may not set up automated syncing. You may need to press the button within your Excel application to complete the syncing process. These methodologies may not work with the online Office 365 office suite. It is targeted at desktop excel software and Google Sheets. Let's learn how to configure this file syncing system.

You can safely try and implement the syncing technique with sample spreadsheet files on both ends. Do not try it on your working files in the first attempt as it may change or modify the data inadvertently.

Read Also:
Best Office Productivity Applications for Freelancers and Professionals

While configuring the syncing process, if things don't work as intended, delete the files and start afresh. Let's get started and learn to quickly connect and sync an Excel spreadsheet with a Google Sheets file.

One Way Sync From Google Sheet to Excel File

The first method we're going to discuss is the one-way syncing of Google Sheet with an Excel file on our desktop. Whatever changes you'll make to the online Google Sheet file will reflect in the Microsoft Excel file on your computer.

But the reverse will not be possible. If you add some data in the excel file, it wouldn't go the other way round to the Google Sheet file. Let's see how to do this setup.

I'm using Office 2019 for this tutorial so you may get slightly different prompts or windows if you're using an older version of Microsoft office. Start with selecting and opening the Google Sheet file in your web browser you want to sync with a local excel file.

Let's assume you already have some data in it you want to sync with your Excel file. Now, go to the File → Publish to the web option to kickstart the syncing process.

You'll get a dialogue box as shown below. Make sure the entire document is selected as an option. Also, ensure that the auto-republish option has been checked as well. It'll ensure that whenever you add content to the sheets file, it is made available to the published instance of that file.

Thereafter, grab the published file link as indicated below. This link will be used to fetch data from the Google Sheets. You can either keep this link in your clipboard or can save it to a text file.

Now, open the Excel on your desktop where you want the Google Sheets file data to be visible. Select the Data tab and go to the Get Data → From Other Sources → From Web option.

Paste the copied Google Sheet URL in the dialogue box input field and click the OK button.

Next, you'll get the Navigator dialog box where you have to select the Table 0 option as shown below.

Thereafter, click the Transform Data button to open the Power Query Editor window. Here, click the Choose Columns option and deselect the first and second columns.

Finally, click the Close & Load button. It'll fetch and import the data from the connected Google Sheets file. Now, right-click on the Table 0 entry on the right side sidebar and select the Properties... option.

It'll open the Query Properties dialogue box where you can adjust the refresh rate and other data fetching options as shown below. Feel free to customize it as per your needs.

Upon a content update in the Google Sheets file, your connected Excel file can be updated in two different ways. Either it'll refresh automatically whenever the next refresh time will come or you can press the Data → Refresh All option to trigger the data fetch operation—instantly.

So, that's how you configure one-way sync from Google Sheets to Excel in a few easy steps. Be aware, that any update in the Excel file done locally will not persist on the next data refresh operation.

So, essentially it's a strict one-way sync configuration that'll keep a mirror data copy of the remote file in the local Excel file. For bi-directional syncing, use tools like Zapier or Sheetgo.