Jump to a step (7)
Your team updates a Google Sheet, but your reports and formulas live in Excel on your computer. Downloading and re-importing the sheet every time it changes is tedious, and the Excel copy is always out of date.
Google Sheets can publish a sheet to the web as a link. Excel can read that link with `From Web` (Power Query), clean up the columns once, and load the data as a table. Set the query to refresh every five minutes and on open, and the Excel file stays current without add-ins.
Google republishes changes on roughly a five-minute cycle, so new data shows up within a few minutes, not instantly.
What you need
- A Google Sheet with the data you want to show in Excel
- Microsoft Excel for Windows (the version with the
Datatab andFrom Web) - Data you are comfortable publishing, because a published sheet can be viewed by anyone with the link
Step by step
0 of 7 done-
01
Publish the Google Sheet to the web
In Google Sheets, open
File, thenShare, thenPublish to web. Select the worksheet that holds your data and selectPublish, then confirm.Under
Published content and settings, make sureAutomatically republish when changes are madeis ticked, so Excel keeps getting updates. Copy the link. -
02
Connect Excel to the published link
Open a blank Excel file. On the
Datatab, selectFrom Web. If you do not see it, go toGet Data,From Other Sources,From Web. Paste the link and selectOK. -
03
Pick the table and open Power Query
In the Navigator, select the table. You will notice a couple of extra columns. Select
Transform Datato clean them up. -
04
Remove extra columns and set the headers
Select each extra column and choose
Remove Columns. Then selectUse First Row as Headersso your column names become the header row. SelectClose & Load. The data appears in Excel as a table. -
05
Tidy the table format (optional)
If you do not like the table style, untick
Banded Rowsto remove the row colors, and turn off theFilter Buttonto remove the filter arrows. -
06
Set the query to refresh automatically
Select a cell in the table, open the
Querytab and selectProperties. TickRefresh everyand set it to5minutes, because Google updates published data about every five minutes. Also tickRefresh data when opening the file. SelectOKand save the workbook, so the query is stored in the file. -
07
Reopen and test
Close and reopen the file. Excel shows a security warning because it is getting data from an external source. Select
Enable Content.Add a row in the Google Sheet and wait about five minutes. Selecting
Refreshright away may still show the old data because Google has not republished yet. After the next cycle the new row appears in Excel on its own.
Tips and common problems
- Google republishes on about a five-minute cycle. A manual refresh in Excel will not show changes Google has not published yet.
- Save the Excel file after you set up the query. The connection and refresh settings are stored in the workbook.
- Select
Enable Contentwhen Excel shows the security warning, or the query will not refresh. - Publish to web makes the sheet viewable by anyone who has the link. Do not use this method for confidential data.
Going the other way
To send Excel data into Google Sheets instead, see Auto-sync data from Excel to Google Sheets.
Questions
Can Excel pull live data from Google Sheets?
Yes. Publish the Google Sheet to the web, then use Data, From Web in Excel with the published link. Set the query properties to refresh every few minutes.
Why does my Excel file not show the latest Google Sheets changes?
Google republishes the sheet about every five minutes. Wait a few minutes, and make sure Automatically republish when changes are made is ticked in the publish settings.
Do I need an add-in to connect Google Sheets to Excel?
No. This method uses Google's Publish to web option and Excel's built-in From Web query.
