How to transfer data from Google Sheets to Excel. Among the users who create spreadsheets, there are those who prefer the online service from Google, while others work in Microsoft Excel. Therefore, sometimes there is a need to transfer data from a web service directly to the program.
How to transfer data from Google Sheets to Excel
This can be done in three different ways.
Method 1: Download the spreadsheet in XLSX format
If you don’t already know, then Google Sheets supports downloading the created document in one of six formats, among which there is XLSX – Microsoft Excel’s proprietary format. This method has its disadvantage, since you cannot update the data and see what changes have been made online with the click of a button. However, if this is not necessary, downloading will be the easiest and fastest solution.
- Open the necessary table through any convenient browser from the account that has access to view. Call the menu “File” , hover over “Download” and select the option “Microsoft Excel (XLSX)” .
- Wait for the download to finish and open the document through the program.
- Be sure to allow editing as the file will initially open in Protected View.
- Now all Excel tools will become active, and you can make the necessary changes on the sheet.
Method 2: Importing a query
Now I propose to consider more complex, but progressive methods that allow you to quickly track changes in Google Sheets and work with up-to-date data in Excel. This option involves opening access by link to the document and then importing the table into Excel, which looks like this:
- Ask the owner of the document to do the following, or do it yourself. If you have already received an access link, skip the setup step and go straight to Excel. Otherwise, in the document, click on “Access Settings” .
- In the window that appears, click on the link “Allow access to everyone who has the link” .
- Copy the link you received. The access level does not matter, so you can leave the standard value – “Reader” .
- Open a blank sheet in Excel, go to the Data tab , expand the Create Query menu, select From Other Sources, and click on the option From the Internet .
- Paste the link in the window that appears.
- The essence of the change is only the correction of the last part to export?format=xlsx instead of edit?usp=sharing .
- Then click OK and wait for the new window to appear. In it, select the import of several sheets or specify a specific one.
- Through the preview tool, make sure you have selected the correct sheet, and then confirm its download.
- The import was successful, although the formatting may differ slightly from the original.
- To check the changes on the Data tab, use the Refresh All button .
- As you can see, someone made changes in Google Sheets, and they successfully loaded into Excel without having to import the entire spreadsheet again.
- To set the automatic update time, in the Update All menu, click on Connection Properties .
- Activate “Refresh every” and specify the desired number of minutes.
It is extremely rare that the style of access links change due to updates released by Google. This really happens very rarely, but if you are faced with the fact that the table stopped loading at one moment, check the link again. If you stop accessing the sheet via the link, the data will not be loaded into Excel either.
Method 3: Importing a CSV file
I suggest paying attention to this option if there are any problems with the implementation of the previous one. Importing a CSV file has its drawbacks, since the entire document in Google Sheets will have to be made public. If this does not matter to you, proceed with the instructions.
- In Google Sheets, open the “File” menu , hover over “Share” and click on the option “Publish to the web” .
- Decide whether to publish the entire document or just one sheet.
- From the next menu, select the “CSV File” option .
- Activate automatic publishing after changes are made.
- When finished, click the Start Publishing button .
- Copy the resulting link and go to Excel.
- On the “Data” tab, select the option to create the query “From file” and “From CSV” .
- Instead of the file name in a new window, paste the received link and click on “Open” .
- Loading the data will take a couple of minutes, after which a table will appear, but instead of all the inscriptions there will be “crazy”. To fix, expand the “File Source” list and select “Unicode (UTF-8)” .
- If all information is displayed correctly, confirm the download and start working with the table.
Updating data is carried out exactly as shown in the previous instruction, so you can return to its last steps to get the relevant information on the topic.
The principle of operation of the last two methods is practically the same, the difference is only in the data import method. At the same time, in the third method , no problems were noticed after updating the links, but there is also a minus associated with the publication, which has already been discussed above. Therefore, weigh the pros and cons and choose the option that suits you best of transferring sheets from Google Sheets to Excel.