How Can I Link Google Sheets to Excel in Real Time?

Get FREE Advanced Excel Exercises with Solutions!

Unquestionably, Microsoft Excel and Google Sheets are two of the most useful and popular tools for data analysis. Though you can easily download Google Sheets as an Excel file, you have to manually replace the existing file every time you update the Google Sheets. Now, is there a way to update the two files automatically? The answer is a resounding Yes! In this article, we’ll answer the question of “how can I link Google Sheets to Excel in real time?”. Moreover, we’ll also discuss how to convert Google Sheets to Excel with formulas.


How to Link Google Sheets to Excel in Real-Time: 3 Simple Steps

First and foremost, let’s assume the Monthly Sales Dataset shown in the B4:D13 cells portrays the Product names and the sales in January and February respectively. Here, we want to link this Google Sheets (source file) to an Excel (destination file) so that changes made to the source file are shown in the destination file in real time. Henceforth, without further delay, let’s explore all the nitty-gritty of the query “How can I link Google Sheets to Excel in real time”.

how can i link google sheets to excel in real time

Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


📌 Step 1: Open the Publish to Web Window

At first, we’ll have to publish the Google Sheets (source file) to the Web to obtain a shareable link. So just follow these steps.

  • First, go to the File tab >> move to the Share option >> click the Publish to web option.

 Open the Publish to Web Window

Now, this opens the Publish to the web window.

  • Next, press the Published content and settings >> insert a check mark for the Automatically republish when changes are made option >> hit the Publish button.

Publishing to Web

  • Later, a pop-up appears, in this case, click the OK button.

  • Then, press the CTRL + C to copy the published link of the Google Sheets.

how can i link google sheets to excel in real time using Keyboard shortcut to copy link


📌 Step 2: Load Data and Establish a Connection to Excel

At this time, we’ll load the data into Excel through the published URL, thus establishing a connection with the source and destination files. Therefore, let’s see it in action.

  • Second, open an Excel workbook >> jump to the Data tab >> proceed to the Get Data drop-down >> select the From Other Sources option >> choose From Web.

Load Data and Establish a Connection to Excel

  • In turn, use the CTRL + V shortcut to paste the link to the Google Sheets.

using keyboard shortcut to paste

  • In addition, press the Connect option to complete the connection process.

how can i link google sheets to excel in real time establishing connection

Read More: How to Import Data from Google Sheets to Excel (3 Easy Ways)


📌 Step 3: Transform Data and Set Connection Properties

Lastly, we’ll transform the data using the Power Query Editor and then set the connection properties accordingly. Hence, let’s see the process in detail.

  • Third, check the Select multiple items option >> choose Table 0 query >> hit Transform Data.

Transform Data and Set Connection Properties

  • Not long after, select the empty columns >> click the Remove Columns option.

Removing columns

  • In a similar manner, select the top row >> go to the Remove Rows drop-down >> choose Remove Top Rows.

Removing Rows

  • Now, enter the number of top rows to remove, in this case, there are 3 rows >> press OK.

  • Afterward, proceed to the Transform tab >> select the Use First Row as Headers option.

Using top rows as headers

  • Eventually, navigate back to the Home tab >> press the Close & Load option.

Closing and loading to Excel

  • Following this, go to the Refresh drop-down >> jump to the Connection Properties option.

how can i link google sheets to excel in real time using refresh option

  • Consequently, check both the Refresh every and Refresh data when opening the file options >> hit the OK button.

📃 Note: On this occasion, a refresh interval of 1 min is chosen, you can set this according to your preference.

how can i link google sheets to excel in real time setting connection properties

Finally, this links the Google Sheets to the Excel file which automatically updates in real time.

Read More: How to Transform Google Sheets to Excel Automatically


How to Convert Google Sheets to Excel

For one thing, converting Google Sheets to Excel is a quick and simple process. In the following section, we’ll go through the steps in detail, so follow along.

📌 Steps:

  • Initially, go to the File tab located at the top-left corner.

How to Convert Google Sheets to Excel

  • Then, choose the Download option >> select Microsoft Excel (.xlsx) format.

Downloading file

Subsequently, navigate to the Downloads folder and you’ll find the Excel file as shown in the screenshot below.

Read More: How to Convert Google Sheets to Excel with Formulas


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section for how can i link google sheets to excel in real time


Download Practice Workbook


Conclusion

To sum up, we hope this tutorial has provided you with helpful knowledge on the query “how can I link Google Sheets to Excel in real time?”. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Eshrak Kader
Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

2 Comments
  1. Hi, thank you for the tip, I mannaged to connect, but I keep on receiveing following note when opening Excel file:

    Repaired Records: External data range from /xl/queryTables/queryTable1.xml part (External data range)

    I have checked the file, nothing seems wrong. As a consequence it doesnot allow me to save it with the same name.

    Please help
    Kristian

  2. Dear Kristian,
    Thank you for your comment.
    You can solve your problem by using one of the following procedures:

    1. Using Document Inspector Command
    2. Editing Trust Center for External Content
    3. Inputting Accurate Data Importing File Destination
    4. Setting Refreshing Option in Beginning
    5. Installing an Updated Version of Excel

    You can read the following two articles to get descriptive information.
    https://www.exceldemy.com/excel-external-data-connections-have-been-disabled/
    https://www.exceldemy.com/excel-data-connection-not-refreshing/

    I hope your problem will be solved.

    Best,
    Afia Aziz Kona

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo