5 Easy Ways to Convert Excel to Google Sheets with Formulas

Converting an Excel file to Google Sheets can streamline your workflow, particularly when collaboration is key. Here's a deep dive into 5 easy methods to achieve this, with an emphasis on preserving formulas and data integrity.

Method 1: Manual Copy and Paste

This is the most straightforward method for small datasets. It involves opening your Excel file and copying the data with formulas directly into Google Sheets.

• Open your Excel file and select all cells you wish to copy.
• Right-click and select ‘Copy’, or use the shortcut Ctrl+C (Cmd+C on Mac).
• Open Google Sheets, right-click on the cell where you want to start the paste, and select ‘Paste’ or use Ctrl+V (Cmd+V on Mac).

⚠️ Note: This method might lose some formatting or functionality from Excel, especially for complex formulas or Excel-specific features.

Method 2: Import Using Google Sheets’ Import Feature

Google Sheets offers an import feature that simplifies the process for larger files:

• Create a new Google Sheet or open an existing one where you want to import the Excel file.
• Go to File > Import.
• Click ‘Upload’, then select the Excel file from your computer or drag and drop it into the import window.
• Choose how you want to import the file (e.g., replace current sheet, create new sheet, etc.).
• Click ‘Import data’ after selecting the options.

💡 Note: This method is efficient for preserving formatting and formulas.

Method 3: Using the Drive App

Google Drive has its own app that can automatically convert files to Google formats:

• Open Google Drive.
• Click on ‘New’ > ‘File upload’ and choose your Excel file.
• After uploading, right-click on the file and choose ‘Open with’ > ‘Google Sheets’.
• Google Drive will create a Google Sheets document, preserving as much formatting and formulas as possible.

Method 4: Using Google Apps Script

For those comfortable with scripting, Google Apps Script can automate the conversion:

• Open Google Sheets.
• Go to ‘Tools’ > ‘Script editor’.
• Enter this code to convert Excel to Google Sheets:

  function excelToSheets() {
    var files = DriveApp.getFilesByType(MimeType.MICROSOFT_EXCEL);
    while (files.hasNext()) {
      var file = files.next();
      var sheet = SpreadsheetApp.create(file.getName().replace(‘.xlsx’, “)).getActiveSheet();
      sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).setValues(file.getBlob().getDataAsString().split(‘\n’).map(x => x.split(‘,’)));
      DriveApp.removeFile(file);
    }
  }
  

• Run the script, which will automatically convert all Excel files in your Drive.

🚀 Note: This method is perfect for bulk conversions, but it requires some scripting knowledge.

Method 5: Convert Online Tools

If you prefer not to handle the conversion yourself, there are online tools that can do it for you:

• Find an online converter tool like “Convertio” or “Zamzar”.
• Upload your Excel file to the service.
• Choose Google Sheets as the output format.
• Download the converted file and open it in Google Sheets.

These methods provide flexibility and ensure that your data, including formulas, remains intact. It's important to choose the method that best suits your needs based on the file size, data complexity, and your comfort with technology. Remember, each method has its quirks, so you might need to adjust or troubleshoot for some scenarios. Keep in mind that Google Sheets has some limitations compared to Excel, so not all Excel functionalities might transfer seamlessly.

Can I convert complex Excel files with macros?

+

Converting Excel files with macros to Google Sheets directly is not straightforward. Google Sheets doesn’t support Excel’s VBA macros, but some functions can be replicated with Google Apps Script.

Will the formatting be preserved?

+

Google Sheets attempts to preserve Excel formatting, but some formatting might not carry over due to differences in how Excel and Google Sheets handle certain features.

How do I keep data integrity during conversion?

+

Opt for the import feature or Google Drive’s built-in conversion to maximize data integrity. Manual methods might require more post-conversion adjustments.