How To Connect And Synchronize Google Sheets To Excel

Both Google Sheets and Excel are powerful tools. Google Sheets are most widely used when you are working globally often sharing data with others. It is very useful for collaboration purposes. Meanwhile, Excel is used offline most of the time for your own calculations, etc. Suppose you are using Google sheets all the time and need to take a backup, you will download it and save it on your computer. But imagine you are working on some data analysis project and you are doing a lot of changes in the Google sheet. Every time you do the change you have to download it for backup. This becomes a tedious task, isn’t it? There is a way to make things simpler. You don’t need to download the Google sheets every time when there is an update but still, you will have the latest data backup on your computer. Thinking how is it possible? In this article, we will learn how to do it that is how to connect and synchronize Google Sheets to Excel.


ADVERTISEMENT

Steps To Connect Google Sheets To Excel

 

Step 1: Open the Google sheets that you want to connect to excel. Make sure you have some data in the Google sheets. Here I have a sample list of data and countries and capitals.

 

Sample Data

 

Step 2: Click on the Share button which is at the top right corner.

 

Share

 

Step 3: If you have not named the google sheet, you can name it now. Or if you do not want to name it click on skip. If it is already named then continue with Step 4

Step 4: You will get a pop-up that has a link. Now you have to choose the access either Restricted which means only people with access can view the data with the help of a link or choose Anyone with the link which means anyone on the internet with the link can view the data

Step 5: If you are selecting Restricted then to add people, you can type the email ids of the people in the Add people and groups field

 

Restricted Mode

 

Step 6: Here I have selected Anyone with the link and click on the Copy link to copy it and then click on Done

 

Copy Link

 

Step 7: Now create Microsoft Excel Worksheet. To do so, go to the location on your computer where you want to create the Excel, right-click on the blank space, and click on New

 


ADVERTISEMENT

New

 

Step 8: Choose Microsoft Excel Worksheet from the list. A worksheet will be created.

 

Excel Worksheet

 

Step 9: You can give it a name. Open the excel worksheet

Step 10: Go to the Data tab from the excel ribbon at the top and click on from web. If you do not find it click on Get Data

 

Getdata

 

Step 11: Select From Other Sources and from the fly-out list click on From web

 

From Web

 

Step 12: In the appeared window paste the copied link in the URL field

Step 13: Move to the end of the link and remove all the characters from the edit till the last

 

Remove Last Charac

 

Step 14: And replace those removed characters with the export?format=xlsx

Step 15: Now the link looks like below. Click on OK

 

Add Export

 

Step 16: Click on Connect

 

Connect

 

Step 17: A navigator window will be opened which will have the google sheets on the left side. When you click on the sheet, on the right side you can see the preview of the data that you had in the google sheet. But the Column headers and not as expected.

 

Sheet

 

Step 18: To change the Column headers, click on Transform data at the bottom and the data will be loaded to the Power Query Editor

Transform Data

 

Step 19: Choose the Transform tab, click on Use first row as headers dropdown and choose Use first row as headers

 

Use First Row

 

Step 20: Now you can observe the headers have been changed. To load the data into the excel worksheet, click on the Home tab at the top

Step 21: Click on Close & Load which is at the top left corner

 

Close Load

 

Step 22: It will take a few seconds and the data will be loaded into the Excel worksheet. You can also see the number of rows loaded and the name of the worksheet on the right side of the Excel

 

Excel Data

 

Steps To Synchronize The Data InTo Excel

 

Now that you have connected and loaded the data present in the google sheets to excel, let us see if there are any modifications or updates made in google sheets, how it reflects in excel immediately.

Step 1: Here, I have added a few more data in the Google Sheet and saved it

 

Add Data

 

Step 2: Excel should also get updated with the latest data. So back in the excel worksheet click on Refresh and the latest data will be updated

 

Refresh

 

 

Updated Data

 


ADVERTISEMENT

Step 3: Instead of clicking on the Refresh button every time, you can auto-update the worksheet at regular intervals. So select the table or click on any cell in the table and click on the Refresh All dropdown button.

Step 4: Choose Connection Properties

 


ADVERTISEMENT


Properties

 

Step 5: Query properties window will appear, tick the checkbox beside refresh every by clicking on it and specify the time for the refresh to take place. It is in minutes.

Step 6: Also click on the checkbox beside Refresh data when opening the file to enable it and click on OK to save the changes

 

Refresh Often

 

Step 7: Save the Excel Workbook and close it

Step 8: Now add some more data in the Google sheets and save it

 

More Data

 

Step 9: Open the excel workbook and if you get any security warning at the top, click on Enable and wait for a second to run the background query

Step 10: And it’s done, the latest data is seen in excel without any clicks.

 

Auto Update

 

That’s it! I hope this article is helpful and you got it done easily. Thank you!!

ADVERTISEMENT