How to Connect Your Google Sheets with Microsoft Excel For Your Work

by

Contents

What is Google Sheets?

Google Sheets is a spreadsheet program provided by Google as part of the free, web-based Google Docs Editor suite. The service includes Google Docs, Google Slides, Google Drawing, Google Forms, Google Sites, and Google Caps. It provides much more than most spreadsheet tools. Google Sheets lets you create and manage spreadsheets online. Google Sheet is a web application, a desktop application for Android, iOS, Windows, Blackberry, and Google Chrome OS.

We will start with the basics in this chapter. As it is cloud-based, Google excel Sheets and other Google Workspace applications are easy to use, accessible from anywhere, and great for collaboration. All you need is an internet connection. You access Google apps Sheets through your web browser, so you don’t need to install any software.

Google docs Sheets automatically saves every change. The advantage of using Google Spreadsheets is that you can manage spreadsheets from anywhere on any device by signing in to your Google Account. Anyone can access Google online docs excel Sheet files through their browser, allowing multiple users to work together on the same sheet.

Read: How to create a desktop shortcut on your desktop windows 10 pro in 2021

What is Microsoft Excel?

Microsoft Excel is a spreadsheet created by Microsoft for Windows, Mac, Android, and iOS. Ms excel online includes a macro programming language called Visual Basic (VBA) for calculations, graphing tools, pivot tables, and applications. A software program created by Microsoft that uses spreadsheets to organize numbers and data, including formulas and functions.

Excel Analysis is ubiquitous worldwide and is used by traders of all sizes to perform financial analysis. Microsoft excel spreadsheet program consisting of several columns and rows, where each intersection of a column and a row is a “cell”. By organizing the data in this way, you can make it easier to find information and automatically draw data from data changes.

Read: 5 Free Online Notepad Online Text Editor and Notepad for Notes (No Login Required)

The main uses of Microsoft Excel include:

  • Data entry
  • Data management
  • Charting and graphing
  • Accounting
  • Financial analysis
  • Time management
  • Programming
  • Task management, and more.

How to connect Google Sheets with Microsoft Excel?

There is no native feature to link your Excel file to Google Sheets, but there are several Chrome add-ons (for Google Sheets) that allow you to set up this linkage. To “read” your Google Sheet Excel file, you need to save your Excel file to Google Drive for most of these add-ons.

Another option is Coda, an online document that is as powerful as a spreadsheet and as flexible as a document. The source language of the code for combining your data across tables is the competitor and most advanced source language of Excel (similar to MySQL databases).

This is especially true when I have automatic scheduling triggers and scripts in Google Sheets but want data in Excel. Google Sheets is a cloud platform so it’s easier to create shading macros than Excel.

  1. It may be something you like but it will be exported to Excel in table/database format. You can’t associate Excel with A1 in Google Sheets, at least not directly. We will export A1 as a table, then attach Excel to A1 as a table.  Now You will understand what I mean below. Open the file you want to sync with MS Excel in Google Sheets or Go to your Google Sheet containing the data you want to export.

Open google sheets

Now:

    1. Click on File (Top menu) in Google Sheets
    2. Then click Publish to the webHow to connect Google Sheets with Microsoft Excel (6)
    3. When you click the Publish to the web option then open a popup window. Then click on Link.Sheet1(or Whatever sheet your data is in)
    4. Comma-separated values (.csv)
    5. Click the “Published Content and Settings” section to expand it and see if the “Automatically republish changes” section is enabled. If not, enable it. Now click on the “Publish” button and select OK in the popup.

How to connect Google Sheets with Microsoft Excel (6)

After publishing the document, you will receive a link. Copy the link (if done correctly, the last end of the link should be = CSV). You need to paste this link into the MS Excel document that you want to link to Google Sheets. If you paste this link into your web browser, you will see that it is available as a web page. What we want for Excel right now is to download automatically whenever you press the “Refresh” button, like a pivot table. The link will be updated automatically every 5 minutes. So if you change your data, the link will be updated in real-time.

How to connect Google Sheets with Microsoft Excel – Now Step 1:

    1. Now, open MS Excel and create a new blank document in it.
    2. Go to Data Tab

Microsoft Excel

    1. Click on
      1. New Query
      2.  From File
      3. From CSV
      4. Copy the file name to your link in the box and click Open.
    2. It will think for a few seconds, then ask you about access to web content. Leave the default and click Connect

Connect ms excel

    1. A snippet of your data will now pop up!
    2. Load (this will create a new sheet and load all the data as a table and select exactly where you want the table created)
    3. Transform Data (this opens the Power Query Editor – this provides to you get your data to exactly the criteria you are after.
      For example, delete unneeded delete text, add new calculated columns, rows/columns, and so on) Definitely have a play here.
    4. Now Click Load

Microsoft excel to google spreadsheet

That’s it.

Read: How to create a desktop shortcut on your desktop windows 10 pro in 2021

How to connect Google Sheets with Microsoft Excel – Step 2:

    1. Now, open MS Excel and create a new blank document in it.
    2. Go to Data Tab
    3. Click on:
      1. New Query
      2. From other sources
      3. From web

google docs Excel

4. Now you will get a popup window, where you need to paste the copied link and click OK.

google apps script

5. The entire Google Sheet data will be available in Excel in the form of a table. Now, in the “Navigator” window, first, click the “Select Multiple Items” check box and then select “Table 0” A preview of the selected item is available in the right panel.

Microsoft excel to google spreadsheet

6. If you need to make changes like columns, rows, etc. click the “Edit” button at the bottom of the navigator window. Depending on your MS Excel version, some of you may have “Transform Data” instead of the “Edit” option. This will open “Query Editor”. If you need to make changes like the first column on the top menu, For this, click on the top left corner and select “Use the first row as headers.”

7. If you want to delete unwanted columns, click the top left corner and select “Choose columns.” After that, uncheck the boxes of the columns that you do not want and click OK.

google excel

Now click Close and Load to data of google sheets in excel.

Whenever you make any changes to Google Sheet, you have to wait for 5 minutes to get those changes to publish on the web. After that, you can have to refresh the excel file to get these changes reflected in the Excel file. For this, go to Data > Refresh All.

Read: 5 Free Online Notepad Online Text Editor and Notepad for Notes (No Login Required)

How to connect Google Sheets with Microsoft Excel

Whenever you change Google you want to update manually but you have to take some more steps. Select the table and go to:

sumif google sheets

  1. Data
  2. Connections
  3. Then open a new window in which you have to select “Properties”
  4. Tick Refresh data when opening the file and/or Refresh Every x Minutes or Fast Data Load. When you are done, click OK and close the previous window. That’s it. This is how you can connect Google Sheets with MS Excel.