Can I convert an Excel macro to Google Sheets?

Occasionally, you may need to convert Excel files to Google Sheets. This may be a single file, multiple files, or a file with formulas and macros. In most cases, it is easy enough to do it, but you might also face some challenges. 

This article explains several options that allow you to convert Excel to Google Sheets. We’ll explore  each of them in detail and understand which method is the best for certain cases. Let’s start!

Why you may need to convert an Excel file to Google Sheets

Microsoft Excel is one of the best tools for large organizations, and it has been used to manage financial data for decades. As Excel is a standalone app, it’s fast and powerful. It can store a large number of cells (17,179,869,184, specifically), and you don’t need to worry about slowing performance – as long as you have a good computer with fast RAM.

Google Sheets, on the other hand, can store only 10,000,000 cells, at the time of this writing. The restriction is because Sheets is a cloud-based spreadsheet; the more cells you use, the slower it gets.

Despite those limitations, more and more people are using Google Sheets for the following reasons:

• Google Sheets is free, while Excel has monthly/yearly subscriptions.
• Most people don’t really need those 10,000,000 cells — and in Google Sheets, when you’re working with smaller spreadsheets, you may not experience any lag.
• It’s fully accessible and editable via browsers. 

“But, Excel also has an online version, and it’s also free,” you may say. 

Indeed, you can also collaborate for free using Excel Online. However, in many cases, you’ll still need to launch Excel while editing your document. So you may want to convert your files to Google Sheets when you’re collaborating with others who don’t have Excel or when you want to switch to a completely free solution.

Can I open and edit an Excel file using Google Sheets without converting it?

Yes, of course! The following table lists some file formats supported in Excel that you can view in Google Sheets. Notice that four of them are editable.

Google continues to improve Office editing in Google Sheets, but currently, only Excel files with .xlsx, .xlsm, .xls, and .xlt extensions are editable. Google Sheets will save any changes you make to these files in the original Excel format.If you edit an Excel file older than Office 2007, it will be saved to a newer format.

To open and edit an Excel file, you first need to upload it to Google Drive. The following steps show you how to upload an edit an XLSX file:

Step 1. Open GDrive using Google Chrome on your computer. You may use other browsers that you have. 

Step 2. Click the New button in the top-left. Then, select File upload from the dropdown menu.

Step 3. Select the Excel file you want to upload from your computer and wait for the upload to complete. 

Step 4. Double-click the file to open it. Your Excel file will open in “Office Editing Mode”, which retains Excel formatting without converting the file. If you’d like, you can also edit the file, view the version history, share, and collaborate with others.

How to convert an Excel spreadsheet to Google Sheets in Google Drive

You might want to convert an Excel file to Google Sheets format, and then continue working on it in Sheets. When converting a file, Google Sheets will create a new spreadsheet and won’t change your original Excel file.

For an editable file in Google Sheets (such as an XLSX file), you can simply open it and save it in Google Sheets format by clicking File > Save as Google Sheets from the menu.

What if the file is not editable in Google Sheets, for example, such as a CSV or XLTX file? In this case, when you open it, there will be an option, “Open with Google Sheets“, at the top of the file. If you click on it, your file will be automatically converted to Google Sheets format.

Check out the reverse integration – from Google Drive to Excel.

How to convert an Excel file to Google Sheets by importing its data

Instead of converting an Excel file, you may want to import its data into a new or existing Sheets file. In this case, your original Excel file won’t be changed. Here are the steps to import data from Excel to Google Sheets:

Step 1. Create a new Google Sheets file or open an existing spreadsheet.

Step 2. Click File > Import.

Step 3. Select an Excel file from your Google Drive or upload it from your hard-drive.

Step 4. Choose an import location, then click the Import data button.

See that in the above screenshot, there are three available locations for the current import. However, if you’re importing a CSV file to Google Sheets, all six options will be available. Be careful when choosing “Replace spreadsheet” or “Replace data at selected cell“, because your existing data will then be replaced with the imported data.

How to convert multiple Excel files to Google Sheets

Suppose you have 100 Excel files, and you want to convert all of them to Google Sheets format. Doing it one at a time can be tedious. Here are two options you can choose to convert those files automatically.

Automatically convert Excel files to Google Sheets on upload

This method is suitable if your Excel files are currently on your hard drive.

Step 1. Open Drive using Google Chrome or other browsers you may have.

Step 2. Click the Settings icon > Settings.

Step 3. Tick the check box next to the Convert upload option.

Step 4. Click the DONE button.

Now, every time you select single or multiple files (including files in Excel format), those files will be converted automatically to Google Sheets format. Note that files previously uploaded before this setting was turned on will not be affected.

How to convert Excel files to Google Sheets using a script

Suppose you have XLSX files already stored in a Google Drive folder named Folder E. You want to convert all of the files and keep them in Folder G.

Here are steps on how to do this using Google App Script.

Step 1. Get the ID of Folder E. It’s easy — open the folder and copy the last part of the URL. Paste this in a text editor or somewhere with easy access.

Step 2. Get the ID of Folder G in the same way as Step 1.

Step 3. Create a new Google Sheets document.

Step 4. Open the Script editor by clicking Tools > Script editor from the menu.

Step 5. Copy and paste the following code into myFunction in Code.gs. Don’t forget to change the values of sourceFolderId and destinationFolderId to the IDs in Step 1 and 2, respectively.

function myFunction() {
  // ID of Folder E. Change it with yours.
  var sourceFolderId = "1QYu4Ymn1Phq1umCkK2EuDkrZ3dLXbQPd"; 
 
  // ID of Folder G. Change it with yours.
  var destinationFolderId = "19x8qXzKCm7Jp7SRmg-zBUELXUCPv8R9o"; 
 
  // Get Microsoft Excel spreadsheet files
  var files = DriveApp.getFolderById(sourceFolderId).getFilesByType(MimeType.MICROSOFT_EXCEL);
  
  while(files.hasNext()){
    var file = files.next();
    var name = file.getName().split('.')[0]; 
    var id = file.getId();
    var blob = file.getBlob();
 
    var newFile = {
        title : name + '_converted',
        parents: [{id: destinationFolderId}] 
      }; 
      
    var sheetFile = Drive.Files.insert(newFile, blob, { convert: true });
  }
}

Your entire code will look like this:

Step 6. Add the Drive API service by clicking the plus (+) button next to Services in the left menu, then click the Add button at the bottom of the popup.

Step 7. Click the Run button, and authorize the script to access your account if requested. After that, check the destination folder. 

Here is an example result. See that the Excel files in Folder E were converted to Google Sheets format in Folder G. 

After exporting your Excel files to Google Sheets, you might want to combine multiple sheets from one or multiple documents into one. Check out our tutorial on how to combine data from multiple sheets into one.

How to convert Excel Online files to Google Sheets

Suppose you’re using Excel Online, and your file is stored in OneDrive. In this case, you can’t convert it directly to Google Sheets format. However, you can import the data to Google Sheets using a third-party tool, such as Coupler.io. 

How to convert Excel Online to Google Sheets using Coupler.io

Coupler.io is a popular data integration solution that can export your data automatically from 25+ sources, including Excel Online. It’s a no-code tool, so it’s easy to use even if you don’t have any tech background. In addition to this, Coupler.io has a feature that comes in particularly handy – Automatic data refresh. Once it’s activated, the tool will automatically update your Excel data in Google Sheets according to the schedule you set. Let’s see how it works.

Step 1. Sign up for Coupler.io with your Google or Microsoft account.

Step 2. On the My importers tab, press Add new. Select Excel as a source and Google Sheets as a destination. Alternatively, you can transfer your Excel data to BigQuery or another Excel file, or even convert a Google Sheets file to Excel, if necessary.

Step 3. Connect your Microsoft account and grant the requested permissions. Then, select the Excel file and sheet you want to convert.

Step 4. Connect your Google Sheets account or just select it in the dropdown menu if you used it to sign up and it’s already connected. Then, specify the worksheet and sheet for importing your Excel data.

Step 5. Choose import mode. Replace will write new data over the previous contents of the sheet, so the old version of your data won’t be available anymore. Select it if you want to have just the latest copy of your data. Append will place every new set of data under the previous one. Select it if you want to track changes in chronological order.

Step 6 (optional). Now, it’s time to set the schedule for the updates. Toggle on the Automatic data refresh feature and specify your preferences. You can select the interval for the updates (from every month to every 15 minutes), the days of the weeks and the times when you want your data to be refreshed. In this example, Coupler.io will update Excel data in Google Sheets every 15 minutes on business days during office hours.

When your schedule is ready, hit Save and run.

Our report has been transferred. Here’s what the converted Excel data looks like in Google Sheets:

You can use the same method to convert a Google Sheets file to Excel. Just select Google Sheets as a source and Excel as a destination app when setting the importer.Besides this, you can use this method to export data automatically from many other apps, such as Airtable, QuickBooks, Pipedrive, Salesforce, Shopify, and many others. Check the full list of the available integrations to see which of your favorite apps are included.

Note: Please note that this method will fetch the data only. Thus, you will lose all formulas, conditional formatting, and so on.

Use IMPORTDATA function to pull Excel from OneDrive

Automation is a great thing, but it might be unnecessary if you don’t need to convert Excel Online to Google Sheets often. If this only happens from time to time and you don’t really need scheduled updates, opting for the IMPORTDATA function can be a good choice for you.
To use this function, unfortunately, your file needs to be in a comma-delimited (.csv) format. If your file is in another format, click the Open in Desktop App menu at the top to launch Excel. 

After the file opens in Excel, click File > Save a Copy and save it as a CSV in anOneDrive folder. 

Note: If your file contains more than one sheet, only the active sheet will be exported to CSV. You’ll need to save each sheet individually to different CSV files.

Next, follow the steps below to import its data to a Google sheet. 

Step 1. Get the iframe code of your CSV file as follows: 

Step 2. Grab the link from the iframe code.

https://onedrive.live.com/embed?cid=D8149252543AB8C1&resid=D8149252543AB8C1%218886&authkey=AO00J5TABupU99A

Step 3. Replace the string “embed” in the link with “download“.

https://onedrive.live.com/download?cid=D8149252543AB8C1&resid=D8149252543AB8C1%218886&authkey=AO00J5TABupU99A

Step 4. Now you can use this link as the URL argument in the IMPORTDATA formula. Create a new Google Sheets document and copy-paste the following formula into A1.

=IMPORTDATA("https://onedrive.live.com/download?cid=D8149252543AB8C1&resid=D8149252543AB8C1%218886&authkey=AO00J5TABupU99A")

Can I convert an Excel file with formulas to Google Sheets?

Yes, of course! You can convert the file using the methods explained previously: by saving it in Google Sheets format or importing it. However, Excel and Google Sheets don’t share all formulas. So, it’s best to check the converted file to ensure all the formulas work as expected. 

Moreover, other things that may not work:

• Some text and cell formats. 
• Password-protected files, so you need to remove any password protection before opening an Excel file in Sheets.
• Any SmartArt.
• Links to any other Excel spreadsheets.
• Pivot tables from Excel may not work in Sheets. 
• Power Pivot and Power Query.

So, it’s best to verify the converted file in Google Sheets to see which ones you might lose so that you can fix them.

Can I convert an Excel file with macros to Google Sheets?

If the original spreadsheet has macros, you’ll have to recreate them in Google Sheets. For the scripting language, Excel is based on VBA (Visual Basic for Applications), while Sheets is based on JavaScript. So, the macros you create in one app won’t work on another. 

Here’s an example of an Excel file with macros. If the macro button is clicked, a message box appears:

Now, let’s compare with the following screenshot that shows how it looks after the file is converted to Google Sheets format:

From the above screenshot, see that the button from the macro is not converted. So, if you have macros in your Excel file, it’s best to note them before converting the file. This way, you have a list of macros to recreate in Google Sheets.

Bonus: How can I convert Google Sheets to Excel?

The easiest way to convert Google Sheets to Excel is by downloading the Google Sheets data as a Microsoft Excel workbook. 

For the first option, right-click on the Google Sheets file, then select Download from the menu, as the following screenshot shows:

As another option, if the document is already opened in Google Sheets, just click File > Download > Microsoft Excel (.xlsx) from the menu.

Observe that you can actually download to other formats such as ODS, PDF, HTML, and others.

Or, alternatively, you can use Coupler.io and convert Google Sheets to Excel automatically and even on a schedule, if needed. We’ve already explained how to do this above. However, in this case only your data will be transferred, but not your formulas.

Best way to convert Excel into Google Sheets

We have explored in detail each method for converting Excel to Sheets (except for coding solutions, for example Python to export Excel to Google Sheets). Now, you may be wondering which method is the best. Well, unfortunately, there is no single best solution for all cases. The best answer depends on several things, such as:

• Whether you want to convert single or multiple files.
• Whether the format of the original file is editable in Google Sheets or not.
• Whether the Excel files are on your hard drive, or in cloud storage such as OneDrive.

So hopefully, after reading this article, you’ll be able to choose the most convenient method based on your needs. Good luck!

Back to Blog