How to Import Data From Google Sheets to Excel - Real Time Auto Sync Google Sheets with Excel

Learn how to easily import data from Google Sheets to Excel and keep them synced in real time. Save time and increase productivity with this helpful tutorial on how to connect Microsoft Excel with Google Sheets.

You can update the Excel import with any changes in Google Sheets. You can create a live connection between Google Sheets and Microsoft Excel. After that, the updates will be reflected in the Excel file when you update the Google Sheets.

Table of Contents:

00:00 - Intro
00:15 - Create a Shared URL in Google Sheets
00:50 - Import data into Excel Using Power Query
02:30 - Refresh the import periodically or whenever the workbook is opened

Quick Guide:

1. First, we must create a shared URL in Google Sheets. Click File, then choose the Publish to Web command. A new window will appear. Choose Microsoft Excel from the drop-down list and review the Published Content and Settings section. Make sure that the Automatically republish when changes are made checkbox is ticked.

2. Click on Start publishing. We want to publish the File, so click OK. Then, copy the shared URL and close the window. Now, switch to Excel back. In Excel, locate the Data Tab on the ribbon. After that, choose the Web import option. Paste the URL into this box. Then click OK. Now, the Power Query Navigator window will appear. Select the Sheet you want to import. You will see a preview of your data set on the right side. Click Transform Data to start the Power Query Editor.

3. The data set looks almost good. We only have a little work with the Column Header.
Locate the Transform Group and apply the following command: Use the first row as headers.
This command will promote the first row of this table into column headers. Now, we are ready to import the Google Sheets data into Excel. Click on close and load. Currently, we have the imported data in Excel.

4. Place the Google Sheets and Excel windows side-by-side to check how the sync works between two data sets. In the example, jump to Google Sheets and select cell B2. Change the default value.
Now, switch to Excel back. We will check how the updates will be reflected in the Excel file.
Select the table. Then click the Refresh button. Okay, the cell is updated. Now, jump to Google Sheets and add a new row. Select the Excel table, then click the Refresh button. You can refresh the data automatically using the Queries and Connections setup. Right-click on the Connection and choose Properties.

5. The Query Properties window provides a refresh control. You can see some options for refreshing data here. For example, if you want to update the data every 5 minutes, tick the refresh every checkbox and add the value. Click OK to close the dialog box.

#Excel #ExcelTips #ExcelTutorial #googlesheets