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.
Download Practice Workbook
3 Steps to Link Google Sheets to Excel in Real Time
First and foremost, let’s assume the Monthly Sales Dataset shown in the B4:D13 cells portraying 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”.
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.
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.
- 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.
📌 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.
- In turn, use the CTRL + V shortcut to paste the link to the Google Sheets.
- In addition, press the Connect option to complete the connection process.
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.
- Not long after, select the empty columns >> click the Remove Columns option.
- In a similar manner, select the top row >> go to the Remove Rows drop-down >> choose Remove Top 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.
- Eventually, navigate back to the Home tab >> press the Close & Load option.
- Following this, go to the Refresh drop-down >> jump to the Connection Properties 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.
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.
- Initially, go to the File tab located at the top-left corner.
- Then, choose the Download option >> select Microsoft Excel (.xlsx) format.
Subsequently, navigate to the Downloads folder and you’ll find the Excel file as shown in the screenshot below.
Read More: How to Download Google Sheets to Excel (2 Easy Ways)
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.
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.
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.
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.
I hope your problem will be solved.
Afia Aziz Kona