How to Link Google Sheets to Excel | Quick Tutorial

How to Link Google Sheets to Excel | Quick Tutorial
Reading Time: 3 minutes

Learn how to link Google sheet to excel

Objective

Learn how to link Google Sheets to Excel.

Excel provides functions that are not available in Google Sheets. Thereby having your data in Excel files is always an added advantage, from an analytical perspective. You can now link Google Sheets to Excel in 2 quick steps for performing better analysis. You can also automate the synchronisation to keep your files updated.

Step 1: Export your Google Sheet Data

  • Open Google Sheet containing your data.
  • Navigate to the File option in the menu bar.
Go to Publish to web
Figure 1: Go to Publish to web
  • Select Share → Publish to web.
  • Click on the Link.
  • Select your sheet, i.e.Sheet1.

Select your sheet
Figure 2: Select your sheet
  • Choose the option comma separated values (.csv)

Select Comma-separated values
Figure 3: Select Comma-separated values
  • Now go to Published content and settings.
  • Tick the option Automatically republish when changes are made. This will update the google sheet whenever changes are made, and republish the .csv file, thereby keeping your file updated.

Automate the changes to update the excel file
Figure 4: Automate the changes to update the excel file
  • Now click on the Publish button.
  • A message box prompts, asking you “Are you sure you want to publish this selection?”. Select OK.
  • A link will be generated. Copy the link.
Publish to web
Figure 5: Publish to web

Step 2: Import into Excel

  • Open a workbook, where you want to import your Google Sheet.
  • Navigate to the Data tab.
  • Click on New Query → From File → From CSV.
  • Copy in your link to the File name: box → click Open.
Copy your link into an excel file
Figure 6: Copy your link into an excel file
  • lt will now ask you about accessing web content. Select Connect.
  • A preview snippet of your data will appear.
  • You can now load your data with three options:
    • Load: This option will create a new sheet for you and load all the data into a table.
    • Load To: This option will load data exactly where you want your table to be created.
    • Edit: This option gives you flexibility with the data you want to load, by allowing you to modify parameters.
  • For now, click on Load and your data is stored in Excel.
  • That’s how you link Google Sheets to Excel.

See Also

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.

One response to “How to Link Google Sheets to Excel | Quick Tutorial”

Leave a Reply