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

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.

📌 Steps:

• 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)

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.

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

• How to Use QUERY Function of Google Sheets in Excel (3 Common Ways)
• How to Import Data from Google Sheets to Excel Using VBA
• [Fixed!]: Google Sheet Downloaded as Excel Is Not Working
• How to Edit Google Sheets in Excel (with Easy Steps)